From Oracle GoldenGate (OGG) version 12.2 and above, there is a transparent integration of OGG with Oracle Data Pump as explained in the Document ID 1276058.1.
The CSN for each table is captured on an Oracle Data Pump export. The CSN is then applied to system tables and views on the target database on the import. These views and system tables are referenced by Replicat when applying data to target database.
This 12.2 feature, no longer requires administrators to know what CSN number Replicat should be started with. Replicat will handle it automatically when the Replicat Parameter DBOPTIONS ENABLE_INSTANTATION_FILTERING is enabled. It also avoids the need of specification of individual MAP for each imported table with the @FILTER(@GETENV(‘TRANSACTION’,’CSN’) or HANDLECOLLISIONS clause.
Let’s see how it works :
Create a new schema DEMO and a new table into the source database :
oracle@ora-gg-s-2: [DB1] sqlplus / as sysdba SQL> grant create session to DEMO identified by toto; Grant succeeded. SQL> grant resource to DEMO; Grant succeeded. SQL> alter user demo quota unlimited on users; User altered. SQL> create table DEMO.ADDRESSES as select * from SOE.ADDRESSES; Table created.
Stop the Extract process :
oracle@ora-gg-s-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1] ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.200414 OGGCORE_19.1.0.0.0OGGBP_PLATFORMS_200427.2331_FBO Linux, x64, 64bit (optimized), Oracle 19c on Apr 28 2020 17:41:48 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI (ora-gg-s-2) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTRSOE 00:00:00 00:00:08 EXTRACT RUNNING PUMPSOE 00:00:00 00:00:01 GGSCI (ora-gg-s-2) 2> stop extract * Sending STOP request to EXTRACT EXTRSOE ... Request processed. Sending STOP request to EXTRACT PUMPSOE ... Request processed. GGSCI (ora-gg-s-2) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EXTRSOE 00:00:00 00:00:07 EXTRACT STOPPED PUMPSOE 00:00:00 00:00:07 GGSCI (ora-gg-s-2) 5>
Stop the Replicat process :
GGSCI (ora-gg-t-2) 4> stop replicat replsoe Sending STOP request to REPLICAT REPLSOE ... GGSCI (ora-gg-t-2) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED REPLSOE 00:00:00 00:00:00 GGSCI (ora-gg-t-2) 9>
Edit Extract process and add the new table :
GGSCI (ora-gg-s-2) 1> edit params EXTRSOE Table DEMO.ADDRESSES; GGSCI (ora-gg-s-2) 1> edit params PUMPSOE Table DEMO.ADDRESSES;
Add schematrandata for the schema DEMO:
GGSCI (ora-gg-s-2) 5> dblogin useridalias ggadmin Successfully logged into database. GGSCI (ora-gg-s-2 as ggadmin@DB1) 6> add schematrandata DEMO 2020-08-19 21:25:47 INFO OGG-01788 SCHEMATRANDATA has been added on schema "DEMO". 2020-08-19 21:25:47 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "DEMO". 2020-08-19 21:25:47 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "DEMO". 2020-08-19 21:25:49 INFO OGG-10471 ***** Oracle Goldengate support information on table DEMO.ADDRESSES ***** Oracle Goldengate support native capture on table DEMO.ADDRESSES. Oracle Goldengate marked following column as key columns on table DEMO.ADDRESSES: ADDRESS_ID, CUSTOMER_ID, DATE_CREATED, HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY, POST_CODE, ZIP_CODE No unique key is defined for table DEMO.ADDRESSES. GGSCI (ora-gg-s-2 as ggadmin@DB1) 7> info schematrandata DEMO 2020-08-19 21:25:54 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "DEMO". 2020-08-19 21:25:54 INFO OGG-01980 Schema level supplemental logging is enabled on schema "DEMO" for all scheduling columns. 2020-08-19 21:25:54 INFO OGG-10462 Schema "DEMO" have 1 prepared tables for instantiation. GGSCI (ora-gg-s-2 as ggadmin@DB1) 8>
Source system tables are automatically prepared when issuing the command ADD TRANDATA / ADD SCHEMATRANDATA
Start and check the extract :
GGSCI (ora-gg-s-2 as ggadmin@DB1) 8> start extract * Sending START request to MANAGER ... EXTRACT EXTRSOE starting Sending START request to MANAGER ... EXTRACT PUMPSOE starting GGSCI (ora-gg-s-2 as ggadmin@DB1) 9> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTRSOE 00:00:00 00:19:51 EXTRACT RUNNING PUMPSOE 00:00:00 00:19:51 GGSCI (ora-gg-s-2 as ggadmin@DB1) 10> ! info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTRSOE 00:00:00 00:00:00 EXTRACT RUNNING PUMPSOE 00:00:00 00:00:01
Let’s do an update to the source table DEMO.ADDRESSES :
oracle@ora-gg-s-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1] sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 21:34:19 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> update DEMO.ADDRESSES set STREET_NAME= 'Demo Street is open' where ADDRESS_ID=1000; 1 row updated. SQL> commit; Commit complete.
Let’s export the DEMO schema :
oracle@ora-gg-s-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1] expdp "'/ as sysdba'" dumpfile=export_tables_DEMO.dmp > logfile=export_tables_DEMO.log > schemas=demo > Export: Release 19.0.0.0.0 - Production on Wed Aug 19 21:37:09 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" dumpfile=export_tables_DEMO.dmp logfile=export_tables_DEMO.log schemas=demo Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE Processing object type SCHEMA_EXPORT/TABLE/TABLE . . exported "DEMO"."ADDRESSES" 35.24 MB 479277 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/DB1/dpdump/export_tables_DEMO.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 19 21:37:43 2020 elapsed 0 00:00:28 oracle@ora-gg-s-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1]
The dba_capture_prepared_tables does not get populated till the first export of the tables. The scn is the smallest system change number (SCN) for which the table can be instantiated. It is not the export SCN.
SQL> select table_name, scn from dba_capture_prepared_tables where table_owner = 'DEMO' ; TABLE_NAME SCN -------------------- ADDRESSES 2989419
Let’s copy the dump file to target database :
oracle@ora-gg-s-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1] scp > /u01/app/oracle/admin/DB1/dpdump/export_tables_DEMO.dmp > oracle@ora-gg-t-2:/u01/app/oracle/admin/DB2/dpdump oracle@ora-gg-t-2's password: export_tables_DEMO.dmp 100% 36MB 120.8MB/s 00:00 oracle@ora-gg-s-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1]
Let’s import the new table into target database :
oracle@ora-gg-t-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB2] impdp system/manager > dumpfile=export_tables_DEMO.dmp > logfile=impdemo_tables.log > Import: Release 19.0.0.0.0 - Production on Wed Aug 19 21:45:18 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=export_tables_DEMO.dmp logfile=impdemo_tables.log Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "DEMO"."ADDRESSES" 35.24 MB 479277 rows Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 19 21:45:46 2020 elapsed 0 00:00:26 oracle@ora-gg-t-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB2]
Datapump import will populate system tables and views with instantiation CSNs :
SQL> select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects where source_object_owner = 'DEMO' ; SOURCE_OBJECT_NAME INSTANTIATION_SCN IGNORE_SCN ----------------------------------------------- ADDRESSES 2995590
Let’s update the table source :
oracle@ora-gg-s-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1] sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 21:48:33 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> update DEMO.ADDRESSES set STREET_NAME= 'Demo Street is open' where ADDRESS_ID=1001; 1 row updated. SQL> commit; Commit complete.
Let’s check transactions occured into source table DEMO.ADDRESSES :
oracle@ora-gg-s-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1] ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.200414 OGGCORE_19.1.0.0.0OGGBP_PLATFORMS_200427.2331_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Apr 28 2020 17:41:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (ora-gg-s-2) 1> stats extract extrsoe table DEMO.ADDRESSES
Sending STATS request to EXTRACT EXTRSOE ...
Start of Statistics at 2020-08-19 21:50:15.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 0.00
Mapped operations 0.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to /u11/app/goldengate/data/DB1/es:
Extracting from DEMO.ADDRESSES to DEMO.ADDRESSES:
*** Total statistics since 2020-08-19 21:34:35 ***
Total inserts 0.00
Total updates 2.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2020-08-19 21:34:35 ***
Total inserts 0.00
Total updates 2.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2020-08-19 21:34:35 ***
Total inserts 0.00
Total updates 2.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2020-08-19 21:34:35 ***
Total inserts 0.00
Total updates 2.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
Let’s modify the replicat parameter file to add MAP statement for the new table DEMO.ADDRESSES plus the parameter DBOPTIONS ENABLE_INSTANTATION_FILTERING.
Replicat REPLSOE
DBOPTIONS INTEGRATEDPARAMS ( parallelism 6 )
DISCARDFILE /u10/app/goldengate/product/19.1.0.0.4/gg_1/dirrpt/REPLSOE_discard.txt, append, megabytes 10
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
USERIDALIAS ggadmin
MAP SOE.*, TARGET SOE.* ;
--MAP DEMO.ADDRESSES ,TARGET DEMO.ADDRESSES,FILTER ( @GETENV ('TRANSACTION', 'CSN') > 2908627) ;
MAP DEMO.ADDRESSES, TARGET DEMO.ADDRESSES;
I commented the old method where we should mention the CSN used by the export. Now with DBOPTIONS ENABLE_INSTANTATION_FILTERING, there is no need to mention the CSN.
Let’s start the replicat process :
GGSCI (ora-gg-t-2) 2> start replicat REPLSOE Sending START request to MANAGER ... REPLICAT REPLSOE starting GGSCI (ora-gg-t-2) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPLSOE 00:00:00 00:41:37 GGSCI (ora-gg-t-2) 4> ! info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPLSOE 00:00:00 00:00:01
Start replicat, who will query instantiation CSN on any new mapping and filter records accordingly Filters out DDL and DML records based on each table’s instantiation CSN . Output in the report file will show the table name and to what CSN the replicat will start applying data :
2020-08-19 21:56:05 INFO OGG-10155 Instantiation CSN filtering is enabled on table DEMO.ADDRESSES at CSN 2,995,59 0.
Let’s wait the lag resolved and let’s check the transaction occured into table DEMO.ADDRESSES:
GGSCI (ora-gg-t-2) 6> stats replicat REPLSOE ,table demo.addresses
Sending STATS request to REPLICAT REPLSOE ...
Start of Statistics at 2020-08-19 21:58:32.
Integrated Replicat Statistics:
Total transactions 1.00
Redirected 0.00
Replicated procedures 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Operation type functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from DEMO.ADDRESSES to DEMO.ADDRESSES:
*** Total statistics since 2020-08-19 21:56:05 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2020-08-19 21:56:05 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2020-08-19 21:56:05 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2020-08-19 21:56:05 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
Let’s check if data are synchronized :
Into source :
oracle@ora-gg-s-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 22:00:52 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> select street_name from demo.ADDRESSES where ADDRESS_ID=1000; STREET_NAME ------------------------------------------------------------ Demo Street is open SQL> select street_name from demo.ADDRESSES where ADDRESS_ID=1001; STREET_NAME ------------------------------------------------------------ Demo Street is open
Into target :
oracle@ora-gg-t-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB2] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 22:02:44 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> select street_name from demo.ADDRESSES where ADDRESS_ID=1000; STREET_NAME ------------------------------------------------------------ Demo Street is open SQL> select street_name from demo.ADDRESSES where ADDRESS_ID=1001; STREET_NAME ------------------------------------------------------------ Demo Street is open
Both table DEMO.ADDRESSES on source database and target database has identical data.
DBOPTIONS ENABLE_INSTANTIATION_FILTERING is no longer required:
GGSCI (ora-gg-t-2)> edit params REPLSOE ... MAP demo.addresses ,TARGET demo.addresses;
Restart the replicat :
GGSCI (ora-gg-t-2) 9> stop replicat replsoe Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STOPPED REPLSOE 00:00:00 00:00:01 GGSCI (ora-gg-t-2) 10> start replicat replsoe Sending START request to MANAGER ... REPLICAT REPLSOE starting GGSCI (ora-gg-t-2) 11> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPLSOE 00:00:00 00:00:00
Let’s doing a last test :
Update on the source table :
SQL> update DEMO.ADDRESSES set STREET_NAME= 'test 1' where ADDRESS_ID=800; 1 row updated. SQL> commit; Commit complete.
Let’s check the target database :
oracle@ora-gg-t-2:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB2] sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 22:11:24 2020 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> select street_name from demo.ADDRESSES where ADDRESS_ID=800; STREET_NAME ------------------------------------------------------------ test 1
Conclusion :
- The parameter DBOPTIONS ENABLE_INSTANTATION_FILTERING avoid for the Golden Gate administrator to find the CSN used for the inital load.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)