Oracle GoldenGate Integration with DATAPUMP – DBOPTIONS ENABLE_INSTANTIATION_FILTERING – Oracle GoldenGate 12.2 New Feature

There are many methods to perform the Initial Load before starting the Online Replication or Real Time Replication processes in Oracle GoldenGate. They are as below,

1. Oracle Transportable Tablespaces
2. Oracle Export / Import or Datapump (Expdp / Impdp)
3. RMAN
4. Oracle Warehouse Builder or Some third party tools like ETL etc.,

Oracle GoldenGate offers different types of Initial Loads.

1. File to Replicat
2. File to Database Utility
3. Direct Load
4. Direct Bulk Load.

In this article, we are going to see about Performing Initial Load with Oracle DataPump Utility (Expdp/Impdp). The below are the basic steps to perform the Initial Load using the Oracle DATAPUMP (Expdp/Impdp).

1. Create the necessary tables (only structures) on the Target side.
2. Start the Change Synchronization Capture / Online Extract process.
3. Start the Datapump Process.
4. Check the current scn of the database using the below command,

select current_scn from V$database;

5. Export the table from Source with the following parameter in the EXPDP flashback_scn=.
6. Import the table dump in the Target.
7. Start the Online Replication process on the Target with the below command,

start replicat , aftercsn

So many steps to be followed to do the Initial Load using Expdp/Impdp. Here the SCN plays a vital role to maintain the Data Integrity. From Oracle GoldenGate 12.2, this method of Initial Load using DATAPUMP utility has been made in more simpler way.

From Oracle GoldenGate 12.2, there is a new feature introduced. Below are the parameters,

ADD SCHEMATRANDATA|TRANDATA SCHEMA|SCHEMA.TABLE PREPARECSN

DBOPTIONS ENABLE_INSTANTIATION_FILTERING

You don’t want to note down the SCN and this will be taken care automatically when executing the ADD SCHEMATRANDATA or ADD TRANDATA command with the optional parameter PREPARECSN at the Oracle GoldenGate level.

ADD SCHEMATRANDATA SCHEMA PREPARECSN

ADD TRANDATA SCHEMA.TABLE PREPARECSN

When you use this parameter PREPARECSN when adding the trandata, it automatically prepares the SCN for that particular table or schema and the SCN part is automatically takencare. There is no requirement of noting down the SCN (FLASHBACK_SCN) over here.

WAIT - Wait for any in-flight transactions and prepare table instantiation.
LOCK - Put a lock on the table (to prepare for table instantiation).
NOWAIT - Default behavior, preparing for instantiation is done immediately.
NONE - No instantiation preparation occurs.

This is at the Source Side. At the Target side, you need to add the below parameter to the Replicat process parameter file,

DBOPTIONS ENABLE_INSTANTIATION_FILTERING

This parameter let’s the Replicat process know after which CSN it should start replicating or applying the data to the Target tables. On the target, the data pump import populates the system tables and views with instantiation SCNs using the DBOPTIONS ENABLE_INSTANTIATION_FILTERING parameter to enable table-level instantiation filtering. So the parameter,

1. Filters out DDL and DML records based on each table’s instantiation CSN

2. Eliminates need for HANDLE_COLLISIONS or specification of individual MAP for each imported table with the @FILTER(@GETENV(‘TRANSACTION’,‘CSN’) clause.

Let us see below in detail on how this feature works.

On the Source side, I have created table VEERA.TEST as below,

SQL> create table veera.test as select * from dba_objects;

Table created.

SQL> desc veera.test
Name                Null?    Type
------------------ --------- --------------
OWNER                        VARCHAR2(30)
OBJECT_NAME                  VARCHAR2(128)
SUBOBJECT_NAME               VARCHAR2(30)
OBJECT_ID                    NUMBER
DATA_OBJECT_ID               NUMBER
OBJECT_TYPE                  VARCHAR2(19)
CREATED                      DATE
LAST_DDL_TIME                DATE
TIMESTAMP                    VARCHAR2(19)
STATUS                       VARCHAR2(7)
TEMPORARY                    VARCHAR2(1)
GENERATED                    VARCHAR2(1)
SECONDARY                    VARCHAR2(1)
NAMESPACE                    NUMBER
EDITION_NAME                 VARCHAR2(30)

SQL> select count(1) from veera.test;

COUNT(1)
----------
88803

At the OGG level, I am executing the ADD SCHEMATRANDATA with the PREPARECSN option.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 4> ADD SCHEMATRANDATA VEERA PREPARECSN

2016-09-30 12:02:18 INFO OGG-01788 SCHEMATRANDATA has been added on schema VEERA.

2016-09-30 12:02:18 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema VEERA.

2016-09-30 12:02:18 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema VEERA.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 5> INFO SCHEMATRANDATA VEERA

2016-09-30 12:02:43 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema VEERA.

2016-09-30 12:02:43 INFO OGG-01980 Schema level supplemental logging is enabled on schema VEERA for all scheduling columns.

2016-09-30 12:02:43 INFO OGG-10462 Schema VEERA have 1 prepared tables for instantiation.

In the above output, you can clearly see the message Schema VEERA has been prepared for instantiation.

Already the Manager process was started and now I have started the Extract and Pump processes.

GGSCI (OGGR2-1.localdomain as ggadmin@GGDB1) 15> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DMP1 00:00:00      00:00:07
EXTRACT RUNNING EXT1 00:00:00      00:00:00

Now, let us take an export of the schema VEERA. Here we do not need to mention any SCN (FLASHBACK_SCN) for taking an Export.

[oracle@OGGR2-1 ~]$ expdp directory=DATA_PUMP_DIR dumpfile=test.dmp tables=veera.test

[oracle@OGGR2-1 ~]$ expdp directory=DATA_PUMP_DIR dumpfile=test.dmp tables=veera.test

Export: Release 11.2.0.4.0 - Production on Fri Sep 30 12:11:13 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=test.dmp tables=veera.test
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 21 MB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "VEERA"."TEST" 8.618 MB 88803 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/oracle/admin/GGDB1/dpdump/test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Sep 30 12:11:25 2016 elapsed 0 00:00:04

The Export (Expdp) has completed successfully. But during the course of the Expdp, I had inserted more rows in to the table VEERA.TEST and these rows are not captured in the export dump. The count before exporting and the count in the Expdp is same 88803.

SQL> insert into veera.test (select * from dba_objects);

88803 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from veera.test;

COUNT(1)
----------
177606

Since I had inserted more rows, the current count of the table is 177606.

At the Target side, edit the parameter of the Replicat process parameter file REP1 and add the below parameter,

DBOPTIONS ENABLE_INSTANTIATION_FILTERING

GGSCI (OGGR2-2.localdomain as ggadmin@GGDB2) 13> view params rep1

REPLICAT REP1
USERID ggadmin, PASSWORD oracle
MAP veera.test, TARGET veera.test;

GGSCI (OGGR2-2.localdomain as ggadmin@GGDB2) 15> view params rep1

REPLICAT REP1
USERID ggadmin, PASSWORD oracle
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
MAP veera.test, TARGET veera.test;

Already same table VEERA.TEST has been created in the Target side which you can see below,

SQL> desc veera.test
Name                Null?    Type
------------------ --------- --------------
OWNER                        VARCHAR2(30)
OBJECT_NAME                  VARCHAR2(128)
SUBOBJECT_NAME               VARCHAR2(30)
OBJECT_ID                    NUMBER
DATA_OBJECT_ID               NUMBER
OBJECT_TYPE                  VARCHAR2(19)
CREATED                      DATE
LAST_DDL_TIME                DATE
TIMESTAMP                    VARCHAR2(19)
STATUS                       VARCHAR2(7)
TEMPORARY                    VARCHAR2(1)
GENERATED                    VARCHAR2(1)
SECONDARY                    VARCHAR2(1)
NAMESPACE                    NUMBER
EDITION_NAME                 VARCHAR2(30)

I had moved the Export Dump file to the Target server using the SCP and I am importing the dump using the IMPDP utility as below,

[oracle@OGGR2-2 ~]$ impdp directory=DATA_PUMP_DIR dumpfile=test.dmp full=y

Import: Release 11.2.0.4.0 - Production on Fri Sep 30 12:28:23 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=test.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 "VEERA"."TEST" 8.618 MB 88803 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Fri Sep 30 12:28:36 2016 elapsed 0 00:00:04

SQL> select count(1) from veera.test;

COUNT(1)
----------
88803

So, 88803 rows are imported to the table VEERA.TEST at the Target side. The rows which are inserted during the Export at the source are not come here.

Start the Replicat process REP1.

GGSCI (OGGR2-2.localdomain as ggadmin@GGDB2) 27> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

The Replicat process REP1 has started and it is in RUNNING state.

GGSCI (OGGR2-2.localdomain as ggadmin@GGDB2) 28> info all

Program  Status  Group Lag at Chkpt  Time Since Chkpt

MANAGER  RUNNING
REPLICAT RUNNING REP1  00:00:00      00:00:06

Below is the report file of the Replicat process REP1.

2016-09-30 12:37:34 INFO OGG-02243 Opened trail file /ogg/dirdat/ft000000004 at 2016-09-30 12:37:34.103888.

2016-09-30 12:37:34 INFO OGG-03522 Setting session time zone to source database time zone 'GMT'.

2016-09-30 12:37:34 INFO OGG-03506 The source database character set, as determined from the trail file, is we8mswin1252.
Processed extract process graceful restart record at seq 4, rba 1459.

2016-09-30 12:37:34 INFO OGG-06505 MAP resolved (entry veera.test): MAP "VEERA"."TEST", TARGET veera.test.

2016-09-30 12:37:36 WARNING OGG-06439 No unique key is defined for table TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2016-09-30 12:37:36 INFO OGG-02756 The definition for table VEERA.TEST is obtained from the trail file.

2016-09-30 12:37:36 INFO OGG-06511 Using following columns in default map by name: OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME.

2016-09-30 12:37:36 INFO OGG-10155 Instantiation CSN filtering is enabled on table VEERA.TEST at CSN 2,972,839.

2016-09-30 12:37:36 INFO OGG-06510 Using the following key columns for target table VEERA.TEST: OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME.

The Replicat process REP1 automatically picks up the CSN and starts applying the record after this CSN. This you can clearly see it from the below line in the above report file.,

2016-09-30 12:37:36 INFO OGG-10155 Instantiation CSN filtering is enabled on table VEERA.TEST at CSN 2,972,839.

Check the count of the table VEERA.TEST at the target side and it is in sync now.

SQL> select count(1) from veera.test;

COUNT(1)
----------
177606

Hope the article was much helpful. Will see another new feature of Oracle GoldenGate 12.2 in the next article.