ORACLE GOLDEN GATE

                                                          ORACLE GOLDEN GATE

 

1)       Hardware and O/S configuration

2)       Database Pre requisites

3)       GG Software Installation

4)       Preparing source database for replication

5)       GG DDL Support Replication

6)       Create Manager process @ Source

7)       Create Extract process @ Source

8)       Create Checkpoint table @ Target

9)       Create Replicate process @ Target

10)   Initial Data load (EXP/IMP)

11)   Start Extract

12)   Start Replicat

13)   TEST CASES

 

Step 1:-

 

SOFTWARE DOWNLOAD:-

Download the oracle Golden Gate software and copy to both source and target servers

 

Step 2:-

 

Database Pre requisites:-

Need to be carried out at both Source and Target.

 

Sys>create tablespace ggs_data datafile '/u01/home/prod/ggs_data01.dbf' size 200m autoextend on;

               Sys>create user ggs_owner identified by ggs_owner default tablespace ggs_data; 

               Sys>grant connect, resource to ggs_owner;

Sys> grant select any dictionary, select any table to ggs_owner;

Sys>grant create table to ggs_owner;

Sys>grant flashback any table to ggs_owner;

Sys>grant execute on dbms_flashback to ggs_owner;

Sys>grant execute on utl_file to ggs_owner;

Sys>grant create any table to ggs_owner;

Sys>grant insert any table to ggs_owner;

Sys>grant update any table to ggs_owner;

Sys>grant delete any table to ggs_owner;

Sys>grant drop any table to ggs_owner;

Sys>startup force  mount

Sys>alter database archivelog;

Sys>alter database open;

Sys>alter user scott account unlock;

Sys>alter user scott identified by scott;

 

Enable minimal supplemental logging on both side Source and Target:

  

Sys> alter database add supplemental log data;

 

Step 3 :-

 

Now create directory for gg software on both side Source and Target.

 

[oracle@source~]$mkdir oradata/ggsoft

 

Now go to the gg s/w location and untar the gg s/w on both side SOURCE & TARGET.

This is my ggs s/w location

 

[oracle@source]$cd /u01/home/ogg112_fbo_ggs_linux_x86_ora11g_32bit.zip

 

 

 

Now go to the oradata/ggsoft directorty and untar the ggs s/w

 

[oracle@source]$cd oradata/ggsoft

 

[oracle@source ggsoft]$tar –xvof  /u01/home/ogg112_fbo_ggs_linux_x86_ora11g_32bit.zip

 

Step 4 :-

 

Set environment variables on both SOURCE and TARGET. Into the .bash_profiles

 

export ORACLE_HOME=/u01/home/app/oracle/product/11.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH:.

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/home/oradata/ggsoft:/usr/share/java

 

 

Step 5:-

 

Run scripts for creating all necessary objects for support DDL replication:------>

 

Need to be carried out at  Source and Target :- Run scripts for creating all necessary objects for support ddl replication:-->

 

Note – run the scripts as SYSDBA

 

Sys> @/oradata/ggsoft/marker_setup.sql   --->  it will check the user

 

Enter GoldenGate schema name: GGS_OWNER

 

Sys> @/oradata/ggsoft/ddl_setup.sql  ----> perform dml operation of the user

 

 Enter GoldenGate schema name: GGS_OWNER

 

Sys> @/oradata/ggsoft/role_setup.sql -----> perform role operation of the user

 

Enter GoldenGate schema name:GGS_OWNER

 

Sys> grant ggs_ggsuser_role to ggs_owner;

 

Sys> @/oradata/ggsoft/ddl_enable;

 

Sys> @/oradata/ggsoft/ddl_pin GGS_OWNER;

 

Step 6:-

 

Run bash_profile before going to use ggsci

 

[oracle@source~]$. .bash_profile

 

Go to golden gate software directoty and run the command:-->

[oracle@source~]$cd oradata/ggsoft

[oracle@source ggsoft]$./ggsci

 

YOU WILL GET GG COMMAND PROMPT on both nodes Source and Target:

 

                   GGSCI (hostname) 1> CREATE SUBDIRS

 

GGSCI (DBSOURCE) 1> DBLOGIN USERID ggs_owner, password ggs_owner

 

 

Enable additional logging at the table level on both nodes Source and Target:-

 

add trandata <owner>.<tablename>   eg:---->  ggsci> add trandata scott.*

               

GGSCI (DBSOURCE) 44> info all  --> to check the information of all services( manager, extract,replicat)

 

GGSCI (DBSOURCE) 45> edit params mgr --> edit the parameters for manager service.(it will open mgr file in vi editor)

 

port 7809

 

USERID ggs_owner, PASSWORD ggs_owner

 

PURGEOLDEXTRACTS /u01/home/oradata/ggsoft/dirdat/ex, USECHECKPOINTS

 

 

:wq!

 

GGSCI (DBSOURCE) 46> info all

 

Create the extract group :--->

 

GGSCI (DBSOURCE) 49> add extract ext1, tranlog, begin now

 

GGSCI (DBSOURCE) 50> add exttrail /u01/home/oradata/ggsoft/dirdat/ld, extract ext1 --> (name extract)

 

GGSCI (DBSOURCE) 51> edit params ext1  --> edit the parameter for extract ..

 

extract ext1    -----> (group name for extraction)

userid ggs_owner, password ggsowner   -----> (user and password )

rmthost 192.168.0.162, mgrport 7810   -----> (remote machine ip address and manager port number)

RMTTRAIL /u01/home/oradata/ggsoft/dirdat/ld  ----> (remote machine trail file location)

table SCOTT.*; --->  (on which user you to perform the replicat)

 

:wq!

 

Step 7:-

 

NEED TO BE CARRIED OUT AT TARGET SIDE:---->

 

CREATE CHECKPOINT TABLE:--->

 

[oracle@Target~]$cd oradata/ggsoft

 

[oracle@Target ggsoft]$./ggsci

 

$ ./ggsci

 

GGSCI (DBTARGET) 2> dblogin userid ggs_owner password ggs_owner

 

GGSCI (DBTARGET) > edit params mgr

 

PORT 7810

 

USERID ggs_owner, PASSWORD ggs_owner

 

PURGEOLDEXTRACTS /u01/home/oradata/ggsoft/dirdat/ex, USECHECKPOINTS

:wq!

 

GGSCI (DBTARGET) 1> edit params ./GLOBAL

 

GGSCHEMA ggs_owner                                       ----->  (ggs owner)

CHECKPOINTTABLE ggs_owner.ggschkpt       -----> (it will create create checkpoint table in "ggs_owner" table name "ggschkpt) you can use any name).

:wq!

 

GGSCI (DBTARGET) 3> add checkpointtable ggs_owner.ggschkpt

 

CREATE REPLICAT:--->

 

GGSCI (DBTARGET) 4> add replicat rep2 ,exttrail /u01/home/oradata/ggsoft/dirdat/ld ,checkpointtable ggs_owner.ggschkpt

GGSCI (DBTARGET) 5> edit param rep2

 

replicat rep2                                    ---> (replicate group name )

HANDLECOLLISIONS                -----> ()

ASSUMETARGETDEFS              -----> ()

userid ggs_owner, password ggs_owner      ----> (username and password)

discardfile /u01/home/oradata/ggsoft/dirrpt/rep1_discard.txt, append, megabytes 10   (discard files)

map scott.*, target scott.*;              ----> ("mapping" source table and "target" target tables)

 

:wq!

 

NEED TO BE CARRIED OUT AT  SOURCE:--->

 

START EXTRACT:--->

 

GGSCI (DBSOURCE) >info all

 

GGSCI (DBSOURCE) > start mgr  

 

GGSCI (DBSOURCE) 45>  start EXT1

 

GGSCI (DBSOURCE)  >info all

 

NEED TO BE CARRIED OUT AT TARGET   :---->

 

START REPLICAT:--->

 

GGSCI (DBTARGET) 10> start manager

 

GGSCI (DBTARGET) 10> start replicat rep1

 

GGSCI (DBSOURCE) >info all

 

TEST CASES:---->

 

Now connect to schema and insert data in SOURCE schema table and check whether it is replicating  to TARGET schema.