Oracle GoldenGate - 1403 and KeyCols defination by GoldenGate automatically

********************* READ My update below for quick read **********

Dec 30 2023:

Can we trust the oracle GG's default mechanism to pick keycols for target when there is no primary key or unique key with not null constraint defined?

NO!!!

Oracle GG tries it level best to pick to pick a proper keycol for the target to perform the DMLs.

But at times it fails, so be prepare to face it, in case your table list part of replication doesn't have PK or UK with not null defined.

GG replication will throw 1403 errors during delete, update operations.

Since the keycol it developed at times may not identify the record in target.

For ex.

You may have a source record with below signature...

N,C1,C2,C3 -- attributes

1,A,C,D

Target:

1,B,D,D

In this case if oracle picks N as keycol and N happened to be a unique key, your good.

If oracle GG goes an extra mile and identifies N,C1 as keycols for target.

Then you will get 1403 error (which is data not found in target).

So it is wise, as soon as you face a 1403 error or proactively identify all the tables with non unique identifiers and get your fixup routine ready.

The discard will tell you the reason and report file will tell you the mapping info. These two piece of info should help you identify the cause and fix.

Hope this helps!

Thanks

****************** Update to this blog **********************

Jan 27 2024:

After handling a migration project involving golden gate and working with oracle support, my experience with oracle goldengate improved. I learnt that 1403 error in my case popped up due to the fact, the keycol values are being updated.

Table 1: cola, colb, colc

unique index; cola,colb

OGG, properly picks unique index as the keycol definition for replication.

Imagine if the keycol value is changed meaning updated in source, the corresponding keycol value pair mightnt be available in target, this means a no data found error.

imagine:

1,2,'A'

kecol: 1,2

if this value is changed to 2,3 --> then this wont be found in target.

Oracle assumes your not changing the search field or index field, which is expected as a product.

So it is upto the GG DBA to ensure this is the case. In my it was my responsibility.

Thank you!

Oracle: Will "set newname for database to new" work in active DB duplication?

Dears,

Today, while I was assisting my colleague to get a standby db build using active db duplication command.

Source DB is 30TB in size.

Background: the standby database was activated last saturday due to primary server hardware issue.

We were left with no flashback option, since it wasnt enabled. So db restore in the old primary was the only way to reinstate (we differed OS utilities).

Challenge: The db had a mix of non-OFM and OFM format of datafiles.

So I discussed with my colleague and proposed to move all the files to OMF format for 

1. auto standby file management
2. ease of datafile maintenance operations later

We noticed the DB had

• db_file_name_convert
• db_create_file_dest

parameters set. When I fired the active db duplication asis, with no change to these 2 param setting.

I noticed, Oracle trying to restore even the OMF files into the same path in target as if it is restoring the non-OMF files (file names had encrypted identity).

So I decided to remove db_file_name_convert, after reading oracle doc, I felt this is the main culprit.

To ensure the datafiles gets the benefit of db_create_file_dest parameter, I introduced

"set newname for database to new" as well to the duplicate rman run command.

run

{

allocate channel prmry1 type disk;

...

allocate channel auxiliary channel stby1 type disk;

...

set newname for database to new;

duplicate target database for standby from active databvase

dorecover

spfile

   parameter...

...

NOFILENAMECHECK;

}

All the files got restored to the location indicated by db_create_file_dest as I expected.

This worked perfectly fine.

Thanks

Block Change Tracking File Invalid (RMAN-08606)

Dears,

Today while I was performing a FULL db backup of my production database to disk, I received a weird RMAN-08606 error.

RMAN-08606: WARNING: The Change Tracking File Is Invalid

On reading oracle note, I learnt it is risk to our incremental level 1s. Since the change tracker is missing changed block information.

Oracle says to ignore any backup taken since last incremental level 0, if you have this error. Since those backups potentially are incomplete due to missing changed block info.

So oracle proposes few options like

1. placing this bct file on a relatively less hot filesystem
2. increasing large_pool_size
3. increasing _bct_public_dba_buffer_size to a larger value (use x$krcstat view for getting this value).

This should reduce the wait events related to bct and avoid the situation highlighted.

What I did is as a first measure disabled the bct, since a risk to backup is huge for me. My DB is small around 2TB. So I am not bothered reading 2TB in place of 200GB.

Thanks

 

DBMS_SQLTUNE.REPORT_SQL_MONITOR - reporting "DONE(ERROR)"

 Dears,

Today while I was watching a SQL query execution statsistics, I noticed a weired behaviour coming out of oracle's DBMS_SQLTUNE.REPORT_SQL_MONITOR package.

DBMS_SQLTUNE.REPORT_SQL_MONITOR is used to report the live execution statistics of the SQL.

This particular package reported my active SQL as "DONE(ERROR)" as if like some one killed the session or it failed due to a temp or undo or datafile space error.

It didnt report why it failed though still. Interestingly the elapsed time is 7748 vs it's overall query duration reported was 317s.

It took me by surprise, then I check v$session and noticed it isnt rolling back the merge it was running; since it wasnt reading from undo datafile.

So I suspected this is a weird reporting issue. So I continued checking if the session is really active.

Yes the session was really active and v$sql perfectly increments elapsed, cpu, io wait times along with its corresponding other metrics.

Cause: the query ran using parallel slaves, query had plan regression and it ended up running longer with few slaves staying idle for more than 30mins.

Now oracle says this particular condition where a px slave part of the query execution staying idle is an anomaly and is reported as a failed sql execution from oracle end; but really isnt the case :)

RDBMS: Oracle

Version: 12.2

Oracle note: 1900060.1

Bug: 13523091 (Incorrect status in v$sql_monitor with parallel query)

Thank you!

One of the reason why my referential constraint enabling is slow - Oracle 19c

Dears,

I was working on a project which involves 

1.    Data Copy with table_exists action Truncate (#70 in nos)

2.    Data Copy with table_exists action replace (#5 in nos)

I had to disable constraints and triggers in preperation for this excercise on the candidate schema before kicking of impdp.

Post the impdp, the constraint enabling took ages for 1 table. At one point it was already looking 20000secs.

I was going through various recommendations and options. One noteable callout was...

a. Enable constraint without validation

b. Enable parallel DDL @ session and run validation statement seperately.

I was thinking to log a SR and try this work around. But suddely I thought I am missing something, which is my basic issue diagnosis attempt.

Though I was constantly collecting metrics for the session, I couldnt sense any issue except the buffer gets crossed 1 billion already!!!!!!!!!!!!!!!!!!!!!

So the session was spinning in CPU always.

Finally I pulled a AWR report for 1 isolated window where only this constrtaint enabling was inprogress.

Found out a recursive SQL which was inprogress triggered by the constraint sql.

Noticed the plan of the recursive sql was pathetic (nested loop - expecting to drive 35 billion buffer gets). The constraint was a FK, so the recursive query was trying to find orphan records in the table where I am attempting to enable constraint.

So I decided to fix the plan

a. Patch the sql

b. Look for proper fix --- more info needed.

I continued, I looked at the table statistics, the auto stats, clashed with truncate and made all the tables part of the refresh with num_rows 0 statistics.

So thought this could be a concern. So I collected fresh stats on the tables participating in the referential constraint.

Tried generating the plan now, it was using hash outer join. Tried running the recursive SQL, all went well - 20000secs came down to 90secs!!!!!!!!!!

Tried the constraint enable statement now, the statement just flew by in no time.

So a happy ending for the story :)

Difference between connections established directly to postgres and a connection established through pgbouncer - a view from netstat

 Difference between connections established directly to postgres and a connection established through pgbouncer

pgbouncer acts as a proxy for client.It is used as a connection pooler.

The pgbouncer reduces the connection latency to a greater extent which is very much useful for the OLTP apps.

Direct connection:

Client:

-bash-4.2$ psql -U postgres -h 127.0.0.1 -p 5432

psql (15.2, server 14.7)

Type "help" for help.

postgres=# \c

psql (15.2, server 14.7)

You are now connected to database "postgres" as user "postgres".

postgres=#

Netstat output:

-bash-4.2$ netstat -plantu|grep 33336

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 127.0.0.1:5432          127.0.0.1:33336         ESTABLISHED 4997/postgres: post

tcp        0      0 127.0.0.1:33336         127.0.0.1:5432          ESTABLISHED 4996/psql

-bash-4.2$

Pgbouncer based connection:

Client:

-bash-4.2$ psql -p 6432 testdb -h 127.0.0.1 -U postgres

Password for user postgres:

psql (15.2, server 14.7)

Type "help" for help.

testdb=# \c

psql (15.2, server 14.7)

You are now connected to database "testdb" as user "postgres".

testdb=#

Netstat output:

-bash-4.2$ netstat -plantu|grep -i 6432

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 0.0.0.0:6432            0.0.0.0:*               LISTEN      4196/pgbouncer <<< ipv4 pgbouncer listener

tcp        0      0 127.0.0.1:6432          127.0.0.1:33676         ESTABLISHED 4196/pgbouncer <<< this the incoming connection

tcp        0      0 127.0.0.1:33674         127.0.0.1:6432          TIME_WAIT   -

tcp        0      0 127.0.0.1:33676         127.0.0.1:6432          ESTABLISHED 4771/psql >>> this is the outgoing connection from client

tcp        0      0 127.0.0.1:33670         127.0.0.1:6432          TIME_WAIT   -

tcp6       0      0 :::6432                 :::*                    LISTEN      4196/pgbouncer <<< ipv6 pgbouncer listener

-bash-4.2$

-bash-4.2$ netstat -plantu|grep 5432

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      1337/postmaster <<< ipv4 postgres listener

tcp        0      0 127.0.0.1:33326         127.0.0.1:5432          ESTABLISHED 4196/pgbouncer <<< pgbouncer redirecting the connection here, which is an outgoing connection

tcp        0      0 127.0.0.1:5432          127.0.0.1:33326         ESTABLISHED 4772/postgres: pgte <<< postgres accepting the incoming connection

tcp6       0      0 :::5432                 :::*                    LISTEN      1337/postmaster <<< ipv6 pgbouncer listener

-bash-4.2$ ps -ef|grep 4196

Overall picture:

YouTube Video:

Thanks for visiting the blog. Thanks

Adding new tables into Replication - Assess the effect of GoldenGate 21.3 replication on triggers

 

Adding new tables into Replication (with trigger and sequence)

Table Datamodel: 2 tables with trigger managing 1 one of the tables based on another table DML (insert,update,delete).

Main table: main_tblapp01_13

Auxiliary (Trigger) managed table: arch_tblapp01_14

Other object involved: seqmain_tblapp01_13 (for inserting the primary key column value in main_tblapp01_13) & seqarch_tblapp01_14 (for inserting the primary key column value in arch_tblapp01_14)

Step 1) 

Ensure extract and replication are stopped

export OGG_HOME=/u01/app/oracle/product/21.3.0/ogg_home_1

export PATH=$PATH:$OGG_HOME/bin

adminclient

connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

using adminclient> info all

Actual output:

OGG (not connected) 1> connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

OGG (http://127.0.0.1:9011 oggdep01) 2> info all

Program     Status      Group       Type             Lag at Chkpt  Time Since Chkpt

EXTRACT     STOPPED     RXT1        INTEGRATED       00:00:00      01:02:47

REPLICAT    STOPPED     RPT1        PARALLEL NONINT  00:00:00      01:02:12

REPLICAT    STOPPED     RPT2        PARALLEL NONINT  00:00:00      00:00:00

OGG (http://127.0.0.1:9011 oggdep01) 3>

Step 2) 

Extract the lowest SCN:

set colsep ,

set lines 200

set pages 3000

col osuser for a20

col username for a20

set time on

set timing on

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

SELECT T.START_SCN, T.STATUS TSTATUS, T.START_DATE,

       S.SID, S.SERIAL#, S.INST_ID, S.USERNAME, S.OSUSER, S.STATUS SSTATUS, S.LOGON_TIME

  FROM gv$transaction T

 INNER JOIN gv$session S

 ON s.saddr = t.ses_addr

UNION ALL

SELECT CURRENT_SCN, 'CURRENT', CURRENT_DATE,

       NULL, NULL, NULL, 'SYS', NULL, NULL, NULL

 from v$database

ORDER BY 1;

Actual output:

 START_SCN,TSTATUS         ,START_DATE          ,       SID,   SERIAL#,   INST_ID,USERNAME            ,OSUSER              ,SSTATUS ,LOGON_TIME

----------,----------------,--------------------,----------,----------,----------,--------------------,--------------------,--------,--------------------

    710475,CURRENT         ,11/OCT/2023 18:55:45,          ,          ,          ,SYS                 ,                    ,          ,

Step 3)

Pump out the tables from source db based on the above scn

expdp directory=DATA_PUMP_DIR tables=userapp01.main_tblapp01_13,userapp01.arch_tblapp01_14 dumpfile=userapp01_subset5.dmp logfile=userapp01_subset5.log FLASHBACK_SCN=710475 logtime=ALL METRICS=YES exclude=statistics, table_statistics, index_statistics

Actual output:

11-OCT-23 18:56:57.559: W-1      Completed 1 TRIGGER objects in 0 seconds 11-OCT-23 18:56:58.427: W-1 . . exported "USERAPP01"."ARCH_TBLAPP01_14"              127.0 KB     999 rows in 0 seconds using direct_path 11-OCT-23 18:56:58.464: W-1 . . exported "USERAPP01"."MAIN_TBLAPP01_13"              114.5 KB     999 rows in 0 seconds using direct_path 11-OCT-23 18:56:58.727: W-1      Completed 2 TABLE_EXPORT/TABLE/TABLE_DATA objects in 0 seconds 11-OCT-23 18:56:59.381: W-1 Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded 11-OCT-23 18:56:59.429: ****************************************************************************** 11-OCT-23 18:56:59.430: Dump file set for SYS.SYS_EXPORT_TABLE_01 is: 11-OCT-23 18:56:59.437:   /u01/app/oracle/admin/GGSRC05T/dpdump/userapp01_subset5.dmp 11-OCT-23 18:56:59.466: Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Oct 11 18:56:59 2023 elapsed 0 00:00:08

Step 4) 

Copy the dump over to target

and import the tables into target

impdp tables=userapp01.main_tblapp01_13,userapp01.arch_tblapp01_14 dumpfile=userapp01_subset5.dmp logfile=userapp01_subset_import5.log  directory=DATA_PUMP_DIR logtime=ALL METRICS=YES

Actual output:

11-OCT-23 18:58:15.518: W-1 . . imported "USERAPP01"."ARCH_TBLAPP01_14"              127.0 KB     999 rows in 1 seconds using direct_path 11-OCT-23 18:58:15.586: W-1 . . imported "USERAPP01"."MAIN_TBLAPP01_13"              114.5 KB     999 rows in 0 seconds using direct_path 11-OCT-23 18:58:15.646: W-1 Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 11-OCT-23 18:58:15.916: W-1      Completed 1 CONSTRAINT objects in 0 seconds 11-OCT-23 18:58:15.916: W-1 Processing object type TABLE_EXPORT/TABLE/TRIGGER 11-OCT-23 18:58:16.180: W-1      Completed 1 TRIGGER objects in 1 seconds 11-OCT-23 18:58:16.247: W-1      Completed 2 TABLE_EXPORT/TABLE/TABLE_DATA objects in 1 seconds 11-OCT-23 18:58:16.280: ORA-39082: Object type TRIGGER:"USERAPP01"."TRG_MAIN_TBLAPP01_13" created with compilation warnings 11-OCT-23 18:58:16.374: Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed Oct 11 18:58:16 2023 elapsed 0 00:00:06 SQL> create sequence seqarch_tblapp01_14 start with 1 increment by 1 nocache nocycle;  2    3    4    5 Sequence created. SQL> alter trigger "USERAPP01"."TRG_MAIN_TBLAPP01_13" compile; Trigger altered. SQL> select status from user_objects where object_name='TRG_MAIN_TBLAPP01_13'; STATUS ------- VALID

Step 5) 

Let us add/delete/update more records:

conn userapp01/userapp01

set time on

set timing on

set colsep ,

set lines 200

set pages 3000

col osuser for a20

col username for a20

col trigger_name for a30

col status for a10

select trigger_name,status from user_triggers;

select count(1) from arch_tblapp01_14;

select count(1) from main_tblapp01_13;

insert into main_tblapp01_13

select seqmain_tblapp01_13.nextval,mod(level,10) partid

,rpad('R',mod(level,200),'X') idstr

from dual

connect by level < 1000;

delete main_tblapp01_13 where id=5;

update main_tblapp01_13 set partid=100 where id=10;

commit;

check the record count:

select count(1) from arch_tblapp01_14;

select count(1) from main_tblapp01_13;

Actual output:

SQL> conn userapp01/userapp01 Connected. SQL> set time on set timing on set colsep , set lines 200 set pages 3000 col osuser for a20 col username for a20 col trigger_name for a30 col status for a1019:00:00 SQL> 19:00:00 SQL> 19:00:00 SQL> 19:00:00 SQL> 19:00:00 SQL> 19:00:00 SQL> 19:00:00 SQL> 19:00:00 SQL> 19:00:01 SQL> select trigger_name,status from user_triggers; TRIGGER_NAME                  ,STATUS ------------------------------,---------- TRG_MAIN_TBLAPP01_07          ,ENABLED TRG_MAIN_TBLAPP01_09          ,ENABLED TRG_MAIN_TBLAPP01_11          ,ENABLED TRG_MAIN_TBLAPP01_13          ,ENABLED Elapsed: 00:00:00.01 19:00:05 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------        999 Elapsed: 00:00:00.01 19:00:12 SQL> select count(1) from main_tblapp01_13;   COUNT(1) ----------        999 Elapsed: 00:00:00.00 19:00:17 SQL> insert into main_tblapp01_13 select seqmain_tblapp01_13.nextval,mod(level,10) partid ,rpad('R',mod(level,200),'X') idstr from dual connect by level < 1000;19:00:22   2  19:00:22   3  19:00:22   4  19:00:22   5 999 rows created. Elapsed: 00:00:00.23 19:00:24 SQL> 19:00:28 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------       1998 Elapsed: 00:00:00.00 19:00:32 SQL> select count(1) from main_tblapp01_13;   COUNT(1) ----------       1998 Elapsed: 00:00:00.00 19:00:35 SQL> delete main_tblapp01_13 where id=5; 1 row deleted. Elapsed: 00:00:00.01 19:00:41 SQL> select count(1) from main_tblapp01_13;   COUNT(1) ----------       1997 Elapsed: 00:00:00.00 19:00:47 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------       1999 Elapsed: 00:00:00.00 19:00:50 SQL> update main_tblapp01_13 set partid=100 where id=10; 1 row updated. Elapsed: 00:00:00.00 19:01:02 SQL> select count(1) from main_tblapp01_13;   COUNT(1) ----------       1997 Elapsed: 00:00:00.00 19:01:05 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------       2000 Elapsed: 00:00:00.00 19:01:08 SQL> commit; Commit complete. Elapsed: 00:00:00.01

Step 6) Extract start

The extract is setup @ schemalevel, so no action needed for the table we created.

export OGG_HOME=/u01/app/oracle/product/21.3.0/ogg_home_1

export PATH=$PATH:$OGG_HOME/bin

adminclient

connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

DBLOGIN USERIDALIAS ggsrc05t

info all

start extract rxt1

info extract rxt1 detail

Actual output:

OGG (http://127.0.0.1:9011 oggdep01) 3> info all Program     Status      Group       Type             Lag at Chkpt  Time Since Chkpt EXTRACT     STOPPED     RXT1        INTEGRATED       00:00:00      01:10:01 REPLICAT    STOPPED     RPT1        PARALLEL NONINT  00:00:00      01:09:26 REPLICAT    STOPPED     RPT2        PARALLEL NONINT  00:00:00      00:00:00 OGG (http://127.0.0.1:9011 oggdep01) 4> start extract rxt1 2023-10-11T18:02:27Z  INFO    OGG-00975  Extract group RXT1 starting. 2023-10-11T18:02:27Z  INFO    OGG-15426  Extract group RXT1 started. OGG (http://127.0.0.1:9011 oggdep01) 5> info all Program     Status      Group       Type             Lag at Chkpt  Time Since Chkpt EXTRACT     RUNNING     RXT1        INTEGRATED       00:00:00      00:00:00 REPLICAT    STOPPED     RPT1        PARALLEL NONINT  00:00:00      01:09:36 REPLICAT    STOPPED     RPT2        PARALLEL NONINT  00:00:00      00:00:00 OGG (http://127.0.0.1:9011 oggdep01) 6> info extract rxt1 detail Extract    RXT1      Last Started 2023-10-11 19:02   Status RUNNING Checkpoint Lag       00:00:00 (updated 00:00:00 ago) Process ID           26117 Log Read Checkpoint  Oracle Integrated Redo Logs                      2023-10-11 19:02:36                      SCN 0.717378 (717378) Encryption Profile   LocalWallet   Target Extract Trails:   Trail Name                                       Seqno        RBA     Max MB Trail Type   src/r1                                               4    1000423        500 EXTTRAIL Integrated Extract outbound server first scn: 0.353578 (353578) Integrated Extract outbound server filtering start scn: 0.353578 (353578)   Extract Source                          Begin             End   Not Available                           2023-10-07 18:36  2023-10-11 19:02   Not Available                           2023-10-07 18:36  2023-10-11 17:52   Not Available                           2023-10-07 18:36  2023-10-11 17:52   Not Available                           2023-10-07 18:36  2023-10-11 17:52   Not Available                           2023-10-07 18:36  2023-10-11 16:48   Not Available                           2023-10-07 18:36  2023-10-11 16:48   Not Available                           2023-10-07 18:36  2023-10-11 16:48   Not Available                           2023-10-07 18:36  2023-10-11 15:06   Not Available                           2023-10-07 18:36  2023-10-11 15:06   Not Available                           2023-10-07 18:36  2023-10-11 15:06   Not Available                           2023-10-07 18:36  2023-10-08 01:11   Not Available                           2023-10-07 18:36  2023-10-08 01:11   Not Available                           2023-10-07 18:36  2023-10-08 01:11   Not Available                           * Initialized *   2023-10-07 18:36   Not Available                           * Initialized *   2023-10-07 18:36   Not Available                           * Initialized *   2023-10-07 18:36 Current directory    / Report file          /oggdata/oggdep01/var/lib/report/RXT1.rpt Parameter file       /oggdata/oggdep01/etc/conf/ogg/rxt1.prm Checkpoint file      /oggdata/oggdep01/var/lib/checkpt/RXT1.cpe Process file         /oggdata/oggdep01/var/run/RXT1.pce Error log            /oggdata/oggdep01/var/log/ggserr.log OGG (http://127.0.0.1:9011 oggdep01) 7>

Step 7) Add the table to replicat param with handlecollision

export OGG_HOME=/u01/app/oracle/product/21.3.0/ogg_home_1

export PATH=$PATH:$OGG_HOME/bin

adminclient

connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

DBLOGIN USERIDALIAS ggtgt06t

info replicat rpt1 detail

vi /oggdata/oggdep01/etc/conf/ogg/rpt1.prm

-- dboptions suppresstriggers;

MAP userapp01.main_tblapp01_13, target userapp01.main_tblapp01_13,  HANDLECOLLISIONS;

MAP userapp01.arch_tblapp01_14, target userapp01.arch_tblapp01_14,  HANDLECOLLISIONS;

actual file:

OGG (http://127.0.0.1:9011 oggdep01) 10> view params rpt1 REPLICAT rpt1 USERIDALIAS ggtgt06t DOMAIN OracleGoldenGate --DDL EXCLUDE ALL DDLERROR default discard REPERROR (default,discard) DDLOPTIONS REPORT MAP userapp01.tblapp01_01, target userapp01.tblapp01_01,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 410419); MAP userapp01.tblapp01_02, target userapp01.tblapp01_02,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 410419); MAP userapp01.main_tblapp01_07, target userapp01.main_tblapp01_07; MAP userapp01.arch_tblapp01_08, target userapp01.arch_tblapp01_08; MAP userapp01.main_tblapp01_09, target userapp01.main_tblapp01_09; MAP userapp01.arch_tblapp01_10, target userapp01.arch_tblapp01_10; MAP userapp01.main_tblapp01_11, target userapp01.main_tblapp01_11; MAP userapp01.arch_tblapp01_12, target userapp01.arch_tblapp01_12; MAP userapp01.main_tblapp01_13, target userapp01.main_tblapp01_13,  HANDLECOLLISIONS; MAP userapp01.arch_tblapp01_14, target userapp01.arch_tblapp01_14,  HANDLECOLLISIONS; -- MAP userapp01.tblapp01_03, target userapp01.tblapp01_03,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 410419); -- MAP userapp01.tblapp01_04, target userapp01.tblapp01_04,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 410419); OGG (http://127.0.0.1:9011 oggdep01) 11>

Step 8) ensure the permission below is granted the goldengate user in target db

  Alias: ggtgt06t

  Userid: rggadmin@//192.168.194.11:1521/GGTGT06T

exec dbms_goldengate_auth.grant_admin_privilege('rggadmin');

Actual output:

This isnt needed if we arent going to use "DBOPTIONS SUPPRESSTRIGGERS"

Step 9)  Verify and start the replication

export OGG_HOME=/u01/app/oracle/product/21.3.0/ogg_home_1

export PATH=$PATH:$OGG_HOME/bin

adminclient

connect http://127.0.0.1:9011 deployment oggdep01 user ggsca password ggsca

DBLOGIN USERIDALIAS ggtgt06t

info replicat RPT1

start replicat RPT1

status replicat RPT1

info all

Actual output:

OGG (http://127.0.0.1:9011 oggdep01) 11> info replicat RPT1 Replicat   RPT1      Last Started 2023-10-11 17:43   Status STOPPED Parallel Checkpoint Lag       00:00:00 (updated 01:12:55 ago) Log Read Checkpoint  File src/r1000000003                      2023-10-11 17:52:55.191445  RBA 985919 Encryption Profile   LocalWallet OGG (http://127.0.0.1:9011 oggdep01) 12> OGG (http://127.0.0.1:9011 oggdep01) 12> OGG (http://127.0.0.1:9011 oggdep01) 12> info replicat rpt1 detail Replicat   RPT1      Last Started 2023-10-11 17:43   Status STOPPED Parallel Checkpoint Lag       00:00:00 (updated 01:13:03 ago) Log Read Checkpoint  File src/r1000000003                      2023-10-11 17:52:55.191445  RBA 985919 Encryption Profile   LocalWallet Current Log BSN value: 696581 Low Watermark CSN value: 696605 High Watermark CSN value: 696605 Current directory    / Report file          /oggdata/oggdep01/var/lib/report/RPT1.rpt Parameter file       /oggdata/oggdep01/etc/conf/ogg/rpt1.prm Checkpoint file      /oggdata/oggdep01/var/lib/checkpt/RPT1.cpr Checkpoint table     rggadmin.ggs_checkpoint Process file         /oggdata/oggdep01/var/run/RPT1.pcr Error log            /oggdata/oggdep01/var/log/ggserr.log OGG (http://127.0.0.1:9011 oggdep01) 13> start replicat RPT1 2023-10-11T18:06:33Z  INFO    OGG-00975  Replicat group RPT1 starting. 2023-10-11T18:06:33Z  INFO    OGG-15445  Replicat group RPT1 started. OGG (http://127.0.0.1:9011 oggdep01) 14> status replicat RPT1 REPLICAT RPT1: RUNNING OGG (http://127.0.0.1:9011 oggdep01) 15> info all Program     Status      Group       Type             Lag at Chkpt  Time Since Chkpt EXTRACT     RUNNING     RXT1        INTEGRATED       00:00:00      00:00:03 REPLICAT    RUNNING     RPT1        PARALLEL NONINT  00:00:00      00:00:03 REPLICAT    STOPPED     RPT2        PARALLEL NONINT  00:00:00      00:00:00 OGG (http://127.0.0.1:9011 oggdep01) 16> DBLOGIN USERIDALIAS ggtgt06t Successfully logged into database. OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 17> lag replicat rpt1 LAG                 AGE                 FROM                TO                  PATH 5.88s               26.14s              GGSRC05T            GGTGT06T            RXT1 ==> RPT1 Sending GETLAG request to Replicat group RPT1 ... Low watermark lag: 25 seconds High watermark lag: 24 seconds At EOF, no more records to process OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 18>

Step 10) check lag

lag replicat rpt1

lag reaches EOF

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 19> lag replicat rpt1 LAG                 AGE                 FROM                TO                  PATH 4.88s               28.44s              GGSRC05T            GGTGT06T            RXT1 ==> RPT1 Sending GETLAG request to Replicat group RPT1 ... Low watermark lag: 27 seconds High watermark lag: 27 seconds At EOF, no more records to process OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 20>

Step 11) stop replicat & remove the handlecollisions param

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 21> status replicat RPT1 REPLICAT RPT1: STOPPED OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 22> view params rpt1 REPLICAT rpt1 USERIDALIAS ggtgt06t DOMAIN OracleGoldenGate --DDL EXCLUDE ALL DDLERROR default discard REPERROR (default,discard) DDLOPTIONS REPORT MAP userapp01.tblapp01_01, target userapp01.tblapp01_01,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 410419); MAP userapp01.tblapp01_02, target userapp01.tblapp01_02,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 410419); MAP userapp01.main_tblapp01_07, target userapp01.main_tblapp01_07; MAP userapp01.arch_tblapp01_08, target userapp01.arch_tblapp01_08; MAP userapp01.main_tblapp01_09, target userapp01.main_tblapp01_09; MAP userapp01.arch_tblapp01_10, target userapp01.arch_tblapp01_10; MAP userapp01.main_tblapp01_11, target userapp01.main_tblapp01_11; MAP userapp01.arch_tblapp01_12, target userapp01.arch_tblapp01_12; MAP userapp01.main_tblapp01_13, target userapp01.main_tblapp01_13; MAP userapp01.arch_tblapp01_14, target userapp01.arch_tblapp01_14; -- MAP userapp01.tblapp01_03, target userapp01.tblapp01_03,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 410419); -- MAP userapp01.tblapp01_04, target userapp01.tblapp01_04,  FILTER ( @GETENV('TRANSACTION', 'CSN') > 410419); OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 23>

Step 12) start replicat

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 23> start replicat RPT1 2023-10-11T18:13:27Z  INFO    OGG-00975  Replicat group RPT1 starting. 2023-10-11T18:13:27Z  INFO    OGG-15445  Replicat group RPT1 started. OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 24> status replicat RPT1 REPLICAT RPT1: RUNNING OGG (http://127.0.0.1:9011 oggdep01 as ggtgt06t@GGTGT06T) 25>

Further test:

Overall result:

Source:

19:01:30 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------       2000 Elapsed: 00:00:00.00 19:08:49 SQL> select count(1) from main_tblapp01_13;   COUNT(1) ----------       1997 Elapsed: 00:00:00.00 19:08:55 SQL> select count(1) from main_tblapp01_13 where id=1;   COUNT(1) ----------          1 Elapsed: 00:00:00.00 19:14:58 SQL> select count(1) from main_tblapp01_13 where id=1001;   COUNT(1) ----------          1 Elapsed: 00:00:00.00 19:15:05 SQL> select count(1) from arch_tblapp01_14 where id=1;   COUNT(1) ----------          1 Elapsed: 00:00:00.01 19:15:14 SQL> select count(1) from arch_tblapp01_14 where id=1001;   COUNT(1) ----------          1 Elapsed: 00:00:00.01 19:15:21 SQL> update main_tblapp01_13 set partid=100 where id=1001; 1 row updated. Elapsed: 00:00:00.00 19:15:40 SQL> select count(1) from arch_tblapp01_14 where id=1001;   COUNT(1) ----------          2 Elapsed: 00:00:00.00 19:15:45 SQL> commit; Commit complete. Elapsed: 00:00:00.01 19:16:02 SQL> select count(1) from arch_tblapp01_14 where id=1;   COUNT(1) ----------          1 Elapsed: 00:00:00.00 19:16:42 SQL> select count(1) from main_tblapp01_13 where id=1001;   COUNT(1) ----------          1 Elapsed: 00:00:00.00 19:16:50 SQL> set lines 200 19:18:15 SQL> set pages 3000 19:18:18 SQL> set colsep , 19:18:21 SQL> alter session set nls_Date_format='DD/MON/YYYY HH24:MI:SS'; Session altered. Elapsed: 00:00:00.00 19:18:28 SQL> col idstr for a20 19:18:33 SQL> select * from arch_tblapp01_14 where id=1001; TSTMP               ,      UNID,        ID,    PARTID,IDSTR --------------------,----------,----------,----------,-------------------- 11/OCT/2023 19:00:24,      1001,      1001,         2,RX 11/OCT/2023 19:15:40,      2001,      1001,       100,RX Elapsed: 00:00:00.01 19:18:36 SQL> select count(1) from main_tblapp01_13 where id=1002;   COUNT(1) ----------          1 Elapsed: 00:00:00.01 19:19:29 SQL> select * from arch_tblapp01_14 where id=1002; TSTMP               ,      UNID,        ID,    PARTID,IDSTR --------------------,----------,----------,----------,-------------------- 11/OCT/2023 19:00:24,      1002,      1002,         3,RXX Elapsed: 00:00:00.00 19:19:47 SQL> delete main_tblapp01_13 where id=1002; 1 row deleted. Elapsed: 00:00:00.01 19:19:59 SQL> commit; Commit complete. Elapsed: 00:00:00.01 19:20:03 SQL> select * from arch_tblapp01_14 where id=1002; TSTMP               ,      UNID,        ID,    PARTID,IDSTR --------------------,----------,----------,----------,-------------------- 11/OCT/2023 19:00:24,      1002,      1002,         3,RXX 11/OCT/2023 19:19:59,      2002,      1002,         3,RXX Elapsed: 00:00:00.00 19:20:06 SQL> select count(1) from main_tblapp01_13 where id=1002;   COUNT(1) ----------          0 Elapsed: 00:00:00.00 19:20:14 SQL> col sequence_name for a30 19:21:36 SQL> col sequence_owner for a30 19:21:47 SQL> select * from user_sequences order by 1; SEQUENCE_NAME                 , MIN_VALUE, MAX_VALUE,INCREMENT_BY,C,O,CACHE_SIZE,LAST_NUMBER,S,E,S,K ------------------------------,----------,----------,------------,-,-,----------,-----------,-,-,-,- SEQAPP01_01                   ,         1,1.0000E+28,           1,N,N,         0,       3997,N,N,N,N SEQAPP01_02                   ,         1,1.0000E+28,           1,N,N,         0,       3997,N,N,N,N SEQARCH_TBLAPP01_10           ,         1,1.0000E+28,           1,N,N,         0,       3002,N,N,N,N SEQARCH_TBLAPP01_12           ,         1,1.0000E+28,           1,N,N,         0,       2002,N,N,N,N SEQARCH_TBLAPP01_14           ,         1,1.0000E+28,           1,N,N,         0,       2003,N,N,N,N SEQMAIN_TBLAPP01_07           ,         1,1.0000E+28,           1,N,N,         0,       2998,N,N,N,N SEQMAIN_TBLAPP01_09           ,         1,1.0000E+28,           1,N,N,         0,       1999,N,N,N,N SEQMAIN_TBLAPP01_11           ,         1,1.0000E+28,           1,N,N,         0,       1999,N,N,N,N SEQMAIN_TBLAPP01_13           ,         1,1.0000E+28,           1,N,N,         0,       1999,N,N,N,N 9 rows selected. Elapsed: 00:00:00.01 19:22:08 SQL> select count(1) from main_tblapp01_13;   COUNT(1) ----------       1996 Elapsed: 00:00:00.00 19:22:59 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------       2002 Elapsed: 00:00:00.00 19:23:13 SQL> insert into main_tblapp01_13 select seqmain_tblapp01_13.nextval,mod(level,10) partid ,rpad('R',mod(level,200),'X') idstr from dual connect by level < 1019:23:17   2  19:23:17   3  19:23:17   4  19:23:17   5  ; 9 rows created. Elapsed: 00:00:00.01 19:23:18 SQL> commit; Commit complete. Elapsed: 00:00:00.00 19:23:25 SQL> select count(1) from main_tblapp01_13;   COUNT(1) ----------       2005 Elapsed: 00:00:00.00 19:23:29 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------       2011 Elapsed: 00:00:00.00 19:23:37 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------       2011 Elapsed: 00:00:00.00 19:23:38 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------       2011 Elapsed: 00:00:00.00 19:23:42 SQL> with t as ( select id,count(1) cnt from arch_tblapp01_14 group by id ) select id,cnt from t where cnt > 1 /19:25:30   2  19:25:30   3  19:25:30   4  19:25:30   5  19:25:30   6         ID,       CNT ----------,----------          5,         2       1001,         2       1002,         2         10,         2 Elapsed: 00:00:00.01 19:25:31 SQL> with t as ( select id,count(1) cnt from arch_tblapp01_14 group by id ) select id,cnt from t where cnt > 1 order by 1 /19:25:46   2  19:25:46   3  19:25:46   4  19:25:46   5  19:25:46   6  19:25:46   7         ID,       CNT ----------,----------          5,         2         10,         2       1001,         2       1002,         2 Elapsed: 00:00:00.01 19:25:47 SQL>

TGT:

SQL> col idstr for a20 SQL> select * from arch_tblapp01_14 where id=10001; no rows selected SQL> SQL> select * from arch_tblapp01_14 where id=1001; TSTMP               ,      UNID,        ID,    PARTID,IDSTR --------------------,----------,----------,----------,-------------------- 11/OCT/2023 19:00:24,      1001,      1001,         2,RX 11/OCT/2023 19:15:40,      2001,      1001,       100,RX SQL> select count(1) from main_tblapp01_13 where id=1002;   COUNT(1) ----------          1 SQL> select * from arch_tblapp01_14 where id=1002; TSTMP               ,      UNID,        ID,    PARTID,IDSTR --------------------,----------,----------,----------,-------------------- 11/OCT/2023 19:00:24,      1002,      1002,         3,RXX SQL> select count(1) from main_tblapp01_13 where id=1002;   COUNT(1) ----------          0 SQL> select * from arch_tblapp01_14 where id=1002; TSTMP               ,      UNID,        ID,    PARTID,IDSTR --------------------,----------,----------,----------,-------------------- 11/OCT/2023 19:00:24,      1002,      1002,         3,RXX 11/OCT/2023 19:19:59,      2002,      1002,         3,RXX SQL> col sequence_name for a30 SQL> col sequence_owner for a30 SQL> select * from user_sequences order by 2; SEQUENCE_NAME                 , MIN_VALUE, MAX_VALUE,INCREMENT_BY,C,O,CACHE_SIZE,LAST_NUMBER,S,E,S,S,K ------------------------------,----------,----------,------------,-,-,----------,-----------,-,-,-,-,- SEQARCH_TBLAPP01_10           ,         1,1.0000E+28,           1,N,N,         0,          1,N,N,N,N,N SEQARCH_TBLAPP01_14           ,         1,1.0000E+28,           1,N,N,         0,          1,N,N,N,N,N SEQARCH_TBLAPP01_12           ,         1,1.0000E+28,           1,N,N,         0,          1,N,N,N,N,N SQL> select * from user_sequences order by 1; SEQUENCE_NAME                 , MIN_VALUE, MAX_VALUE,INCREMENT_BY,C,O,CACHE_SIZE,LAST_NUMBER,S,E,S,S,K ------------------------------,----------,----------,------------,-,-,----------,-----------,-,-,-,-,- SEQARCH_TBLAPP01_10           ,         1,1.0000E+28,           1,N,N,         0,          1,N,N,N,N,N SEQARCH_TBLAPP01_12           ,         1,1.0000E+28,           1,N,N,         0,          1,N,N,N,N,N SEQARCH_TBLAPP01_14           ,         1,1.0000E+28,           1,N,N,         0,          1,N,N,N,N,N SQL> select count(1) from main_tblapp01_13;   COUNT(1) ----------       2005 SQL> select count(1) from arch_tblapp01_14;   COUNT(1) ----------       2012   1  with t as   2  (   3  select id,count(1) cnt from arch_tblapp01_14 group by id   4  )   5  select id,cnt from t where cnt > 1   6* order by 1 SQL> /         ID,       CNT ----------,----------          1,         2 << Handlecollision issue          5,         2         10,         2       1001,         2       1002,         2 SQL>

Replication works – YES

Like we saw: 

handlecollision needs Primary Key (PK) or Not Null Unique Constraint

https://docs.oracle.com/en/middleware/goldengate/core/19.1/reference/handlecollisions-nohandlecollisions.html#GUID-D5A23DE0-9B9F-4497-AF74-30439D55B807

Triggers are disabled by default by the GoldenGate replication.

https://docs.oracle.com/en/middleware/goldengate/core/21.3/reference/dboptions-19.1.html#GUID-BA8C0CED-D87F-4106-862E-4AD22D910160