Oracle Tips & Technique: Oracle GoldenGate configuration Step By Step

Oracle GoldenGate configuration Step By Step

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: ORCL_1
Oracle 11g release 1, ASM @ Oracle Linux Enterprise 5.7 32-bit

Target:

SID: ORCL_2
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 "LOGIX" @ SID ORCL_1 to schema "MYLOGIX" @ SID ORCL_2.

Steps To Follow:

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@Khurram~]$ mkdir golden_gate
[oracle@Khurram~]$ cd golden_gate/

Target Machine:

[oracle@ORCL_2 ~]$ mkdir golden_gate

3. Place and extract the Oracle GoldenGate Mediapack zipped file on the directories we've just created, both machines.

Source Machine:

[oracle@ORCL_1 golden_gate]$ ls
V26188-01.zip
[oracle@ORCL_1 golden_gate]$ unzip V26188-01.zip

[oracle@ORCL_1 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@ORCL_1 golden_gate]$ tar -xvof fbo_ggs_Linux_x86_ora11g_32bit.tar

Target Machine:
[oracle@ORCL_2 golden_gate]$ ls
V26188-01.zip
[oracle@ORCL_2 golden_gate]$ unzip V26188-01.zip

[oracle@ORCL_2 golden_gate]$ tar -xvof fbo_ggs_Linux_x86_ora11g_32bit.tar
4. Create GoldenGate -related directories on both machines.

Source Machine:
[oracle@ORCL_1 golden_gate]$ pwd
/home/oracle/golden_gate
[oracle@ORCL_1 golden_gate]$ ./ggsci

GGSCI (ORCL_1.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 (ORCL_1.localdomain) 2> exit
[oracle@ORCL_1 golden_gate]$

Target Machine:
[oracle@ORCL_2 golden_gate]$ pwd
/home/oracle/golden_gate
[oracle@ORCL_2 golden_gate]$ ./ggsci

GGSCI (ORCL_2.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 (ORCL_2.localdomain) 2> exit
[oracle@ORCL_2 golden_gate]$

5. Check tnsnames configured properly on both machines.

Source Machine:
[oracle@Khurram~]$ cd $ORACLE_HOME/network
[oracle@ORCL_1 network]$ pwd
/u01/app/oracle/product/11.1.0/db_1/network
[oracle@ORCL_1 network]$ cd admin/
[oracle@ORCL_1 admin]$ cat tnsnames.ora
ASM =
(DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.4.121)(PORT=1521))
)
(CONNECT_DATA =
   (SERVICE_NAME=+ASM)
)
)
ORCL_1 =
(DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL=TCP)(HOST=192.168.4.121)(PORT=1521))
)
(CONNECT_DATA =
   (SERVICE_NAME=ORCL_1)
)
)
[oracle@ORCL_1 admin]$
[oracle@ORCL_1 admin]$ sqlplus system/xxx@ORCL_1
SQL*Plus: Release 11.1.0.6.0 -
Copyright (c) 1982, 2007, Oracle. All rights reserved.
...
SQL> exit
[oracle@ORCL_1 admin]$ sqlplus sys/xxx@asm as sysdba
SQL*Plus: Release 11.1.0.6.0 -
...
SQL> exit

Target Machine:
[oracle@ORCL_2 ~]$ cd /u01/app/oracle/product/11.1.0/db_1/network/admin/
[oracle@ORCL_2 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.4.131)(PORT = 1524))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
)
ORCL_2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.131)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = parisdb)
    )
)
[oracle@ORCL_2 admin]$ sqlplus sys/xxx@asm as sysdba
SQL*Plus: Release 11.1.0.6.0 -
...
SQL> exit
[oracle@ORCL_2 admin]$ sqlplus sys/xxx@ORCL_2 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@Khurram~]$ 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

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@Khurram~]$ 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@ORCL_2 ~]$ sqlplus / as sysdba
SQL> create user gg_admin identified by gg_admin;
User created.

SQL> grant dba to gg_admin;
Grant succeeded.

8. Let's create some sample table on the source database schema:

Source Machine:
[oracle@Khurram~]$ sqlplus LOGIX/123

SQL> create table MYTAB(id number, firstname varchar2(100), birthday date);
Table created.
SQL> alter table MYTAB add constraint MYTAB_PK primary key(ID);
Table altered.
SQL> insert into MYTAB values(1, 'Linda', '01-Jan-1999');
1 row created.
SQL> insert into MYTAB values(2, 'James', '02-Mar-1960');
1 row created.
SQL> insert into MYTAB values(3, 'Maggy', '05-Apr-2002');
1 row created.
SQL> insert into MYTAB values(4, 'Dylan', '07-Jun-1985');
1 row created.
SQL> commit;
SQL> set lines 120
SQL> column firstname format a30
SQL> select * from MYTAB;

        ID FIRSTNAME                      BIRTHDAY
---------- ------------------------------ ---------
         1 Linda                          01-JAN-99
         2 James                          02-MAR-60
         3 Maggy                          05-APR-02
         4 Dylan                          07-JUN-85

9. Let's create the same structure table on the target db. Note: we create MYTAB structure only. The MYTAB table is empty on the target schema.

Target Machine:
[oracle@ORCL_2 ~]$ sqlplus MYLOGIX/123
SQL> create table MYTAB(id number, firstname varchar2(100), birthday date);
Table created.
SQL> alter table MYTAB add constraint MYTAB_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@Khurram~]$ cd golden_gate/
[oracle@ORCL_1 golden_gate]$ ./ggsci

[oracle@ORCL_1 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@ORCL_1 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 (ORCL_1.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@ORCL_1 golden_gate]$ pwd
/home/oracle/golden_gate
[oracle@ORCL_1 golden_gate]$ cd dirprm
[oracle@ORCL_1 dirprm]$ cat mgr.prm
PORT 7809
PURGEOLDEXTRACTS ./dirdat/sa*, USECHECKPOINTS, MINKEEPHOURS 2
[oracle@ORCL_1 dirprm]$
GGSCI (ORCL_1.localdomain) 2> start mgr
Manager started.
GGSCI (ORCL_1.localdomain) 3> info mgr
Manager is running (IP port ORCL_1.localdomain.7809).
GGSCI (ORCL_1.localdomain) 4>

Target Machine:
[oracle@ORCL_2 ~]$ cd golden_gate/
[oracle@ORCL_2 golden_gate]$ ./ggsci

11. Configure Extract process on the source machine.

Source Machine:
[oracle@ORCL_1 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 (ORCL_1.localdomain) 1> edit params ext01
EXTRACT EXT01
SETENV (ORACLE_SID=ORCL_1)
USERID gg_admin, PASSWORD gg_admin
EXTTRAIL ./dirdat/sa
TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD xxx
TABLE LOGIX.MYTAB;
Important: the ORACLE_SID is case sensitive. It should be spelled exactly as it appears at $ORACLE_HOME/dbs/
GGSCI (ORCL_1.localdomain) 2> add extract ext01, tranlog, begin now, threads 1
EXTRACT added.
GGSCI (ORCL_1.localdomain) 3> add exttrail ./dirdat/sa, extract ext01, megabytes 50
EXTTRAIL added.
GGSCI (ORCL_1.localdomain) 4> start extract ext01
Sending START request to MANAGER ...
EXTRACT EXT01 starting
GGSCI (ORCL_1.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@ORCL_1 golden_gate]$ ./ggsci

GGSCI (ORCL_1.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 (ORCL_1.localdomain) 6>

13. Run Initial Data Load using Direct Load Method.

Source Machine:
GGSCI (ORCL_1.localdomain) 1> ADD EXTRACT EINI01, SOURCEISTABLE
GGSCI (ORCL_1.localdomain) 2> info extract *, tasks
EXTRACT    EINI01    Last Started 2011-10-07 13:17   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint Table LOGIX.MYTAB
                     2011-10-07 13:17:28 Record 3
Task                 SOURCEISTABLE
GGSCI (ORCL_1.localdomain) 3> edit params eini01
EXTRACT EINI01
USERID gg_admin, PASSWORD gg_admin
RMTHOST 192.168.4.131, MGRPORT 7809
RMTTASK REPLICAT, GROUP RINI01
TABLE LOGIX.MYTAB;

Target Machine:
GGSCI (ORCL_2.localdomain) 1> ADD REPLICAT RINI01, SPECIALRUN
GGSCI (ORCL_2.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 (ORCL_2.localdomain) 3> edit param RINI01
REPLICAT RINI01
ASSUMETARGETDEFS
USERID gg_admin, PASSWORD gg_admin
DISCARDFILE ./dirrpt/RINI01.dsc, PURGE
MAP LOGIX.*, TARGET MYLOGIX.*;
Execute the Initial Load Process:

Source Machine:
START EXTRACT EINI01
VIEW REPORT EINI01
You'll see similar to this output:
...
Processing table LOGIX.MYTAB
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
Report at 2011-10-07 13:17:30 (activity since 2011-10-07 13:17:23)
Output to RINI01:
From Table LOGIX.MYTAB:
       #                   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 (ORCL_2.localdomain) 1> VIEW REPORT RINI01
...
***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
Wildcard MAP resolved (entry LOGIX.*):
MAP LOGIX.MYTAB, TARGET MYLOGIX.MYTAB;
Using following columns in default map by name:
ID, FIRSTNAME, BIRTHDAY
Using the following key columns for target table MYLOGIX.MYTAB: ID.
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
Report at 2011-10-07 17:17:37 (activity since 2011-10-07 17:17:32)
From Table LOGIX.MYTAB to MYLOGIX.MYTAB:
       #                   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 MYTAB on the target db. Let's check out it now:
[oracle@ORCL_2 ~]$ sqlplus MYLOGIX/123@ORCL_2

SQL> select * from MYTAB;
        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@ORCL_2 golden_gate]$ ./ggsci

Note: it's important to exit the session in which the changes were made.
[oracle@ORCL_2 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 (ORCL_2.localdomain) 1> dblogin userid gg_admin, password gg_admin
Successfully logged into database.
GGSCI (ORCL_2.localdomain) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (GGSCHKPT)...
Successfully created checkpoint table GGSCHKPT.
GGSCI (ORCL_2.localdomain) 3> add replicat rep01, exttrail ./dirdat/ta
REPLICAT added.
GGSCI (ORCL_2.localdomain) 4> start replicat rep01
Sending START request to MANAGER ...
REPLICAT REP01 starting
GGSCI (ORCL_2.localdomain) 5> info all
Program     Status      Group       Lag           Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     REP01       00:00:00      00:00:01
GGSCI (ORCL_2.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@Khurram~]$ sqlplus LOGIX/123@ORCL_1

Source Machine:
SQL> set lines 120
SQL> column firstname format a30
SQL> select * from MYTAB;
        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 MYTAB;
        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 "MYLOGIX"
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 "LOGIX"
SQL> insert into MYTAB values (5, 'Nataly', '26-Mar-1984');
1 row created.
SQL> commit;
Commit complete.
Target Machine:
SQL> show user
USER is "MYLOGIX"
SQL> select * from MYTAB;
        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 "LOGIX"
SQL> update MYTAB
2 set firstname = firstname || '#';
5 rows updated.
SQL> commit;
Commit complete.
SQL> select * from MYTAB;
        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 "MYLOGIX"
SQL> select * from MYTAB;
        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 MYTAB
2 where id < 5;
4 rows deleted.
SQL> commit;
Commit complete.
SQL> show user;
USER is "LOGIX"
SQL>

Target Machine:
SQL> select * from MYTAB;
ID FIRSTNAME BIRTHDAY
---------- ------------------------------ ---------
5 Nataly# 26-MAR-84
SQL> show user;
USER is "MYLOGIX"
SQL>

3 comments:

UnknownOctober 1, 2015 at 11:39 PM
We are providing best Oracle Golden Gate Online training in usa at very reaposable price with Adithyaelearning. We will provide you well expert teacher and environment for your learning.
AnonymousDecember 14, 2017 at 9:40 AM
Thank you Khurram bhai. This blog was very helful to configure gg. Its configured successfully. May Allah bless you for this work.
Yusuf Shaikh
+91 9145686926
Keerthi55May 21, 2021 at 10:43 AM
Thanks for the blog article.Thanks Again. Keep writing.
oracle bpm training
angular js training
sql server dba training
oracle golden gate training

Sunday, June 9, 2013

Oracle GoldenGate configuration Step By Step

3 comments: