Infrastructure at your Service

Lazhar Felahi

Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate

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 :

[email protected]: [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 :

[email protected]:/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 [email protected]) 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 [email protected]) 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 [email protected]) 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 [email protected]) 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 [email protected]) 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 [email protected]) 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 :

[email protected]:/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 :

[email protected]:/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

[email protected]:/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 :

[email protected]:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1] scp \
> /u01/app/oracle/admin/DB1/dpdump/export_tables_DEMO.dmp \
> [email protected]:/u01/app/oracle/admin/DB2/dpdump
[email protected]'s password:
export_tables_DEMO.dmp                                                            100%   36MB 120.8MB/s   00:00
[email protected]:/u10/app/goldengate/product/19.1.0.0.4/gg_1/ [DB1]

Let’s  import the new table into target database :

[email protected]:/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

[email protected]:/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 :

[email protected]:/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 :

[email protected]:/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 :

[email protected]:/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 :

[email protected]:/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 :

[email protected]:/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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Lazhar Felahi
Lazhar Felahi

Consultant