Search This Blog

Wednesday, March 28, 2012

Oracle GoldenGate DDL Replication.

This post is built upon the previous post on Oracle GoldenGate DML replication.

Points to consider before implementing DDl replication.


1) Recyclebin feature should be turned off(Oracle 10g onwards).

2) The source and target schemas must be identical.

3) DDL replication is supported  only between two systems. Bi-directional DDL replication
   is also supported. DDL replication between one to many systems is not supported. 

4) DDl statements that are less than 2MB in length will be replicated automatically,
   any statement longer than that will have to be manually processed by Golden Gate script.

5) Passthru paramater is to be configured while configuring datapump for tables that require
   DDL replication.


Main differences when implementing  DDL replication as compared to DML replication is
that you have  to create GLOBALS file in both source and target schema.

Run certain scripts on source to facilitate DDL replication.

Add one additional parameter to the local extract 
file (i.e ddl include mapped objname "SCOTT.*).


On Source

SQL> grant execute on utl_file to ggs_admin;

Grant succeeded.

[oracle@canada gg]$ export ORACLE_SID=orcl
[oracle@canada gg]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 27 21:50:27 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggs_admin


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_ADMIN

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.

SQL> @ddl_setup.sql

GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be 

enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggs_admin

You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using GGS_ADMIN as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGS_ADMIN

DDLORA_GETTABLESPACESIZE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

CLEAR_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

CREATE_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

INITIAL_SETUP STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors  No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED

STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
0

LOCATION OF DDL TRACE FILE
-----------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log

Analyzing installation status...


STATUS OF DDL REPLICATION
------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.


SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql
script to change the gg_role parameter 
to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggs_admin
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI,
and Manager processes, by using the following SQL 

command:

GRANT GGS_GGSUSER_ROLE TO 

where  is the user assigned to the GoldenGate processes.

SQL> GRANT GGS_GGSUSER_ROLE TO ggs_admin;

Grant succeeded.


SQL> @ddl_enable.sql

Trigger altered.


Do Both on Source and Target

GGSCI>edit param GLOBALS

GGSCHEMA GGS_ADMIN


On Source
--parameter to include in local extract file ext1

GGSCI> edit param ext1

exttrail dirdat/t1
ddl include mapped objname "SCOTT.*"
table SCOTT.*;
            


Lets check
                      
On Source

SQL> alter table emp add ncol varchar2(10);

Table altered.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
 NCOL                                               VARCHAR2(10)


 

On Target.



SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
 NCOL                                               VARCHAR2(10)




Also

On Source 

SQL> create table test as select * from emp;

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TEST                           TABLE


On Target

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TEST                           TABLE







0 comments:

Post a Comment