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 < 101; 2 3 4
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
No comments:
Post a Comment