Thursday, December 5, 2013
Adding Tables to an Existing GoldenGate Configuration with Transaction Integrity
There is always something new to learn every day. For me, today was one of those days, where I learnt a new way to add new tables to an existing Extract, Pump, and Replicat setup.
The current configuration consists of a single Extract and Replicat running OGG 11.2.1.0.13 on OEL 6.4 64bit. Both databases are on the same host.
From the source database, we capture DDL for all tables from the DAY and NIGHT schema and replicate them to the RIVER and WATER schemas on the target respectively.
If you noticed, I’m not using a Pump. The two databases reside on the same server, therefore there’s no need for one.
Onward!
The goal here is to add two more tables STONE and GREYSTREET to the Extract and Pump while GoldenGate is replicating data.
1. Log into the source database via GGSCI
[oracle@blue ggate]$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.13 17435036 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131002.1206_FBO Linux, x64, 64bit (optimized), Oracle 11g on Oct 3 2013 02:39:46 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (blue.color) 2> dblogin userid ggate, password ggate Successfully logged into database.
2. Add trandata for the new table(s).
GGSCI (blue.color) 3> add trandata song.stone 2013-12-05 15:17:07 WARNING OGG-00869 No unique key is defined for table 'STONE'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. Logging of supplemental redo data enabled for table SONG.STONE. GGSCI (blue.color) 4> add trandata song.greystreet 2013-12-05 15:17:15 WARNING OGG-00869 No unique key is defined for table 'GREYSTREET'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. Logging of supplemental redo data enabled for table SONG.GREYSTREET.
3. Add the two tables in the Extract Parameter file
GGSCI (blue.color) 5> edit params e_gg ... TABLE song.stone; TABLE song.greystreet; ...
4. Restart the Extract e_gg
GGSCI (blue.color) 2> stop e_gg Sending STOP request to EXTRACT E_GG ... Request processed. GGSCI (blue.color) 3> start e_gg Sending START request to MANAGER ... EXTRACT E_GG starting
5. At this point, capture the Current SCN from the database. Do not lose this number, we need it later!
[oracle@blue ggate]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 5 15:28:31 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1113396
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
6. Take a Data Pump export of the two tables. Please note the use of FLASHBACK_SCN.
[oracle@blue ggate]$ expdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet Export: Release 11.2.0.3.0 - Production on Thu Dec 5 15:32:04 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "SONG"."GREYSTREET" 5.882 KB 100 rows . . exported "SONG"."STONE" 5.679 KB 100 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/admin/sky/dpdump/additional_tables.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:14
7. Run the import on the Target database.
[oracle@blue ggate]$ impdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river Import: Release 11.2.0.3.0 - Production on Thu Dec 5 15:45:29 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "RIVER"."GREYSTREET" 5.882 KB 100 rows . . imported "RIVER"."STONE" 5.679 KB 100 rows Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 15:45:31
8. Here’s the important step, you need to edit the replicat’s parameter file and include the following string in the map statement.
GGSCI (blue.color) 2> edit params r_gg
...
MAP song.stone, TARGET river.stone , FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);
MAP song.greystreet, TARGET river.greystreet, FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);
...
9. Recycle the replicat.
GGSCI (blue.color) 4> stop r_gg Sending STOP request to REPLICAT R_GG ... start Request processed. GGSCI (blue.color) 5> start r_gg Sending START request to MANAGER ... REPLICAT R_GG starting GGSCI (blue.color) 6> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E_GG 00:00:00 00:00:09 REPLICAT RUNNING R_GG 00:00:00 00:00:03
10.Check the counts on both systems.
10.1. On the Source
SQL> select name from v$database;
NAME
---------
SKY
SQL> select count(1) from song.stone;
COUNT(1)
----------
100
10.2. On the Target
SQL> select name from v$database;
NAME
---------
WATER
SQL> select count(1) from river.stone;
COUNT(1)
----------
100
10.3. Insert data on the source.
SQL> INSERT INTO song.stone
SELECT dbms_random.string('A', 2) RNDMSTR
FROM all_objects
WHERE ROWNUM;
100 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from song.stone;
COUNT(1)
----------
200
10.4. Check the counts on the target table.
SQL> select count(1) from river.stone;
COUNT(1)
----------
200
10.5. Check lag on the replicat.
GGSCI (blue.color) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E_GG 00:00:00 00:00:02 REPLICAT RUNNING R_GG 00:00:00 00:00:08
11. Once the table is caught up, remove the FILTER parameters from the replicat.
GGSCI (blue.color) 2> edit params r_gg ... MAP song.stone, TARGET river.stone; MAP song.greystreet, TARGET river.greystreet; ...
12. Restart the replicat and we’re done!
GGSCI (blue.color) 4> stop r_gg Sending STOP request to REPLICAT R_GG ... start Request processed. GGSCI (blue.color) 5> start r_gg Sending START request to MANAGER ... REPLICAT R_GG starting GGSCI (blue.color) 6> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E_GG 00:00:00 00:00:06 REPLICAT RUNNING R_GG 00:00:00 00:00:02
According to the MOS note I’ve found to accomplish this task, this only works when DDL Replication is enabled. I’ve yet to try the steps without the former enabled, and on databases other than Oracle.
MOS Note: OGG How to Resync Tables / Schemas on Different SCN s in a Single Replicat (Doc ID 1339317.1)
Update Dec 15, 2013: I had shared this note with an Engineer @ Oracle who actually converted this into a MOS Note! I'm excited!!
How to add tables to an Existing GoldenGate Configuration with Transaction Integrity? (Doc ID 1607591.1)
Update Dec 15, 2013: I had shared this note with an Engineer @ Oracle who actually converted this into a MOS Note! I'm excited!!
How to add tables to an Existing GoldenGate Configuration with Transaction Integrity? (Doc ID 1607591.1)
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment