In earlier versions when we had to do an Oracle database table instantiation or initial load, we had to perform a number of steps – basically to handle DML changes which were occurring on the source table while the export was in progress.
So we had to first ensure that there were no open or long running transactions in progress. Then obtain the Current SCN of the database – pass this SCN to the FLASHBACK_SCN parameter of the Export Datapump. Then after the import was over we had to ensure that we used the HANDLECOLLISIONS parameter initially for the replicat and also start the Replicat from a particular position in the trail using the AFTERCSN parameter.
Now with Goldengate 12.2, there is tighter integration with Oracle Datapump Export and Import.
The ADD SCHEMATRANDATA command with the PREPARECSN parameter will ensure that the Datapump export will have information about the instantiation CSN’s for each table part of the export – this will populate the system tables and views with instantiation CSNs on the import and further the new Replicat parameter DBOPTIONS ENABLE_INSTANTIATION_FILTERING will filter out DML and DDL records based on the table’s instantiation CSN.
Let us look at an example of this new 12.2 feature.
We have a table called TESTME in the SYSADM schema which initially has 266448 rows.
Before running the Datapump export, let us ‘prepare’ the tables via the PREPARECSN parameter of the ADD SCHEMATRANDATA command.
GGSCI (pcu008 as oggsuser@BSDIT1) 12> add schematrandata sysadm preparecsn 2015-12-10 06:38:58 INFO OGG-01788 SCHEMATRANDATA has been added on schema sysadm. 2015-12-10 06:38:58 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema sysadm. 2015-12-10 06:38:59 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema sysadm. GGSCI (pcu008 as oggsuser@omqat41) 3> info schematrandata SYSADM 2015-12-13 07:21:55 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema SYSADM. 2015-12-13 07:21:55 INFO OGG-01980 Schema level supplemental logging is enabled on schema SYSADM for all scheduling columns. 2015-12-13 07:21:55 INFO OGG-10462 Schema SYSADM have 571 prepared tables for instantiation.
We run the Datapump export. Note the line :
“FLASHBACK automatically enabled to preserve database integrity.”
pcu008@oracle:BSSTG1>expdp directory=BACKUP_DUMP_DIR dumpfile=testme.dmp tables=sysadm.testme Export: Release 220.127.116.11.0 – Production on Mon Jan 25 23:45:27 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 – 64bitProduction With the Partitioning, Real Application Clusters, Automatic Storage Management,OLAP, Advanced Analytics and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting “SYS”.”SYS_EXPORT_TABLE_01?: sys/******** AS SYSDBA directory=BACKUP_DUMP_DIR dumpfile=testme.dmp tables=sysadm.testme Estimate in progress using BLOCKS method… Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 28 MB Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER . . exported “SYSADM”.”TESTME” 26.86 MB 266448 rows Master table “SYS”.”SYS_EXPORT_TABLE_01? successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /home/oracle/backup/testme.dmp Job “SYS”.”SYS_EXPORT_TABLE_01? successfully completed at Mon Jan 25 23:46:45 2016 elapsed 0 00:00:49
While the export of the TESTME table is in progress, we will insert 29622 more rows into the table. The table will now have 296070 rows.
SQL> insert into sysadm.testme select * from dba_objects; 29622 rows created. SQL> select count(*) from sysadm.testme; COUNT(*) ———- 296070 SQL> commit; Commit complete.
We perform the import on the target database next. Note the number of rows imported. So we do not have the 29622 rows which were inserted into the table while export is in progress.
qat408@oracle:BSSTG1>impdp directory=BACKUP_DUMP_DIR dumpfile=testme.dmp full=y Import: Release 22.214.171.124.0 – Production on Mon Jan 25 23:51:42 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 12c Enterprise Edition Release 126.96.36.199.0 – 64bitProduction With the Partitioning, Real Application Clusters, Automatic Storage Management,OLAP, Advanced Analytics and Real Application Testing options Master table “SYS”.”SYS_IMPORT_FULL_01? successfully loaded/unloaded import done in AL32UTF8 character set and AL16UTF16 NCHAR character set export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set WARNING: possible data loss in character set conversions Starting “SYS”.”SYS_IMPORT_FULL_01?: sys/******** AS SYSDBA directory=BACKUP_DUMP_DIR dumpfile=testme.dmp full=y Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported “SYSADM”.”TESTME” 26.86 MB 266448 rows Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job “SYS”.”SYS_IMPORT_FULL_01? successfully completed at Mon Jan 25 23:52:22 2016 elapsed 0 00:00:25
We start the Replicat process on the target – note we are not positioning the replicat liked we used to do earlier using the AFTERCSN command.
GGSCI (qat408 as oggsuser@BSSTG2) 7> start rbsstg1 Sending START request to MANAGER … REPLICAT RBSSTG1 starting
After starting the replicat, if we look at the report file for the replicat, we can see that the Replicat process is aware of the SCN or CSN number existing in the database while the export was in progress and it knows that any DML or DDL changes post that SCN now need to be applied on the target table.
2016-01-25 23:56:59 INFO OGG-10155 Instantiation CSN filtering is enabled on table SYSADM.TESTME at CSN 402,702,624.
If we query the replicat statistics a while after the replicat has started, we can see that the replicat has applied the insert statement (29622 rows) which was running while the export of the table was in progress.
GGSCI (qat408 as oggsuser@BSSTG1) 12> stats rbsstg1 latest Sending STATS request to REPLICAT RBSSTG1 … Start of Statistics at 2016-01-26 00:14:55. Integrated Replicat Statistics: Total transactions 1.00 Redirected 0.00 DDL operations 0.00 Stored procedures 0.00 Datatype functionality 0.00 Event actions 0.00 Direct transactions ratio 0.00% Replicating from SYSADM.TESTME to SYSADM.TESTME: *** Latest statistics since 2016-01-26 00:05:19 *** Total inserts 29622.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 29622.00 End of Statistics.