AskDba.org Weblog

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.