Oracle Golden Gate DDL Replication with Existing DML replication

1.                                      Introduction

In this document, we have provided the step by step instructions to implement DDL replication for replicating a single procedure/package without disturbing the existing DML replication using Oracle Golden Gate.

2.                                     Single Procedure/Package Replication

Consider the facts that we already have an OGG replication enabled between source and destination databases.  Now, we need to enable replication for a single procedure so that any changes made on the procedure on Source database will be replicated on target db via OGG.

2.1.                                 Pre-requisites

This section provides the pre-requisites for enabling a single procedure replication.

·         Oracle Golden Gate is already installed and DML replication is configured

·         All dependent objects (tables or views) are already replicated and data are in sync for both source and target.

·         Necessary privileges are provided to Golden Gate Administrator

2.2.                                 Steps

2.2.1.                              Step 1

Login as SYS user and execute the below scripts for enabling DDL replication if not executed already.

@marker_setup.sql

@ddl_setup.sql

@role_setup.sql

@ddl_enable.sql

@ddl_pin <gg_owner_schema>

2.2.2.                              Step 2

Create table GGUSER.GGSRC on both source and destination database using the below structure.

CREATE TABLE GGUSER.GGSRC

(

  OBJECT_ID    NUMBER,

  OWNER        VARCHAR2(30 BYTE),

  OBJECT_NAME  VARCHAR2(128 BYTE),

  OBJECT_TYPE  VARCHAR2(19 BYTE)

)LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

2.2.3.                              Step 3

Login as GGUSER on source database, insert the rows into table GGSRC

Insert into ggsrc select object_id,owner,object_name,object_type from dba_objects where object_id<=1000;

2.2.4.                              Step 4

INITIAL LOAD - Create Extract Group LOAD1 for Initial load (used intial load as the data is less, not recommended for bigger tables on production)

GGSCI (TEST1) 12> dblogin userid gg_admin,password gg_admin

Successfully logged into database.

GGSCI (TEST1) 13> add extract load1,SOURCEISTABLE

Extract Added.

2.2.5.                              Step 5

Edit the extract parameter for Initial Load

GGSCI (TEST1) 14> edit params load1

GGSCI (TEST1) 15> edit params load1

EXTRACT load1

userid gg_admin,password gg_admin

RMTHOST 10.10.10.20,mgrport 7809

rmttask replicat, group load2

table gguser.ggsrc;

2.2.6.                              Step 6

On Target database,  create a replicat group LOAD2  and edit the parameters.

GGSCI (TEST2) 18> add replicat load2,SPECIALRUN

REPLICAT added.

GGSCI (TEST2) 19> edit params load2

REPLICAT load2

USERID gg_admin, PASSWORD gg_admin

ASSUMETARGETDEFS

MAP gguser.ggsrc,TARGET

2.2.7.                              Step 7

On source database ,start the Extract load LOAD1. The data from source table GGSRC will be loaded into GGSRC on target database.

GGSCI (TEST1) 33> start extract load1

Sending START request to MANAGER ...

EXTRACT LOAD1 starting

GGSCI (TEST1) 34> info extract load1

EXTRACT    LOAD1     Last Started 2015-04-06 19:05   Status STOPPED

Checkpoint Lag       Not Available

Log Read Checkpoint  Table GGUSER.GGSRC

                     2015-04-06 19:05:30  Record 998

Task                 SOURCEISTABLE

GGSCI (TEST2) 25> info  replicat load2

REPLICAT   LOAD2     Initialized   2015-04-06 18:49   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:16:28 ago)

Log Read Checkpoint  Not Available

Task                 SPECIALRUN

2.2.8.                              Step 8

Now, we will create a new Extract Group for replicating a single procedure that we are going to create with name “INSERTNEWROW”

On source database,Create an extract group DDLEX and edit the parameters for the extract group                

GGSCI (TEST1) 29> add extract ddlex,tranlog,begin now

EXTRACT added.

GGSCI (TEST1) 30> add rmttrail /home/oracle/gg/dirdat/dt, EXTRACT DDLEX

RMTTRAIL added.

GGSCI (TEST1) 31> edit params ddlex

extract ddlex

userid gg_admin,password gg_admin

rmthost 10.10.10.20,mgrport 7809

rmttrail /home/oracle/gg/dirdat/dt

ddl include, OBJTYPE 'PROCEDURE', OBJNAME gguser.INSERTNEWROW;

2.2.9.                              Step 9

Create a Replicat Group on target database, edit the parameters are shown below.

GGSCI (TEST2) 33> add replicat ddrep,exttrail /home/oracle/gg/dirdat/dt,checkpointtable gg_admin.chkptab

REPLICAT added.

GGSCI (TEST2) 34> edit params ddrep

replicat ddrep

userid gg_admin,password gg_admin

discardfile /home/oracle/gg/dirrpt/ddrep.dsc,purge

DDL INCLUDE MAPPED OBJNAME  gguser.ggsrc,target gguser.ggsrc

DDLOPTIONS MAPSESSIONSCHEMA ggsuser, TARGET ggtuser, MAPSCHEMAS;

2.2.10.                          Step 10

Start the Extract group on source database.

GGSCI (TEST1) 35> start extract ddlex

2.2.11.                          Step 11

Start the replicat group on target database

GGSCI (TEST2) 36> start replicat ddrep

Sending START request to MANAGER ...

REPLICAT DDREP starting

2.2.12.                          Step 12

Now, on Source database, create a procedure under gguser schema.

CREATE OR REPLACE PROCEDURE insertnewrow(

p_objid IN ggsrc.OBJECT_ID%TYPE,

p_owner IN ggsrc.OWNER%TYPE,

p_objname IN ggsrc.OBJECT_NAME%TYPE,

p_objtype IN ggsrc.OBJECT_TYPE%TYPE)

IS

BEGIN

  INSERT INTO ggsrc ("OBJECT_ID", "OWNER", "OBJECT_NAME", "OBJECT_TYPE")

  VALUES (p_objid, p_owner,p_objname, p_objtype);

  COMMIT;

END;

/

2.2.13.                          Step 13

On Target database, login as GGUSER and check whether the procedure is replicated.

The same can be confirmed via the report file under “dirrpt” directory