配置Goldengate支持DDL

以Sysdba角色运行脚本

SQL> @marker_setup

SQL> alter session set recyclebin=OFF;

Session altered.

SQL> @ddl_setup

SQL> @role_setup

SQL> grant ggs_ggsuser_role to ggs;

Grant succeeded.

SQL> @ddl_enable

Trigger altered.

SQL> @ddl_pin GGS

BEGIN dbms_shared_pool.keep('SYS. GGS_DDL_TRIGGER_BEFORE', 'R'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 @$ORACLE_HOME/rdbms/admin/dbmspool.sql
 @ddl_pin goldengate

Turn Recyclebin OFF 

We need to set the parameter recyclebin to OFF via the ALTER SYSTEM SET RECYCLEBIN=OFF command in order to prevent this error which we will see if we try and configure DDL support and then start the Extract process.

2010-02-19 11:13:30 GGS ERROR 2003 RECYCLEBIN must be turned off. For 10gr2 and up, set RECYCLEBIN in parameter file to OFF. For 10gr1, set _RECYCLEBI
N in parameter file to FALSE. Then restart database and extract.
2010-02-19 11:13:30 GGS ERROR 190 PROCESS ABENDING.

Enable additional logging at the table level

Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by GoldenGate for DDL support.

GGSCI (linuxoel54.gj.com) 5> DBLOGIN USERID ggs, PASSWORD ggs

Successfully logged into database.

GGSCI (linuxoel54.gj.com) 6> ADD TRANDATA scott.emp

Logging of supplemental redo data enabled for table SCOTT.EMP.

Edit the parameter file for the Extract process to enable DDL synchronization 

We had earlier created a parameter file for an Extract process ext1. We now edit that parameter file and add the entry
DDL INCLUDE MAPPED

This means that DDL support is now enabled for all tables which have been mapped and in this case it will only apply to the SCOTT.EMP table as that is the only table which is being processed here. We can also use the INCLUDE ALL or EXCLUDE ALL or wildcard characters to specify which tables to enable the DDL support for.

GGSCI (linuxoel54.gj.com) 1> EDIT PARAM EXT1

EXTRACT ext1
USERID ggs, PASSWORD ggs
RMTHOST zeusaix53 , MGRPORT 7809
RMTTRAIL /u01/oracle/software/goldengate/dirdat/rt
DDL INCLUDE MAPPED
TABLE scott.emp;

Test the same 

We will now alter the structure of the EMP table by adding a column and we can see that this new table structure is also reflected on the target system.

On Source

SQL> ALTER TABLE EMP ADD NEW_COL VARCHAR2(10);
Table altered.

On Target 

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