we wanted to add new extract & replicat for existing table on both source and target and both has records and it has same table structure.
When we add replicate need to add the NODBcheckpoint or will pull the record from previous trail.
Here is the step by step table level replication procedure.
SENDER>create table t3 as select * from t2;
Table created.
SENDER>select count(*) from t3;
COUNT(*)
----------
10
RECEIVER>select count(*) from t3;
COUNT(*)
----------
10
[oracle@SOURCE]$ ggsci
GGSCI (SOURCE) 1> EDIT PARAMS ex3
The above command created the file /home/oracle/goldengate/dirprm/ex3.prm
Add the following parameters to the new file:
EXTRACT ex3
USERID ggusr, PASSWORD ggusr
EXTTRAIL /media/sf_12cR1/gg/source/extrail/ex
TABLE sender.t3;
Configure Data Pump Parameters
In this example the Data Pump process will be called dp3
On the source server create the parameter file for Data Pump process dp3:
[oracle@SOURCE]$ ggsci
GGSCI (SOURCE) 1> EDIT PARAMS dp3
The above command created the file /home/oracle/goldengate/dirprm/dp3.prm
Add the following parameters to the new file:
EXTRACT dp3
USERID ggusr, PASSWORD ggusr
RMTHOST 192.168.78.52, MGRPORT 7809
RMTTRAIL /gg/target/targettrail/rt
TABLE sender.t3;
Save and close the parameter file
Create Check Point Table
The check point table should be created in the target database.
On the target server login as the ggusr user and add the check point table:
[oracle@TARGET]$ ggsci
GGSCI (TARGET) 1> DBLOGIN USERID ggusr, PASSWORD ggusr
Successfully logged into database.
GGSCI (TARGET) 2> ADD CHECKPOINTTABLE ggusr.checkpointtable
Successfully created checkpoint table ggusr.checkpointtable.
The name of the check point table must be added to the GLOBALS file on the target server.
On the target server edit the GLOBALS file
[oracle@TARGET]$ ggsci
GGSCI (TARGET) 1> EDIT PARAMS ./GLOBALS
Add the CHECKPOINTTABLE parameter to the existing file. For example:
GGSCHEMA ggusr
CHECKPOINTTABLE ggusr.checkpointtable
Save and close the GLOBALS parameter file.
Configure Replication Parameters
On the target server create the parameter file for replication process rep3:
[oracle@TARGET]$ ggsci
GGSCI (TARGET) 1> EDIT PARAMS rep3
The above command created the file /home/oracle/goldengate/dirprm/rep3.prm
Add the following parameters to the new file:
REPLICAT rep3
USERID ggusr, PASSWORD ggusr
ASSUMETARGETDEFS
DISCARDFILE /media/sf_racattack12c/gg/target/discardfile, PURGE
MAP sender.t3, TARGET receiver.t3;
Note that the DISCARDFILE parameter includes the PURGE keyword. If PURGE is not specified them the replication process will fail the second time it is started. Alternatively use the APPEND keyword to append output to the existing file.
Configure Supplemental Logging for Replicated Tables
On the source server configure supplemental logging for all tables that will be replicated. In this example there is only one table (t3)
Supplemental logging can be configured by any user that has privileges to modify the underlying database table.
[oracle@SOURCE]$ ggsci
GGSCI (SOURCE) 1> DBLOGIN USERID sender, PASSWORD sender
Successfully logged into database.
GGSCI (SOURCE) 2> ADD TRANDATA t3
Logging of supplemental redo data enabled for table sender.t3.
Add the Extract Process
GGSCI (collabn1.racattack) 5> ADD TRANDATA t3
2017-10-14 19:26:42 WARNING OGG-06439 No unique key is defined for table t3. 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 SENDER.t3.
TRANDATA for scheduling columns has been added on table 'SENDER.t3'.
GGSCI (collabn1.racattack) 6> delete trandata t3
Logging of supplemental redo log data disabled for table SENDER.t3.
TRANDATA for scheduling columns has been disabled on table 'SENDER.t3'.
ALTER TABLE t3 ADD CONSTRAINT t3_ix1 PRIMARY KEY (object_id);
GGSCI (collabn1.racattack) 7> ADD TRANDATA t3
Logging of supplemental redo data enabled for table SENDER.t3.
TRANDATA for scheduling columns has been added on table 'SENDER.t3'.
On the source server add the Extract process (ex3)
[oracle@SOURCE]$ ggsci
GGSCI (SOURCE) 1> ADD EXTRACT ex3, TRANLOG, BEGIN NOW
EXTRACT added.
Add the Extract Trail
On the source server add the Extract trail (/media/sf_12cR1/gg/source/extrail)
[oracle@SOURCE]$ ggsci
GGSCI (SOURCE) 1> ADD EXTTRAIL /media/sf_12cR1/gg/source/extrail/ex, EXTRACT ex3
EXTTRAIL added.
Add the Data Pump Process
On the source server add the Data Pump process (dp3)
[oracle@SOURCE]$ ggsci
GGSCI (SOURCE) 1> ADD EXTRACT dp3 EXTTRAILSOURCE /media/sf_12cR1/gg/source/extrail/ex
EXTRACT added.
Add the Data Pump Trail
On the source server add the Data Pump trail (/home/oracle/gg/dirdat/rt). This trail is created on the target server. However, the name is required in order to set up the Data Pump process on the source server.
[oracle@SOURCE]$ ggsci
GGSCI (SOURCE) 1> ADD RMTTRAIL /gg/target/targettrail/rt, EXTRACT dp3
RMTTRAIL added.
Add the Replication Process
On the target server add the Replication process (rep3)
[oracle@TARGET]$ ggsci
GGSCI (TARGET) 1> ADD REPLICAT rep3, EXTTRAIL /gg/target/targettrail/rt
alter REPLICAT rep3, EXTTRAIL /gg/target/targettrail/rt
GGSCI (collabn2.racattack) 77> edit params rep3
GGSCI (collabn2.racattack) 78> alter REPLICAT rep3, EXTTRAIL /gg/target/targettrail/rt
ERROR: REPLICAT rep3 is running and cannot be altered (1,11,Resource temporarily unavailable).
GGSCI (collabn2.racattack) 79> stop rep3
Sending STOP request to REPLICAT rep3 ...
Request processed.
GGSCI (collabn2.racattack) 80> alter REPLICAT rep3, EXTTRAIL /gg/target/targettrail/rt
REPLICAT altered.
GGSCI (collabn2.racattack) 81> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT added.
Start GoldenGate
Start Manager
On the source server, start the GoldenGate manager:
[oracle@SOURCE]$ ggsci
GGSCI (SOURCE) 1> START MANAGER
Manager started.
On the target server, start the GoldenGate manager:
[oracle@TARGET]$ ggsci
GGSCI (TARGET) 1> START MANAGER
Manager started.
Start Extract Process
On the source server start the Extract (ex3)
[oracle@SOURCE]$ ggsci
GGSCI (SOURCE) 1> START EXTRACT ex3
Sending START request to MANAGER ...
EXTRACT ex3 starting
INSERT INTO t3 (object_id,object_name,object_type)
SELECT object_id,object_name,object_type
FROM dba_objects
WHERE object_id BETWEEN 11000 AND 12000;
INSERT INTO t3 (object_id,object_name,object_type,owner)
SELECT object_id,object_name,object_type
FROM dba_objects WHERE object_id < 10000;
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
INSERT INTO t3 (OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, OWNER)
VALUES (789644, 'bala', 'bala2', 'GG');
WHERE object_id BETWEEN 12000 AND 12100;
alter table t3 add (place VARCHAR2(128));
INSERT INTO t3 (OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, OWNER,place)
VALUES (289644, '2bala', '2bala2', '2GG','2plano');
REPLICAT rep3
USERID ggusr, PASSWORD ggusr
ASSUMETARGETDEFS
DISCARDFILE /gg/target/discardfile112, PURGE
MAP sender.t3, TARGET receiver.t3;
~
~
GGSCI (collabn2.racattack) 2> delete replicat rep3
ERROR: Could not delete DB checkpoint for REPLICAT rep3 (Database login required to delete database checkpoint).
GGSCI (collabn2.racattack) 3> DBLOGIN USERID ggusr, PASSWORD ggusr
Successfully logged into database.
GGSCI (collabn2.racattack) 4> delete replicat rep3
Deleted REPLICAT rep3.
oracle@collabn2 dirprm]$ ls -ltr
total 48
-rwxr-x--- 1 oracle oinstall 103 Sep 24 2013 jagent.prm
-rw-r--r-- 1 oracle oinstall 9 Dec 19 2015 mgr.prm
-rw-r----- 1 oracle oinstall 336 Dec 19 2015 rep_gg2.prm
-rw-r----- 1 oracle oinstall 120 Dec 25 2015 tar_rep.prm
-rw-r----- 1 oracle oinstall 199 Dec 27 2015 dpump2.prm
-rw-r----- 1 oracle oinstall 292 Dec 27 2015 ext3.prm
-rw-r----- 1 oracle oinstall 348 Feb 2 2016 rep_grp2.prm
-rw-r----- 1 oracle oinstall 197 May 4 2016 load2.prm
-rw-r----- 1 oracle oinstall 181 Oct 9 00:47 load3.prm
-rw-r----- 1 oracle oinstall 159 Oct 14 18:17 rep1.prm
-rw-r----- 1 oracle oinstall 140 Oct 14 20:08 rep3.prm_bak_oct14
-rw-r----- 1 oracle oinstall 140 Oct 14 20:08 rep3.prm
[oracle@collabn2 dirprm]$ cd ..
ADD REPLICAT rep3, EXTTRAIL /gg/target/targettrail/rt
GGSCI (collabn2.racattack) 12> alter replicat rep3 begin now
REPLICAT altered.
alter replicat rep3 begin 2017-10-15 00:04:30
INSERT INTO t3 (OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, OWNER,place)
VALUES (896424, '6bala', '6bala2', '6GG','6plano');
INSERT INTO t3 (OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, OWNER,place)
VALUES (108424, '8bala', '8bala2', '8GG','8plano');
Testing
-------
SENDER>SELECT COUNT(*) FROM T3;
COUNT(*)
----------
12
RECEIVER>SELECT COUNT(*) FROM T3;
COUNT(*)
----------
12
