GOLDENGATE之RAC下的复制(1)

一.环境描述

Source Database:

192.168.0.50/192.168.0.60 (RAC)

REDHAT 4.0 x64

ORACLE 10.2.0.1

Target Database

192.168.0.32

REDHAT 4.0 x64

ORACLE 10.2.0.1

二.环境说明

1. 源数据库和目标数据库创建ggs用户，授予DBA权限，用于保存GoldenGate管理数据；在源数据库创建test用户，目标数据库创建test用户，授予DBA权限，用于schema级的数据同步测试

2. Source数据库需要打开附加日志和强制归档，DDL同步需设置source数据库系统参数recyclebin为off

3. 软件安装

从oracle网站下载GolenGate 软件：
source及target 端使用软件包：ggs_redhatAS40_x86_ora10g_32bit_v10.4.0.19_002.tar

source端安装于192.168.8.21的/home/oracle/ggs

target端安装于192.168.3.106的/home/oracle/ggs

安装方法：

 将相应的软件包放入/home/oracle/ggs s下，使用操作系统的oracle用户，解包（ggs目录属于oracle用户）：

然后执行：./ggsci

如果出现如下错误：

oracle@ractest1 ggs]$ ./ggsci

./ggsci: error while loading shared libraries: libnnz10.so: cannot open shared object file: No such file or directory

修改.BASH_PROFILE文件

添加export LD_LIBRARY_PATH=$ORACLE_HOME/lib

 

[oracle@ractest1 ggs]$ ./ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x86, 32bit (optimized), Oracle 10 on Sep 17 2009 23:49:42

 

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

 

GGSCI (ractest1) 1> create subdirs

 

Creating subdirectories under current directory /home/oracle/ggs

 

Parameter files                /home/oracle/ggs/dirprm: created

Report files                   /home/oracle/ggs/dirrpt: created

Checkpoint files               /home/oracle/ggs/dirchk: created

Process status files           /home/oracle/ggs/dirpcs: created

SQL script. files               /home/oracle/ggs/dirsql: created

Database definitions files     /home/oracle/ggs/dirdef: created

Extract data files             /home/oracle/ggs/dirdat: created

Temporary files                /home/oracle/ggs/dirtmp: created

Veridata files                 /home/oracle/ggs/dirver: created

Veridata Lock files            /home/oracle/ggs/dirver/lock: created

Veridata Out-Of-Sync files     /home/oracle/ggs/dirver/oos: created

Veridata Out-Of-Sync XML files /home/oracle/ggs/dirver/oosxml: created

Veridata Parameter files       /home/oracle/ggs/dirver/params: created

Veridata Report files          /home/oracle/ggs/dirver/report: created

Veridata Status files          /home/oracle/ggs/dirver/status: created

Veridata Trace files           /home/oracle/ggs/dirver/trace: created

Stdout files                   /home/oracle/ggs/dirout: created

 

以上目录成功创建后，goldengate软件即安装完成。由于备库之前做过相关的测试，所以这里不再进行藐视。

 

修改数据库归档模式及添加数据库附加日志

 

[oracle@ractest1 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 29 15:36:10 2010

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Current log sequence           2

SQL> select supplemental_log_data_min from v$database;

 

SUPPLEME

--------

NO

 

SQL> select force_logging from v$database;

 

FOR

---

NO

 

SQL> alter database force logging ;

 

Database altered.

 

SQL> alter database add supplemental log data;

 

Database altered.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  281018368 bytes

Fixed Size                  1218968 bytes

Variable Size              92276328 bytes

Database Buffers          184549376 bytes

Redo Buffers                2973696 bytes

Database mounted.

 

SQL> alter database archivelog;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

以上都修改完毕。

 

创建goldengate用户，并授予dba权限（在source和target数据库端均要创建）
SQL> create user ggs identified by ggs ;

 

User created.

 

SQL> grant connect,resource,dba to ggs;

 

Grant succeeded.

 

三.GoldenGate 配置

以下操作需要在source和target数据库端均操作

1.配置mgr进程

GGSCI (ractest1)>edit param mgr

输入：port 10002

2.配置GLOBALS

GGSCI (ractest1)>edit param ./GLOBALS

输入：

GGSCHEMA ggs

CHECKPOINTTABLE ggs.ggs_checkpoint

3.启动mgr进程

GGSCI (ractest1) 5> start mgr

Manager started.

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

 

GGSCI (ractest1) 6> info all

 

Program     Status      Group       Lag           Time Since Chkpt

 

MANAGER     RUNNING     

 

四.DML同步测试
 测试数据使用GoldenGate自带测试脚本生成，脚本位于安装目录（/home/oracle/ggs）下：

 注意：经过测试，GoldenGate管理所用schema与数据同步的schema应该分离，使用不用的schema，并且source和target应该使用同名的tablespace。

 Source：

[oracle@ractest1 ggs]$ sqlplus / as sysdba

SQL> create user test identified by test;

User created.

SQL> grant connect,resource,dba to test;

Grant succeeded.

SQL> conn test/test

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

DROP TABLE tcustmer

           *

ERROR at line 1:

ORA-00942: table or view does not exist

 

Table created.

 

DROP TABLE tcustord

           *

ERROR at line 1:

ORA-00942: table or view does not exist

 

Table created.

SQL> select table_name from user_tables;

 

TABLE_NAME

------------------------------

TCUSTMER

TCUSTORD

 

SQL> conn ggs/ggs

Connected.

SQL> @ chkpt_ora_create.sql

DROP TABLE ggs_checkpoint

           *

ERROR at line 1:

ORA-00942: table or view does not exist

 

 

 

Table created.

 

GGSCI (ractest1) 8> dblogin userid ggs, password ggs

Successfully logged into database.

 

GGSCI (ractest1) 10> add trandata test.*

 

Logging of supplemental redo data enabled for table TEST.TCUSTMER.

 

Logging of supplemental redo data enabled for table TEST.TCUSTORD.

 

GGSCI (ractest1) 12> start mgr

MGR is already running.

GGSCI (ractest1) 14> edit params racext

extract racext

userid ggs,password ggs

exttrail ./dirdat/dd

tranlogoptions altarchivelogdest instance orcl1 /home/oracle/arch1,altarchivelogdest instance orcl2 /home/oracle/arch2

tranlogoptions asmuser sys@ASM1,asmpassword oracle

TRANLOGOPTIONS ASMUSER sys@ASM1, ASMPASSWORD oracle

table test.*;

 

由于source服务器是rac环境，两节点archivelog分别在/home/oracle/arch1和/home/oracle/arch2，归档日志必须要能够同时读到，所以在2台主机上设置了NFS，将主机2上的归档目录通过NFS到主机1的/home/oracle/arch2。

由于source服务器使用ASM存放数据文件，在extract中必须这定ASM实例的登录用户sys和密码，此处ASM1是tnsnames.ora中的串名。

修改如下内容在listener.ora文件

SID_LIST_LISTENER_RACTEST1 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)

      (PROGRAM = extproc)

    )

   (SID_DESC =

      (GLOBAL_DBNAME = ASM)

      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)

      (SID_NAME = +ASM1)

   )

  )

添加如下内容到tnsnames.ora：

ASM1 =

   (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST = ractest1-vip)(PORT = 1521))

       (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = ASM)

       (INSTANCE_NAME = +ASM1)

      )

   )

 

ASM2 =

    (DESCRIPTION =

       (ADDRESS = (PROTOCOL = TCP)(HOST = ractest2-vip)(PORT = 1521))

          (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = ASM)

          (INSTANCE_NAME = +ASM2)

       )

    )

GGSCI (ractest1) 15> add extract racext,tranlog,begin now,threads 2

EXTRACT added.

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

GGSCI (ractest1) 16> add exttrail ./dirdat/dd,extract racext,megabytes 10

EXTTRAIL added.

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

 

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

GGSCI (ractest1) 17> edit param racpump

extract racpump

passthru

userid ggs,password ggs

rmthost 192.168.0.32,mgrport 10002

rmttrail ./dirdat/dd

table test.*;

GGSCI (ractest1) 18> add extract racpump,exttrailsource ./dirdat/dd

EXTRACT added.

GGSCI (ractest1) 19> add rmttrail ./dirdat/dd,extract racpump,megabytes 10

RMTTRAIL added.

GGSCI (ractest1) 20> start racext

Sending START request to MANAGER ...

EXTRACT RACEXT starting

GGSCI (ractest1) 21> start racpump

Sending START request to MANAGER ...

EXTRACT RACPUMP starting

GGSCI (ractest1) 89> info all

 

Program     Status      Group       Lag           Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     RUNNING     RACEXT      00:00:00      00:00:06   

EXTRACT     RUNNING     RACPUMP     00:00:00      00:00:01   

 

登陆目标端：

GGSCI (gctest3) 1> dblogin userid ggs , password ggs

Successfully logged into database.

 

GGSCI (gctest3) 2> edit params racrep

replicat racrep

userid ggs,password ggs

handlecollisions

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/rorabb.dsc,PURGE

MAP test.*, TARGET test.*;

 

GGSCI (gctest3) 3> info all

 

Program     Status      Group       Lag           Time Since Chkpt

 

MANAGER     STOPPED                                          

EXTRACT     ABENDED     EXTTEST     00:00:00      1293:49:17 

REPLICAT    ABENDED     REPTEST     00:00:00      1293:49:16 

 

 

GGSCI (gctest3) 4> start mgr

 

Manager started.

 

 

GGSCI (gctest3) 5> add replicat racrep,checkpointtable ggs.ggs_checkpoint,exttrail ./dirdat/dd

REPLICAT added.

 

 

GGSCI (gctest3) 6> start racrep

 

Sending START request to MANAGER ...

REPLICAT RACREP starting

 

 

GGSCI (gctest3) 7> info all

 

Program     Status      Group       Lag           Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     ABENDED     EXTTEST     00:00:00      1293:50:13 

REPLICAT    RUNNING     RACREP      00:00:00      00:00:00   

REPLICAT    ABENDED     REPTEST     00:00:00      1293:50:12 

 

进行测试：

首先在原端：

[oracle@ractest1 ggs]$ sqlplus test/test

 

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 29 18:44:55 2010

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

 

SQL> @demo_ora_insert.sql

 

1 row created.

 

 

1 row created.

 

 

1 row created.

 

 

1 row created.

 

 

Commit complete.

SQL> select count(*) from TCUSTMER;

 

  COUNT(*)

----------

         2

 

登陆目标端：

 

SQL> select count(*) from TCUSTMER;

 

  COUNT(*)

----------

         2

 

测试完成，由于此次测试属于比较笼统的，所以以后的其他测试待续………………….

 

补充LIUNX NFS设置

[root@ractest1 ~]# rpm -qa | grep port

portmap-4.0-63

oracleasm-support-2.0.3-1

sysreport-1.3.15-5

[root@ractest1 ~]# rpm -qa | grep nfs

nfs-utils-1.0.6-65.EL4

system-config-nfs-1.2.8-1

节点2上：

root@ractest2 ~]# vi /etc/exports

/home/oracle/arch2     *(ro)

[root@ractest2 ~]# service portmap restart

Stopping portmap: [  OK  ]

Starting portmap: [  OK  ]

[root@ractest2 ~]# service nfs restart

Shutting down NFS mountd: [  OK  ]

Shutting down NFS daemon: [  OK  ]

Shutting down NFS quotas: [  OK  ]

Shutting down NFS services:  [  OK  ]

Starting NFS services:  [  OK  ]

Starting NFS quotas: [  OK  ]

Starting NFS daemon: [  OK  ]

Starting NFS mountd: [  OK  ]

[root@ractest2 ~]# exportfs

/home/oracle/arch2

               

[root@ractest2 ~]#

 

参考文档：

http://gavinsoorma.com/2010/02/oracle-goldengate-tutorial-1-configuring-data-pump-process/

此处是一套连载，介绍的比较详细

http://www.aug828.com/wp/2010/01/gg-on-rac/

http://space.itpub.net/17997/viewspace-664570

 

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

转载于:http://blog.itpub.net/7882490/viewspace-677084/