Step-by-step Oracle GoldenGate configuration to replicate one-to-one DMLs

Oracle GoldenGate software enables your mission-critical systems to have continuous availability and access to real-time data. It offers a robust yet easy platform for moving real-time transactional data between operational and analytical systems to enable both high availability and real time integration. Oracle GoldenGates captures, filters, transforms, delivers transactional data in real-time, across Oracle and heterogeneous environments with very low impact and preserved transaction integrity. The transaction data management provides read consistency, maintaining referential integrity between source and target systems.
We have 2 databases:
Source:
SID: london
Oracle 11g release 1, ASM @ Oracle Linux Enterprise 5.5 32-bit
and
Target:
SID: paris
Oracle 11g release 1, ASM @ Oracle Linux Enterprise 5.7 32-bit.
In this post we will show how to configure GoldenGate to replicate transactions from schema "MYUSER" @ SID london to
schema "MYREMUSER" @ SID paris.
1. Download GoldenGate software from http://edelivery.oracle.com
In our example we'll be using Oracle GoldenGate v11.1.1.1.0 for Oracle 11g on Linux x86


2. On both source and target machines login with oracle user and create directory for GoldenGate software.
Source Machine:
[oracle@london ~]$ mkdir golden_gate
[oracle@london ~]$ cd golden_gate/
Target Machine:
[oracle@paris ~]$ mkdir golden_gate
[oracle@paris ~]$
3. Place and extract the Oracle GoldenGate Mediapack zipped file on the directories we've just created, both machines.
Source Machine:
[oracle@london golden_gate]$ ls
V26188-01.zip
[oracle@london golden_gate]$ unzip V26188-01.zip
Archive:  V26188-01.zip
  inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar
  inflating: OGG_WinUnix_Rel_Notes_11.1.1.1.0.pdf
  inflating: README.txt
[oracle@london golden_gate]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar  OGG_WinUnix_Rel_Notes_11.1.1.1.0.pdf  README.txt  V26188-01.zip
[oracle@london golden_gate]$ tar -xvof fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@london golden_gate]$ ls
bcpfmt.tpl                 ddl_ora10upCommon.sql     demo_ora_insert.sql                 marker_setup.sql
bcrypt.txt                 ddl_ora11.sql             demo_ora_lob_create.sql             marker_status.sql
cfg                        ddl_ora9.sql              demo_ora_misc.sql                   mgr
chkpt_ora_create.sql       ddl_oracle.tpl            demo_ora_pk_befores_create.sql      notices.txt
cobgen                     ddl_pin.sql               demo_ora_pk_befores_insert.sql     
OGG_WinUnix_Rel_Notes_11.1.1.1.0.pdf
convchk                    ddl_purgeRecyclebin.sql   demo_ora_pk_befores_updates.sql     params.sql
db2cntl.tpl                ddl_remove.sql            dirjar                              prvtclkm.plb
ddl_access.tpl             ddl_session1.sql          emsclnt                             pw_agent_util.sh
ddl_cleartrace.sql         ddl_session.sql           extract                             README.txt
ddlcob                     ddl_setup.sql             fbo_ggs_Linux_x86_ora11g_32bit.tar  remove_seq.sql
ddl_db2_os390.tpl          ddl_sqlmx.tpl             freeBSD.txt                         replicat
ddl_db2.tpl                ddl_status.sql            ggMessage.dat                       reverse
ddl_ddl2file.sql           ddl_staymetadata_off.sql  ggsci                               role_setup.sql
ddl_disable.sql            ddl_staymetadata_on.sql   help.txt                            sequence.sql
ddl_enable.sql             ddl_sybase.tpl            jagent.sh                           server
ddl_filter.sql             ddl_tandem.tpl            keygen                              sqlldr.tpl
ddlgen                     ddl_tracelevel.sql        libicudata.so.38                    tcperrs
ddl_informix.tpl           ddl_trace_off.sql         libicui18n.so.38                    UserExitExamples
ddl_mss.tpl                ddl_trace_on.sql          libicuuc.so.38                      usrdecs.h
ddl_mysql.tpl              defgen                    libxerces-c.so.28                   V26188-01.zip
ddl_nopurgeRecyclebin.sql  demo_more_ora_create.sql  libxml2.txt                         zlib.txt
ddl_nssql.tpl              demo_more_ora_insert.sql  logdump
ddl_ora10.sql              demo_ora_create.sql       marker_remove.sql
[oracle@london golden_gate]$
Target Machine:
[oracle@paris golden_gate]$ ls
V26188-01.zip
[oracle@paris golden_gate]$ unzip V26188-01.zip
Archive:  V26188-01.zip
  inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar
  inflating: OGG_WinUnix_Rel_Notes_11.1.1.1.0.pdf
  inflating: README.txt
[oracle@paris golden_gate]$ tar -xvof fbo_ggs_Linux_x86_ora11g_32bit.tar
4. Create GoldenGate -related directories on both machines.
Source Machine:
[oracle@london golden_gate]$ pwd
/home/oracle/golden_gate
[oracle@london golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (london.localdomain) 1> CREATE SUBDIRS
Creating subdirectories under current directory /home/oracle/golden_gate
Parameter files                /home/oracle/golden_gate/dirprm: created
Report files                   /home/oracle/golden_gate/dirrpt: created
Checkpoint files               /home/oracle/golden_gate/dirchk: created
Process status files           /home/oracle/golden_gate/dirpcs: created
SQL script files               /home/oracle/golden_gate/dirsql: created
Database definitions files     /home/oracle/golden_gate/dirdef: created
Extract data files             /home/oracle/golden_gate/dirdat: created
Temporary files                /home/oracle/golden_gate/dirtmp: created
Veridata files                 /home/oracle/golden_gate/dirver: created
Veridata Lock files            /home/oracle/golden_gate/dirver/lock: created
Veridata Out-Of-Sync files     /home/oracle/golden_gate/dirver/oos: created
Veridata Out-Of-Sync XML files /home/oracle/golden_gate/dirver/oosxml: created
Veridata Parameter files       /home/oracle/golden_gate/dirver/params: created
Veridata Report files          /home/oracle/golden_gate/dirver/report: created
Veridata Status files          /home/oracle/golden_gate/dirver/status: created
Veridata Trace files           /home/oracle/golden_gate/dirver/trace: created
Stdout files                   /home/oracle/golden_gate/dirout: created
GGSCI (london.localdomain) 2> exit
[oracle@london golden_gate]$
Target Machine:
[oracle@paris golden_gate]$ pwd
/home/oracle/golden_gate
[oracle@paris golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (paris.localdomain) 1> CREATE SUBDIRS
Creating subdirectories under current directory /home/oracle/golden_gate
Parameter files                /home/oracle/golden_gate/dirprm: created
Report files                   /home/oracle/golden_gate/dirrpt: created
Checkpoint files               /home/oracle/golden_gate/dirchk: created
Process status files           /home/oracle/golden_gate/dirpcs: created
SQL script files               /home/oracle/golden_gate/dirsql: created
Database definitions files     /home/oracle/golden_gate/dirdef: created
Extract data files             /home/oracle/golden_gate/dirdat: created
Temporary files                /home/oracle/golden_gate/dirtmp: created
Veridata files                 /home/oracle/golden_gate/dirver: created
Veridata Lock files            /home/oracle/golden_gate/dirver/lock: created
Veridata Out-Of-Sync files     /home/oracle/golden_gate/dirver/oos: created
Veridata Out-Of-Sync XML files /home/oracle/golden_gate/dirver/oosxml: created
Veridata Parameter files       /home/oracle/golden_gate/dirver/params: created
Veridata Report files          /home/oracle/golden_gate/dirver/report: created
Veridata Status files          /home/oracle/golden_gate/dirver/status: created
Veridata Trace files           /home/oracle/golden_gate/dirver/trace: created
Stdout files                   /home/oracle/golden_gate/dirout: created
GGSCI (paris.localdomain) 2> exit
[oracle@paris golden_gate]$
5. Check tnsnames configured properly on both machines.
Source Machine:
[oracle@london ~]$ cd $ORACLE_HOME/network
[oracle@london network]$ pwd
/u01/app/oracle/product/11.1.0/db_1/network
[oracle@london network]$ cd admin/
[oracle@london admin]$ cat tnsnames.ora
ASM =
(DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.1.110)(PORT=1521))
  )
  (CONNECT_DATA =
   (SERVICE_NAME=+ASM)
  )
)
LONDON =
(DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.1.110)(PORT=1521))
  )
  (CONNECT_DATA =
   (SERVICE_NAME=london)
  )
)
[oracle@london admin]$
[oracle@london admin]$ sqlplus system/xxx@london
SQL*Plus: Release 11.1.0.6.0 - 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
...
SQL> exit
[oracle@london admin]$ sqlplus sys/xxx@asm as sysdba
SQL*Plus: Release 11.1.0.6.0 -
...
SQL> exit
Target Machine:
[oracle@paris ~]$ cd /u01/app/oracle/product/11.1.0/db_1/network/admin/
[oracle@paris admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.120)(PORT = 1524))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )
PARIS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.120)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = parisdb)
    )
  )
[oracle@paris admin]$ sqlplus sys/xxx@asm as sysdba
SQL*Plus: Release 11.1.0.6.0 -
...
SQL> exit
[oracle@paris admin]$ sqlplus sys/xxx@paris as sysdba
SQL*Plus: Release 11.1.0.6.0 - ...
...
SQL> exit
6. To extract the committed transactions from the source Oracle database's online redo log files, as a minimum the database must be configured for supplemental logging on Primary Key columns. Every source table must have a Primary Key enabled else GoldenGate will define all viable columns to represent uniqueness. This will increase the volume of supplemental log data being written to the redo logs and subsequent trail files.
Source Database:
[oracle@london ~]$ sqlplus / as sysdba
SQL> alter database
  2  add supplemental log data (primary key) columns;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui from v$database;
SUPPLEME SUP SUP
-------- --- ---
IMPLICIT YES NO
SQL>
7. Create a GoldenGate Administrator user account on both source and target databases. This account will provide access to the database tables for GoldenGate configuration and runtime operations.
Source Machine:
[oracle@london ~]$ sqlplus / as sysdba
SQL> create user gg_admin identified by gg_admin;
User created.
SQL> grant dba to gg_admin
  2  ;
Grant succeeded.
SQL>
Target Machine:
[oracle@paris ~]$ sqlplus / as sysdba
SQL> create user gg_admin identified by gg_admin;
User created.
SQL> grant dba to gg_admin;
Grant succeeded.
SQL>
8. Let's create some sample table on the source database schema:
Source Machine:
[oracle@london ~]$ sqlplus myuser/123
SQL> create table MYTABLE(id number, firstname varchar2(100), birthday date);
Table created.
SQL> alter table MYTABLE add constraint MYTABLE_PK primary key(ID);
Table altered.
SQL> insert into MYTABLE values(1, 'Linda', '01-Jan-1999');
1 row created.
SQL> insert into MYTABLE values(2, 'James', '02-Mar-1960');
1 row created.
SQL>  insert into MYTABLE values(3, 'Maggy', '05-Apr-2002');
1 row created.
SQL> insert into MYTABLE values(4, 'Dylan', '07-Jun-1985');
1 row created.
SQL> commit;
SQL> set lines 120
SQL> column firstname format a30
SQL> select * from MYTABLE;
        ID FIRSTNAME                      BIRTHDAY
---------- ------------------------------ ---------
         1 Linda                          01-JAN-99
         2 James                          02-MAR-60
         3 Maggy                          05-APR-02
         4 Dylan                          07-JUN-85
SQL>
9. Let's create the same structure table on the target db. Note: we create MYTABLE structure only. The MYTABLE table is empty on the target schema.
Target Machine:
[oracle@paris ~]$ sqlplus myremuser/123
SQL> create table MYTABLE(id number, firstname varchar2(100), birthday date);
Table created.
SQL> alter table MYTABLE add constraint MYTABLE_PK primary key(ID);
Table altered.
10. Configure GoldenGate to add supplemental log data to the source table(s) using GGSCI's ADD TRANDATA command.
Source Machine:
[oracle@london ~]$ cd golden_gate/
[oracle@london golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (london.localdomain) 2> dblogin userid gg_admin password gg_admin
Successfully logged into database.
GGSCI (london.localdomain) 3> add trandata myuser.mytable
Logging of supplemental redo data enabled for table MYUSER.MYTABLE.
GGSCI (london.localdomain) 5> info trandata myuser.*
Logging of supplemental redo log data is enabled for table MYUSER.MYTABLE
GGSCI (london.localdomain) 6> exit
[oracle@london golden_gate]$
10. Next, we should configure the Manager process both on source and target machines.
The Manager process runs on both source and target machines. It controls activities such as starting, monitoring and restarting processes; allocating data storage; and reporting errors and events. The Manager process can have either of the following statuses: STOPPED or RUNNING.
Source Machine:
[oracle@london golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (london.localdomain) 1> EDIT PARAMS MGR
PORT 7809
PURGEOLDEXTRACTS ./dirdat/sa*, USECHECKPOINTS, MINKEEPHOURS 2
Optionally you can edit this parameter file manually.
It's placed at /home/oracle/golden_gate/dirprm
[oracle@london golden_gate]$ pwd
/home/oracle/golden_gate
[oracle@london golden_gate]$ cd dirprm
[oracle@london dirprm]$ cat mgr.prm
PORT 7809
PURGEOLDEXTRACTS ./dirdat/sa*, USECHECKPOINTS, MINKEEPHOURS 2
[oracle@london dirprm]$
GGSCI (london.localdomain) 2> start mgr
Manager started.
GGSCI (london.localdomain) 3> info mgr
Manager is running (IP port london.localdomain.7809).
GGSCI (london.localdomain) 4>
Target Machine:
[oracle@paris ~]$ cd golden_gate/
[oracle@paris golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (paris.localdomain) 1> EDIT PARAMS MGR
PORT 7809
GGSCI (paris.localdomain) 2> start mgr
Manager started.
GGSCI (paris.localdomain) 3> info mgr
Manager is running (IP port paris.localdomain.7809).
GGSCI (paris.localdomain) 4> exit
The Manager process must be configured on both source and target machines and must be started before any other configuration tasks are performed.
11. Configure Extract process on the source machine.
Source Machine:
[oracle@london golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (london.localdomain) 1>  edit params ext01
EXTRACT EXT01
SETENV (ORACLE_SID=london)
USERID gg_admin, PASSWORD gg_admin
EXTTRAIL ./dirdat/sa
TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD xxx
TABLE MYUSER.MYTABLE;
Important: the ORACLE_SID is case sensitive. It should be spelled exactly as it appears at $ORACLE_HOME/dbs/
GGSCI (london.localdomain) 2> add extract ext01, tranlog, begin now, threads 1
EXTRACT added.
GGSCI (london.localdomain) 3> add exttrail  ./dirdat/sa, extract ext01, megabytes 50
EXTTRAIL added.
GGSCI (london.localdomain) 4> start extract ext01
Sending START request to MANAGER ...
EXTRACT EXT01 starting
GGSCI (london.localdomain) 5> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXT01       00:00:00      00:00:11
In case of errors, check the GoldenGate errors log file: /home/oracle/golden_gate/ggserr.log
12. Configure Data Pump process on the source machine.
The GoldenGate Data Pump process sends the source trail files data in large blocks across a TCP/IP network to the target system. This is the best practice and we'll adopt it for our extract configuration. The Data Pump process is in essence an Extract process that sends changed data to the target system.
Source Machine:
[oracle@london golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (london.localdomain) 1> EDIT PARAMS EPMP01
EXTRACT EPMP01
PASSTHRU
RMTHOST 192.168.1.120, MGRPORT 7809
RMTTRAIL ./dirdat/ta
TABLE MYUSER.MYTABLE;
GGSCI (london.localdomain) 2> add extract epmp01, exttrailsource ./dirdat/sa, begin now
EXTRACT added.
GGSCI (london.localdomain) 3> add rmttrail ./dirdat/ta, extract epmp01
RMTTRAIL added.
GGSCI (london.localdomain) 4> start extract epmp01
Sending START request to MANAGER ...
EXTRACT EPMP01 starting
GGSCI (london.localdomain) 5> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EPMP01      00:00:00      00:03:26
EXTRACT     RUNNING     EXT01       00:00:00      00:00:06
GGSCI (london.localdomain) 6>
13. Run Initial Data Load using Direct Load Method.
Source Machine:
GGSCI (london.localdomain) 1> ADD EXTRACT EINI01, SOURCEISTABLE
GGSCI (london.localdomain) 2> info extract *, tasks
EXTRACT    EINI01    Last Started 2011-10-07 13:17   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table MYUSER.MYTABLE
                     2011-10-07 13:17:28  Record 3
Task                 SOURCEISTABLE
GGSCI (london.localdomain) 3> edit params eini01
EXTRACT EINI01
USERID gg_admin, PASSWORD gg_admin
RMTHOST 192.168.1.120, MGRPORT 7809
RMTTASK REPLICAT, GROUP RINI01
TABLE MYUSER.MYTABLE;
Target Machine:
GGSCI (paris.localdomain) 1> ADD REPLICAT RINI01, SPECIALRUN
GGSCI (paris.localdomain) 2> info replicat *, TASKS
REPLICAT   RINI01    Initialized   2011-10-07 17:02   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:25:20 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN
GGSCI (paris.localdomain) 3> edit param RINI01
REPLICAT RINI01
ASSUMETARGETDEFS
USERID gg_admin, PASSWORD gg_admin
DISCARDFILE ./dirrpt/RINI01.dsc, PURGE
MAP MYUSER.*, TARGET MYREMUSER.*;
Execute the Initial Load Process:
Source Machine:
START EXTRACT EINI01
VIEW REPORT EINI01
You'll see similar to this output:
...
Processing table MYUSER.MYTABLE
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
Report at 2011-10-07 13:17:30 (activity since 2011-10-07 13:17:23)
Output to RINI01:
From Table MYUSER.MYTABLE:
       #                   inserts:         3
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
REDO Log Statistics
  Bytes parsed                    0
  Bytes output                  335
Check the same on the target machine:
Target Machine:
GGSCI (paris.localdomain) 1> VIEW REPORT RINI01
...
***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
Wildcard MAP resolved (entry MYUSER.*):
  MAP MYUSER.MYTABLE, TARGET MYREMUSER.MYTABLE;
Using following columns in default map by name:
  ID, FIRSTNAME, BIRTHDAY
Using the following key columns for target table MYREMUSER.MYTABLE: ID.
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
Report at 2011-10-07 17:17:37 (activity since 2011-10-07 17:17:32)
From Table MYUSER.MYTABLE to MYREMUSER.MYTABLE:
       #                   inserts:         3
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
...
You can see the initial loaded data on the target table. Remember it was empty when we've created MYTABLE on the target db. Let's check out it now:
[oracle@paris ~]$ sqlplus myremuser/123@paris
SQL> set lines 120
SQL> column firstname format a30
SQL> select * from mytable;
        ID FIRSTNAME                      BIRTHDAY
---------- ------------------------------ ---------
         1 Linda                          01-JAN-99
         2 James                          02-MAR-60
         3 Maggy                          05-APR-02
         4 Dylan                          07-JUN-85
SQL>
14. Next, we'll need to create the Replicat process on the target machine.
The Replicat process (i.e. in simple words "apply process") is the final step in the data delivery. It reads the trail file on target machine and applies it on the target database in the form of DML or DDL. The GoldenGate Replicat process will regularly checkpoint its read and write position, typically to a file. The checkpoint data ensures that GoldenGate can recover its processes without data loss in the case of failure.
Target Machine:
[oracle@paris golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (paris.localdomain) 1> edit params rep01
REPLICAT REP01
SETENV (ORACLE_SID=parisdb)
USERID gg_admin, PASSWORD gg_admin
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rolap01.dsc, PURGE
MAP MYUSER.*, TARGET MYREMUSER.*;
Optionally you can edit this file manually, it's placed at /home/oracle/golden_gate/dirprm directory.
It's important to set up the checkpoint table on the target machine.
GGSCI (paris.localdomain) 2> edit params ./GLOBALS
Note: GLOBALS must be uppercase.
In the text editor type:
CHECKPOINTTABLE GGSCHKPT
GGSCI (paris.localdomain) 3> exit
Note: it's important to exit the session in which the changes were made.
[oracle@paris golden_gate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x86, 32bit (optimized), Oracle 11g on Apr 21 2011 22:38:06
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (paris.localdomain) 1> dblogin userid gg_admin, password gg_admin
Successfully logged into database.
GGSCI (paris.localdomain) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (GGSCHKPT)...
Successfully created checkpoint table GGSCHKPT.
GGSCI (paris.localdomain) 3> add replicat rep01, exttrail ./dirdat/ta
REPLICAT added.
GGSCI (paris.localdomain) 4>  start replicat rep01
Sending START request to MANAGER ...
REPLICAT REP01 starting
GGSCI (paris.localdomain) 5> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     REP01       00:00:00      00:00:01
GGSCI (paris.localdomain) 6>
In case of errors, check the file ggserr.log
placed at /home/oracle/golden_gate/dirprm/ on the target machine.
15. Let's check how the data will be replicated from source to target db now:
[oracle@london ~]$ sqlplus myuser/123@london
Source Machine:
SQL> set lines 120
SQL> column firstname format a30
SQL> select * from MYTABLE;
        ID FIRSTNAME                      BIRTHDAY
---------- ------------------------------ ---------
         1 Linda                          01-JAN-99
         2 James                          02-MAR-60
         3 Maggy                          05-APR-02
         4 Dylan                          07-JUN-85
SQL>
Target Machine:
In parallel check out target db:
SQL> select * from mytable;
        ID FIRSTNAME                      BIRTHDAY
---------- ------------------------------ ---------
         1 Linda                          01-JAN-99
         2 James                          02-MAR-60
         3 Maggy                          05-APR-02
         4 Dylan                          07-JUN-85
SQL> show user
USER is "MYREMUSER"
SQL>
Now let’s do some changes on the source db schema and check if it'll be applied on the target db schema:
Source Machine:
SQL> show user;
USER is "MYUSER"
SQL> insert into MYTABLE values (5, 'Nataly', '26-Mar-1984');
1 row created.
SQL> commit;
Commit complete.
Target Machine:
SQL> show user
USER is "MYREMUSER"
SQL> select * from mytable;
        ID FIRSTNAME                      BIRTHDAY
---------- ------------------------------ ---------
         1 Linda                          01-JAN-99
         2 James                          02-MAR-60
         3 Maggy                          05-APR-02
         4 Dylan                          07-JUN-85
         5 Nataly                         26-MAR-84
SQL>
Source Machine:
SQL> show user;
USER is "MYUSER"
SQL> update mytable
  2  set firstname = firstname || '#';
5 rows updated.
SQL> commit;
Commit complete.
SQL> select * from mytable;
        ID FIRSTNAME                      BIRTHDAY
---------- ------------------------------ ---------
         1 Linda#                         01-JAN-99
         2 James#                         02-MAR-60
         3 Maggy#                         05-APR-02
         4 Dylan#                         07-JUN-85
         5 Nataly#                        26-MAR-84
SQL>
Target Machine:
SQL> SQL>  show user
USER is "MYREMUSER"
SQL>  select * from mytable;
        ID FIRSTNAME                      BIRTHDAY
---------- ------------------------------ ---------
         1 Linda#                         01-JAN-99
         2 James#                         02-MAR-60
         3 Maggy#                         05-APR-02
         4 Dylan#                         07-JUN-85
         5 Nataly#                        26-MAR-84
SQL>
Source Machine:
SQL> delete mytable
  2  where id < 5;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> show user;
USER is "MYUSER"
SQL>
Target Machine:
SQL> select * from mytable;
        ID FIRSTNAME                      BIRTHDAY
---------- ------------------------------ ---------
         5 Nataly#                        26-MAR-84
SQL> show user;
USER is "MYREMUSER"
SQL>