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) Pass thru 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
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) Pass thru 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
$cd /u01/app/gg
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
Code:
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
Code:
SQL> @/u01/app/gg/ddl_setup.sql
Oracle 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 Oracle 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 Oracle GoldenGate schema name:ggs_admin
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 Oracle GoldenGate schema name.
Working, please wait ...
RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.
Enter yes or no:yes
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
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 IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO 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/admin/ORCLDB/udump/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL>
@role_setup.sql
Code:
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. ---- Trigger name required when ddl_disable.sql else ddl_remove.sql
Grant succeeded.
SQL> @ddl_enable.sql
Trigger altered. ---- Trigger name required when ddl_disable.sql else ddl_remove.sql
1st Method To replication.
Follow doc “Datapump Replication.doc” into my disk with datapump bcz eassy to track
with errors on ggserror.log file
Command.
1. view mgr /extract ext1/ rep1
2. stats
mgs /ext1 /rep1
3. view report
ext1
4. info all
5. info all *
2nd
Method To replication.
Without Dpump (Datapump) & mostly that’s
not using for production for direct extract.
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.*" ## DDL include All
table SCOTT.*;
Lets check
On Source
SQL> alter table emp add ncol varchar2(10);
Table altered.
SQL> desc emp;
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.*" ## DDL include All
table SCOTT.*;
Lets check
On Source
SQL> alter table emp add ncol varchar2(10);
Table altered.
SQL> desc emp;
Code:
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;
Code:
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;
Code:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST TABLE
On Target
SQL> select * from tab;
SQL> select * from tab;
Code:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST TABLE
No comments:
Post a Comment