GoldenGate schema级复制 实施过程

一.环境描述

Source Database:
192.168.8.21/192.168.8.22 (RAC)
REDHAT 4.0 x64
ORACLE 10.2.0.4

Target Database
192.168.3.106
AIX 5.3
ORACLE 10.2.0.4

二.环境说明
1. 源数据库和目标数据库创建ggs用户，授予DBA权限，用于保存GoldenGate管理数据；在源数据库创建test1用户，目标数据库创建test2用户，授予DBA权限，用于schema级的数据同步测试
2. Source数据库需要打开附加日志和强制归档，DDL同步需设置source数据库系统参数recyclebin为off
3. 软件安装
   从oracle网站下载GolenGate 软件：
source端使用软件包：ggs_redhatAS40_x64_ora10g_64bit_v10.4.0.19_002.tar
target端使用软件包：ggs_aix52_ppc_ora102_64bit_v10.4.0.19_002.tar

source端安装于192.168.8.21的/u02/ggs
target端安装于192.168.3.106的/u02/ggs

安装方法：
 将相应的软件包放入/u02/ggs下，使用操作系统的oracle用户，解包（ggs目录属于oracle用户）：
然后执行：./ggsci
GGSCI (idcdb01) 1> CREATE SUBDIRS

Creating subdirectories under current directory /u02/ggs

Parameter files                /u02/ggs/dirprm: created
Report files                   /u02/ggs/dirrpt: created
Checkpoint files               /u02/ggs/dirchk: created
Process status files           /u02/ggs/dirpcs: created
SQL script. files               /u02/ggs/dirsql: created
Database definitions files     /u02/ggs/dirdef: created
Extract data files             /u02/ggs/dirdat: created
Temporary files                /u02/ggs/dirtmp: created
Veridata files                 /u02/ggs/dirver: created
Veridata Lock files            /u02/ggs/dirver/lock: created
Veridata Out-Of-Sync files     /u02/ggs/dirver/oos: created
Veridata Out-Of-Sync XML files /u02/ggs/dirver/oosxml: created
Veridata Parameter files       /u02/ggs/dirver/params: created
Veridata Report files          /u02/ggs/dirver/report: created
Veridata Status files          /u02/ggs/dirver/status: created
Veridata Trace files           /u02/ggs/dirver/trace: created
Stdout files                   /u02/ggs/dirout: created

以上目录成功创建后，goldengate软件即安装完成。

添加数据库附加日志

SQLPLUS / AS SYSDBA
SQL>select supplemental_log_data_min from v$database;

如果返回结果为YES，则说明目前数据库已经打开附加日志功能。如果为NO,则需要执行下面的命令修改：

SQL>alter database add supplemental log data;

配置数据库强制归档

SQLPLUS / AS SYSDBA
SQL>ALTER DATABASE FORCE LOGGING;

创建goldengate用户，并授予dba权限（在source和target数据库端均要创建）
SQL> CREATE USER ggs IDENTIFIED BY ggs;
SQL> GRANT DBA TO ggs;

三.GoldenGate 配置
以下操作需要在source和target数据库端均操作
1.配置mgr进程
GGSCI>edit param mgr
输入：port 7809

2.配置GLOBALS
GGSCI>edit param ./GLOBALS
输入：
GGSCHEMA ggs
CHECKPOINTTABLE ggs.ggs_checkpoint

3.启动mgr进程

GGSCI>start mgr

4.我们可以通过下面的命令看到状态：

GGSCI>info all

四.DML同步测试
 测试数据使用GoldenGate自带测试脚本生成，脚本位于安装目录（/u02/ggs）下：
 注意：
经多次测试，GoldenGate管理所用schema与数据同步的schema应该分离，使用不用的schema，并且source和target应该使用同名的tablespace。
 Source：
 [oracle@idcdb01 ggs] $ cd /u02/ggs
 [oracle@idcdb01 ggs] $ sqlplus / as sysdba
 SQL> create user ggs identified by ggs;
 
User created.

SQL> grant dba to ggs;

Grant succeeded.

SQL> grant dba to ggs;

Grant succeeded.

SQL> create user test1 identified by test1;

User created.

SQL> grant dba to test1;

Grant succeeded.

SQL> conn ggs/ggs

SQL> @ demo_ora_create.sql
(创建tcustmer、tcustord两表)

SQL> exit

[oracle@idcdb01 ggs] $ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

GGSCI (idcdb01) 1> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (idcdb01) 2> add trandata ggs.*

GGSCI (idcdb01) 3> start mgr

Manager started.

GGSCI (idcdb01) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

GGSCI (idcdb01) 5> edit param eiexaa

extract eiexaa
userid ggs,password ggs
exttrail ./dirdat/dd
tranlogoptions altarchivelogdest instance payroll1 /u02/archive,altarchivelogdest instance payroll2 /nfs
tranlogoptions asmuser sys@ASM1,asmpassword orapayrolla
table ggs.*;

由于source服务器是rac环境，两节点archivelog分别在各自本地目录，所以首先将节点2（192.168.8.22）的archivelog目录配置为nfs服务目录，指定给节点1使用，在节点1上mount节点2的archivelog目录到挂载点/nfs；
由于source服务器使用ASM存放数据文件，在extract中必须这定ASM实例的登录用户sys和密码，此处ASM1是tnsnames.ora中的串名。

GGSCI (idcdb01)6> add extract eiexaa,tranlog,begin now,threads 2
EXTRACT added.

threads 2表示，extract是由RAC的两个节点中抽取数据

GGSCI (idcdb01) 7> add exttrail ./dirdat/dd,extract eiexaa,megabytes 5
EXTTRAIL added.

megabytes 5表示，extract生成的文件每个大小为5M

下面配置pump用于传输extract生成的日志文件到target端：

GGSCI (idcdb01) 10> edit param epmpaa
extract epmpaa
passthru
rmthost 192.168.3.106,mgrport 7809
rmttrail ./dirdat/dd
table ggs.*;

GGSCI (idcdb01) 12> add extract epmpaa,exttrailsource ./dirdat/dd
EXTRACT added.

GGSCI (idcdb01) 13> add rmttrail ./dirdat/dd,extract epmpaa,megabytes 5
RMTTRAIL added.

GGSCI (idcdb01) 14> start eiexaa

GGSCI (idcdb01) 15> start epmpaa

GGSCI (idcdb01) 16> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EIEXAA      00:00:00      00:03:59   
EXTRACT     RUNNING     EPMPAA      00:00:00      00:04:05   

Target：
 $ cd /u02/ggs
 $ sqlplus / as sysdba
 SQL> create user ggs identified by ggs;
 
User created.

SQL> grant dba to ggs;

Grant succeeded.

SQL> create user test2 identified by test2;

User created.

SQL> grant dba to test2;

Grant succeeded.

SQL> conn ggs/ggs

SQL> @ chkpt_ora_create.sql
(创建ggs_checkpoint表)

SQL> @ demo_ora_create.sql
(创建tcustmer、tcustord两表)

SQL> exit

$ ./ggsci

GGSCI (hrostemp) 2> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (hrostemp) 8> edit param rorabb

replicat rorabb
userid ogg,password ogg
handlecollisions
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rorabb.dsc,PURGE
MAP ggs.*, TARGET ggs.*;

GGSCI (hrostemp) 10> add replicat rorabb,checkpointtable ggs.ggs_checkpoint,exttrail ./dirdat/dd
REPLICAT added.

GGSCI (hrostemp) 11> start mgr

GGSCI (hrostemp) 12> start rorabb

GGSCI (hrostemp) 13> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     RORABB      00:09:10      00:02:56   

以上配置完成后，进行DML验证测试。
Source：
 [oracle@idcdb01 ggs] $ cd /u02/ggs
 [oracle@idcdb01 ggs] $ sqlplus ggs/ggs

SQL> @demo_ora_insert.sql
(在tcustmer、tcustord两表中插入数据)

SQL> select * from tcustmer;
SQL> select * from tcustord;

Target：
 $ cd /u02/ggs
 $ sqlplus ggs/ggs

SQL> select * from tcustmer;
SQL> select * from tcustord;
验证这两个表中的数据是否已从source端同步到target端。

五.DDL同步测试
 Source：
 [oracle@idcdb01 ggs] $ cd /u02/ggs
 [oracle@idcdb01 ggs] $ sqlplus / as sysdba
 
 SQL> show parameter recyclebin

NAME                     TYPE        VALUE
-------------------   ----------- --------------
recyclebin                string      ON
 
 SQL> alter system set recyclebi=off;
 
Recyclebin原值为on，DDL同步必须改为off

SQL> @marker_setup
Enter GoldenGate schema name:ggs

SQL> @ddl_setup
Enter GoldenGate schema name:ggs
Enter mode of installation:INITIALSETUP

SQL> @role_setup
Enter GoldenGate schema name:ggs

SQL> @ddl_enable

SQL> exit

[oracle@idcdb01 ggs] $ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Linux, x64, 64bit (optimized), Oracle 10 on Sep 22 2009 14:18:08

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

GGSCI (idcdb01) 1> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (idcdb01) 3> start mgr

Manager started.
 
GGSCI (idcdb01) 5> edit param eiexaa

extract eiexaa
userid ggs,password ggs
exttrail ./dirdat/dd
DDL
tranlogoptions altarchivelogdest instance payroll1 /u02/archive,altarchivelogdest instance payroll2 /nfs
tranlogoptions asmuser sys@ASM1,asmpassword orapayrolla
table test1.*;

在extract参数中增加关键字：DDL

GGSCI (idcdb01)6> delete eiexaa

GGSCI (idcdb01)7> add extract eiexaa,tranlog,begin now,threads 2
EXTRACT added.

重新配置extract

GGSCI (idcdb01) 8> add exttrail ./dirdat/dd,extract eiexaa,megabytes 5
EXTTRAIL added.
 
重新配置pump用于传输extract生成的日志文件到target端：

GGSCI (idcdb01) 10> edit param epmpaa
extract epmpaa
passthru
rmthost 192.168.3.106,mgrport 7809
rmttrail ./dirdat/dd
table test1.*;

GGSCI (idcdb01) 11> delete epmpaa

GGSCI (idcdb01) 12> add extract epmpaa,exttrailsource ./dirdat/dd
EXTRACT added.

GGSCI (idcdb01) 13> add rmttrail ./dirdat/dd,extract epmpaa,megabytes 5
RMTTRAIL added.

GGSCI (idcdb01) 14> start eiexaa

GGSCI (idcdb01) 15> start epmpaa

GGSCI (idcdb01) 16> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EIEXAA      00:00:00      00:03:59   
EXTRACT     RUNNING     EPMPAA      00:00:00      00:04:05   

Target：
 $ cd /u02/ggs

$ ./ggsci

GGSCI (hrostemp) 2> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (hrostemp) 8> edit param rorabb

replicat rorabb
userid ogg,password ogg
handlecollisions
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rorabb.dsc,PURGE
MAP test1.*, TARGET test2.*;

GGSCI (hrostemp) 9> delete rorabb

GGSCI (hrostemp) 10> add replicat rorabb,checkpointtable ggs.ggs_checkpoint,exttrail ./dirdat/dd
REPLICAT added.

GGSCI (hrostemp) 11> start mgr

GGSCI (hrostemp) 12> start rorabb

GGSCI (hrostemp) 13> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     RORABB      00:09:10      00:02:56   

以上配置完成后，进行DDL验证测试。
Source：
 [oracle@idcdb01 ggs] $ cd /u02/ggs
 [oracle@idcdb01 ggs] $ sqlplus ggs/ggs

SQL> @ demo_ora_lob_create.sql
(创建了带BLOB和CLOB字段的表TSRSLOB，使用过程TESTING_LOBS，可向此表中插入测试数据)

SQL> exec testing_lobs;

SQL> select count(*) from tsrslob;

SQL> create table t(id int);

SQL> alter table t add name varchar2(20);

SQL> alter table t drop column id;

SQL> drop table t;

Target：
 $ cd /u02/ggs
 $ sqlplus ggs/ggs

SQL> desc tsrslob;

SQL> select count(*) from tsrslob;
验证表tsrslob是否已在target中创建，并查看表中的数据是否同步到target端。

SQL> desc t;

六.Goldengate监控
在goldengate软件安装目录下，有关ggserr.log文件
我们可以通过查看此日志了解goldengate的运行过程：
Tail –f ggserr.log
同时GoldenGate还提供了view report命令：
GGSCI>view report eiexaa

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

转载于:http://blog.itpub.net/17997/viewspace-664570/