ORACLE GOLDEN GATE – UNIDIRECTIONAL

Introduction:

Oracle Golden Gate is a comprehensive software
package for real-time data integration and replication in heterogeneous IT
environments. The product enables
different business requirements like high availability solutions, real-time
data migration and upgrades, decision support systems and data ware housing as
well as data integration and consolidation.

Configuration
Method:

Golden Gate can be configured in following method

Method	Use in Business Scenario
Unidirectional	Reporting Instance
Bi-directional	Instance Fail Active-Active
Peer-To-Peer	Load Balancing, High Availability
Broad cost	Data distribution
Consolidation	Data warehouse or Mart or Store
Cascading	Scalability, Database tiering

The pictorial representation of the same is given
below

Golden Gate
components and processes:

The pictorial representation of golden gate
components and processes is given below (not going to explore all the
components and processes) to understand golden gate flow before going to
Unidirectional Configuration/Setup.

Golden Gate
Unidirectional Configuration:

Now Let us see the Golden Gate Configuration method
Unidirectional without using Datapumb.

Advantage:

The advantage of this Unidirectional configuration
method is mainly performance, because the business reports(mainly huge reports
by means of volume and time consuming) can be run on Reporting server(Target)
instead of Production Server(Source) so that Production server resources will
be free and provide best performance on real time business (exmpale: month-end
closing period in financial sector). The source and target database can be
synchronized always for specific schema or entire database based on business
requirement.

Prerequisite:

Assumption that two
database (Source and Destination) are available with archive log enabled. Both source & target hosts should be able to
do ping/ssh/scp each other.

Environment:

Particulars	Source	Target
Operating Systems	RHEL 5.4	RHEL 5.4
HOSTNAME	dbgs	dbgt
DOMAIN	doyensys.com	doyensys.com
DB Name	gg1	gg2
DB Version	11.2.0.1.0	11.2.0.1.0
Golden Gate Installation Location	/u01/app/GGS/	/u01/app/GGS/

Installation
of Golden Gate Software:

1)
Download V32409-01.zip from edelivery, unzip that under /u01/app/GSS/ which will
create fbo_ggs_Linux_x86_ora11g_32bit.tar file.

$ cd /u01/app/GSS

$ unzip V32409-01.zip

2)
Extract the tar file

$ tar –xvf
fbo_ggs_Linux_x86_ora11g_32bit.tar

3) Add golden gate
installed location in the LD_LIBRARY_PATH and PATH variable on OS User profile.

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

PATH=$ORACLE_HOME/bin:/u01/app/GGS:$PATH;
export PATH

4) Login
into Golden Gate

cd /u01/app/GGS

$./ggsci

Oracle
GoldenGate Command Interpreter for Oracle

Version
11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86,
32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

GGSCI (dbgs.doyensys.com)

5)
Create the mandatory files and directories:

GGSCI (dbgs.doyensys.com) 1> create subdirs

GGSCI (dbgs.doyensys.com) 2> exit

$ mkdir /u01/app/GGS/discard

This will complete the
Golden Gate software Installation on dbgs.doyensys.com

Repeat all the above
steps on dbgt.doyensys.com
to install the software on second host.

Preparing
the database for replication:

Source Database:

Switch the database to Archive log mode

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archive log;

SQL> alter database open

Enable Minimal Database Level
Supplemental Logging

SQL> alter database add supplemental log data;

Prepare the database to support DDL
Replication

a)
Turn Off Recyclebin for The
Database and then bounce it.

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

b)
Create New Schema for DDL Support Replication And Grant a Necessary Privileges To
New User

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

SQL> grant
connect, resource, unlimited tablespace to ggate;

SQL> grant
execute on utl_file to ggate;

c)
Go to Golden Gate Installed location
(in our scenario /u01/app/GGS) and then run the following Golden Gate inbuild
scripts for creating all necessary objects to support DDL replication.

SQL>@marker_setup.sql

SQL>@DDL_setup.sql

SQL>@GGS/role_setup.sql

SQL>grant
GGS_GGSUSER_ROLE to ggate;

SQL>@DDL_enable.sql

Note:
There are two ways/approach to connect the golden gate on
target.

1.
Use the same user (ggate) which
created on source database by using tns service name and database link.

2.
Create an individual/same user
on target.

Here we following the create an individual/same user
approach, so the above points b) and c) to be executed on target database also.

Create Test
Schemas for Replication.

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

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 (dbgt.doyensys.com):

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

SQL> grant
connect, resource, unlimited tablespace to receiver;

Setup
Replication:

The goal is to create DDL and transform DML 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 InterpreterforOracle

Version
11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100

Linux, x64,
64bit (optimized), Oracle 11gonOct 4
2011 23:49:46

GGSCI
(dbgs.doyensys.com) 1> info all

Program Status
Group Lag Time Since Chkpt

MANAGER STOPPED

GGSCI
(dbgs.doyensys.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
(dbgs.doyensys.com) 1> start mgr

Manager started.

GGSCI
(dbgs.doyensys.com) 1> info all

Program Status
Group Lag Time Since Chkpt

MANAGER RUNNING

Note:

(a)The status shows it is running

(b)Repeat the same on TARGET to create manager process.

2. Create the extract group on the source side (gg1)

GGSCI (dbgs.doyensys.com) 1> add extract ext_gg1, tranlog,begin
now

EXTRACT added.

GGSCI (dbgs.doyensys.com) 2> add exttrail
/u01/app/GGS/dirdat/lt, extract ext_gg1

EXTTRAIL added.

GGSCI (dbgs.doyensys.com) 3> edit params ext_gg1

Enter the
following for the content of extract parameter ext_gg1 and then save and exit.

EXTRACT ext_gg1

USERID ggate, PASSWORD oracle

DBOPTIONS ALLOWNOLOGGING

SETENV (ORACLE_HOME = “/u01/app/oracle/product/10.2.0/db_1”)

SETENV (ORACLE_SID = “gg1”)

RMTHOST 192.168.1.179, MGRPORT 7809

RMTTRAIL /u01/app/GGS/dirdat/lt

DISCARDFILE discard.txt, APPEND

DDL include mapped objname sender.*;

DDLOPTIONS ADDTRANDATA

table sender.*;

3. Create replicat on the destination side (gg2)

$ cd
/u01/app/GGS

$ ./ggsci

To create a checkpoint Table in the target database.

GGSCI
(dbgt.doyensys.com) 1> edit params ./GLOBAL

CHECKPOINTTABLE ggate.checkpoint

Save
and quit the file.

GGSCI
(dbgt.doyensys.com) 2> dblogin userid ggate, Password oracle

Successfully
logged into database.

GGSCI
(dbgt.doyensys.com) 3>add checkpointtable ggate.checkpoint

Successfully
created checkpointtableGGATE.CHECKPOINT.

Create replicat
group: gg2

GGSCI
(dbgt.doyensys.com) 4>add replicat rep_gg2, exttrail /u01/app/GGS/dirdat/lt,
checkpointtable ggate.checkpoint

REPLICAT added.

Create Parameter
File For Replicat:

GGSCI
(dbgt.doyensys.com) 5> edit params rep_gg2

And put
following lines in the parameter file:

replicat rep_gg2

ASSUMETARGETDEFS

userid ggate,
password oracle

SETENV
(ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db_1”)

SETENV
(ORACLE_SID = “gg2”)

DDL INCLUDE ALL

DDLERROR DEFAULT
IGNORE RETRYOP

discardfile
/u01/app/GGS/discard/rep_gg2_discard.txt, append, megabytes 10

map sender.*,
target receiver.*;

Save and quit

4. Start Extract and
Replicat:

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

Source: GG1

GGSCI
(dbgs.doyensys.com) 14> start extract ext_gg1

Destination: GG2

GSCI
(dbgt.doyensys.com) 15> start replicat rep_gg2

5.
Check the extract and replicat running in
source and target system

Source: GG1

1 GGSCI (dbgs.doyensys.com) 8> info all

2 Program
Status Group Lag Time Since Chkpt

3 MANAGER
RUNNING

4vEXTRACT
RUNNING EXT_GG1 00:00:00 00:12:25

Destination: GG2

GGSCI
(dbgt.doyensys.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>conn sender/oracle

SQL> select * from t1;

Select *
from t1

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> create table t1 (id number primary key,
name varchar2(50));

Table created.

SQL> insert into t1 values (1,’test1′);

1 row created.

SQL> insert into t1 values (2,’test2′);

1 row created.

SQL> commit;

Commit complete.

2. Destination Database:

SQL> conn receiver/oracle

SQL> select * from t1;

ID
NAME

———-
————————————————–

1
test1

2
test2

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

The above is the example of extract and
replica of one particular schema from sender(GG1) to receiver(GG2), likewise
all the transactions of required schema or entire database can be extracted and
replicated based on business requirement.

Doyensys

A technology service company that provides innovative solutions around the oracle platform With Laser focus on customer delight as the primary success parameter.