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:
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:
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:
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:
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:
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:
Step 10) check lag
lag replicat rpt1
lag reaches EOF
Step 11) stop replicat & remove the handlecollisions param
Step 12) start replicat
Further test:
Overall result:
Source:
TGT:
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
