{"id":14554,"date":"2020-08-19T20:46:41","date_gmt":"2020-08-19T18:46:41","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/"},"modified":"2020-08-19T20:46:41","modified_gmt":"2020-08-19T18:46:41","slug":"oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/","title":{"rendered":"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate"},"content":{"rendered":"<p>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.<\/p>\n<p>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.<\/p>\n<p>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\u00a0 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(&#8216;TRANSACTION&#8217;,&#8217;CSN&#8217;) or HANDLECOLLISIONS clause.<\/p>\n<p>Let&#8217;s see how it works :<\/p>\n<p>Create a new schema DEMO and a new table into the source database :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">oracle@ora-gg-s-2: [DB1] sqlplus \/ as sysdba\nSQL&gt; grant create session to DEMO identified by toto;\n\nGrant succeeded.\n\nSQL&gt; grant resource to DEMO;\n\nGrant succeeded.\n\nSQL&gt; alter user demo quota unlimited on users;\n\nUser altered.\n\nSQL&gt; create table DEMO.ADDRESSES as select * from SOE.ADDRESSES;\n\nTable created.<\/pre>\n<p>Stop the Extract process :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">oracle@ora-gg-s-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB1] .\/ggsci\n\nOracle GoldenGate Command Interpreter for Oracle\nVersion 19.1.0.0.200414 OGGCORE_19.1.0.0.0OGGBP_PLATFORMS_200427.2331_FBO\nLinux, x64, 64bit (optimized), Oracle 19c on Apr 28 2020 17:41:48\nOperating system character set identified as UTF-8.\n\nCopyright (C) 1995, 2019, Oracle and\/or its affiliates. All rights reserved.\n\n\n\nGGSCI (ora-gg-s-2) 1&gt; info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nEXTRACT     RUNNING     EXTRSOE     00:00:00      00:00:08\nEXTRACT     RUNNING     PUMPSOE     00:00:00      00:00:01\n\n\nGGSCI (ora-gg-s-2) 2&gt; stop extract *\n\nSending STOP request to EXTRACT EXTRSOE ...\nRequest processed.\n\nSending STOP request to EXTRACT PUMPSOE ...\nRequest processed.\n\nGGSCI (ora-gg-s-2) 4&gt; info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nEXTRACT     STOPPED     EXTRSOE     00:00:00      00:00:07\nEXTRACT     STOPPED     PUMPSOE     00:00:00      00:00:07\n\n\nGGSCI (ora-gg-s-2) 5&gt;\n\n<\/pre>\n<p>Stop the Replicat process :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">GGSCI (ora-gg-t-2) 4&gt; stop replicat replsoe\n\nSending STOP request to REPLICAT REPLSOE ...\n\nGGSCI (ora-gg-t-2) 5&gt; info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nREPLICAT    STOPPED     REPLSOE     00:00:00      00:00:00\n\n\nGGSCI (ora-gg-t-2) 9&gt;\n<\/pre>\n<p>Edit Extract process and add the new table :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">GGSCI (ora-gg-s-2) 1&gt; edit params EXTRSOE\nTable DEMO.ADDRESSES;\nGGSCI (ora-gg-s-2) 1&gt; edit params PUMPSOE\nTable DEMO.ADDRESSES;<\/pre>\n<p>Add schematrandata for the schema DEMO:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">GGSCI (ora-gg-s-2) 5&gt; dblogin useridalias ggadmin\nSuccessfully logged into database.\n\nGGSCI (ora-gg-s-2 as ggadmin@DB1) 6&gt; add schematrandata DEMO\n\n2020-08-19 21:25:47  INFO    OGG-01788  SCHEMATRANDATA has been added on schema \"DEMO\".\n\n2020-08-19 21:25:47  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema \"DEMO\".\n\n2020-08-19 21:25:47  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema \"DEMO\".\n\n2020-08-19 21:25:49  INFO    OGG-10471  ***** Oracle Goldengate support information on table DEMO.ADDRESSES *****\nOracle Goldengate support native capture on table DEMO.ADDRESSES.\nOracle 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\nNo unique key is defined for table DEMO.ADDRESSES.\n\nGGSCI (ora-gg-s-2 as ggadmin@DB1) 7&gt; info schematrandata DEMO\n\n2020-08-19 21:25:54  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema \"DEMO\".\n\n2020-08-19 21:25:54  INFO    OGG-01980  Schema level supplemental logging is enabled on schema \"DEMO\" for all scheduling columns.\n\n2020-08-19 21:25:54  INFO    OGG-10462  Schema \"DEMO\" have 1 prepared tables for instantiation.\n\nGGSCI (ora-gg-s-2 as ggadmin@DB1) 8&gt;<\/pre>\n<p>Source system tables are automatically prepared when issuing the command ADD TRANDATA \/ ADD SCHEMATRANDATA<\/p>\n<p>Start and check the extract :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">GGSCI (ora-gg-s-2 as ggadmin@DB1) 8&gt; start extract *\n\nSending START request to MANAGER ...\nEXTRACT EXTRSOE starting\n\nSending START request to MANAGER ...\nEXTRACT PUMPSOE starting\n\n\nGGSCI (ora-gg-s-2 as ggadmin@DB1) 9&gt; info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nEXTRACT     RUNNING     EXTRSOE     00:00:00      00:19:51\nEXTRACT     RUNNING     PUMPSOE     00:00:00      00:19:51\n\n\nGGSCI (ora-gg-s-2 as ggadmin@DB1) 10&gt; !\ninfo all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nEXTRACT     RUNNING     EXTRSOE     00:00:00      00:00:00\nEXTRACT     RUNNING     PUMPSOE     00:00:00      00:00:01<\/pre>\n<p>Let&#8217;s do an update to the source table DEMO.ADDRESSES :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">oracle@ora-gg-s-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB1] sqlplus \/ as sysdba\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 21:34:19 2020\nVersion 19.4.0.0.0\n\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.4.0.0.0\n\nSQL&gt; update DEMO.ADDRESSES set STREET_NAME= 'Demo Street is open' where ADDRESS_ID=1000;\n\n1 row updated.\n\nSQL&gt; commit;\n\nCommit complete.<\/pre>\n<p>Let&#8217;s\u00a0 export the DEMO schema :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">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 \n&gt; logfile=export_tables_DEMO.log \n&gt; schemas=demo \n&gt;\n\nExport: Release 19.0.0.0.0 - Production on Wed Aug 19 21:37:09 2020\nVersion 19.4.0.0.0\n\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\nPassword:\n\nConnected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nFLASHBACK automatically enabled to preserve database integrity.\nStarting \"SYS\".\"SYS_EXPORT_SCHEMA_01\":  \"\/******** AS SYSDBA\" dumpfile=export_tables_DEMO.dmp logfile=export_tables_DEMO.log schemas=demo\nProcessing object type SCHEMA_EXPORT\/TABLE\/TABLE_DATA\nProcessing object type SCHEMA_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\nProcessing object type SCHEMA_EXPORT\/STATISTICS\/MARKER\nProcessing object type SCHEMA_EXPORT\/USER\nProcessing object type SCHEMA_EXPORT\/SYSTEM_GRANT\nProcessing object type SCHEMA_EXPORT\/ROLE_GRANT\nProcessing object type SCHEMA_EXPORT\/DEFAULT_ROLE\nProcessing object type SCHEMA_EXPORT\/TABLESPACE_QUOTA\nProcessing object type SCHEMA_EXPORT\/PRE_SCHEMA\/PROCACT_SCHEMA\nProcessing object type SCHEMA_EXPORT\/TABLE\/PROCACT_INSTANCE\nProcessing object type SCHEMA_EXPORT\/TABLE\/TABLE\n. . exported \"DEMO\".\"ADDRESSES\"                          35.24 MB  479277 rows\nMaster table \"SYS\".\"SYS_EXPORT_SCHEMA_01\" successfully loaded\/unloaded\n******************************************************************************\nDump file set for SYS.SYS_EXPORT_SCHEMA_01 is:\n  \/u01\/app\/oracle\/admin\/DB1\/dpdump\/export_tables_DEMO.dmp\nJob \"SYS\".\"SYS_EXPORT_SCHEMA_01\" successfully completed at Wed Aug 19 21:37:43 2020 elapsed 0 00:00:28\n\noracle@ora-gg-s-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB1]<\/pre>\n<p>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.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name, scn from dba_capture_prepared_tables where table_owner = 'DEMO' ;\n\nTABLE_NAME   SCN\n--------------------\nADDRESSES    2989419<\/pre>\n<p>Let&#8217;s copy the dump file to target database :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">oracle@ora-gg-s-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB1] scp \n&gt; \/u01\/app\/oracle\/admin\/DB1\/dpdump\/export_tables_DEMO.dmp \n&gt; oracle@ora-gg-t-2:\/u01\/app\/oracle\/admin\/DB2\/dpdump\noracle@ora-gg-t-2's password:\nexport_tables_DEMO.dmp                                                            100%   36MB 120.8MB\/s   00:00\noracle@ora-gg-s-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB1]<\/pre>\n<p>Let&#8217;s\u00a0 import the new table into target database :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">oracle@ora-gg-t-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB2] impdp system\/manager \n&gt; dumpfile=export_tables_DEMO.dmp \n&gt; logfile=impdemo_tables.log \n&gt;\n\nImport: Release 19.0.0.0.0 - Production on Wed Aug 19 21:45:18 2020\nVersion 19.4.0.0.0\n\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\n\nConnected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nMaster table \"SYSTEM\".\"SYS_IMPORT_FULL_01\" successfully loaded\/unloaded\nStarting \"SYSTEM\".\"SYS_IMPORT_FULL_01\":  system\/******** dumpfile=export_tables_DEMO.dmp logfile=impdemo_tables.log\nProcessing object type SCHEMA_EXPORT\/USER\nProcessing object type SCHEMA_EXPORT\/SYSTEM_GRANT\nProcessing object type SCHEMA_EXPORT\/ROLE_GRANT\nProcessing object type SCHEMA_EXPORT\/DEFAULT_ROLE\nProcessing object type SCHEMA_EXPORT\/TABLESPACE_QUOTA\nProcessing object type SCHEMA_EXPORT\/PRE_SCHEMA\/PROCACT_SCHEMA\nProcessing object type SCHEMA_EXPORT\/TABLE\/PROCACT_INSTANCE\nProcessing object type SCHEMA_EXPORT\/TABLE\/TABLE\nProcessing object type SCHEMA_EXPORT\/TABLE\/TABLE_DATA\n. . imported \"DEMO\".\"ADDRESSES\"                          35.24 MB  479277 rows\nProcessing object type SCHEMA_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\nProcessing object type SCHEMA_EXPORT\/STATISTICS\/MARKER\nJob \"SYSTEM\".\"SYS_IMPORT_FULL_01\" successfully completed at Wed Aug 19 21:45:46 2020 elapsed 0 00:00:26\n\noracle@ora-gg-t-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB2]<\/pre>\n<p>Datapump import will populate system tables and views with instantiation CSNs :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects where source_object_owner = 'DEMO' ;\n\nSOURCE_OBJECT_NAME INSTANTIATION_SCN IGNORE_SCN\n-----------------------------------------------\nADDRESSES          2995590<\/pre>\n<p>Let&#8217;s update the table source :<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">oracle@ora-gg-s-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB1] sqlplus \/ as sysdba\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 21:48:33 2020\nVersion 19.4.0.0.0\n\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.4.0.0.0\n\nSQL&gt; update DEMO.ADDRESSES set STREET_NAME= 'Demo Street is open' where ADDRESS_ID=1001;\n\n1 row updated.\n\nSQL&gt; commit;\n\nCommit complete.<\/pre>\n<p>Let&#8217;s check transactions occured into source table DEMO.ADDRESSES :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">oracle@ora-gg-s-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB1] .\/ggsci\n\nOracle GoldenGate Command Interpreter for Oracle\nVersion 19.1.0.0.200414 OGGCORE_19.1.0.0.0OGGBP_PLATFORMS_200427.2331_FBO\nLinux, x64, 64bit (optimized), Oracle 19c on Apr 28 2020 17:41:48\nOperating system character set identified as UTF-8.\n\nCopyright (C) 1995, 2019, Oracle and\/or its affiliates. All rights reserved.\n\nGGSCI (ora-gg-s-2) 1&gt; stats extract extrsoe table DEMO.ADDRESSES\n\nSending STATS request to EXTRACT EXTRSOE ...\n\nStart of Statistics at 2020-08-19 21:50:15.\n\nDDL replication statistics (for all trails):\n\n*** Total statistics since extract started     ***\n        Operations                                         0.00\n        Mapped operations                                  0.00\n        Unmapped operations                                0.00\n        Other operations                                   0.00\n        Excluded operations                                0.00\n\nOutput to \/u11\/app\/goldengate\/data\/DB1\/es:\n\nExtracting from DEMO.ADDRESSES to DEMO.ADDRESSES:\n\n*** Total statistics since 2020-08-19 21:34:35 ***\n        Total inserts                                      0.00\n        Total updates                                      2.00\n        Total deletes                                      0.00\n        Total upserts                                      0.00\n        Total discards                                     0.00\n        Total operations                                   2.00\n\n*** Daily statistics since 2020-08-19 21:34:35 ***\n        Total inserts                                      0.00\n        Total updates                                      2.00\n        Total deletes                                      0.00\n        Total upserts                                      0.00\n        Total discards                                     0.00\n        Total operations                                   2.00\n\n*** Hourly statistics since 2020-08-19 21:34:35 ***\n        Total inserts                                      0.00\n        Total updates                                      2.00\n        Total deletes                                      0.00\n        Total upserts                                      0.00\n        Total discards                                     0.00\n        Total operations                                   2.00\n\n*** Latest statistics since 2020-08-19 21:34:35 ***\n        Total inserts                                      0.00\n        Total updates                                      2.00\n        Total deletes                                      0.00\n        Total upserts                                      0.00\n        Total discards                                     0.00\n        Total operations                                   2.00<\/pre>\n<p>Let&#8217;s modify the replicat parameter file to add MAP statement for the new table DEMO.ADDRESSES plus the parameter DBOPTIONS ENABLE_INSTANTATION_FILTERING.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">Replicat REPLSOE\nDBOPTIONS INTEGRATEDPARAMS ( parallelism 6 )\nDISCARDFILE \/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/dirrpt\/REPLSOE_discard.txt, append, megabytes 10\nDBOPTIONS ENABLE_INSTANTIATION_FILTERING\nUSERIDALIAS ggadmin\nMAP SOE.*, TARGET SOE.* ;\n--MAP DEMO.ADDRESSES ,TARGET DEMO.ADDRESSES,FILTER ( @GETENV ('TRANSACTION', 'CSN') &gt; 2908627) ;\nMAP DEMO.ADDRESSES, TARGET DEMO.ADDRESSES;<\/pre>\n<p>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.<\/p>\n<p>Let&#8217;s start the replicat process :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">GGSCI (ora-gg-t-2) 2&gt; start replicat REPLSOE\n\nSending START request to MANAGER ...\nREPLICAT REPLSOE starting\n\n\nGGSCI (ora-gg-t-2) 3&gt; info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nREPLICAT    RUNNING     REPLSOE     00:00:00      00:41:37\n\n\nGGSCI (ora-gg-t-2) 4&gt; !\ninfo all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nREPLICAT    RUNNING     REPLSOE     00:00:00      00:00:01<\/pre>\n<p>&nbsp;<\/p>\n<p>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\u2019s instantiation CSN . Output in the report file will show the table name and to what CSN the replicat will start applying data :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">2020-08-19 21:56:05 INFO OGG-10155 Instantiation CSN filtering is enabled on table DEMO.ADDRESSES at CSN 2,995,59\n0.<\/pre>\n<p>Let&#8217;s wait the lag resolved and let&#8217;s check the transaction occured into table DEMO.ADDRESSES:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">GGSCI (ora-gg-t-2) 6&gt; stats replicat REPLSOE ,table demo.addresses\n\nSending STATS request to REPLICAT REPLSOE ...\n\nStart of Statistics at 2020-08-19 21:58:32.\n\n\nIntegrated Replicat Statistics:\n\n        Total transactions                                 1.00\n        Redirected                                         0.00\n        Replicated procedures                              0.00\n        DDL operations                                     0.00\n        Stored procedures                                  0.00\n        Datatype functionality                             0.00\n        Operation type functionality                       0.00\n        Event actions                                      0.00\n        Direct transactions ratio                          0.00%\n\nReplicating from DEMO.ADDRESSES to DEMO.ADDRESSES:\n\n*** Total statistics since 2020-08-19 21:56:05 ***\n        Total inserts                                      0.00\n        Total updates                                      1.00\n        Total deletes                                      0.00\n        Total upserts                                      0.00\n        Total discards                                     0.00\n        Total operations                                   1.00\n\n*** Daily statistics since 2020-08-19 21:56:05 ***\n        Total inserts                                      0.00\n        Total updates                                      1.00\n        Total deletes                                      0.00\n        Total upserts                                      0.00\n        Total discards                                     0.00\n        Total operations                                   1.00\n\n*** Hourly statistics since 2020-08-19 21:56:05 ***\n        Total inserts                                      0.00\n        Total updates                                      1.00\n        Total deletes                                      0.00\n        Total upserts                                      0.00\n        Total discards                                     0.00\n        Total operations                                   1.00\n\n*** Latest statistics since 2020-08-19 21:56:05 ***\n        Total inserts                                      0.00\n        Total updates                                      1.00\n        Total deletes                                      0.00\n        Total upserts                                      0.00\n        Total discards                                     0.00\n        Total operations                                   1.00\n\nEnd of Statistics.<\/pre>\n<p>Let&#8217;s check if data are synchronized :<\/p>\n<p>Into source :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">oracle@ora-gg-s-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB1] sqh\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 22:00:52 2020\nVersion 19.4.0.0.0\n\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.4.0.0.0\n\nSQL&gt; select street_name from demo.ADDRESSES where ADDRESS_ID=1000;\n\nSTREET_NAME\n------------------------------------------------------------\nDemo Street is open\n\nSQL&gt; select street_name from demo.ADDRESSES where ADDRESS_ID=1001;\n\nSTREET_NAME\n------------------------------------------------------------\nDemo Street is open<\/pre>\n<p>Into target :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">oracle@ora-gg-t-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB2] sqh\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 22:02:44 2020\nVersion 19.4.0.0.0\n\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.4.0.0.0\n\nSQL&gt; select street_name from demo.ADDRESSES where ADDRESS_ID=1000;\n\nSTREET_NAME\n------------------------------------------------------------\nDemo Street is open\n\nSQL&gt; select street_name from demo.ADDRESSES where ADDRESS_ID=1001;\n\nSTREET_NAME\n------------------------------------------------------------\nDemo Street is open<\/pre>\n<p>Both table DEMO.ADDRESSES on source database and target database has identical data.<\/p>\n<p>DBOPTIONS ENABLE_INSTANTIATION_FILTERING is no longer required:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">GGSCI (ora-gg-t-2)&gt; edit params REPLSOE\n... \nMAP demo.addresses ,TARGET demo.addresses;<\/pre>\n<p>Restart the replicat :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">GGSCI (ora-gg-t-2) 9&gt; stop replicat replsoe\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nREPLICAT    STOPPED     REPLSOE     00:00:00      00:00:01\n\nGGSCI (ora-gg-t-2) 10&gt; start replicat replsoe\n\nSending START request to MANAGER ...\nREPLICAT REPLSOE starting\n\n\nGGSCI (ora-gg-t-2) 11&gt; info all\n\nProgram     Status      Group       Lag at Chkpt  Time Since Chkpt\n\nMANAGER     RUNNING\nREPLICAT    RUNNING     REPLSOE     00:00:00      00:00:00<\/pre>\n<p>Let&#8217;s doing a last test :<br \/>\nUpdate on the source table :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; update DEMO.ADDRESSES set STREET_NAME= 'test 1' where ADDRESS_ID=800;\n\n1 row updated.\n\nSQL&gt; commit;\n\nCommit complete.<\/pre>\n<p>Let&#8217;s check the target database :<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">oracle@ora-gg-t-2:\/u10\/app\/goldengate\/product\/19.1.0.0.4\/gg_1\/ [DB2] sqlplus \/ as sysdba\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 19 22:11:24 2020\nVersion 19.4.0.0.0\n\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.4.0.0.0\n\nSQL&gt; select street_name from demo.ADDRESSES where ADDRESS_ID=800;\n\nSTREET_NAME\n------------------------------------------------------------\ntest 1\n\n<\/pre>\n<p>Conclusion :<\/p>\n<ul>\n<li>The parameter DBOPTIONS ENABLE_INSTANTATION_FILTERING avoid for the Golden Gate administrator to find the CSN used for the inital load.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[2013],"type_dbi":[],"class_list":["post-14554","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-oracle-goldengate"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-08-19T18:46:41+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate\",\"datePublished\":\"2020-08-19T18:46:41+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/\"},\"wordCount\":493,\"commentCount\":0,\"keywords\":[\"Oracle GoldenGate\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/\",\"name\":\"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-08-19T18:46:41+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/","og_locale":"en_US","og_type":"article","og_title":"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate","og_description":"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 [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/","og_site_name":"dbi Blog","article_published_time":"2020-08-19T18:46:41+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate","datePublished":"2020-08-19T18:46:41+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/"},"wordCount":493,"commentCount":0,"keywords":["Oracle GoldenGate"],"articleSection":["Database Administration &amp; Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/","name":"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-08-19T18:46:41+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-data-pump-integration-for-table-instantiation-with-oracle-golden-gate\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14554","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14554"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14554\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14554"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14554"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14554"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14554"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}