GOLDENGATE之RAC下的复制(1)

最新推荐文章于 2022-01-16 21:01:25 发布

csubx4572

最新推荐文章于 2022-01-16 21:01:25 发布

阅读量142

点赞数

文章标签：数据库网络操作系统

一.环境描述

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

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

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

--------

SQL> select force_logging from v$database;

FOR

---

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

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.

Commit complete.

SQL> select count(*) from TCUSTMER;

COUNT(*)

----------

登陆目标端：

SQL> select count(*) from TCUSTMER;

COUNT(*)

----------

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

补充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/

csubx4572

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫