DBA Hub

📋Steps in this guide1/21

ADD NEW TABLE TO AN EXISTING EXTRACT, PUMP AND REPLICAT

ADD NEW TABLE TO AN EXISTING EXTRACT, PUMP AND REPLICAT

oracle replicationintermediate
by OracleDba
14 views
1

Overview

ADD NEW TABLE TO AN EXISTING EXTRACT, PUMP AND REPLICAT Source Side: Target Side:
2

Section 2

Target Side: Source Target Source Target
3

Section 3

0. Overview: We are going to add new table SOURCE.DASANI to existing extract,pump and replicat Environment Source Database Name : PSG01DAS Golden Gate User : OGGADMIN Golden Gate Extract : ERAJ Golden Gate Pump : PRAJ Source Schema : SOURCE

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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
4

Section 4

Target Database Name : USG01DAS Golden Gate User : OGGADMIN Golden Gate Replicat : RRAJ Target Schema : TARGET Source: 1. Login to GGSCI
5

Section 5

1. Login to GGSCI 2. Add trandata for new table ALTER TABLE SOURCE.DASANI ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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>
6

Section 6

ADD TRANDATA SOURCE.DASANI 3. Add table to Extract TABLE SOURCE.DASANI; 4. Add table to Pump

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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>
7

Section 7

4. Add table to Pump TABLE SOURCE.DASANI; 5. Restart Extract

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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>
8

Section 8

6. Restart Pump 7. Verify Status

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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>
9

Section 9

8. Capture current SCN 2298283 <---- 9. Export Table using FLASHBACK_SCN

Code/Command (click line numbers to comment):

1
2
3
4
5
6
SQL>
select current_scn from v$database;
CURRENT_SCN
-----------
2298283 <----
SQL>
10

Section 10

select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR'; DATA_PUMP_DIR expdp \'/ as sysdba\' directory=data_pump_dir dumpfile=source_dasani.dmp logfile=source_dasani.log tables=source.dasani flashback_scn= 2298283 . . exported "SOURCE"."DASANI" 5.687 KB 15 rows 10. Transfer dump to target server

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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 ~]$
11

Section 11

10. Transfer dump to target server scp /u01/app/oracle/admin/PSG01DAS/dpdump/source_dasani.dmp oracle@rac2:/u01/app/oracle/admin/USG01DAS/dpdump/ Target: 11. Import new table

Code/Command (click line numbers to comment):

1
2
3
4
5
[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 ~]$
12

Section 12

11. Import new table 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 . . imported "TARGET"."DASANI" 5.687 KB 15 rows 12. Add table to Replicat

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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 ~]$
13

Section 13

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. Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283); 13. Restart Replicat

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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>
14

Section 14

14. Verify Status 15. Data sync verify Source:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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>
15

Section 15

Source: Target: SELECT COUNT(*) FROM TARGET.DASANI;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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>

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

Section 16

SELECT COUNT(*) FROM TARGET.DASANI; It's sync now, next step we need to FILTER PARAMETER from Relicat 16. Remove the FILTER parameters replicat dblogin UserIdAlias OGGADMIN

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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>
17

Section 17

At EOF, no more records to process -- From Map SOURCE.DASANI, target TARGET.DASANI, FILTER ( @GETENV('TRANSACTION', 'CSN') > 2298283); -- To Map SOURCE.DASANI, target TARGET.DASANI; 17. Restart the replicat 18. Verify Status

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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>
18

Section 18

18. Verify Status 19. Re-verify data sync Source:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
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>
19

Section 19

SELECT COUNT(*) FROM SOURCE.DASANI; 20 <----- Target:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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>

SQL>
SELECT COUNT(*) FROM TARGET.DASANI;
COUNT(*)
----------
17
SQL> 

SQL>
SELECT COUNT(*) FROM TARGET.DASANI;
COUNT(*)
----------
20 <------
SQL>
20

Section 20

SELECT COUNT(*) FROM TARGET.DASANI; 20 <------ 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. 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. Reference:
21

Section 21

Reference: OGG How to Resync Tables / Schemas on Different SCN s in a Single Replicat (Doc ID 1339317.1)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!