Using GoldenGate LogDump To Find Bad Data

GoldenGate Primary Extract from source database captured data without any issues;

however, target was not able to consume the data since GoldenGate process would ABEND.

Unfortunately, I cannot provide all the details but high level.

Logdump 2433 >pos 0
+++ Starting with GoldenGate 12.2 TDR – Table Definition Record is in trail
+++ This will provide metadata for the table

Reading forward from RBA 0 
Logdump 2434 >SCANFORMETADATA
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :    86  (x0056)   IO Time    : 2018/03/25 18:24:23.307.797   
IOType     :   170  (xaa)     OrigNode   :     1  (x01) 
TransInd   :     .  (x01)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
DDR/TDR Idx:   (001, 000)     AuditPos   : 3277290592 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/03/25 18:24:23.307.797 Metadata             Len 86 RBA 1689 
Name:  
*
DDR Version: 1
Database type: ORACLE
Character set ID: CESU-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
TimeZone: GMT-05:00
Global name: DBNAME
* 
Logdump 2435 >n
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :   561  (x0231)   IO Time    : 2018/03/25 18:28:16.317.879   
IOType     :   170  (xaa)     OrigNode   :     2  (x02) 
TransInd   :     .  (x01)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
DDR/TDR Idx:   (001, 001)     AuditPos   : 3352410292 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/03/25 18:28:16.317.879 Metadata             Len 561 RBA 1826 
Name: SCHEMA.TABLE 
*
 1)Name          2)Data Type        3)External Length  4)Fetch Offset      5)Scale         6)Level
 7)Null          8)Bump if Odd      9)Internal Length 10)Binary Length    11)Table Length 12)Most Sig DT
13)Least Sig DT 14)High Precision  15)Low Precision   16)Elementary Item  17)Occurs       18)Key Column
19)Sub DataType 20)Native DataType 21)Character Set   22)Character Length 23)LOB Type     24)Partial Type
*
TDR version: 1
Definition for table SCHEMA.TABLE
Record Length: 4298
Columns: 7
ID           64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1   2    2       -1      0 0 0
A            B      C         D  E  F G H     I      J      K  L M N O P    Q R

Position 18)Key Column identify column is Primary Key; hence, ID a primary key column.
I labeled each column using the alphabet for reference and R is the 18th letter of the alphabet.

Even though ID is the first column of the table, GoldenGate offset starts with 0

SQL> desc SCHEMA.TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
0 ID                                        NOT NULL NUMBER
5 TEXT                                               VARCHAR2(4000)

SQL> r
  1  select b.column_name,a.constraint_type
  2  from dba_constraints a, dba_cons_columns b
  3  where a.table_name = b.table_name
  4  and a.constraint_name=b.constraint_name
  5  and a.constraint_type = 'P'
  6  and a.table_name='TABLE'
  7  and a.owner='SCHEMA'
  8*

COLUMN_NAME                    C
------------------------------ -
ID                             P

Logdump 2500 >n
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)  
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
RecLength  :   885  (x0375)   IO Time    : 2018/05/10 14:16:43.000.514   
IOType     :    15  (x0f)     OrigNode   :   255  (xff) 
TransInd   :     .  (x02)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
AuditRBA   :      36261       AuditPos   : 2145515112 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/05/10 14:16:43.000.514 FieldComp            Len   885 RBA 88598006 
Name: SCHEMA.TABLE  (TDR Index: 1) 
After  Image:                                        ....................
 0000 000c 0000 0008 3239 3533 3432 3936 0001 0009 | ........29534296....  
 0000 0005 3937 3932 3600 0200 0500 0000 0131 0003 | ....................
 0005 0000 0001 3600 0400 0b00 0000 0731 3738 3337 | ....................
 3939 0005 0325 0000 0321 456e 6a6f 7920 796f 7572 | ....................  
 2073 7461 7920 696e 206f 7572 2068 6f74 656c 7320 | ....................
 7769 7468 2074 6865 206d 6f73 7420 6469 7363 6f75 | ....................  
 6e74 6564 2072 6174                               | ....................
Column     0 (x0000), Len    12 (x000c)  
 0000 0008 3239 3533 3432 3936                     | ....29534296 --- PRIMARY KEY VALUE
Column     1 (x0001), Len     9 (x0009)  
 0000 0005 3937 3932 36                            | ....97926  
Column     2 (x0002), Len     5 (x0005)  
 0000 0001 31                                      | ....1  
Column     3 (x0003), Len     5 (x0005)  
 0000 0001 36                                      | ....6  

SQL> select id, substr(TEXT,1,30) txt, vsize(TEXT), length(TEXT) from SCHEMA.TABLE where ID in (29534296);

        ID LTXT                           VSIZE(LTEXT) LENGTH(LTEXT)
---------- ------------------------------ ------------ -------------
  29534296 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx          801           800

SQL>

For database, NLS_LENGTH_SEMANTICS=BYTE; hence, 800 characters should equal 800 bytes. 
However, one character is most likely a multi-byte character which target cannot consume 
as it is not able to handle multi-byte.

Advertisements

Goldengate REPORTING P2

Previous post for Goldengate REPORTING

Goldengate reporting has to be the least implemented functionality until it is needed and often in hindsight.

Here is an example of what I would normally implement.

STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 15 MINUTES, RATE
DISCARDROLLOVER AT 00:01 ON SUNDAY

If business only cares about monthly data, then would not make sense to collect daily nor would it make sense for business to request such.

DataGuard Convention

Good convention and implementation make life and automation so much simpler and more time for golfing.

I have seen some really poor and really good implementation and here’s a good one.

Wish I can take credit for it and unfortunately I cannot.

The scripts were created by whoa.

Scripts an be run from primary or standby for any instances provided profile to source database environment exists on host.

Use ORACLE_UNQNAME for DataGuard Environment

====================================================================================================
+++ PRIMARY RACONENODE
====================================================================================================
SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
SQL> 

$ sysresv|tail -1
Oracle Instance alive for sid "test_1"

$ env|grep ORACLE

ORACLE_SID=test_1 (db_name)
ORACLE_UNQNAME=test (db_unique_name)

$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: test
Database name: test
Oracle home: /u01/app/oracle/product/11g/db_1
Oracle user: oracle
Spfile: +FLASH/test/spfiletest.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: test
Database instances:
Disk Groups: FLASH,DATA
Mount point paths:
Services: testsvc
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: test
Candidate servers: host01,host02
Database is administrator managed

====================================================================================================
+++ STANDBY NON-RAC
====================================================================================================
SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      testdr
SQL> 

$ sysresv|tail -1
Oracle Instance alive for sid "test"

$ env|grep ORACLE
ORACLE_SID=test (db_name)
ORACLE_UNQNAME=testdr (db_unique_name)

$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: testdr
Database name: test
Oracle home: /u01/app/oracle/product/11g/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: test
Disk Groups: DATA,FLASH
Services:

====================================================================================================
DATAGUARD BROKER CONFIGURATION
====================================================================================================
DGMGRL> show configuration

Configuration - dg_test (db_name)

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database (db_unique_name)
    testdr - Physical standby database (db_unique_name)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database test

Database - test

  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    test_1
    test_2

Database Status:
SUCCESS

DGMGRL> show database testdr

Database - testdr

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       7 seconds (computed 0 seconds ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    test

Database Status:
SUCCESS

DGMGRL> exit

====================================================================================================
ls -l dg*.sh
====================================================================================================
-rwxr-xr-x    1 oracle   dba             377 May 08 21:50 dg_lag.sh
-rwxr-x---    1 oracle   dba             445 May 08 20:12 dg_start.sh
-rwxr-xr-x    1 oracle   dba             337 May 08 20:05 dg_status.sh
-rwxr-x---    1 oracle   dba             447 May 08 20:12 dg_stop.sh

====================================================================================================
dg_lag.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
show database ${ORACLE_SID} SendQEntries
show database ${ORACLE_UNQNAME} RecvQEntries
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
cat dg_start.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
edit database ${ORACLE_SID} set state='TRANSPORT-ON';
edit database ${ORACLE_UNQNAME} set state='APPLY-ON';
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
dg_status.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
dg_stop.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
edit database ${ORACLE_SID} set state='TRANSPORT-OFF';
edit database ${ORACLE_UNQNAME} set state='APPLY-OFF';
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
check_dg
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

DBFS Nightmare

====================================================================================================
How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1)	
====================================================================================================
High level overview of the DBMS_DBFS_SFS.REORGANIZEFS procedure:
----------------------------------------------------------------------------------------------------
1) Create a NEW tablespace
2) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the new tablespace.
3) Run dbms_dbfs_sfs.reorganizeFS -->>
   EXEC DBMS_DBFS_SFS.REORGANIZEFS(SRCSTORE=>'FS_FS1', DSTSTORE=>'FS_TMP_FS');
4) The dbfs data is now in the smaller NEW tablespace.
5) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem
6) The OLD original tablespace is empty now.
----------------------------------------------------------------------------------------------------
To reuse the same tablespace again please follow the below steps
----------------------------------------------------------------------------------------------------
7) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the OLD ORIGINAL tablespace.
8) Run dbms_dbfs_sfs.reorganizeFS
9) The dbfs data is now in the smaller the ORIGINAL tablespace.
10) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem
11) The NEW small tablespace is empty now and can be dropped.

====================================================================================================
DBFS tablespace keep growing not using expired blocks (Doc ID 2327299.1)	
====================================================================================================
----------------------------------------------------------------------------------------------------
First perform the DBFS re-org to cleanup the tablespace after removing the files at file system level by following below document
----------------------------------------------------------------------------------------------------
How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1)

----------------------------------------------------------------------------------------------------
After the re-org, set the  below parameter to reuse the expired blocks
----------------------------------------------------------------------------------------------------
ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0;

====================================================================================================
ORA-1654 - DBFS FREE SPACE NOT RECLAIMED AFTER CLEARING FILES (Doc ID 1948305.1)	
====================================================================================================
----------------------------------------------------------------------------------------------------
a) Check Lob retention setting:
----------------------------------------------------------------------------------------------------
  ex. SQL> select 
  table_name,retention_type,retention_value,retention,securefile from dba_lobs 
  where table_name = 'T_FS1';

----------------------------------------------------------------------------------------------------  
b) Change LOB retention to none.
----------------------------------------------------------------------------------------------------
  ex. SQL> alter table DBFS_USER.T_FS1 modify lob (FILEDATA) (retention none);

After countless discussions, team found GOLD.

12.1 Shrinking and Reorganizing DBFS Filesystems

STEPS:

sqlplus /as sysdba
create bigfile tablespace DBFS_NEW datafile size 8G autoextend on next 1G maxsize 70G;
grant dba to dbfs_user;
alter user dbfs_user default role all;
exit

+++ REORGANIZE DBFS FILESYSTEM FS1 IN TABLESPACE DBFS_TS INTO A NEW TABLESPACE DBFS_NEW, 
+++ USING A TEMPORARY FILESYSTEM NAMED TMP_FS, WHERE ALL FILESYSTEMS BELONG TO DATABASE USER DBFS_USER
cd $ORACLE_HOME/rdbms/admin
sqlplus dbfs_user
@dbfs_create_filesystem DBFS_NEW TMP_FS
exec dbms_dbfs_sfs.reorganizefs('FS1','TMP_FS');
@dbfs_drop_filesystem TMP_FS
purge user_recyclebin;
exit

sqlplus / aa sysdba 
revoke dba from dbfs_user;
select count(*) from dba_extents where tablespace_name='DBFS_TS'; 
select count(*) from dba_extents where tablespace_name='DBFS_NEW'; 
-- DROP TABLESPACE HAVING ZERO EXTENTS
-- BE CAREFUL IN CASE USER AND DBFS ARE USING SAME TABLESPACE
Example:
create user dbfs_user identified by **** default tablespace dbfs_ts quota unlimited on dbfs_ts;
@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts FS1

TRUE CONFESSION:

I created an imperfect plan at first that might have saved me from a major catastrophe.

Create DBFS_TMP in a separate Disk Group since I was concern existing may not have enough storage.
Reorg from DBFS_TS to DBFS_TMP (bad idea to name tablespace as DBFS_TMP as LOB Segments migrated here).
Reorg from DBFS_TMP back to DBFS_TS.
Drop tablespace DBFS_TMP.

There’s 29G difference between old and new. How much if this space will be reclaimable due to HWM in data file?

Performing reorg twice is really not an option in prod.

RESULTS:

SEGMENT_NAME                   TABLESPACE_NAME                        MB    EXTENTS
------------------------------ ------------------------------ ---------- ----------
LOB_SFS$_FST_1                 DBFS_TS                        30647.1875       1851
LOB_SFS$_FST_6225924           DBFS_NEW                         1025.125        130

WARNINGS:

This has only been tested in a vacuum (non-prod env w very low activities) – YMMV.

Multiplex Redo Log

When db_create_online_log_dest_1 is defined, REDO log is not multiplexed which is good for creating STANDBY REDO.

REDO log is created at db_create_online_log_dest_1 ONLY.

However, when creating ONLINE REDO, db_create_online_log_dest_1 should NOT be defined to be multiplexed.

REDO logs are created at db_create_file_dest and db_recovery_file_dest.

[oracle@db-asm-1 sql]$ sqlplus / as sysdba @ logfile.sql

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 3 05:56:30 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

05:56:30 SYS @ owl:>show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1                      +FRA
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
05:56:30 SYS @ owl:>show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 7116M

05:56:30 SYS @ owl:>select group#,member,type,status from v$logfile order by 1,2 asc;

    GROUP# MEMBER                                                                           TYPE    STATUS
---------- -------------------------------------------------------------------------------- ------- -------
         1 +DATA/OWL/ONLINELOG/group_1.261.962643743                                        ONLINE
         1 +FRA/OWL/ONLINELOG/group_1.257.962643743
         2 +DATA/OWL/ONLINELOG/group_2.262.962643743
         2 +FRA/OWL/ONLINELOG/group_2.258.962643743
         3 +DATA/OWL/ONLINELOG/group_3.263.962643745
         3 +FRA/OWL/ONLINELOG/group_3.259.962643745

6 rows selected.

05:56:30 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$log order by 1,2;

    GROUP#    THREAD#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ---------- ----------------
         1          1         79  104857600 CURRENT
         2          1         77  104857600 INACTIVE
         3          1         78  104857600 INACTIVE

05:56:30 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$standby_log order by 1,2;

no rows selected

05:56:30 SYS @ owl:>alter database add standby logfile thread 1 group 11 size 104857600;

Database altered.

05:57:03 SYS @ owl:>alter system set db_create_online_log_dest_1='';

System altered.

05:57:39 SYS @ owl:>alter database add logfile thread 1 group 4 size 104857600;

Database altered.

05:58:01 SYS @ owl:>@logfile.sql
05:58:06 SYS @ owl:>set lines 200 tab off trimsp on pages 1000
05:58:06 SYS @ owl:>col member for a80
05:58:06 SYS @ owl:>break on TYPE
05:58:06 SYS @ owl:>set echo on
05:58:06 SYS @ owl:>show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1
db_create_online_log_dest_2
db_create_online_log_dest_3
db_create_online_log_dest_4
db_create_online_log_dest_5
05:58:06 SYS @ owl:>show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 7116M
05:58:06 SYS @ owl:>select group#,member,type,status from v$logfile order by 1,2 asc;

    GROUP# MEMBER                                                                           TYPE    STATUS
---------- -------------------------------------------------------------------------------- ------- -------
         1 +DATA/OWL/ONLINELOG/group_1.261.962643743                                        ONLINE
         1 +FRA/OWL/ONLINELOG/group_1.257.962643743
         2 +DATA/OWL/ONLINELOG/group_2.262.962643743
         2 +FRA/OWL/ONLINELOG/group_2.258.962643743
         3 +DATA/OWL/ONLINELOG/group_3.263.962643745
         3 +FRA/OWL/ONLINELOG/group_3.259.962643745
         4 +DATA/OWL/ONLINELOG/group_4.267.975131881
         4 +FRA/OWL/ONLINELOG/group_4.295.975131881
        11 +FRA/OWL/ONLINELOG/group_11.296.975131823                                        STANDBY

9 rows selected.

05:58:07 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$log order by 1,2;

    GROUP#    THREAD#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ---------- ----------------
         1          1         79  104857600 CURRENT
         2          1         77  104857600 INACTIVE
         3          1         78  104857600 INACTIVE
         4          1          0  104857600 UNUSED

05:58:07 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$standby_log order by 1,2;

    GROUP#    THREAD#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ---------- ----------
        11          1          0  104857600 UNASSIGNED

05:58:07 SYS @ owl:>

Who’s Gathering DB Stats?

There was an incident where statistics were being gathered during prime operating hours causing performance issues.
One DBA already verified GATHER_STATS_JOB has already been configured to not run during critical hours.
Speculation is stats are being gathered manually and how to prove this?

AUTO JOB has OPERATION : gather_database_stats (auto).
MANUAL JOB is not being run by scheduler either; otherwise, there would be JOB_NAME.

Half of the mystery is solve, but where is gather_table_stats running from?

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> @pr "SELECT ID,OPERATION,START_TIME,END_TIME,STATUS,JOB_NAME,SESSION_ID FROM dba_optstat_operations where START_TIME>trunc(sysdate-1) ORDER BY start_time DESC"
ID                            : 6939
OPERATION                     : gather_table_stats
START_TIME                    : 26-APR-18 05.00.12.387231 PM -07:00
END_TIME                      : 26-APR-18 05.00.21.509607 PM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 2871
-------------------------
ID                            : 6918
OPERATION                     : export_stats_for_dp
START_TIME                    : 26-APR-18 03.47.09.574643 PM -07:00
END_TIME                      : 26-APR-18 03.47.25.336241 PM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 201
-------------------------
ID                            : 6917
OPERATION                     : gather_table_stats
START_TIME                    : 26-APR-18 03.10.16.126374 PM -07:00
END_TIME                      : 26-APR-18 04.59.51.410241 PM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 2871
-------------------------
ID                            : 6916
OPERATION                     : gather_table_stats
START_TIME                    : 26-APR-18 02.09.44.123132 PM -07:00
END_TIME                      : 26-APR-18 02.09.45.695904 PM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 2871
-------------------------
ID                            : 6915
OPERATION                     : gather_table_stats
START_TIME                    : 26-APR-18 12.57.11.352671 PM -07:00
END_TIME                      : 26-APR-18 02.09.43.579331 PM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 2871
-------------------------
ID                            : 6922
OPERATION                     : restore_table_stats
START_TIME                    : 26-APR-18 02.00.00.949528 AM -07:00
END_TIME                      : 26-APR-18 02.00.01.297300 AM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 877
-------------------------
ID                            : 6914
OPERATION                     : gather_table_stats
START_TIME                    : 25-APR-18 11.57.35.764007 PM -07:00
END_TIME                      : 26-APR-18 12.05.11.086928 AM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 870
-------------------------
ID                            : 6921
OPERATION                     : gather_database_stats (auto)
START_TIME                    : 25-APR-18 10.00.06.197933 PM -07:00
END_TIME                      : 26-APR-18 02.00.01.621582 AM -07:00
STATUS                        : TIMED OUT
JOB_NAME                      : ORA$AT_OS_OPT_SY_16648
SESSION_ID                    : 877
-------------------------
ID                            : 6913
OPERATION                     : gather_table_stats
START_TIME                    : 25-APR-18 02.26.33.270421 PM -07:00
END_TIME                      : 25-APR-18 02.26.42.759697 PM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 1921
-------------------------
ID                            : 6912
OPERATION                     : gather_table_stats
START_TIME                    : 25-APR-18 12.20.35.728909 PM -07:00
END_TIME                      : 25-APR-18 02.26.18.996076 PM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 864
-------------------------
ID                            : 6911
OPERATION                     : gather_table_stats
START_TIME                    : 25-APR-18 10.13.45.127514 AM -07:00
END_TIME                      : 25-APR-18 10.13.46.570807 AM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 864
-------------------------
ID                            : 6910
OPERATION                     : gather_table_stats
START_TIME                    : 25-APR-18 08.57.44.914619 AM -07:00
END_TIME                      : 25-APR-18 10.13.44.623245 AM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 864
-------------------------
ID                            : 6920
OPERATION                     : restore_table_stats
START_TIME                    : 25-APR-18 02.00.00.220393 AM -07:00
END_TIME                      : 25-APR-18 02.00.00.490261 AM -07:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 208
-------------------------

PL/SQL procedure successfully completed.

SQL> 

Skip Goldengate Replicat Transaction

Overview:

It’s easy to skip replicat transaction; however, there is a requirement to understand the result.

In the example provided, DELETE from PARENT table was not successful because there are ROWS from CHILD table.

Skip replicat transactions resulted in difference of 15273 rows for PARENT table between source and target.

In this case, there were 3 rows from 1 CHILD table causing the issue.

I know what you are thinking – Why was DELETE rows from CHILD table not replicate to begin with?

May be someone did the same – skip transaction and not synchronize the data?

That’s a story for another time. The problem is solved but not the cause.

====================================================================================================
ORA-02292: integrity constraint (OWNER.MARY_JOE_FK) violated - child record found (status = 2292). DELETE FROM "OWNER"."T_JOE"  WHERE "JOENUMMER" = :b0.
====================================================================================================

GGATE@SQL> select JOENUMMER from NEWS.T_MARY minus select JOENUMMER from NEWS.T_MARY@source;

JOENUMMER
------------
    50000402
    50000412
    50000422

GGATE@SQL> select JOENUMMER from NEWS.T_MARY@source minus select JOENUMMER from OWNER.T_MARY;

no rows selected

GGATE@SQL> 

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

GGATE@SQL> select JOENUMMER  from OWNER.T_MARY@source where JOENUMMER in (50000402,50000412,50000422);

no rows selected

GGATE@SQL> select JOENUMMER  from OWNER.T_MARY where JOENUMMER in (50000402,50000412,50000422);

JOENUMMER 
------------
    50000402
    50000412
    50000422

GGATE@SQL> delete from OWNER.T_MARY where JOENUMMER in (50000402,50000412,50000422);

3 rows deleted.

GGATE@SQL> commit;

Commit complete.

GGATE@SQL> 

====================================================================================================
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.15 17640173 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131101.0605.2_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Nov 19 2013 03:18:45

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

====================================================================================================
ORA-02292: integrity constraint (OWNER.MARY_JOE_FK) violated - child record found (status = 2292). DELETE FROM "OWNER"."T_JOE"  WHERE "JOENUMMER" = :b0.
====================================================================================================

+++ SKIPTRANSACTION
GGSCI> start replicat REP1 SKIPTRANSACTION

+++ REVIEW PRM
[gguser]$ grep -i discard rep1.prm
--REPERROR (DEFAULT, DISCARD)
REPERROR (-1, DISCARD)
REPERROR (2291, DISCARD) 
DISCARDFILE ./discard/rep1.discard append, MEGABYTES 1024
DISCARDROLLOVER AT 00:01
[gguser]$ 

+++ REVIEW SKIPPING FROM DISCARD
[gguser]$ grep -c "Skipping delete from OWNER.T_JOE" rep1.discard
15276

[gguser]$ grep -A2 "Skipping delete from OWNER.T_JOE" ./discard/rep1.discard|head
Skipping delete from OWNER.T_JOE at seqno 4475 rba 87850906
*
JOENUMMER = 1
--
Skipping delete from OWNER.T_JOE at seqno 4475 rba 87851339
*
JOENUMMER = 2
--
Skipping delete from OWNER.T_JOE at seqno 4475 rba 87851735
*
[gguser@viz-cp-dc1-p11 oracle]$ grep -A2 "Skipping delete from OWNER.T_JOE" ./discard/rep1.discard|tail
*
JOENUMMER = 50093291
--
Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033367
*
JOENUMMER = 50094681
--
Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033767
*
JOENUMMER = 50094741

+++ REVIEW RBA FROM DISCARD
[gguser]$ grep rba rep1.discard|head -1
Aborting transaction on ./dirdat/nd beginning at seqno 4475 rba 87850906

[gguser]$ grep rba rep1.discard|tail -1
Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033767
[gguser]$ 

+++ NOTICE MATCH WITH LOGDUMP
Logdump 23 >scanforendtrans
End of Transaction found at RBA 94033767 

====================================================================================================
GATHER DATA
====================================================================================================

GGATE@SQL> r
1 select count(*) from
2 (
3 (select JOENUMMER from OWNER.T_JOE minus select JOENUMMER from OWNER.T_JOE@source)
4 union all
5 (select JOENUMMER from OWNER.T_JOE@source minus select JOENUMMER from OWNER.T_JOE)
6 )
7*

COUNT(*)
———-
15273

GGATE@SQL>

+++ CREATE TEMPORARY TABLE

GGATE@SQL> create table T_JOE_DEL as select JOENUMMER from OWNER.T_JOE minus select JOENUMMER from OWNER.T_JOE@source;

+++ REVIEW DATA FROM TEMPORARY TABLE TO COMPARE WITH DISCARD

GGATE@SQL> r
1 select * from (
2 select JOENUMMER from T_JOE_DEL order by 1 asc
3* ) where rownum <11

JOE
————
1
2
3
21
23
24
25
26
27
28

10 rows selected.

GGATE@SQL>

GGATE@SQL> r
1 select * from (
2 select JOE from T_JOE_DEL order by 1 desc
3* ) where rownum <11

JOE
————
50094741
50094681
50093291
50093221
50093191
50093101
50092851
50092791
50092781
50092741

10 rows selected.

GGATE@SQL>

====================================================================================================
CORRECT DATA
====================================================================================================

GGATE@SQL> delete from OWNER.T_JOE where JOENUMMER in (select JOENUMMER from T_JOE_DEL);

15273 rows deleted.

GGATE@SQL> commit;

Commit complete.

====================================================================================================
VERIFY ROW COUNT
====================================================================================================

+++ USING COUNT MAY NOT BE THE BEST OPTION.
GGATE@SQL> select count(*) from OWNER.T_JOE;

  COUNT(*)
----------
      9939

GGATE@SQL> select count(*) from OWNER.T_JOE@source;

  COUNT(*)
----------
      9939

GGATE@SQL> 

====================================================================================================
REVIEW REPORT FILE
====================================================================================================

[gguser]$ grep SKIPTRANSACTION REP1*.rpt
rep1.rpt:2018-04-17 12:15:15  INFO    OGG-01370  User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 22.30.1923599, position Seqno 4475, RBA 87850906.

[gguser]$ grep -i skip ggserr.log
2018-04-17 12:15:14  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): start replicat rep1 SKIPTRANSACTION.
2018-04-17 12:15:14  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.232.135.44:33310 (START REPLICAT rep1 SKIPTRANSACTION).
2018-04-17 12:15:15  INFO    OGG-01370  Oracle GoldenGate Delivery for Oracle, rep1.prm:  User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 22.30.1923599, position Seqno 4475, RBA 87850906.
[gguser]$ 

====================================================================================================
LOGDUMP TO FIND END OF TRANSACTONS
====================================================================================================

Logdump 15 >open ./dirdat/nd004475
Current LogTrail is ./dirdat/nd004475 
Logdump 16 >detail on
Logdump 17 >fileheader detail
Logdump 18 >ghdr on
Logdump 19 >detail data
Logdump 20 >ggstoken detail
Logdump 21 >pos 87850906
Reading forward from RBA 87850906 
Logdump 22 >n
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)  
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)  
RecLength  :   310  (x0136)   IO Time    : 2018/04/17 10:47:16.475.512   
IOType     :     3  (x03)     OrigNode   :   255  (xff) 
TransInd   :     .  (x00)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
AuditRBA   :     167409       AuditPos   : 779280 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/04/17 10:47:16.475.512 Delete               Len   310 RBA 87850906 
Name: OWNER.T_JOE 
Before Image:                                             Partition 4   G  b   

GGS tokens: 
TokenID x52 'R' ORAROWID         Info x00  Length   20 
 4141 4148 6b55 4141 5441 4141 6264 7141 4159 0001 | AAAHkUAATAAAbdqAAY..  
TokenID x4c 'L' LOGCSN           Info x00  Length   10 
 3732 3833 3730 3834 3135                          | 7283708415  
TokenID x36 '6' TRANID           Info x00  Length   13 
 3232 2e33 302e 3139 3233 3539 39                  | 22.30.1923599  
   
Logdump 23 >scanforendtrans
End of Transaction found at RBA 94033767 
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)  
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)  
RecLength  :   331  (x014b)   IO Time    : 2018/04/17 10:47:16.429.234   
IOType     :     3  (x03)     OrigNode   :   255  (xff) 
TransInd   :     .  (x02)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
AuditRBA   :     167409       AuditPos   : 13903264 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/04/17 10:47:16.429.234 Delete               Len   331 RBA 94033767 
Name: OWNER.T_JOE 
Before Image:                                             Partition 4   G  e   
GGS tokens: 
TokenID x52 'R' ORAROWID         Info x00  Length   20 
 4141 4148 6b55 4141 5741 4141 4e6c 6c41 4177 0001 | AAAHkUAAWAAANllAAw..  
   
Logdump 24 >n
___________________________________________________________________ 
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)  
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42)  
RecLength  :   174  (x00ae)   IO Time    : 2018/04/17 10:47:24.429.491   
IOType     :    15  (x0f)     OrigNode   :   255  (xff) 
TransInd   :     .  (x00)     FormatType :     R  (x52) 
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
AuditRBA   :     167409       AuditPos   : 13947088 
Continued  :     N  (x00)     RecCount   :     1  (x01) 

2018/04/17 10:47:24.429.491 FieldComp            Len   174 RBA 94034190 
Name: OWNER.NEW_DATA 
Before Image:                                             Partition 4   G  b   
GGS tokens: 
TokenID x52 'R' ORAROWID         Info x00  Length   20 
 4141 4148 6a59 4141 5441 4142 794b 4541 412f 0001 | AAAHjYAATAAByKEAA/..  
TokenID x4c 'L' LOGCSN           Info x00  Length   10 
 3732 3833 3730 3834 3538                          | 7283708458  
TokenID x36 '6' TRANID           Info x00  Length   13 
 3132 2e31 362e 3330 3031 3139 37                  | 12.16.3001197  
   
Logdump 25 >open ./dirdat/nd004475
Current LogTrail is ./dirdat/nd004475 
Logdump 26 >count
LogTrail ./dirdat/nd004475 has 92822 records 
Total Data Bytes          92730182 
  Avg Bytes/Record             999 
Delete                       20937 
Insert                        5405 
FieldComp                      724 
LargeObject                  65755 
Others                           1 
Before Images                21163 
After Images                 71658 

Average of 1589 Transactions 
    Bytes/Trans .....      61161 
    Records/Trans ...         58 
    Files/Trans .....          5 
 
Logdump 27 >detail on
Logdump 28 >filter inc filename OWNER.T_JOE
Logdump 29 >count
Scanned     10000 records, RBA   12734577, 2018/04/17 07:25:42.524.558 
Scanned     20000 records, RBA   25670230, 2018/04/17 08:00:11.480.213 
Scanned     30000 records, RBA   38698934, 2018/04/17 08:30:24.488.669 
Scanned     40000 records, RBA   51436567, 2018/04/17 08:59:11.452.549 
Scanned     50000 records, RBA   63868041, 2018/04/17 09:43:10.477.605 
Scanned     60000 records, RBA   76010927, 2018/04/17 10:14:59.472.122 
Scanned     70000 records, RBA   94264594, 2018/04/17 10:47:31.447.436 
LogTrail ./dirdat/nd004475 has 15296 records 
Total Data Bytes           4757365 
  Avg Bytes/Record             311 
Delete                       15296 
Before Images                15296 
Filtering matched        15296 records 
          suppressed     77526 records 

Average of 2 Transactions 
    Bytes/Trans .....    2745786 
    Records/Trans ...       7648 
    Files/Trans .....        110 
 

OWNER.T_JOE                                      Partition 4 
Total Data Bytes           4757365 
  Avg Bytes/Record             311 
Delete                       15296 
Before Images                15296 
Logdump 30 >

 

Framework To Run SQL For All Active DB Instances

Requirement is to configure hugepages for multiple RAC database instances.

pmon processes

grid     12692     1  0 09:39 ?        00:00:00 asm_pmon_+ASM1
grid     13296     1  0 09:39 ?        00:00:00 mdb_pmon_-MGMTDB
oracle   13849     1  0 09:40 ?        00:00:00 ora_pmon_DEV1
oracle   13851     1  0 09:40 ?        00:00:00 ora_pmon_QA1
oracle   13854     1  0 09:40 ?        00:00:00 ora_pmon_PERF1
oracle   13855     1  0 09:40 ?        00:00:00 ora_pmon_TEST1
oracle   14998     1  0 09:40 ?        00:00:00 ora_pmon_INT1

Create parameter.sh which will run parameter.sql.
You might be thinking, WTH is this person thinking!
I wanted SQL script to be reusable.

Update April 16, 2018:

Afterthought, might be better to only create one shell script, e.g. runsql.sh vs multiples

Run parameter.sql

oracle@racnode-dc1-1:hawk1:/home/oracle
$ sqlplus / as sysdba @ parameter.sql

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:25:56 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options


NAME                           CDB
------------------------------ ---
HAWK                           NO


NAME                           DISPLAY_VALUE INST_ID     CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
cluster_database               TRUE                1          0 FALSE         FALSE
                               TRUE                2          0 FALSE         FALSE
cluster_database_instances     2                   1          0 4294967295    TRUE
                               2                   2          0 4294967295    TRUE
db_file_name_convert                               1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
db_name                        hawk                1          0 NULL          FALSE
                               hawk                2          0 NULL          FALSE
db_unique_name                 hawk                1          0 NONE          TRUE
                               hawk                2          0 NONE          TRUE
instance_groups                                    1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
instance_name                  hawk1               1          0 NULL          TRUE
                               hawk2               2          0 NULL          TRUE
instance_number                1                   1          0 0             FALSE
                               2                   2          0 0             FALSE
instance_type                  RDBMS               1          0 NONE          TRUE
                               RDBMS               2          0 NONE          TRUE
memory_max_target              0                   1          0 0             TRUE
                               0                   2          0 0             TRUE
memory_target                  0                   1          0 0             TRUE
                               0                   2          0 0             TRUE
pdb_file_name_convert                              1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
pga_aggregate_limit            2G                  1          0 1             TRUE
                               2G                  2          0 1             TRUE
pga_aggregate_target           256M                1          0 0             FALSE
                               256M                2          0 0             FALSE
sga_max_size                   768M                1          0 1000          TRUE
                               768M                2          0 1000          TRUE
sga_target                     768M                1          0 0             FALSE
                               768M                2          0 0             FALSE
use_large_pages                TRUE                1          0 NULL          FALSE
                               TRUE                2          0 NULL          FALSE

34 rows selected.

13:25:56 SYS @ hawk1:>

Run parameter.sh

oracle@racnode-dc1-1:hawk1:/u01/app/oracle/12.1.0.2/db1
$ ~/parameter.sh

******** Current ora_pmon:
----------------------------------------
ora_pmon_hawk1
----------------------------------------

******** SQL Script: /home/oracle/parameter.sql

The Oracle base remains unchanged with value /u01/app/oracle
Oracle Instance alive for sid "hawk1"

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:26:55 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

13:26:55 SYS @ hawk1:>13:26:55 SYS @ hawk1:>13:26:55 SYS @ hawk1:>
NAME                           CDB
------------------------------ ---
HAWK                           NO


NAME                           DISPLAY_VALUE INST_ID     CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
cluster_database               TRUE                1          0 FALSE         FALSE
                               TRUE                2          0 FALSE         FALSE
cluster_database_instances     2                   1          0 4294967295    TRUE
                               2                   2          0 4294967295    TRUE
db_file_name_convert                               1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
db_name                        hawk                1          0 NULL          FALSE
                               hawk                2          0 NULL          FALSE
db_unique_name                 hawk                1          0 NONE          TRUE
                               hawk                2          0 NONE          TRUE
instance_groups                                    1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
instance_name                  hawk1               1          0 NULL          TRUE
                               hawk2               2          0 NULL          TRUE
instance_number                1                   1          0 0             FALSE
                               2                   2          0 0             FALSE
instance_type                  RDBMS               1          0 NONE          TRUE
                               RDBMS               2          0 NONE          TRUE
memory_max_target              0                   1          0 0             TRUE
                               0                   2          0 0             TRUE
memory_target                  0                   1          0 0             TRUE
                               0                   2          0 0             TRUE
pdb_file_name_convert                              1          0 NULL          TRUE
                                                   2          0 NULL          TRUE
pga_aggregate_limit            2G                  1          0 1             TRUE
                               2G                  2          0 1             TRUE
pga_aggregate_target           256M                1          0 0             FALSE
                               256M                2          0 0             FALSE
sga_max_size                   768M                1          0 1000          TRUE
                               768M                2          0 1000          TRUE
sga_target                     768M                1          0 0             FALSE
                               768M                2          0 0             FALSE
use_large_pages                TRUE                1          0 NULL          FALSE
                               TRUE                2          0 NULL          FALSE

34 rows selected.

13:26:55 SYS @ hawk1:>Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@racnode-dc1-1:hawk1:/u01/app/oracle/12.1.0.2/db1
$

Requirements are .sh and .sql must have the same name and reside in the same location.
.sh can be called from any location.

parameter.sh

#!/bin/sh
# --------------------------------------------------------------------------------
# parameter.sh
# MDinh April 12, 2018
#
# Shell script will run SQL script having the same base name
# for all active database instances.
# --------------------------------------------------------------------------------
DN=`dirname $0`
BN=`basename $0`
SQL_SCRIPT_DIR=$DN
SQL=`echo $BN|cut -d'.' -f1`.sql
echo
echo "******** Current ora_pmon:"
echo "----------------------------------------"
ps -eo cmd|grep ora_pmon|grep -v grep
echo "----------------------------------------"
echo
echo "******** SQL Script: "$SQL_SCRIPT_DIR/$SQL
echo
for x in `ps -eo cmd|grep ora_pmon|grep -v grep|awk -F "_" '{print $NF}'`
do
  ORAENV_ASK=NO
  set -a
  ORACLE_SID=$x
  . oraenv
  set +a
  sysresv|tail -1

sqlplus -L "/ as sysdba" << EOF
whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
start $SQL_SCRIPT_DIR/$SQL
exit
EOF
if [ "$?" != "0" ]; then
  echo "$ORACLE_SID ERROR: Running $SQL_SCRIPT_DIR/$SQL"
  exit 1
fi
done
exit

 

parameter.sql

col name for a30
col value for a30
col default_value for a13
col display_value for a13
col inst_id for 99
break on name
set lines 200 pages 1000 trimsp on tab off
select name,CDB from v$database
;
select name,display_value,inst_id,con_id,default_value,isdefault
from gv$parameter
where regexp_like (name,'^sga|^pga|^memory|^cluster.*database|^instance|use_large_pages|db.*name','i')
order by name,value,inst_id
;

 

Frame work for shell script is the same. Just make a copy and update any comments.

oracle@racnode-dc1-1:hawk1:/home/oracle
$ ll
total 36
-rwxr-xr-x 1 oracle oinstall   19 Feb 10 20:44 db.env
-rwxr-xr-x 1 oracle oinstall   49 Feb 10 20:45 gi.env
-rwxr-xr-x 1 oracle oinstall 1020 Apr 14 13:24 parameter.sh
-rw-r--r-- 1 oracle oinstall  414 Apr 14 13:24 parameter.sql
-rwxr-xr-x 1 oracle oinstall 1038 Apr 14 13:23 set_db_use_large_pages_only.sh
-rw-r--r-- 1 oracle oinstall  430 Apr 12 17:50 set_db_use_large_pages_only.sql
-rwxr-xr-x 1 oracle oinstall 1038 Apr 14 13:23 set_db_use_large_pages_true.sh
-rw-r--r-- 1 oracle oinstall  430 Apr 12 17:53 set_db_use_large_pages_true.sql
-rw-r--r-- 1 oracle oinstall 1909 Jan 29 02:39 wc.sql

oracle@racnode-dc1-1:hawk1:/home/oracle
$ diff parameter.sh set_db_use_large_pages_true.sh
3c3
< # parameter.sh 
--- 
> # set_db_use_large_pages_true.sh

oracle@racnode-dc1-1:hawk1:/home/oracle
$ ./set_db_use_large_pages_true.sh

******** Current ora_pmon:
----------------------------------------
ora_pmon_hawk1
----------------------------------------

******** SQL Script: ./set_db_use_large_pages_true.sql

The Oracle base remains unchanged with value /u01/app/oracle
Oracle Instance alive for sid "hawk1"

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:48:17 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

13:48:17 SYS @ hawk1:>13:48:17 SYS @ hawk1:>13:48:17 SYS @ hawk1:>
NAME                           CDB
------------------------------ ---
HAWK                           NO


NAME                           DISPLAY_VALUE INST_ID     CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
use_large_pages                TRUE                1          0 NULL          FALSE
                               TRUE                2          0 NULL          FALSE

13:48:17 SYS @ hawk1:>alter system set USE_LARGE_PAGES=TRUE scope=spfile sid='*'
13:48:17   2  ;

System altered.

13:48:17 SYS @ hawk1:>Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@racnode-dc1-1:hawk1:/home/oracle
$

Check 12.1.0.2 Alert Log For HugePages Usage

What! Another post on hugepages – seriously?

+ grep 'Dump of system resources acquired for SHARED GLOBAL AREA' -B1 -A22 database alert log
+ tail -25
2018-04-13T09:40:23.908633-07:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA) 

2018-04-13T09:40:23.916573-07:00
 Per process system memlock (soft) limit = UNLIMITED
2018-04-13T09:40:23.920591-07:00
 Expected per process system memlock (soft) limit to lock
 SHARED GLOBAL AREA (SGA) into memory: 2996M
2018-04-13T09:40:23.928517-07:00
 Available system pagesizes:
  4K, 2048K 
2018-04-13T09:40:23.936717-07:00
 Supported system pagesize(s):
 
2018-04-13T09:40:23.943044-07:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2018-04-13T09:40:23.947112-07:00
     2048K             2303            1498            1498        NONE
2018-04-13T09:40:23.951899-07:00
 Reason for not supporting certain system pagesizes: 
2018-04-13T09:40:23.960107-07:00
  4K - Large pagesizes only
2018-04-13T09:40:23.965247-07:00

====================================================================================================

Tue Apr 10 12:29:13 2018
Dump of system resources acquired for SHARED GLOBAL AREA (SGA) 

Tue Apr 10 12:29:13 2018
 Per process system memlock (soft) limit = 128G
Tue Apr 10 12:29:13 2018
 Expected per process system memlock (soft) limit to lock
 SHARED GLOBAL AREA (SGA) into memory: 4002M
Tue Apr 10 12:29:13 2018
 Available system pagesizes:
  4K, 2048K 
Tue Apr 10 12:29:13 2018
 Supported system pagesize(s):
Tue Apr 10 12:29:13 2018
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
Tue Apr 10 12:29:13 2018
        4K       Configured               5         1024005        NONE
Tue Apr 10 12:29:13 2018
     2048K                0            2001               0        NONE
Tue Apr 10 12:29:13 2018
RECOMMENDATION:
Tue Apr 10 12:29:13 2018
 1. For optimal performance, configure system with expected number 
 of pages for every supported system pagesize prior to the next 

Oracle Different Levels of Hell

Did not know there exists many levels of hell and Oracle certainly has them.

Would it be bad if someone is searching for hell and this blog is listed as top 10? :=(

Here’s the effort one needs to go through to determine what patch to apply for Goldengate software (binary) as part of quarterly Critical Patch Updates.

Hint: Goldengate does not participate in quarterly patch updates.

Find the latest patch available and patch using Opatch or install newer database compatible version using runInstaller.

Oracle GoldenGate — Oracle RDBMS Server Recommended Patches (Doc ID 1557031.1)
Latest RDBMS version Oracle Server 12.1.0.2

Let’s create a new doc for new version but don’t provide reference to it. Make the user find it.

Latest GoldenGate/Database (OGG/RDBMS) Patch recommendations (Doc ID 2193391.1)
Latest OGG Release 12.3
Latest RDBMS Release 12.2.0.1

OGG Release/OGG Patch
12.3/Recommended 12.3.0.1.2 or higher *** This is not a patch
12.2/Recommended 12.2.0.2.0 or higher *** This is not a patch
12.2/Minimum 12.2.0.1.170221 *** This is a patch

Master Note for Oracle GoldenGate Core Product Patch Sets (Doc ID 1645495.1)
There are no patch sets available for 12.3.0.1 at this time.
Latest OGG v12.2.0.1 Patch Set Availability Notes