首页 > 代码库 > 搭建一个Oracle到Oracle的Goldengate双向复制环境
搭建一个Oracle到Oracle的Goldengate双向复制环境
目标：搭建一个Oracle到Oracle的Goldengate双向复制环境（支持ＤＤＬ＋ＤＭＬ）。
环境：
ＯＳ：Red Hat Enterprise Linux Server release 5.5 (Tikanga)
ＤＢ：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
gg1和gg2互为source和target，所处ＯＳ和所安ＤＢ均一样。
一、安装ＧＧ
在gg1和gg2都运行例如以下操作：
（１）加入环境变量，在/home/oracle/.bash_profile文件中加入例如以下内容：
export PATH=/u01/ggate:$PATH
exportLD_LIBRARY_PATH=/u01/ggate:$LD_LIBRARY_PATH
export ggtest=/u01/ggate
这里的GG 和Oracle 使用的是同样的用户，所以把GG 的变量加上就能够了。载入刚刚设置的环境变量：
source/home/oracle/.bash_profile
（２）使用ggsci工具，创建必要的文件夹
gg1:/u01/ggate> ggsci
--调用ggsci 工具
Oracle GoldenGate Command Interpreter forOracle
Version 11.1.1.1OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11gon Apr 21 2011 22:42:14
Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.
GGSCI (gg1) 1> create subdirs
--使用ggsci 工具创建文件夹
Creating subdirectories under currentdirectory /u01/ggate
Parameter files /u01/ggate/dirprm: created
Report files /u01/ggate/dirrpt: created
Checkpoint files /u01/ggate/dirchk: created
Process status files /u01/ggate/dirpcs: created
SQL script files /u01/ggate/dirsql: created
Database definitions files /u01/ggate/dirdef: created
Extract data files /u01/ggate/dirdat: created
Temporary files /u01/ggate/dirtmp: created
Veridata files /u01/ggate/dirver: created
Veridata Lock files /u01/ggate/dirver/lock: created
Veridata Out-Of-Sync files /u01/ggate/dirver/oos: created
Veridata Out-Of-Sync XML files/u01/ggate/dirver/oosxml: created
Veridata Parameter files /u01/ggate/dirver/params: created
Veridata Report files /u01/ggate/dirver/report: created
Veridata Status files /u01/ggate/dirver/status: created
Veridata Trace files /u01/ggate/dirver/trace: created
Stdout files /u01/ggate/dirout: created
GGSCI (gg1) 2>
以上就是GG 的安装，在source 和target database 都运行。
二、分别配置ｓｏｕｒｃｅ和ｔａｒｇｅｔ
（１）GoldenGate通过抓取源端数据库重做日志进行分析，将获取的数据应用到目标端，实现数据同步。因此，源数据库须要必须处于归档模式，并启用附加日志和强制日志。
归档模式、附加日志、强制日志
--查看
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG NO NO
--改动
（1）archivelog
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
（2）force logging
SQL>alterdatabase force logging;
（3）supplemental log data
SQL>alterdatabase add supplemental log data;
（２）禁用Recycle Bin
假设启用DDL 支持，必须关闭recycle bin。官网的解释例如以下：
If the recyclebin is enabled, the Oracle GoldenGate DDL trigger session receives implicitrecycle bin DDL operations that cause the trigger to fail.
Oracle 11g：
SQL> alter system set recyclebin=offscope=spfile;
System altered.
假设数据库是10g，须要关闭recyclebin并重新启动；或者手工purge recyclebin。
（３）创建存放DDL 信息的user并赋权
SQL> create user ggtest identified by oracle default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to ggtest;
Grant succeeded.
SQL> grant execute on utl_file to ggtest;
Grant succeeded.
退出全部使用Oracle 的session，然后使用SYSDBA权限的用户运行例如以下脚本：
gg1:/u01/ggate> echo $ggate
/u01/ggate
--进入GG的文件夹，然后调用脚本：
gg1:/home/oracle> cd $ggate
gg1:/u01/ggate> sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production onTue Nov 8 19:41:58 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
--脚本1：
SQL> @marker_setup.sql;
Marker setup 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.
--输入我们之前创建的username：
Enter GoldenGate schema name:ggtest
Marker setup table script complete, runningverification script...
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to ggtest
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
--脚本2：
SQL> @ddl_setup.sql;
GoldenGate DDL Replication setup script
Verifying that current user has privilegesto install DDL Replication...
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the systemrecycle bin must be disabled. For Oracle 11g and later, it can be enabled.
--注意这里提示我们在10g里，必须关闭recycle bin，在11g以后的版本号，能够不用关闭。
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
--提示输入GG的用户：
Enter GoldenGate schema name:ggtest
You will be prompted for the mode ofinstallation.
To install or reinstall DDL replication,enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
--这里让我们选择安装模式： install 和 reinstall 选择INITIALSETUP
Enter mode of installation:INITIALSETUP
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holdinglocks on Oracle Golden Gate metadata tables ...
Check complete.
Using ggtest as a GoldenGate schema name,INITIALSETUP as a mode of installation.
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 ggtest
DDLORA_GETTABLESPACESIZE STATUS:
……
STATUS OF DDL REPLICATION
-------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replicationsoftware components
Script complete.
--脚本3：
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.
--相同输入GGusername：
Enter GoldenGate schema name:ggtest
Wrote 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.
--脚本4：赋权
SQL> grant GGS_GGSUSER_ROLE to ggtest;
Grant succeeded.
--脚本5：
SQL> @ddl_enable.sql;
Trigger altered.
注意这里脚本创建的table都是使用默认的名称，当然也能够改动这些table的默认名。
三、測试
注意：
（1） 目标库的username和对象名称可以与源端不同，关键在于配置文件里要可以正确匹配。
（2） 配置源和目标两端tnsnames，保持互联互通。（记得启动lsnrctl）
gg1:
SQL> create user ssgg identified by oracle default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,dba to ssgg;
Grant succeeded.
gg2:
SQL> create user ttgg identified by oracle default tablespace users temporary
tablespace temp;
User created.
SQL> grant connect,resource,dba to ttgg;
Grant succeeded.
參数设置
配置gg1，加入checkpoint表：
GGSCI (gg1) 1> dblogin userid ggtest,password oracle
Successfully logged into database.
GGSCI (gg1) 2> view params ./GLOBAL
ggschema ggtest
checkpointtable ggtest.checkpoint
GGSCI (gg1) 27> view params mgr
PORT 7809 --指定管理port
dynamicportlist 7810-7900 --动态port列表，当指定port不可用时，管理进程会自己主动选择一个可用port。最大可指定256个port
autostart er * --mgr开启时自己主动开启全部的extract and replicat process
autorestart er *,retries 5,waitminutes 2 --extract and replicat processes fail mgr automatically start
參数每2分钟重新启动一次，重新启动5次后放弃
1、 t o c o n f i g u r e t h e p r i m a r y e x t r a c t g o u p
GGSCI (gg1) 10> add extract ext01,tranlog,begin now
EXTRACT added.
GGSCI (gg1) 13> add exttrail /u01/ggate/dirdat/sd,extract ext01
EXTTRAIL added.
GGSCI (gg1) 28> view params ext01
extract ext01 --提取进程组的名字
userid ggtest, password oracle --同步用户
exttrail /u01/ggate/dirdat/sd --trail 文件文件夹和标识（类似于sd*这样文件)，命名仅仅能是2个字符。
tranlogoptions excludeuser ggtest --避免循环复制的出现
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
table ssgg.*; --同步的表或用户下全部的表
当你须要同步除Goldengate和Oracle数据库自带的用户外全部用户的DDL操作时，可在源端主抽取进程中增加以下的内容
ddl include all
然后再在目标端Replicat进程參数文件里增加DDL错误的处理语句就可以。
2、 t o c o n f i g u r e t h e d a t a p u m p
GGSCI (gg1) 19> add extract p01,exttrailsource /u01/ggate/dirdat/sd,begin now
EXTRACT added.
GGSCI (gg1) 20> add rmttrail /u01/ggate/dirdat/pd,extract p01
RMTTRAIL added.
GGSCI (gg1) 29> view params p01
extract p01
userid ggtest,password oracle
rmthost 192.168.50.231,mgrport 7809 --指定远端IP和MGR端口
rmttrail /u01/ggate/dirdat/pd
table ssgg.*;
3、 t o c o n f i g u r e t h e r e p l i c a t g r o u p
GGSCI (gg1) 3> add replicat r02,exttrail /u01/ggate/dirdat/dp,begin now
ERROR: No checkpoint table specified for ADD REPLICAT.
GGSCI (gg1) 4> add replicat r02,exttrail /u01/ggate/dirdat/dp,checkpointtable ggtest.checkpoint
REPLICAT added.
GGSCI (gg1) 30> view params r02
replicat r02
assumetargetdefs --定义目标表和源表使用MAP语法有同样的列结构，当出现热点时，不查看源结构而直接从源定义文件查
看
userid ggtest,password oracle
DISCARDFILE /u01/ggate/dirdat/sdisc.dsc,append,megabytes 100
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map ttgg.*, target ssgg.*;
启动全部进程：
GGSCI (gg1) 3> start mgr
Manager started.
GGSCI (gg1) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:00:00 00:00:07
EXTRACT RUNNING P01 00:00:00 00:00:01
REPLICAT RUNNING R02 00:00:00 00:00:08
配置gg2，加入checkpoint表：
GGSCI (gg2) 1> dblogin userid ggtest,password oracle
Successfully logged into database.
GGSCI (gg2) 25> view params ./GLOBAL
ggschema ggtest
checkpointtable ggtest.checkpoint
GGSCI (gg2) 29> view params mgr
PORT 7809
dynamicportlist 7810-7900
autostart er *
autorestart er *,retries 5,waitminutes 2
c o n f i g u r a t i o n f r o m s e c o u n d a r y s y s t e m t o p r i m a r y s y s t e m
1 、t o c o n f i g u r e t h e p r i m a r y e x t r a c t g r o u p
GGSCI (gg2) 12> add extract ext02,tranlog,begin now
EXTRACT added.
GGSCI (gg2) 13> add exttrail /u01/ggate/dirdat/td,extract ext02
EXTTRAIL added.
GGSCI (gg2) 26> view params ext02
extract ext02
userid ggtest, password oracle
exttrail /u01/ggate/dirdat/td
tranlogoptions excludeuser ggtest
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
table ttgg.*;
2 、t o c o n f i g u r e t h e p u m p
GGSCI (gg2) 17> add extract p02,exttrailsource /u01/ggate/dirdat/td,begin now
EXTRACT added.
GGSCI (gg2) 19> add rmttrail /u01/ggate/dirdat/dp,extract p02
RMTTRAIL added.
GGSCI (gg2) 27> view params p02
extract p02
userid ggtest,password oracle
rmthost 192.168.50.230,mgrport 7809
rmttrail /u01/ggate/dirdat/dp
table ttgg.*;
3.t o c o n f i g u r e t h e r e p l i c a t g r o u p
GGSCI (gg2) 7> add replicat r01,exttrail /u01/ggate/dirdat/pd,begin now
ERROR: No checkpoint table specified for ADD REPLICAT.
GGSCI (gg2) 8> add replicat r01,exttrail /u01/ggate/dirdat/pd,checkpointtable ggtest.checkpoint;
REPLICAT added.
GGSCI (gg2) 28> view params r01
replicat r01
assumetargetdefs
userid ggtest,password oracle
DISCARDFILE /u01/ggate/dirdat/sdisc.dsc,append,megabytes 100
DISCARDFILE不能直接在安装文件夹/u01/ggate/，进程可能起不来。
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map ssgg.*, target ttgg.*;
启动全部进程：
GGSCI (gg2) 3> start mgr
Manager started.
GGSCI (gg2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT02 00:00:00 00:00:03
EXTRACT RUNNING P02 00:00:00 00:00:07
REPLICAT RUNNING R01 00:00:14 00:00:04
数据同步測试：
Gg1：
SQL> conn ssgg/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SSGG TABLE
TTGG TABLE
SQL> create table test as select * from ssgg;
Table created.
SQL> select * from test;
ID
----------
1
5
SQL> commit;
Commit complete.
然后查看gg2，看数据是否同步
Gg2：
SQL> conn ttgg/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SSGG TABLE
TTGG TABLE
SQL> /
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SSGG TABLE
TEST TABLE
TTGG TABLE
SQL> insert into test values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
1
5
10
再返回gg1，查看数据是否同步：
SQL> host hostname
gg1
SQL> select * from test;
ID
----------
1
5
10
至此，Oracle到Oracle的双向数据同步成功(DML+DDL)。
实现双向复制，一般用于双业务中心环境下在实际应用中。双向复制（ＤＤＬ＋ＤＭＬ）面临着很多问题，主要有例如以下几点：
1. 假设两个库同一时候更新同一条记录怎样处理？调整业务规则
2. 假设网络出现失败怎样处理？支持断点续传
3. 假设数据不同步后怎样修复？导出源端，导入目标端
总结：
双向复制避免数据冲突是难点，解决这个难点根本还是调整业务：
a) 仅仅在一段开展业务
b) 两端开展不同业务，涉及不同数据集
c) 两端开展同样业务，但根据地域或其它条件对数据予以区分，两边不操作同一条数据
补充：
在oracle中能够通过rowid来定位某条记录，可是目标端的数据库和源端数据库的数据库可能全然不一样，所以无法通过rowid来确定源端数据库的逻辑变化，这时附加日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后，对于源端的改动操作，oracle会同一时候追加能够唯一标示记录的列到redo log。这样目标端数据库就能够知道源端发生了哪些详细的变化。
在目标库上创建checkpointtable ，此表中保存的检查点指示 Replicat 进程的当前读写位置。这用于在进程须要重新启动时或者在server出现不论什么故障或网络停顿时防止数据丢失。假设没有这一机制，将导致数据丢失。还有一个优点是，通过使用检查点，多个 Extract 或 Replicat 进程可通过同样的一组线索进行读取。
对于以批处理模式执行的 Extract 和 Replicat 进程，检查点没必要的，由于这种进程总是能够重新启动。然而，在 Extract 和 Replicat 进程连续工作的情况下，检查点是必需的。检查点通常保存在 dirchk 子文件夹的文件里，可是对于 Replicat，能够选择在数据库的检查点表中保存检查点。
搭建一个Oracle到Oracle的Goldengate双向复制环境