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
