oracle GoldenGate系列：（三）DDL的同步

GoldenGate的DDL复制概述
GoldenGate目前只支持oracle和teradata的ddl复制。
有两种配置方法
1.基于trigger的DDL
2.Native DLL
ps：Native DLL方式要求通过logmining Server进行日志解析 – ogg12c+integrated extract+db11204及以上

基于trigger的DDL同步原理：
与DML同步不同，DDL复制并不是简单得读取日志文件，然后生成trail文件进行传输。而是通过触发器的方式实现的。
当一条DML语句执行后，会触发该触发器，触发器会让oracle将所执行的DML语句和其他一些记录写到ogg的用户的表下。 
然后ogg进程再从该这些表中获取该语句写到trail文件中,再进行传输。。


一、源端配置
[oracle@linfan ~]$ cd /oracle/ggs -------一定要进入ogg的目录下
[oracle@linfan ggs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 17 18:13:21 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.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 Oracle GoldenGate database objects.
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:gg


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


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


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


Script complete.
SQL>  alter system set recyclebin=off scope=spfile;


System altered.


SQL> startup force;
ORACLE instance started.


Total System Global Area  828608512 bytes
Fixed Size                  1348104 bytes
Variable Size             553651704 bytes
Database Buffers          268435456 bytes
Redo Buffers                5173248 bytes
Database mounted.
Database opened.
SQL> @ddl_setup


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:gg


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 GG as a Oracle GoldenGate schema name.


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 GG


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/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


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:gg
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 gg;

Grant succeeded.
创建触发器
SQL> @ddl_enable

Trigger altered.

二、目标端：执行脚本（同上源端）
SQL>alter system set recyclebin=off scope=spfile;   --关闭回收站
SQL>startup force;         --重启数据库
SQL>@marker_setup
SQL>@ddl_setup
SQL>@role_setup
SQL>grant GGS_GGSUSER_ROLE to gg;
SQL>@ddl_enable

三、进程配置
GGSCI (linfan) 11> edit param capdb1


EXTRACT capdb1
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID = orcl)
USERID gg, PASSWORD gg
EXTTRAIL /oracle/ggs/dirdat/lt
ddl include all
ddloptions addtrandata,report
TABLE scott.*;

GGSCI (linfan2) 10> edit param repdb2
REPLICAT repdb2
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
SETENV (ORACLE_SID = orcl)
ASSUMETARGETDEFS
USERID gg, PASSWORD gg
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERROR DEFAULT DISCARD
DDLERROR DEFAULT IGNORE RETRYOP
MAP scott.*, TARGET scott.*;
 

四、启动进程
启动目标端的Replicat进程
GGSCI (linfan2) 6> START repdb2
启动源端的pumpdb12进程.
GGSCI (linfan) 6> START pumpdb12
EXTRACT PUMPDB12 starting
启动源端的capdb1进程
GGSCI (linfan) 19> START capdb1
Sending START request to MANAGER ...
EXTRACT CAPDB1 starting

五、建表测试
源端建表
SQL> create table laha as select * from dept;

Table created.
目标端查看
SQL> select count(*) from laha;


  COUNT(*)
----------
        13


1 row selected.

目标表自动建了表，并插入语句，实验成功

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31386161/viewspace-2127100/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31386161/viewspace-2127100/