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
