Starting with Oracle Goldengate

Overview:

With Oracle GoldenGate, you can replicate Oracle DML and DDL operations. You can move, filter, and transform data between similar or dissimilar supported Oracle versions, or you can move, filter, and transform data between an Oracle database and a database of another type.

Oracle GoldenGate logical architecture:

Oracle GoldenGate is composed of the following components:

• Extract
• Data pump
• Replicat
• Trails or extract files
• Checkpoints
• Manager
• Collector

For detailed information about the concept please refer to following document:

OracleGoldengateDocumentaionIndex

Environment Details :

Following is the environment used to setup this replication

1. OS Version: Linux x86_64  OEL5.4
2. Database Version: Oracle 11gr2 : 11.2.0.1
3. GoldenGate Version: 11.1.1

 

Software can be downloaded from following location by making sure to login to your oracle account first:

– Click here to download OEL5.

– Click here to download Oracle 11gR2.

– Click here to download GoldenGate.

Hostname used: gg1.company.com and gg2.company.com

Database name used: gg1 on gg1.company.com and gg2 on gg2.company.com

 

The data will be replicated from gg1 –> gg2

Prerequisites:

Oracle databases on Linux software should be running before we start the GoldenGate installation.The base directory used for GoldenGate software is /u01/app/GGS.

Both hosts should be able to do ping/ssh/scp each other.

Installing Oracle GoldenGate Software:

1.     On host gg1.company.com login as Oracle user:

$ cd /u01/app/GGS

2.     Unzip the downloaded software.

$ unzip fbo_ggs_Linux_x64_ora11g_64bit.zip

It will create a tar file under /u01/app/GGS, extract the tar file in the same directory:

3.     Extract the tar file created.

$ tar –xvf  fbo_ggs_Linux_x64_ora11g_64bit.tar

This will create the required files and directories for GoldenGate.

4.      Add this location in the LD_LIBRARY_PATH for setting up the environment.

  LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/GGS

5.     Invoke the GoldenGate command line utility, GGSCI prompt:

$ cd /u01/app/GGS
$./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 11g on Oct  4 2011 23:49:46
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (gg1.company.com) 1>

6.     Create the mandatory files and directories:

GGSCI (gg1.company.com) 1> create subdirs
GGSCI (gg1.company.com) 2> exit
$ mkdir /u01/app/GGS/discard

This will complete the GoldenGate software Installation on gg1.company.com

7.     Repeat all the above steps on gg2.company.com to install the software on second host.

This will complete the installation of GG software on both nodes.

 

Prepare the database for replication:

Source Database:

1.     Switch the database to archivelog mode:

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

2.     Enable minimal supplemental logging:

SQL> alter database add supplemental log data;

3.     Prepare the database to support ddl replication.

a)     Turn off recyclebin for the database . . .

SQL> alter system set recyclebin=off scope=spfile;

and bounce it.

b)      Create schema for ddl support replication

SQL> create user ggate identified by oracle default tablespace users temporary tablespace temp;

grant the necessary privileges to the new user:

SQL> grant connect,resource,unlimited tablespace to ggate;
SQL> grant execute on utl_file to ggate;

c)     Run scripts for creating all necessary objects for support ddl replication:

SQL> @/u01/app/GGS/marker_setup.sql
SQL> @/u01/app/GGS/ddl_setup.sql
SQL> @/u01/app/GGS/role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @/u01/app/GGS/ddl_enable.sql

4.     Create test schemas for replication.

I will create a replication from schema sender to schema receiver (on gg2 database).

a) Source database:

SQL> create user sender identified by oracle default tablespace users temporary tablespace temp;
SQL> grant connect,resource, unlimited tablespace to sender;

On Destination Database GG2 (gg2.company.com):

SQL> create user receiver identified by oracle default tablespace users temporary tablespace temp;
SQL> grant connect, resource, unlimited tablespace to receiver;

 

Setup Replication:

We’re going to create the simplest replication without the GG data pump . The goal is to create ddl and ddl replication from the sender schema on the GG1 database to receiver schema on the destination GG2 database.

1.     Create and start manager on the source and the destination.

Source:GG1

[oracle@gg1 ~]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 11g on Oct  4 2011 23:49:46
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (gg1.company.com) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED

GGSCI (gg1.company.com) 2> edit params mgr

It will open the parameter file for manager. Enter the following in the file:

PORT 7809

Then save and quit the file.

GGSCI (gg1.company.com) 1> start mgr

Manager started.

GGSCI (gg1.company.com) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER    RUNNING

Repeat the same on GG2 to create manager process.

2.      Create the extract group on the source side(GG1):

GGSCI (gg1.company.com) 1> add extract ext_gg1, tranlog, begin now  
EXTRACT added.

GGSCI  (gg1.company.com) 2> add exttrail /u01/app/GGS/dirdat/lt, extract ext_gg1  
EXTTRAIL added.

GGSCI  (gg1.company.com) 3> edit params ext_gg1

Add the following lines to the new parameter file for our extract:

--extract group--
extract ext_gg1
--connection to database--
userid ggate, password oracle
--hostname and port for trail--
rmthost gg2, mgrport 7809
--path and name for trail--
rmttrail /u01/app/GGS/dirdat/lt
--DDL support
ddl include mapped objname sender.*;
--DML
table sender.*

Save and quit the file.

We can check our processes again:

GGSCI  (gg1.company.com) 4> info all 
 Program     Status      Group       Lag           Time Since Chkpt  
MANAGER     STOPPED  
EXTRACT     STOPPED     EXT_GG1        00:00:00      00:12:53

3.     Create replicat on the destination side(GG2):

$ cd /u01/app/GGS
$ ./ggsci

add checkpoint table to the destination database

GGSCI (gg2.company.com) 1> edit params ./GLOBAL

and put following lines to the global parameter file:

GGSCHEMA ggate  
CHECKPOINTTABLE ggate.checkpoint

Save and quit the file.

GGSCI (gg2.company.com) 2> dblogin userid ggate,  Password oracle

Successfully logged into database.

GGSCI (gg2.company.com) 3> add checkpointtable ggate.checkpoint  
Successfully created checkpoint table GGATE.CHECKPOINT.

Create replicat group:GG2  

GGSCI (gg2.company.com) 4> add replicat rep_gg2, exttrail /u01/app/GGS/dirdat/lt, checkpointtable ggate.checkpoint  
REPLICAT added.

create parameter file for replicat:

GGSCI (gg2.company.com) 5> edit params rep_gg2

And put following lines in the parameter file:

--Replicat group --
replicat rep_gg2
--source and target definitions
ASSUMETARGETDEFS
--target database login --
userid ggate, password oracle
--file for dicarded transaction --
discardfile /u01/app/GGS/discard/rep_gg2_discard.txt, append, megabytes 10
--ddl support
DDL
--Specify table mapping ---
map sender.*, target receiver.*;

4.     Start extract and replicat:

Make sure that manager is up and running before starting these.

Source: GG1

GGSCI (gg1.company.com) 14> start extract ext_gg1

Destination: GG2

GGSCI (gg2.company.com) 15> start replicat rep_gg2

5.     Check all processes.

Source: GG1

GGSCI (gg1.company.com) 8> info all  
Program     Status      Group       Lag           Time Since Chkpt  
MANAGER     RUNNING  
EXTRACT     RUNNING     EXT_GG1        00:00:00      00:12:25

Destination: GG2

GGSCI (gg2.company.com) 8> info all  
Program     Status      Group       Lag           Time Since Chkpt  
MANAGER     RUNNING  
REPLICAT    RUNNING     REP_GG2        00:00:00      00:12:30

Once all the processes are running means that replication is created successfully.

Results

Now we can check our replication.

We will create some table in the sender schema on the source, insert some rows, and check how it will replicate to destination side.

1.Source database:

SQL> create table sender.SOOD (c1 number,c2 varchar2(12));
SQL> insert into sender.SOOD values (1,'TEST');
SQL>commit;

2.Destination database:

SQL> select * from receiver.SOOD;  
C1         C2
---------- ------------
1           TEST

Our GoldenGate replication is now running fine.The table was created on the GG2 side and data were replicated

This is a very basic setup for replicating data using GoldenGate just to understand the replication setup and taken reference from here