[实施目的]
1、OGG双向复制配置
[项目环境]
source system(gc5)
操作系统 | RedHat 5.4 |
主机名 | GC5 |
数据库版本 | Oracle 10.2.0.1.0 |
字符集 | ZHS16GBK |
生产库实例名 | EMREP |
监听 | LISTENER/1521 |
target system(gc1)
操作系统 | RedHat 5.4 |
主机名 | GC1 |
数据库版本 | Oracle 10.2.0.1.0 |
字符集 | ZHS16GBK |
生产库实例名 | PROD |
监听 | LISTENER/1521 |
[实施步骤]
- 添加补充日志和数据库强记日志source system并授权
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
SQL> alter database force logging;
Database altered.
SQL> grant alter any table to ogg;
- 为要传送的表,添加日志跟踪
[oracle@gc5 ogg]$ ./ggsci
GGSCI (gc5) 1> DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.
GGSCI (gc5) 2> ADD TRANDATA scott.EMP_OGG
Logging of supplemental redo data enabled for table SCOTT.EMP_OGG.
GGSCI (gc5) 3> ADD TRANDATA scott.DEPT_OGG
Logging of supplemental redo data enabled for table SCOTT.DEPT_OGG.
GGSCI (gc5) 4> INFO TRANDATA scott.*
Logging of supplemental redo log data is disabled for table SCOTT.BONUS.
Logging of supplemental redo log data is disabled for table SCOTT.DEPT.
Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG
Logging of supplemental redo log data is disabled for table SCOTT.EMP.
Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGG
Logging of supplemental redo log data is disabled for table SCOTT.SALGRADE.
- 配置EXTRACT进程
GGSCI (gc5) 5> EDIT PARAMS EORA_1
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
TRANLOGOPTIONS EXCLUDEUSER ogg
EXTTRAIL ./dirdat/aa
TABLE scott.emp_ogg;
TABLE scott.dept_ogg;
GGSCI (gc5) 6> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (gc5) 7> ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5
EXTTRAIL added.
GGSCI (gc5) 8> START EXTRACT EORA_1
Sending START request to MANAGER ...
EXTRACT EORA_1 starting
GGSCI (gc5) 9> INFO EXTRACT EORA_1
EXTRACT EORA_1 Last Started 2014-08-12 14:58 Status RUNNING
Checkpoint Lag 00:00:28 (updated 00:00:09 ago)
Log Read Checkpoint Oracle Redo Logs
2014-08-12 14:58:24 Seqno 7, RBA 58384
- 配置pump进程
GGSCI (gc5) 10> EDIT PARAMS PORA_1
EXTRACT PORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
PASSTHRU
RMTHOST gc1, MGRPORT 7809
RMTTRAIL ./dirdat/pa
TABLE scott.emp_ogg;
TABLE scott.dept_ogg;
GGSCI (gc5) 11> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa
EXTRACT added.
GGSCI (gc5) 12> ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5
RMTTRAIL added.
GGSCI (gc5) 13> START EXTRACT PORA_1
Sending START request to MANAGER ...
EXTRACT PORA_1 starting
GGSCI (gc5) 14> info EXTRACT PORA_1
EXTRACT PORA_1 Last Started 2014-08-12 15:01 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint File ./dirdat/aa000000
First Record RBA 895
- target system(gc1)
- 配置checkpoint
[oracle@gc1 ~]$ cd /u01/app/ogg
[oracle@gc1 ogg]$ ./ggsci
GGSCI (gc1) 1> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ogg.ggschkpt
GGSCI (gc1) 1> DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.
GGSCI (gc1) 2> ADD CHECKPOINTTABLE
No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...
Successfully created checkpoint table OGG.GGSCHKPT.
- 配置replicate
GGSCI (gc1) 3> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa
REPLICAT added.
GGSCI (gc1) 4> EDIT PARAM RORA_1
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE
MAP scott.*, TARGET scott.*;
GGSCI (gc1) 5> START REPLICAT RORA_1
Sending START request to MANAGER ...
REPLICAT RORA_1 starting
GGSCI (gc1) 6> INFO REPLICAT RORA_1
REPLICAT RORA_1 Last Started 2014-08-12 15:07 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Log Read Checkpoint File ./dirdat/pa000000
First Record RBA 0
- 配置支持DDL复制
- 在两个节点执行执行DDL同步脚本命令
先进入goldengate软件安装目录,以SYSDBA身份登录oracle执行以下脚本,执行脚本过程中,需要输入的用户全部是ogg,安装模式为INITIALSETUP,如果数据字典或者某些内部的包有错误,则需要运行catalog.sql和catproc.sql脚本。
[oracle@gc5 ogg]$ !sql
sqlplus '/as sysdba'
SQL> alter system set recyclebin=off scope=spfile; --关闭回收站
SQL> shutdown immediate
SQL> startup
SQL>@marker_setup
SQL>@ddl_setup
SQL>@role_setup
SQL>grant GGS_GGSUSER_ROLE to ogg;
SQL>@ddl_enable
如果某项脚本执行错误,需要重新执行时,先要执行清除的脚本:ddl_remove.sql和marker_remove.sql
- 先关闭eora_1进程、PORA_1进程、RORA_1进程
source system
GGSCI (gc5) 2> stop extract eora_1
GGSCI (gc5) 3> stop extract PORA_1
GGSCI (gc5) 4> stop REPLICAT rora_1
target system
GGSCI (gc1) 4> stop extract eora_1
GGSCI (gc1) 5> stop extract PORA_1
GGSCI (gc1) 6> stop REPLICAT rora_1
- 配置两个节点的extract,在eora_1配置文件中添加以下一行:
DDL INCLUDE OBJNAME "scott.*"
最终的内容如下:
GGSCI (gc1) 3> edit param eora_1
-- Change Capture parameter file to capture
-- EMP_OGG and DEPT_OGG changes
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/aa
DDL INCLUDE OBJNAME "scott.*"
TABLE scott.EMP_OGG;
TABLE scott.DEPT_OGG;
GGSCI (gc5) 1> edit param eora_1
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
TRANLOGOPTIONS EXCLUDEUSER ogg
EXTTRAIL ./dirdat/aa
DDL INCLUDE OBJNAME "scott.*"
TABLE scott.emp_ogg;
TABLE scott.dept_ogg;
- 配置两个节点的replicat
source system
GGSCI (gc5) 5> DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.
GGSCI (gc5) 6> add checkpointtable ogg.checkpoint
Successfully created checkpoint table OGG.CHECKPOINT.
Target system
GGSCI (gc1) 7> DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.
GGSCI (gc1) 8> add checkpointtable ogg.checkpoint
Successfully created checkpoint table OGG.CHECKPOINT.
如果之前已经添加过,则无需操作。
- 配置REPLICAT进程参数文件,添加以下几行到rora_1配置文件中
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERROR DEFAULT DISCARD
DDLERROR DEFAULT IGNORE RETRYOP
最终的内容如下:
GGSCI (gc5) 7> edit param rora_1
-- Change Delivery parameter file to apply
-- EMP_OGG and DEPT_OGG Changes
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERROR DEFAULT DISCARD
DDLERROR DEFAULT IGNORE RETRYOP
MAP scott.emp_ogg, TARGET scott.emp_ogg;
MAP scott.dept_ogg, TARGET scott.dept_ogg;
GGSCI (gc1) 9> edit param rora_1
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERROR DEFAULT DISCARD
DDLERROR DEFAULT IGNORE RETRYOP
MAP scott.emp_ogg, TARGET scott.emp_ogg;
MAP scott.dept_ogg, TARGET scott.dept_ogg;
- 开启进程并验证
- 开启进程
source system
GGSCI (gc5) 2> start extract eora_1
GGSCI (gc5) 3> start extract PORA_1
GGSCI (gc5) 4> start REPLICAT rora_1
GGSCI (gc5) 12> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:05
EXTRACT RUNNING PORA_1 00:00:00 00:00:02
REPLICAT RUNNING RORA_1 00:00:00 00:00:04
target system
GGSCI (gc1) 4> start extract eora_1
GGSCI (gc1) 5> start extract PORA_1
GGSCI (gc1) 6> start REPLICAT rora_1
GGSCI (gc1) 13> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_1 00:00:00 00:00:06
EXTRACT RUNNING PORA_1 07:02:41 00:00:04
REPLICAT RUNNING RORA_1 00:00:00 00:00:02
- 验证
source system 插入数据
[oracle@gc5 ogg]$ !sql
SQL> conn scott/tiger
SQL> select * from dept_ogg;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> INSERT INTO dept_ogg VALUES(10,'aa','bb');
SQL> commit;
Commit complete.
SQL> select * from dept_ogg;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 aa bb
target system 查询数据,发现数据已经过来了
[oracle@gc1 ogg]$ !sql
SQL> conn scott/tiger
SQL> select * from dept_ogg;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 aa bb
target system 再删掉数据
SQL> delete from dept_ogg where deptno=10;
SQL> commit;
SQL> select * from dept_ogg;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
source system 查看数据,发现数据已应用
SQL> select * from dept_ogg;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
- 补充:
- 如果新建的表进行同步,update可能不成功,需要进行一下操作,这样子ogg才会去捕
捉new_tab的日志信息:
ADD TRANDATA scott.new_tab;
注意:先关闭rora_1进程,再添加,然后重新启动。
- 如果是序列,无需关注,因为oracle是取得序列的值进行insert的。
- 如果源端建表的时候是基于子查询,如果子查询中访问的表在目标端没有,则无法实现
ddl同步。或者子查询中的表如果数据不一样,则同步的表数据也不一样,根据各自数据库的子查询中的表来定。
- 如果是insert操作,数据基于子查询,如果子查询访问的表目标端没有,却不受影响,
能够同步。
- update某行时,如果目标数据库没有符合条件的行,ogg会insert一条新行。
解决办法: 在最新的GG11.2版本中针对此种情况设置了专门的冲突解决机制,在11.2以前配置解决这个冲突很复杂
(11:16:58 AM) louise.liang@oracle.com: 在initial load 中有一个参数可以避免这个冲突,但是在正常复制时一般不建议用
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29819001/viewspace-1255288/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29819001/viewspace-1255288/