RAC环境下配置OGG同步-白红宇

RAC环境下配置OGG同步

阅读量：5867 次

发布时间：2019-06-19

本文共 11607 字，大约阅读时间需要 38 分钟。

在本文中，介绍了rac环境下配置ogg的单向同步；在配置过程中，2节点的rac数据库做为source端，单实例的数据库作为target端，下面是具体的环境介绍!

source端：

操作系统版本：rhel5.4 64bit

数据库版本：11.2.0.3 64bit

ogg版本：fbo_ggs_Linux_x64_ora10g_64bit.tar

rac数据库名：rac.yang.com

public ip:192.168.1.41/24,192.168.1.42/24

private ip:192.168.122.41/24,192.168.122.42/24

vip:192.168.1.141/24,192.168.1.142/24

scan名：rac-scan.yang.com

scan-ip:192.168.1.144/24,192.168.1.145/24,192.168.1.146/24

target端：

操作系统版本：rhel5.4 64bit

数据库版本：11.2.0.3 64bit

ogg版本：fbo_ggs_Linux_x64_ora10g_64bit.tar

数据库名：dg3.yang.com

ip:192.168.1.63/24

一：首先在两个rac节点上配置ASM动态注册,11g的监听器引入了endpoints_listener.ora文件管理

[grid@rac1 rac1]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): rac2,rac1

[grid@rac1 rac1]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-JUL-2012 21:09:20

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "rac.yang.com" has 1 instance(s).

Instance "rac1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "racXDB.yang.com" has 1 instance(s).

Instance "rac1", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: rac1.yang.com, pid: 5183>

(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.yang.com)(PORT=63054))

The command completed successfully

[grid@rac1 rac1]$ cat $TNS_ADMIN/listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent

LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

[grid@rac1 rac1]$ cat /u01/app/11.2.0/grid/network/admin/endpoints_listener.ora

LISTENER_RAC1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.1.41)(PORT=1521)(IP=FIRST)))) # line added by Agent

[grid@rac1 rac1]$ tail -8 $TNS_ADMIN/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = +ASM)

(ORACLE_HOME=/u01/app/11.2.0/grid)

(SID_NAME = +ASM1)

)

)

[grid@rac2 ~]$ tail -8 $TNS_ADMIN/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = +ASM)

(ORACLE_HOME=/u01/app/11.2.0/grid)

(SID_NAME = +ASM2)

)

)

[oracle@rac1 ~]$ sqlplus sys/123456@192.168.1.41:1521/+ASM as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 21:31:30 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter name;

NAME TYPE VALUE

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

db_unique_name string +ASM

instance_name string +ASM1

lock_name_space string

service_names string +ASM

SQL> conn sys/123456@192.168.1.42:1521/+ASM as sysdba

Connected.

SQL> show parameter name;

NAME TYPE VALUE

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

db_unique_name string +ASM

instance_name string +ASM2

lock_name_space string

service_names string +ASM

[oracle@rac1 admin]$ cat tnsnames.ora //节点2的tnsnames.ora文件做相应的配置

RAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rac.yang.com)

)

)

ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.41)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = +ASM)

(SID_NAME = +ASM1)

)

)

二：acfs上安装ogg，acfs用来存放ogg的安装目录，便于集群件的共享，在10g上部署可以选择ocfs2，acfs的部署过程参考：

[root@rac1 ~]# ll -d /vol2/

drwxrwx--- 4 root asmadmin 4096 Jul 9 09:38 /vol2/

[root@rac1 ~]# id oracle

uid=501(oracle) gid=500(oinstall) groups=500(oinstall),502(asmdba),504(dba),505(oper)

[root@rac1 ~]# chown -R oracle.asmadmin /vol2/ （节点2做同样的操作）

[root@rac1 ~]# ll -d /vol2/

drwxrwx--- 4 oracle asmadmin 4096 Jul 9 09:38 /vol2/

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$ mkdir -p /vol2/ogg

[oracle@rac1 ogg]$ tar -xvf /home/oracle/fbo_ggs_Linux_x64_ora11g_64bit.tar

[oracle@rac1 ~]$ grep 'LD_LIBRARY_PATH' .bash_profile （节点2做同样的操作）

export LIBRARY_PATH=/vol2/ogg:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

[oracle@rac1 ~]$ source .bash_profile

[oracle@rac1 ~]$ cd -

/vol2/ogg

[oracle@rac1 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

GGSCI (rac1.yang.com) > create subdirs

Creating subdirectories under current directory /vol2/ogg

Parameter files /vol2/ogg/dirprm: already exists

Report files /vol2/ogg/dirrpt: created

Checkpoint files /vol2/ogg/dirchk: created

Process status files /vol2/ogg/dirpcs: created

SQL script files /vol2/ogg/dirsql: created

Database definitions files /vol2/ogg/dirdef: created

Extract data files /vol2/ogg/dirdat: created

Temporary files /vol2/ogg/dirtmp: created

Stdout files /vol2/ogg/dirout: created

GGSCI (rac1.yang.com) > view params mgr

port 7809

autostart er *

autorestart er *

GGSCI (rac1.yang.com) > start mgr

Manager started.

GGSCI (rac1.yang.com) > info mgr

Manager is running (IP port rac1.yang.com.7809).

GGSCI (rac1.yang.com) > exit

[oracle@rac1 ogg]$ netstat -ntpl |grep 7809

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp 0 0 :::7809 :::* LISTEN 10622/mgr

三：rac数据库上进行配置，创建用户，授权，运行执行序列号和ddl复制的相关脚本等

[oracle@rac1 ogg]$ sqlplus sys/123456@rac as sysdba

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

rac2

SQL> create user ogg identified by ogg;

User created.

SQL> grant connect,resource,dba to ogg;

Grant succeeded.

SQL> @sequence.sql

SQL> alter database add supplemental log data;

Database altered.

SQL> alter database add supplemental log data (primary key) columns;

Database altered.

SQL> alter database add supplemental log data (foreign key) columns;

Database altered.

SQL> alter database add supplemental log data (unique) columns;

Database altered.

SQL> alter system archive log current;

System altered.

SQL> grant execute on utl_file to ogg;

Grant succeeded.

SQL> @marker_setup.sql

SQL> @ddl_setup.sql

SQL> @role_setup.sql

SQL> grant ggs_ggsuser_role to ogg;

SQL> @ddl_enable.sql

SQL> @ddl_pin ogg

四：在source端配置extract group

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

GGSCI (rac1.yang.com) > dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (rac1.yang.com) > add extract testext,tranlog,begin now,threads 2

EXTRACT added.

GGSCI (rac1.yang.com) > add exttrail /vol2/ogg/dirdat/et, extract testext

EXTTRAIL added.

GGSCI (rac1.yang.com) > view params testext

EXTRACT testext

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db1")

USERID ogg@rac, PASSWORD ogg

TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD 123456

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000

EXTTRAIL /vol2/ogg/dirdat/et

DYNAMICRESOLUTION

DDL INCLUDE ALL

TABLE test.*;

五：在source端配置data pump extract group

GGSCI (rac1.yang.com) > add extract testpump,exttrailsource /vol2/ogg/dirdat/et,begin now

EXTRACT added.

GGSCI (rac1.yang.com) > add rmttrail /vol2/ogg/dirdat/rt,extract testpump

RMTTRAIL added.

GGSCI (rac1.yang.com) > view params testpump

EXTRACT testpump

RMTHOST 192.168.1.63, MGRPORT 7809

RMTTRAIL /vol2/ogg/dirdat/rt

PASSTHRU

TABLE test.*

六：target端配置，这里要保证在tnsnames.ora文件中配置了dg3连接串，同时建立/vol2/ogg/dirdat/rt目录，并授权；在这里未测试过target端和source使用不同的目录是否可行！

SQL> create user ogg identified by ogg;

User created.

SQL> grant connect,resource,dba to ogg;

Grant succeeded.

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

GGSCI (dg3.yang.com) > add replicat testrpt,exttrail /vol2/ogg/dirdat/rt,nodbcheckpoint

REPLICAT added.

GGSCI (dg3.yang.com) > view params testrpt

REPLICAT testrpt

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db1")

USERID ogg@dg3,PASSWORD ogg

ASSUMETARGETDEFS

HANDLECOLLISIONS

REPERROR (DEFAULT, DISCARD)

DDLERROR DEFAULT DISCARD

DDLOPTIONS REPORT

DISCARDFILE /vol2/ogg/repsz.dsc,append,megabytes 100

MAP test.*, TARGET test.*;

七：启动各进程

GGSCI (rac1.yang.com) > start testext

Sending START request to MANAGER ...

EXTRACT TESTEXT starting

GGSCI (rac1.yang.com) > start testpump

Sending START request to MANAGER ...

EXTRACT TESTPUMP starting

GGSCI (rac1.yang.com) > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING TESTEXT 00:00:00 00:19:49

EXTRACT RUNNING TESTPUMP 00:00:00 00:03:24

GGSCI (dg3.yang.com) > start testrpt

Sending START request to MANAGER ...

REPLICAT TESTRPT starting

GGSCI (dg3.yang.com) > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING TESTRPT 00:00:00 00:00:02

进程安排在最后启动，在启动source端的pump进程之前，如果target端的replicat进程未启动，则会报如下错误：

2012-07-15 13:56:14 ERROR OGG-01033 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Remote file used is /vol2/ogg/dirdat/rt000000, reply received is Could not create /vol2/ogg/dirdat/rt000000).

八：测试同步情况