ADD NEW TABLE TO AN EXISTING GOLDENGATE CONFIGURATION
GoldenGateadd new table extract, ADD NEW TABLE TO AN EXISTING EXTRACT, ADD NEW TABLE TO AN EXISTING GOLDENGATE CONFIGURATION, goldengate, How to Resync Tables / Schemas on Different SCN s in a Single Replicat, oracle goldengate, Oracle GoldenGate 12c, PUMP AND REPLICAT, sync table in oggADD NEW TABLE TO AN EXISTING EXTRACT, PUMP AND REPLICAT
Table of Contents
___________________________________________________________________________________________________
Source Side:
1. Login to GGSCI
2. Add trandata for new table
3. Add table to Extract
4. Add table to Pump
5. Restart Extract
6. Restart Pump
7. Verify Status
8. Capture current SCN
9. Export Table using FLASHBACK_SCN
10. Transfer dump to target server
Target Side:
11. Import new table
12. Add table to Replicat
13. Restart Replicat
14. Verify Status
Source
Target
16. Remove the FILTER parameters replicat
17. Restart the replicat
18. Verify Status
___________________________________________________________________________________________________
To resync one or more tables/schemas on different SCN's using a single or minimum number of replicats without using handlecollisions. This can be done with Oracle goldenGate (OGG) 11.1.1.0.x or higher. We are going to add new table SOURCE.DASANI to existing extract,pump and replicat Environment Source Server Name : RAC1 OS Version : Red Hat Enterprise Linux Server release 6.5 RAC : No Database Version : 12.2.0.1.0 GoldenGate Version : Version 12.3.0.1.4 for oracle db 12.2.0.1.0 Database Name : PSG01DAS Golden Gate User : OGGADMIN Golden Gate Extract : ERAJ Golden Gate Pump : PRAJ Source Schema : SOURCE Oracle Home : /u01/app/oracle/product/12.2.0/dbhome_1 GoldenGate Home : /u01/app/oracle/product/12.3/ogg Target Server Name : RAC1 OS Version : Red Hat Enterprise Linux Server release 6.5 RAC : No Database Version : 12.2.0.1.0 GoldenGate Version : Version 12.3.0.1.4 for oracle db 12.2.0.1.0 Database Name : USG01DAS Golden Gate User : OGGADMIN Golden Gate Replicat : RRAJ Target Schema : TARGET Oracle Home : /u01/app/oracle/product/12.2.0/dbhome_1 GoldenGate Home : /u01/app/oracle/product/12.3/ogg
Source:
GGSCI (rac1.rajasekhar.com) 1> dblogin UserIdAlias OGGADMIN Successfully logged into database. GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 2>
SQL> SELECT COUNT (*) FROM SOURCE.DASANI;
COUNT(*)
----------
15 <-----
SQL> ALTER TABLE SOURCE.DASANI ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Table altered.
SQL>
GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 2> ADD TRANDATA SOURCE.DASANI
2019-04-24 00:39:01 WARNING OGG-06439 No unique key is defined for table DASANI. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2019-04-24 00:39:01 INFO OGG-15130 No key found for table SOURCE.DASANI. All viable columns will be logged.
2019-04-24 00:39:03 INFO OGG-15132 Logging of supplemental redo data enabled for table SOURCE.DASANI.
2019-04-24 00:39:03 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SOURCE.DASANI.
2019-04-24 00:39:03 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SOURCE.DASANI.
2019-04-24 00:39:04 INFO OGG-10471 ***** Oracle Goldengate support information on table SOURCE.DASANI *****
Oracle Goldengate support native capture on table SOURCE.DASANI.
Oracle Goldengate marked following column as key columns on table SOURCE.DASANI: NAME, ROLE
No unique key is defined for table SOURCE.DASANI.
GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 3>
GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 4> edit params eraj -- Add below TABLE SOURCE.DASANI; GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 5> view params eraj Extract ERAJ SETENV (ORACLE_SID='PSG01DAS') UserIdAlias OGGADMIN TranlogOptions IntegratedParams (max_sga_size 256) Exttrail ./dirdat/ea LOGALLSUPCOLS UPDATERECORDFORMAT COMPACT TABLE SOURCE.EMP; TABLE SOURCE.EMPLOYEE; TABLE SOURCE.X; TABLE SOURCE.X1; TABLE SOURCE.DASANI; <--- GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 6>
GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 7> edit params praj -- Add below TABLE SOURCE.DASANI; GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 8> view params praj Extract PRAJ SETENV (ORACLE_SID='PSG01DAS') UserIdAlias OGGADMIN rmthost rac2, mgrport 7809 rmttrail ./dirdat/pa TABLE SOURCE.EMP; TABLE SOURCE.EMPLOYEE; TABLE SOURCE.X; TABLE SOURCE.X1; TABLE SOURCE.DASANI; <---- GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 9>
GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 11> stop eraj Sending STOP request to EXTRACT ERAJ ... Request processed. GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 12> start eraj Sending START request to MANAGER ... EXTRACT ERAJ starting GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 13>
GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 13> stop praj Sending STOP request to EXTRACT PRAJ ... Request processed. GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 14> start praj Sending START request to MANAGER ... EXTRACT PRAJ starting GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 15>
GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 15> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ERAJ 00:00:03 00:00:00 EXTRACT RUNNING PRAJ 00:00:00 00:00:10 GGSCI (rac1.rajasekhar.com as OGGADMIN@PSG01DAS) 16>
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2298283 <----
SQL>
9. Export Table using FLASHBACK_SCN
SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR'; OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID ---------- ------------------------- ---------------------------------------- ------------- SYS DATA_PUMP_DIR /u01/app/oracle/admin/PSG01DAS/dpdump/ 0 SQL> [oracle@rac1 ~]$ expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani.log tables=source.dasani flashback_scn=2298283 Export: Release 12.2.0.1.0 - Production on Wed Apr 24 00:52:11 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani.log tables=source.dasani flashback_scn=2298283 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "SOURCE"."DASANI" 5.687 KB 15 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/admin/PSG01DAS/dpdump/source_dasani.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Apr 24 00:52:48 2019 elapsed 0 00:00:28 [oracle@rac1 ~]$
10. Transfer dump to target server
[oracle@rac1 ~]$ scp /u01/app/oracle/admin/PSG01DAS/dpdump/source_dasani.dmp oracle@rac2:/u01/app/oracle/admin/USG01DAS/dpdump/ oracle@rac2's password: source_dasani.dmp 100% 200KB 200.0KB/s 00:00 [oracle@rac1 ~]$
Target:
SQL> col OWNER for a10 SQL> col DIRECTORY_NAME for a25 SQL> col DIRECTORY_PATH for a40 SQL> set lines 180 SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR'; OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID ---------- ------------------------- ---------------------------------------- ------------- SYS DATA_PUMP_DIR /u01/app/oracle/admin/USG01DAS/dpdump/ 0 SQL> [oracle@rac2 ~]$ impdp \'/ as sysdba\' directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani_imp.log remap_schema=source:target transform=segment_attributes:n transform=oid:n Import: Release 12.2.0.1.0 - Production on Wed Apr 24 00:58:28 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani_imp.log remap_schema=source:target transform=segment_attributes:n transform=oid:n Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TARGET"."DASANI" 5.687 KB 15 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 Wed Apr 24 00:58:54 2019 elapsed 0 00:00:23 [oracle@rac2 ~]$
Note :
Please use single quotes since OGG v12 uses ANSI SQL parameter by default, if you use double quotes then you may receive this error.
You can use double quotes in pre OGG v12
2019-04-24T01:10:34.244+0800 ERROR OGG-00375 Oracle GoldenGate Delivery for Oracle, rraj.prm: Error in FILTER clause.
2019-04-24T01:10:39.386+0800 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rraj.prm: PROCESS ABENDING.
GGSCI (rac2.rajasekhar.com) 14> edit params rraj
-- Add below line
Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283);
GGSCI (rac2.rajasekhar.com) 15> view params rraj
Replicat RRAJ
SETENV(ORACLE_SID='USG01DAS')
UserIdAlias OGGADMIN
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserIdAlias OGGADMIN
Map SOURCE.EMP, target TARGET.EMP;
Map SOURCE.EMPLOYEE, target TARGET.EMPLOYEE;
Map SOURCE.X, target TARGET.X;
Map SOURCE.X1, target TARGET.X1;
Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283); <-----
GGSCI (rac2.rajasekhar.com) 16>
GGSCI (rac2.rajasekhar.com) 16> stop rraj Sending STOP request to REPLICAT RRAJ ... Request processed. GGSCI (rac2.rajasekhar.com) 17> GGSCI (rac2.rajasekhar.com) 19> start rraj Sending START request to MANAGER ... REPLICAT RRAJ starting GGSCI (rac2.rajasekhar.com) 20>
GGSCI (rac2.rajasekhar.com) 20> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RRAJ 00:00:00 00:00:11 GGSCI (rac2.rajasekhar.com) 21>
Source:
SQL> SELECT COUNT(*) FROM SOURCE.DASANI;
COUNT(*)
----------
15 <---
SQL>
SQL> INSERT INTO SOURCE.DASANI VALUES ('CHITTI','MANAGER');
1 row created.
SQL> INSERT INTO SOURCE.DASANI VALUES ('SRINIVAS','LEAD');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT COUNT(*) FROM SOURCE.DASANI;
COUNT(*)
----------
17 <-----
SQL>
Target:
SQL> SELECT COUNT(*) FROM TARGET.DASANI;
COUNT(*)
----------
15
SQL>
SQL> SELECT COUNT(*) FROM TARGET.DASANI;
COUNT(*)
----------
17 <----
SQL>
It's sync now, next step we need to FILTER PARAMETER from Relicat
16. Remove the FILTER parameters replicat
GGSCI (rac2.rajasekhar.com) 31> dblogin UserIdAlias OGGADMIN
Successfully logged into database.
GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 32> lag rraj
Sending GETLAG request to REPLICAT RRAJ ...
Last record lag 246 seconds.
Low watermark lag: 1.
High watermark lag: 258.
Low watermark position: 2211928.
High watermark position: 2316576.
At EOF, no more records to process
GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 33> edit params rraj
-- From
Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283);
-- To
Map SOURCE.DASANI, target TARGET.DASANI;
GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 34> view params rraj
Replicat RRAJ
SETENV(ORACLE_SID='USG01DAS')
UserIdAlias OGGADMIN
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
AssumeTargetDefs
DiscardFile ./dirrpt/rpdw.dsc, Purge
UserIdAlias OGGADMIN
Map SOURCE.EMP, target TARGET.EMP;
Map SOURCE.EMPLOYEE, target TARGET.EMPLOYEE;
Map SOURCE.X, target TARGET.X;
Map SOURCE.X1, target TARGET.X1;
Map SOURCE.DASANI, target TARGET.DASANI; <-----
GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 35>
GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 35> stop rraj Sending STOP request to REPLICAT RRAJ ... Request processed. GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 36> GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 37> start rraj Sending START request to MANAGER ... REPLICAT RRAJ starting GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 38>
GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 38> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RRAJ 00:00:00 00:00:05 GGSCI (rac2.rajasekhar.com as OGGADMIN@USG01DAS) 39>
Source:
SQL> SELECT COUNT(*) FROM SOURCE.DASANI;
COUNT(*)
----------
17 <----
SQL> INSERT INTO SOURCE.DASANI VALUES ('SOMU','DBA');
1 row created.
SQL> INSERT INTO SOURCE.DASANI VALUES ('ZABI','DBA');
1 row created.
SQL> INSERT INTO SOURCE.DASANI VALUES ('GIRI','BIGDATA');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT COUNT(*) FROM SOURCE.DASANI;
COUNT(*)
----------
20 <-----
SQL>
Target:
SQL> SELECT COUNT(*) FROM TARGET.DASANI;
COUNT(*)
----------
17
SQL>
SQL> SELECT COUNT(*) FROM TARGET.DASANI;
COUNT(*)
----------
20 <------
SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,