In this post we discussed about GoldenGate(GG) concepts and setting GG without DataPump Process.
Now I will talk about DataPump process and show how to setup GG replication which also uses DataPump.
In GG environment, extract process will write the data to local trail files and then pump process reads from this local trail file and sends the data, over the network, to destination trail file where this will be read by replicat process to write the changes to destination database.
The advantage of using datapump process is to improve the performance of overall replication as it will free-up the extract process from sending the data to destination side.
To setup this, download and extract the GG software as discussed in earlier post(Step 1-7).
Once the GG software is extracted, go the directory where it is extracted, know as GG home directory.
In this case it will be /u01/app/GGS.
1. Configure and start Manager process:
GGSCI (gg1.company.com) 1> edit params mgr
It will open the parameter file for manager. Enter the following in the file:
PORT 7809
save and quit the file.
2. Start the Manager process:
GGSCI (gg1.company.com) 2> start mgr Manager started.
3.
GGSCI (gg1.company.com) 3> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING MANAGER RUNNING
4. Now add and configure extract process:
The syntax to be used is:
ADD EXTRACT <extract name>, TRANLOG, BEGIN NOW GGSCI (gg1.company.com) 2>add extract ext_gg1, tranlog, begin now Extract Added GGSCI (gg1.company.com) 1> info * EXTRACT EXT_GG1 Initialized 2012-06-10 16:15 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:01:45 ago) Log Read Checkpoint Oracle Redo Logs 2012-06-10 16:15:20 Seqno 0, RBA 0
5. Configure the trail file to which extract will write the records:
Syntax:
ADD EXTTRAIL <extract trail path/two character trail id> ,EXTRACT <extract name>, Megabytes <n> GGSCI (gg1.company.com) 2> add exttrail /u01/app/GGS/dirdat/lt, extract ext_gg1, megabytes 50 EXTTRAIL added.
6. Create extract parameter file:
GGSCI (gg1.company.com) 1> edit params ext_gg1 extract ext_gg1 userid gggate, password oracle exttrail /u01/app/GGS/dirdat/lt
7. Configure Pump Process which will read the local extract trail file and send data to remote trail:
Syntax: ADD EXTRACT <pump name>, EXTTRAILSOURCE <extract trail Path/two character trail id> GGSCI (gg1.company.com) 1> add extract pump_gg1, exttrailsource /u01/app/GGS/dirdat/lt EXTRACT added. GGSCI (gg1.company.com) 2> info * EXTRACT EXT_GG1 Initialized 2012-06-10 16:15 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:16:56 ago) Log Read Checkpoint Oracle Redo Logs 2012-06-10 16:15:20 Seqno 0, RBA 0 EXTRACT PUMP_GG1 Initialized 2012-06-10 16:32 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:04 ago) Log Read Checkpoint File /u01/app/GGS/dirdat/lt000000 First Record RBA 0
8. Configure a remote trail file name with Pump process:
Syntax: ADD RMTTRAIL <pump trail path/two character trail id> ,EXTRACT <pump name>, Megabytes <n> GGSCI (gg1.company.com) 4> add rmttrail /u01/app/GGS/dirdat/rt, extract pump_gg1 RMTTRAIL added.
9. Add remote host information to Pump process:
— Remote host and remort manager port to write trail
RMTHOST <Remote hostname>, MGRPORT <Target manager port number>
— Remote trail info
RMTTRAIL <extract trail path/two character trail id> GGSCI (gg1.company.com) 1> edit params pump_gg1 [oracle@gg1 dirprm]$ cat pump_gg1.prm extract pump_gg1 passthru userid gggate, password oracle rmthost gg2.company.com, mgrport 7809 rmttrail /u01/app/GGS/dirdat/rt [oracle@gg1 dirprm]$ pwd /u01/app/GGS/dirprm
Target system:
==============
1. On target database, we need to configure Replicat process which will apply the changes
to target database after reading from remote trail file.
Syntax:
ADD REPLICAT <REPLICAT name>, EXTTRAIL <trail file path/two -- character trail id> Name of the replicat process. GGSCI (gg2.company.com) 8> add replicat rep_gg2, exttrail /u01/app/GGS/dirdat/rt ERROR: No checkpoint table specified for ADD REPLICAT.
2. Add checkpoint table.
A checkpoint table is a small table in the target database used by the Replicat
process to maintain checkpoints. A checkpoint records a known position in the trail from which
to start after an expected or unexpected shutdown. By default, a record of these checkpoints is maintained
in a file on disk in the GoldenGate directory. Optionally, the checkpoint record can also be maintained in a
checkpoint table in the target database.
More Information about check point table can be found in MOS note: 965698.1
GGSCI (gg2.company.com) 10> add checkpointtable chktbl Successfully created checkpoint table CHKTBL. GGSCI (gg2.company.com) 13> add replicat rep_gg2, exttrail /u01/app/GGS/dirdat/rt, checkpointtable gggate.chktbl REPLICAT added. GGSCI (gg2.company.com) 14> info * REPLICAT REP_GG2 Initialized 2012-06-10 16:58 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:00:12 ago) Log Read Checkpoint File /u01/app/GGS/dirdat/rt000000 First Record RBA 0
Now start all the processes on Source system.
GGSCI (gg1.company.com) 11> start manager Manager started. GGSCI (gg1.company.com) 12> start extract ext_gg1 Sending START request to MANAGER ... EXTRACT EXT_GG1 starting GGSCI (gg1.company.com) 13> start extract pump_gg1 Sending START request to MANAGER ... EXTRACT PUMP_GG1 starting GGSCI (gg1.company.com) 15> info * EXTRACT EXT_GG1 Last Started 2012-06-10 17:02 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:00 ago) Log Read Checkpoint Oracle Redo Logs 2012-06-10 17:02:20 Seqno 108, RBA 14447104 EXTRACT PUMP_GG1 Last Started 2012-06-10 17:02 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:30:18 ago) Log Read Checkpoint File /u01/app/GGS/dirdat/lt000000 First Record RBA 0 [oracle@gg1 GGS]$ ls -ltr /u01/app/GGS/dirdat/lt000000 -rw-rw-rw- 1 oracle oinstall 983 Jun 10 17:02 /u01/app/GGS/dirdat/lt000000 GGSCI (gg1.company.com) 2> exit
Once the processes are strated, we will create the sample tables for replication using Oracle provided scripts.
These scripts can be found under /u01/app/GGS
[oracle@gg1 GGS]$ ls -ltr *demo* -r--r--r-- 1 oracle oinstall 2275 Oct 15 2010 demo_ora_misc.sql -r--r--r-- 1 oracle oinstall 4015 Oct 15 2010 demo_ora_lob_create.sql -r--r--r-- 1 oracle oinstall 821 Oct 15 2010 demo_ora_insert.sql -r--r--r-- 1 oracle oinstall 883 Oct 15 2010 demo_ora_create.sql -r--r--r-- 1 oracle oinstall 2520 Oct 15 2010 demo_ora_pk_befores_updates.sql -r--r--r-- 1 oracle oinstall 1227 Oct 15 2010 demo_ora_pk_befores_insert.sql -r--r--r-- 1 oracle oinstall 1269 Oct 15 2010 demo_ora_pk_befores_create.sql -r--r--r-- 1 oracle oinstall 967 Oct 15 2010 demo_more_ora_insert.sql -r--r--r-- 1 oracle oinstall 1217 Oct 15 2010 demo_more_ora_create.sql
we will create tables for mapping:
Source:
======
SQL> connect scott/tiger Connected. SQL> @demo_ora_create.sql DROP TABLE tcustmer * ERROR at line 1: ORA-00942: table or view does not exist Table created. DROP TABLE tcustord * ERROR at line 1: ORA-00942: table or view does not exist Table created. SQL>
Target:
=======
SQL> conn scott/tiger Connected. SQL> @demo_ora_create.sql DROP TABLE tcustmer * ERROR at line 1: ORA-00942: table or view does not exist Table created. DROP TABLE tcustord * ERROR at line 1: ORA-00942: table or view does not exist Table created. SQL> select count(*) from tcustmer; COUNT(*) ---------- 0 SQL>
Once the structure is created, we will tell the extract, pump and replicat process
to capture/apply the changes done under these tables. For that we need to configure
the respective parameter files and these should look like:
GGSCI (gg1.company.com) 13> edit params ext_gg1 extract ext_gg1 userid gggate, password oracle exttrail /u01/app/GGS/dirdat/lt Table SCOTT.TCUSTMER; Table SCOTT.TCUSTORD; GGSCI (gg1.company.com) 14> edit params pump_gg1 extract pump_gg1 passthru userid gggate, password oracle rmthost gg2.company.com, mgrport 7809 rmttrail /u01/app/GGS/dirdat/rt Table SCOTT.TCUSTMER; Table SCOTT.TCUSTORD; GGSCI (gg2.company.com) 4> edit params rep_gg2 REPLICAT rep_gg2 ASSUMETARGETDEFS userid gggate, password oracle discardfile /u01/app/GGS/discard/rep_gg2_discard.txt, append, megabytes 10 MAP SCOTT.TCUSTMER, Target SCOTT.TCUSTMER; MAP SCOTT.TCUSTORD, Target SCOTT.TCUSTORD;
Once the parameter files are updated with the table information, we can use the
Oracle provided scripts to test the replication i.e “demo_ora_insert.sql”
After running the insert on source, we can see the count(*) on both source and target,
which will confirm the changes applied at the target side.