DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, June 24, 2014

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
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
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:
  1. Switch the database to Archive log mode
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archive log;
SQL> alter database open

  1. Enable Minimal Database Level Supplemental Logging
SQL> alter database add supplemental log data;

  1. 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>@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.

  1. 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
Copyright (C) 1995, 2011, Oracleand/orits affiliates.Allrights reserved.    
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. 
     (c) 7809 is Default Port for Golden Gate      

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
1
2
3
4
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.

4 comments:

Joseph A. Wallace said...


It was so nice article.I was really satisified by seeing this article and we are also giving Oracle online training.The Oracle online training is one of the best Oracle online training institute in USA.

Cat Minds said...

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.

Pensee Dumont said...

Online Test Series for Gate is planned considering the real GATE examination. The gate online tests papers are made by experienced GATE resources.They will provide all the possible solutions for each test.

Harris Johnson said...

Hello Friends,Is it necessary to join gate online tests series?