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