实验目的:在两台虚机模拟实现简单的单向的DDL复制(接上一篇《(二)OGG双向复制(DML操作)》)。
说明:因《(二)OGG双向复制(DML操作)》中已配置过双向的dml操作,所以本次配置内容在前一篇的基础之上(本次只添加单向的DDL复制操作内容)。
实验环境:64位系统
操作系统:rhel6.6-x86_64
数据库:oracle_11.2-x86-64
goldengate:for_11g_x64
配置步骤:
1、
源端:
库关闭回收站
注明:在oracle11g 中, recyclebin参数的 System Modifiable为DEFERRED,意思是要修改系统级的话,就要加deferred参数,对当前已经连接的sesion没有影响,但新连接的session将受到影响。(可以查询视图selectname,isses_modifiable,issys_modifiable from v$parameter wherename='recyclebin';)
SQL> show parameter recyclebin
NAME TYPE VALUE
----------------------------------------------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off DEFERRED;
System altered.
SQL> show parameter recyclebin
NAME TYPE VALUE
----------------------------------------------- ------------------------------
recyclebin string OFF
2、
源端:
配置./GLOBALS
GGSCI (oggsource.localdomain) 12> edit params ./GLOBALS
GGSCHEMA goldengate
3、
源端:
运行相关的sql脚本
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of aschema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
Enter Oracle GoldenGate schemaname:GOLDENGATE
Marker setup table script complete, runningverification script...
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to GOLDENGATE
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
注意:执行ddl_setup.sql 这个文件时,需要显示的给goldengate用户赋予create table,create sequence的权限(之前goldengate用户已经有了dba权限,不知这里为何要再一次显示的赋权,oracle的bug??)。
如果没有显示赋权,执行ddl_setup.sql会遇到类似以下的错误(笔者自己是遇到了)
1600/4 PL/SQL: SQL Statement ignored
1602/32 PL/SQL: ORA-00942: table or viewdoes not exist
SQL> grant create table,create sequence to goldengate;
Grant succeeded.
SQL> @ddl_setup.sql
Oracle GoldenGate DDL Replication setupscript
Verifying that current user has privilegesto install DDL Replication...
You will be prompted for the name of aschema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the systemrecycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
Enter Oracle GoldenGate schemaname:goldengate
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding lockson Oracle Golden Gate metadata tables ...
Check complete.
Using GOLDENGATE as a Oracle GoldenGateschema name.
Working, please wait ...
DDL replication setup script complete,running verification script...
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to GOLDENGATE
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/oggs/oggs/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replicationsoftware components
Script complete.
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the roleGGS_GGSUSER_ROLE
To use a different role name, quit thisscript and then edit the params.sql script to change the gg_role parameter tothe preferred name. (Do not run the script.)
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
Enter GoldenGate schema name:goldengate
SP2-0606: Cannot create SPOOL file"role_setup_spool.txt"
SP2-0606: Cannot create STORE file"role_setup_set.txt"
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned tothe Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO<loggedUser>
where <loggedUser> is the userassigned to the GoldenGate processes.
SQL> grant ggs_ggsuser_role to goldengate;
Grant succeeded.
SQL> @ddl_enable.sql
Trigger altered.
SQL>@?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
SQL> @ddl_pin.sql goldengate
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
4、
源端:
修改extract进程的params文件,添加"ddl include all"参数,重启extract进程
GGSCI (oggsource.localdomain) 5> edit params ext_demo
EXTRACT ext_demo
setenv (ORACLE_SID=oggs)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid goldengate,password goldengate
ddl include all
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE./dirrpt/ext_demo.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TRANLOGOPTIONS EXCLUDEUSER goldengate
TRANLOGOPTIONS convertucs2clobs
TABLE OGG_USER.*;
GGSCI (oggsource.localdomain) 6> stop extract ext_demo
Sending STOP request to EXTRACT EXT_DEMO ...
Request processed.
GGSCI (oggsource.localdomain) 7> start extract ext_demo
Sending START request to MANAGER ...
EXTRACT EXT_DEMO starting
GGSCI (oggsource.localdomain) 8> info extract ext_demo
EXTRACT EXT_DEMO Last Started 2015-07-2911:26 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint Oracle Redo Logs
2015-07-29 11:26:42 Thread 1, Seqno 30, RBA 24593408
SCN 0.431606 (431606)
5、
目标端:
修改replicat进程的params文件,添加"ddl include all"和"ddlerrordefault ignore retryop maxretries 3 retrydelay 5" 参数,重启replicat进程
GGSCI (oggtarget.localdomain) 7> edit params rep_demo
REPLICAT rep_demo
SETENV (ORACLE_SID=oggt)
SETENV (NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
ddl include all
ddlerror default ignoreretryop maxretries 3 retrydelay 5
USERID goldengate,PASSWORD goldengate
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/rep_demo.dsc, APPEND,MEGABYTES 1000
ALLOWNOOPUPDATES
MAP OGG_USER.*, TARGET OGG_USER.*;
GGSCI (oggtarget.localdomain) 1> stop replicat rep_demo
Sending STOP request to REPLICAT REP_DEMO...
Request processed.
GGSCI (oggtarget.localdomain) 2> start replicat rep_demo
Sending START request to MANAGER ...
REPLICAT REP_DEMO starting
GGSCI (oggtarget.localdomain) 4> info replicat rep_demo
REPLICAT REP_DEMO Last Started2015-07-29 11:31 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File ./dirdat/t1000000
First Record RBA 1615
6、 测试源端和目标端的数据。
源端:
SQL> create table ogg_user.t5(a int);
Table created.
SQL> insert into ogg_user.t5 values(111);
1 row created.
SQL> commit;
Commit complete.
目标端:
SQL> select * from ogg_user.t5;
A
----------
111
7、
测试数据同步成功,实现了单向DDL复制。