Tuesday, July 21, 2015

OGG_DDL Replication when Existing DML replication running


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.

 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.

                 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

  Steps

 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>

    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;

 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;

 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 (SVRVRAC92) 12> dblogin userid gg_admin,password gg_admin
Successfully logged into database.

GGSCI (SVRVRAC92) 13> add extract load1,SOURCEISTABLE
Extract Added.

  Step 5

Edit the extract parameter for Initial Load
GGSCI (SVRVRAC92) 14> edit params load1



GGSCI (SVRVRAC92) 15> edit params load1
EXTRACT load1
userid gg_admin,password gg_admin
RMTHOST 192.168.4.93,mgrport 7809
rmttask replicat, group load2
table gguser.ggsrc;


 Step 6

On Target database,  create a replicat group LOAD2  and edit the parameters.
GGSCI (SVRVRAC93) 18> add replicat load2,SPECIALRUN
REPLICAT added.
GGSCI (SVRVRAC93) 19> edit params load2
REPLICAT load2
USERID gg_admin, PASSWORD gg_admin
ASSUMETARGETDEFS
MAP gguser.ggsrc,TARGET

   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 (SVRVRAC92) 33> start extract load1

Sending START request to MANAGER ...
EXTRACT LOAD1 starting

GGSCI (SVRVRAC92) 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 (SVRVRAC93) 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

 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 (SVRVRAC92) 29> add extract ddlex,tranlog,begin now
EXTRACT added.


GGSCI (SVRVRAC92) 30> add rmttrail /home/oracle/gg/dirdat/dt, EXTRACT DDLEX
RMTTRAIL added.


GGSCI (SVRVRAC92) 31> edit params ddlex
extract ddlex
userid gg_admin,password gg_admin
rmthost10.10.1.10,mgrport 7809
rmttrail /home/oracle/gg/dirdat/dt
ddl include, OBJTYPE 'PROCEDURE', OBJNAME gguser.INSERTNEWROW;

 Step 9

Create a Replicat Group on target database, edit the parameters are shown below.
GGSCI (SVRVRAC93) 33> add replicat ddrep,exttrail /home/oracle/gg/dirdat/dt,checkpointtable gg_admin.chkptab
REPLICAT added.


GGSCI (SVRVRAC93) 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;

Step 10

Start the Extract group on source database.
GGSCI (SVRVRAC92) 35> start extract ddlex


 Step 11

Start the replicat group on target database
GGSCI (SVRVRAC93) 36> start replicat ddrep

Sending START request to MANAGER ...
REPLICAT DDREP starting

 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;
/

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

No comments:

Post a Comment