Source db - LONDON
target db - GERMANY
GG version - 19.1.0.4
DB version - 12.1.0.2
replicated user - us01
table - t3We wan to test scenario where our source db is flashback to a restore point. Now how we should chnage our golden gate setup so that it continue to work.
we will first create the table t3
US01 @ LONDON > create table us01.t3 (a number(10),b number(10),c number(10));
Table created.
US01 @ LONDON > select * from us01.t3;
no rows selected
we will now insert some records on source side
US01 @ LONDON > begin
for i in 1..1000 loop
insert into us01.t3 values (i, 1,1);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SYS @ LONDON > select count(*) from us01.t3;
COUNT(*)
----------
1000verify if records populated on stragte side via gg replication
US01 @ GERMANY > select count(*) from us01.t3;
COUNT(*)
----------
1000
create restore point
first create restorepoint in target then in source
on target db – GERMANY
create restore point CLEAN_DB1 guarantee flashback database;
select * from v$restore_point;
SYS @ GERMANY > create restore point CLEAN_DB1 guarantee flashback database;
Restore point created.
SYS @ GERMANY > set lines 300
SYS @ GERMANY > select * from v$restore_point;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE
---------- --------------------- --- ------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---
NAME CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
365647 1 YES 52428800 01-OCT-20 03.38.34.000000000 PM YES
CLEAN_DB1 0
on source db – LONDON
SYS @ LONDON > create restore point CLEAN_DB1 guarantee flashback database;
Restore point created.
SYS @ LONDON > set lines 300
SYS @ LONDON > select * from v$restore_point;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE
---------- --------------------- --- ------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---
NAME CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
672855 1 YES 52428800 01-OCT-20 03.39.42.000000000 PM YES
CLEAN_DB1 0
insert more rows on source
SYS @ LONDON > begin
for i in 1001..1500 loop
insert into us01.t3 values (i, 1,1);
commit;
end loop;
end;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SYS @ LONDON > select count(*) from us01.t3;
COUNT(*)
----------
1500verify new rows are also there in target
SYS @ GERMANY > select count(*) from us01.t3;
COUNT(*)
----------
1500
SYS @ GERMANY >flashback source database LONDON
SYS @ LONDON > shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ LONDON > startup mount
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 2932336 bytes
Variable Size 696254864 bytes
Database Buffers 343932928 bytes
Redo Buffers 5455872 bytes
Database mounted.
SYS @ LONDON > flashback database to restore point CLEAN_DB1;
Flashback complete.
SYS @ LONDON > alter database open resetlogs;
Database altered.verify row count is back to 1000 on source
SYS @ LONDON > select count(*) from us01.t3;
COUNT(*)
----------
1000archive sequence set to 1
SYS @ LONDON > archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
modify extract
check current status of extract it is abended (notice the scn)
GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 17> info E_ICR01, detail
EXTRACT E_ICR01 Last Started 2020-10-01 16:48 Status ABENDED
Checkpoint Lag 00:00:09 (updated 00:01:52 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
2020-10-02 11:32:24
SCN 0.775910 (775910)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/home/oracle/EXTRACT/LONDON/EA 1 77888 10 EXTTRAIL
Integrated Extract outbound server first scn: Unavailable.
Integrated Extract outbound server filtering start scn: Unavailable.
Extract Source Begin End
Not Available * Initialized * 2020-10-02 11:32
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available 2020-10-01 13:14 2020-10-01 15:53
Not Available * Initialized * 2020-10-01 13:14
Not Available * Initialized * 2020-10-01 13:14
Not Available * Initialized * 2020-10-01 13:14
Current directory /home/oracle/GG_LONDON
Report file /home/oracle/GG_LONDON/dirrpt/E_ICR01.rpt
Parameter file /home/oracle/GG_LONDON/dirprm/e_icr01.prm
Checkpoint file /home/oracle/GG_LONDON/dirchk/E_ICR01.cpe
Process file
Error log /home/oracle/GG_LONDON/ggserr.logalter extract to scn which was for guaranteed restorepoint CLEAN_DB1
GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 2> ALTER EXTRACT E_ICR01, SCN 672855
EXTRACT altered.
check the status of extract (notice the scn)
GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 3> info E_ICR01, detail
EXTRACT E_ICR01 Initialized 2020-10-02 11:40 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:41 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
First Record
SCN 0.672855 (672855)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/home/oracle/EXTRACT/LONDON/EA 1 77888 10 EXTTRAIL
Integrated Extract outbound server first scn: 0.639742 (639742)
Integrated Extract outbound server filtering start scn: 0.639742 (639742)
Extract Source Begin End
Not Available * Initialized * First Record
Not Available * Initialized * 2020-10-02 11:32
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available 2020-10-01 13:14 2020-10-01 15:53
Not Available * Initialized * 2020-10-01 13:14
Not Available * Initialized * 2020-10-01 13:14
Not Available * Initialized * 2020-10-01 13:14
Current directory /home/oracle/GG_LONDON
Report file /home/oracle/GG_LONDON/dirrpt/E_ICR01.rpt
Parameter file /home/oracle/GG_LONDON/dirprm/e_icr01.prm
Checkpoint file /home/oracle/GG_LONDON/dirchk/E_ICR01.cpe
Process file
Error log /home/oracle/GG_LONDON/ggserr.log
now rollover the extract
GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 4> alter EXTRACT E_ICR01 etrollover
2020-10-02 11:41:04 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
EXTRACT altered.
check the extcat status (notice the seq no – 2)
GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 5> info E_ICR01, detail
EXTRACT E_ICR01 Initialized 2020-10-02 11:40 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:13 ago)
Log Read Checkpoint Oracle Integrated Redo Logs
First Record
SCN 0.672855 (672855)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/home/oracle/EXTRACT/LONDON/EA 2 0 10 EXTTRAIL
Integrated Extract outbound server first scn: 0.639742 (639742)
Integrated Extract outbound server filtering start scn: 0.639742 (639742)
Extract Source Begin End
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * 2020-10-02 11:32
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available * Initialized * First Record
Not Available 2020-10-01 13:14 2020-10-01 15:53
Not Available * Initialized * 2020-10-01 13:14
Not Available * Initialized * 2020-10-01 13:14
Not Available * Initialized * 2020-10-01 13:14
Current directory /home/oracle/GG_LONDON
Report file /home/oracle/GG_LONDON/dirrpt/E_ICR01.rpt
Parameter file /home/oracle/GG_LONDON/dirprm/e_icr01.prm
Checkpoint file /home/oracle/GG_LONDON/dirchk/E_ICR01.cpe
Process file
Error log /home/oracle/GG_LONDON/ggserr.log
start the extract
GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 6> start extract *
Sending START request to MANAGER ...
EXTRACT E_ICR01 starting
GGSCI (oel6-node02.localdomain as ggadmin@LONDON) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E_ICR01 00:00:00 00:01:54change replicat
stop replicat
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_ICJ01 00:00:00 00:00:04
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 15> stop replicat *
Sending STOP request to REPLICAT R_ICJ01 ...
Request processed.
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED R_ICJ01 00:00:00 00:00:02
flashback target
SYS @ GERMANY > shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ GERMANY > startup mount
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 2932336 bytes
Variable Size 696254864 bytes
Database Buffers 343932928 bytes
Redo Buffers 5455872 bytes
Database mounted.
SYS @ GERMANY > flashback database to restore point CLEAN_DB1;
Flashback complete.
SYS @ GERMANY > alter database open resetlogs;
Database altered.
check the count in target it is back to 1000
SYS @ GERMANY > select count(*) from us01.t3;
COUNT(*)
----------
1000modify the replicat
add this paremeter
aGROUPTRANSOPS 1
MAP us01.*, target us01.*;
TABLE us01.t3, FILTER ( @GETENV('TRANSACTION','CSN') > 672855 ), EVENTACTIONS (abort);
672855 is the scn of source restorepoint CLEAN_DB1
parameter file looks like this
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 8> view params R_ICJ01
Replicat R_ICJ01
SETENV(ORACLE_HOME="/u01/app/oracle/product/12.1.0.2/db_1")
SETENV(ORACLE_SID = "GERMNAY")
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, append, megabytes 500
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
SOURCECHARSET PASSTHRU
UserID ggadmin@GERMANY, Password ggadmin
--showsyntax
REPERROR (1403, DISCARD)
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
--map us01.*, target us01.*;
GROUPTRANSOPS 1
MAP us01.*, target us01.*;
TABLE us01.t3, FILTER ( @GETENV('TRANSACTION','CSN') > 672855 ), EVENTACTIONS (abort);
start the replicat. It will start and run and then will abend at that scn
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED R_ICJ01 00:00:00 00:30:35
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 4> start replicat *
Sending START request to MANAGER ...
REPLICAT R_ICJ01 starting
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_ICJ01 00:00:00 00:30:53
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED R_ICJ01 00:00:00 00:31:34
from the ggserr.log we can see that replicat is abended at scn 672855
2020-10-02T11:58:12.879+0530 INFO OGG-02527 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: Integrated Replicat does not populate a trace table.
2020-10-02T11:58:18.804+0530 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: REPLICAT R_ICJ01 started.
2020-10-02T11:58:20.101+0530 INFO OGG-02530 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: Integrated replicat successfully attached to inbound server OGG$R_ICJ01.
2020-10-02T11:58:20.190+0530 INFO OGG-02243 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: Opened trail file /home/oracle/EXTRACT/LONDON/EA000000000 at 2020-10-02 11:58:20.190568.
2020-10-02T11:58:20.190+0530 WARNING OGG-02760 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: ASSUMETARGETDEFS is ignored because trail file /home/oracle/EXTRACT/LONDON/EA000000000 contains table definitions.
2020-10-02T11:58:20.190+0530 INFO OGG-03506 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: The source database character set, as determined from the trail file, is UTF-8.
2020-10-02T11:58:20.190+0530 INFO OGG-06506 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: Wildcard MAP resolved (entry us01.*): MAP "US01"."T3", target us01."T3".
2020-10-02T11:58:23.276+0530 WARNING OGG-06439 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: No unique key is defined for table T3. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2020-10-02T11:58:23.277+0530 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: The definition for table US01.T3 is obtained from the trail file.
2020-10-02T11:58:23.277+0530 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: Using following columns in default map by name: A, B, C.
2020-10-02T11:58:23.302+0530 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: Using the following key columns for target table US01.T3: A, B, C.
2020-10-02T11:58:23.459+0530 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: MAP resolved (entry us01.t3): TABLE "US01"."T3", FILTER ( @GETENV('TRANSACTION','CSN') > 672855 ), EVENTACTIONS (abort).
2020-10-02T11:58:26.228+0530 ERROR OGG-01289 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: Aborting process due to ABORT event for source table US01.T3 in file /home/oracle/EXTRACT/LONDON/EA000000000, RBA 387704.
2020-10-02T11:58:32.491+0530 INFO OGG-02333 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: Reading /home/oracle/EXTRACT/LONDON/EA000000000, current RBA 387,704, 2 records, m_file_seqno = 0, m_file_rba = 387,856.
2020-10-02T11:58:32.505+0530 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, r_icj01.prm: PROCESS ABENDING.
check replicat status
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 11> info R_ICJ01
REPLICAT R_ICJ01 Last Started 2020-10-02 11:58 Status ABENDED
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:16:19 ago)
Log Read Checkpoint File /home/oracle/EXTRACT/LONDON/EA000000000
2020-10-01 15:38:03.000000 RBA 387704
now we will alter replicat to seq – 2 ( which we got from extract info , details)
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 12> alter REPLICAT R_ICJ01 extseqno 2, extrba 0
2020-10-02 12:05:57 INFO OGG-06594 Replicat R_ICJ01 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start R_ICJ01 with NOFILTERDUPTRANSACTIONS option.
REPLICAT (Integrated) altered.
edit replicat and comment out the lines added
Replicat R_ICJ01
SETENV(ORACLE_HOME="/u01/app/oracle/product/12.1.0.2/db_1")
SETENV(ORACLE_SID = "GERMNAY")
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, append, megabytes 500
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
SOURCECHARSET PASSTHRU
UserID ggadmin@GERMANY, Password ggadmin
--showsyntax
REPERROR (1403, DISCARD)
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
map us01.*, target us01.*;
--GROUPTRANSOPS 1
--MAP us01.*, target us01.*;
--TABLE us01.t3, FILTER ( @GETENV('TRANSACTION','CSN') > 672855 ), EVENTACTIONS (abort);
start the replicat
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 12> start replicat *
Sending START request to MANAGER ...
REPLICAT R_ICJ01 starting
GGSCI (oel6-node02.localdomain as ggadmin@GERMANY) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_ICJ01 00:00:00 00:00:06
test replication again
insert reocrds in source and verify they replicated to target
SYS @ LONDON > begin
for i in 1001..1500 loop
insert into us01.t3 values (i, 1,1);
commit;
end loop;
end;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SYS @ LONDON > select count(*) from us01.t3;
COUNT(*)
----------
1500
SYS @ GERMANY > select count(*) from us01.t3;
COUNT(*)
----------
1500
DBATracker 