以前写过几篇关于OGG（Oracle GlodenGate）部署的文章，是关于单实例到单实例，具体内容可参考：
今天测试了一下从RAC 到 单实例的简单部署，做简要记录如下：
测试环境：
source 端： 操作系统版本：rhel4.8 32bit 数据库版本：10.2.0.4 32bit ogg 版本：fbo_ggs_Linux_x86_ora10g_32bit.tar RAC 数据库名：honcho 实例：honcho1、honcho2 Hostname: xhz1、xhz2 Public IP: 192.168.0.166 、192.168.0.168 Private IP：10.10.10.166 、10.10.10.168 VIP: 192.168.0.167 、192.168.0.169target 端：
操作系统版本：rhel4.8 32bit 数据库版本：10.2.0.1 32bit ogg 版本：fbo_ggs_Linux_x86_ora10g_32bit.tar Database Name：honcho IP:192.168.0.991.在RAC节点上配置ASM实例监听动态注册，确保goldengate用户能够连接到所有的ASM实例。
[oracle@xhz1 admin]$ cat listener.ora # listener.ora.xhz1 Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.xhz1 # Generated by Oracle configuration tools. LISTENER_XHZ1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xhz1-vip)(PORT = 1521)(IP = FIRST)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.166)(PORT = 1521)(IP = FIRST)) ) ) SID_LIST_LISTENER_XHZ1 = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = +ASM) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = +ASM1) ) ) [oracle@xhz1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENERS_HONCHO = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xhz1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = xhz2-vip)(PORT = 1521)) ) HONCHO1 = (description_list = (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=192.168.0.167)(Port=1521)) (ADDRESS=(PROTOCOL=TCP)(Host=192.168.0.169)(Port=1521)) ) (CONNECT_DATA= (SERVICE_NAME=honcho1) (server=dedicated) (FAILOVER_MODE= (RETRIES=30) (DELAY=5) (TYPE=SELECT) ) ) ) ) HONCHO2 = (description_list = (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(Host=192.168.0.169)(Port=1521)) (ADDRESS=(PROTOCOL=TCP)(Host=192.168.0.167)(Port=1521)) ) (CONNECT_DATA= (SERVICE_NAME=honcho2) (server=dedicated) (FAILOVER_MODE= (RETRIES=30) (DELAY=5) (TYPE=SELECT) ) ) ) ) HONCHO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xhz1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = xhz2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = honcho) ) ) EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) ASM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.166)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = +ASM) (SID_NAME = +ASM1) ) )
在节点2上做类似的配置，然后重启监听：
[oracle@xhz1 admin]$ srvctl stop listener -n xhz1 [oracle@xhz1 admin]$ srvctl stop listener -n xhz2 [oracle@xhz1 admin]$ srvctl start listener -n xhz1 [oracle@xhz1 admin]$ srvctl start listener -n xhz2
[ oracle @ xhz1 admin ]$ export ORACLE_SID =+ ASM1 [ oracle @ xhz1 admin ]$ sqlplus / as sysdba SQL * Plus : Release 10.2.0.4.0 - Production on Thu Sep 6 16 : 18 : 24 2012 Copyright ( c ) 1982 , 2007 , Oracle . All Rights Reserved . Connected to : Oracle Database 10 g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning , Real Application Clusters , OLAP , Data Mining and Real Application Testing options SQL > conn sys / oracle @ 192.168.0.168 : 1521 /+ ASM as sysdba Connected . SQL > show parameter instance NAME TYPE VALUE ---------------------------------- -- ----------- ------------------------------ active_instance_count integer cluster_database_instances integer 2 instance_groups string instance_name string + ASM2 instance_number integer 2 instance_type string asm open_links_per_instance integer 4 parallel_instance_group string parallel_server_instances integer 2 SQL > conn sys / oracle @ 192.168.0.166 : 1521 /+ ASM as sysdba Connected . SQL > show parameter instance NAME TYPE VALUE ---------------------------------- -- ----------- ------------------------------ active_instance_count integer cluster_database_instances integer 2 instance_groups string instance_name string + ASM1 instance_number integer 1 instance_type string asm open_links_per_instance integer 4 parallel_instance_group string parallel_server_instances integer 2
查看数据库字符集：
SQL > show parameter nls_lang NAME TYPE VALUE ---------------------------------- -- ----------- ------------------------------ nls_language string AMERICAN SQL > show parameter nls_terr NAME TYPE VALUE ---------------------------------- -- ----------- ------------------------------ nls_territory string AMERICA SQL > col name for a20 SQL > col value $ for a50 SQL > set linesize 130 SQL > select name , value $ from PROPS $ WHERE name = ' NLS_CHARACTERSET ' ; NAME VALUE $ ------------------ -- -------------------------------------------------- NLS_CHARACTERSET WE8ISO8859P1
2.在source和target端部署ogg，由于前面几个文章里关于部署ogg做个介绍，这里不做太多解释：
2.1 创建安装路径：
[oracle@xhz1 ~]$ mkdir -p /u01/app/oracle/ogg
2.2 配置环境变量：
[oracle@xhz1 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH unset USERNAME export ORACLE_SID=honcho1 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 export ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crs_1 export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$ORACLE_BASE/ogg:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_BASE/ogg:/lib:/usr/lib export OGG=/u01/app/oracle/ogg umask 022
2.3 解压安装包到安装路径：
[oracle@xhz1 software]$ tar xvf fbo_ggs_Linux_x86_ora10g_32bit.tar -C /u01/app/oracle/ogg
2.4 启动ggsci并创建目录，然后进行必要的设置，启动manager
[oracle@xhz1 ogg]$ ldd ggsci libdl.so.2 => /lib/libdl.so.2 (0x007d3000) libicui18n.so.38 => /u01/app/oracle/ogg/libicui18n.so.38 (0x 00321000) libicuuc.so.38 => /u01/app/oracle/ogg/libicuuc.so.38 (0x001e1000) libicudata.so.38 => /u01/app/oracle/ogg/libicudata.so.38 (0xb74e7000) libpthread.so.0 => /lib/tls/libpthread.so.0 (0x008ef000) libxerces-c.so.28 => /u01/app/oracle/ogg/libxerces-c.so.28 (0x00c 26000) libnnz10.so => /u01/app/oracle/product/10.2.0/db_1/lib/libnnz10.so (0x 00482000) libclntsh.so.10.1 => /u01/app/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1 (0x00fd3000) libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x009c8000) libm.so.6 => /lib/tls/libm.so.6 (0x007d9000) libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x0094f000) libc.so.6 => /lib/tls/libc.so.6 (0x006a5000) /lib/ld-linux.so.2 (0x0068b000) libnsl.so.1 => /lib/libnsl.so.1 (0x00bdb000) libnsl.so.1 => /lib/libnsl.so.1 (0x00bdb000) [oracle@xhz1 ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_ 111004.2100 Linux, x86, 32bit (optimized), Oracle 10g on Oct 4 2011 23:54:04 Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved. GGSCI (xhz1) 1> create subdirs Creating subdirectories under current directory /u01/app/oracle/ogg Parameter files /u01/app/oracle/ogg/dirprm: created Report files /u01/app/oracle/ogg/dirrpt: created Checkpoint files /u01/app/oracle/ogg/dirchk: created Process status files /u01/app/oracle/ogg/dirpcs: created SQL script files /u01/app/oracle/ogg/dirsql: created Database definitions files /u01/app/oracle/ogg/dirdef: created Extract data files /u01/app/oracle/ogg/dirdat: created Temporary files /u01/app/oracle/ogg/dirtmp: created Veridata files /u01/app/oracle/ogg/dirver: created Veridata Lock files /u01/app/oracle/ogg/dirver/lock: created Veridata Out-Of-Sync files /u01/app/oracle/ogg/dirver/oos: created Veridata Out-Of-Sync XML files /u01/app/oracle/ogg/dirver/oosxml: created Veridata Parameter files /u01/app/oracle/ogg/dirver/params: created Veridata Report files /u01/app/oracle/ogg/dirver/report: created Veridata Status files /u01/app/oracle/ogg/dirver/status: created Veridata Trace files /u01/app/oracle/ogg/dirver/trace: created Stdout files /u01/app/oracle/ogg/dirout: created GGSCI (xhz1) 2> edit param mgr PORT 7809 autostart er * autorestart er * GGSCI (xhz1) 3> start mgr Manager started. GGSCI (xhz1) 4> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING GGSCI (xhz1) 5> view params mgr PORT 7809 autostart er * autorestart er * [oracle@xhz1 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 0.0.0.0:7809 0.0.0.0:* LISTEN 16333/mgr
2.5 创建用户，授权，运行执行序列号和ddl复制的相关脚本等
SQL > select log_mode , supplemental_log_data_min , force_logging from v $ database ; LOG_MODE SUPPLEME FOR ---------- -- -------- --- ARCHIVELOG YES NO SQL > shutdown immediate Database closed . Database dismounted . ORACLE instance shut down . SQL > startup mount ORACLE instance started . Total System Global Area 285212672 bytes Fixed Size 1267068 bytes Variable Size 121637508 bytes Database Buffers 159383552 bytes Redo Buffers 2924544 bytes Database mounted . SQL > alter database force logging ; Database altered . SQL > alter database open ; Database altered . SQL > create user ogg identified by ogg default tablespace users temporary tablespace temp ; User created . SQL > @ sequence . sql Please enter the name of a schema for the GoldenGate database objects : ogg Setting schema name to OGG UPDATE_SEQUENCE STATUS : Line / pos Error -------- -- ----------------------------------------------------------------- No errors No errors GETSEQFLUSH Line / pos Error -------- -- ----------------------------------------------------------------- No errors No errors SEQTRACE Line / pos Error -------- -- ----------------------------------------------------------------- No errors No errors REPLICATE_SEQUENCE STATUS : Line / pos Error -------- -- ----------------------------------------------------------------- No errors No errors STATUS OF SEQUENCE SUPPORT ------------------------------------------------------------ -- SUCCESSFUL installation of Oracle Sequence Replication support SQL > alter database add supplemental log data ; Database altered . SQL > select log_mode , supplemental_log_data_min , force_logging from v $ database ; LOG_MODE SUPPLEME FOR ---------- -- -------- --- ARCHIVELOG YES YES SQL > select supplemental_log_data_min , 2 supplemental_log_data_pk , supplemental_log_data_ui 3 from v $ database ; SUPPLEME SUP SUP ------ -- --- --- YES NO NO 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 > select supplemental_log_data_min , 2 supplemental_log_data_pk , supplemental_log_data_ui 3 from v $ database ; SUPPLEME SUP SUP ------ -- --- --- YES YES YES SQL > alter system archive log current ; System altered . SQL > grant execute on utl_file to ogg ; Grant succeeded . SQL > @ marker_setup . sql Marker setup script You will be prompted for the name of a schema for the GoldenGate database objects . NOTE : The schema must be created prior to running this script . NOTE : Stop all DDL replication before starting this installation . Enter GoldenGate schema name : ogg Marker setup table script complete , running verification script ... Please enter the name of a schema for the GoldenGate database objects : Setting schema name to OGG MARKER TABLE ----------------------------- -- OK MARKER SEQUENCE ----------------------------- -- OK Script complete . SQL > @ ddl_setup . sql ---根据提示输入 initialsetup ，最后输入 yes SQL > @ role_setup . sql GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name , quit this script and then edit the params . sql script to change the gg_role parameter to the preferred name . ( Do not run the script . ) You will be prompted for the name of a schema for the GoldenGate database objects . NOTE : The schema must be created prior to running this script . NOTE : Stop all DDL replication before starting this installation . Enter GoldenGate schema name : ogg Wrote file role_setup_set . txt PL / SQL procedure successfully completed . Role setup script complete Grant this role to each user assigned to the Extract , GGSCI , and Manager processes , by using the following SQL command : GRANT GGS_GGSUSER_ROLE TO < loggedUser > where < loggedUser > is the user assigned to the GoldenGate processes . SQL > grant ggs_ggsuser_role to ogg ; Grant succeeded . SQL > @ ddl_enable . sql SQL > @?/ rdbms / admin / dbmspool . sql Package created . Grant succeeded . View created . Package body created . SQL > @ ddl_pin ogg PL / SQL procedure successfully completed . PL / SQL procedure successfully completed . PL / SQL procedure successfully completed .
3.在source端配置extract group
SQL > create user test identified by test ; User created . SQL > grant connect , resource to test ; Grant succeeded .
GGSCI (xhz1) 1> dblogin userid ogg,password ogg Successfully logged into database. GGSCI (xhz1) 2> add extract testext,tranlog,begin now,threads 2 EXTRACT added. GGSCI (xhz1) 3> add exttrail /u01/app/oracle/ogg/dirdat/et, extract testext EXTTRAIL added. GGSCI (xhz1) 4> edit params testext GGSCI (xhz1) 5> view params testext EXTRACT testext SETENV (ORACLE_HOME = "/u01/app/oracle/product/10.2.0/db_1") USERID ogg@honcho, PASSWORD ogg TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000 EXTTRAIL /u01/app/oracle/ogg/dirdat/et DYNAMICRESOLUTION DDL INCLUDE ALL TABLE test.*;
4.在source端配置data pump extract group
GGSCI (xhz1) 6> add extract testpump,exttrailsource /u01/app/oracle/ogg/dirdat/et,begin now EXTRACT added. GGSCI (xhz1) 7> add rmttrail /u01/app/oracle/ogg/dirdat/et,extract testpump RMTTRAIL added. GGSCI (xhz1) 8> edit params testpump GGSCI (xhz1) 9> view params testpump EXTRACT testpump USERID ogg,PASSWORD ogg RMTHOST 192.168.0.99, MGRPORT 7809 RMTTRAIL /u01/app/oracle/ogg/dirdat/et PASSTHRU TABLE test.*;
5.target端配置，这里要保证在tnsnames.ora文件中配置了honcho连接串，这里路径是一直的，可以根据自己需要配置不同路径亦可。
GGSCI (honcho) 1> add replicat testrpt,exttrail /u01/app/oracle/ogg/dirdat/et,nodbcheckpoint REPLICAT added. GGSCI (honcho) 2> edit params testrpt GGSCI (honcho) 3> view params testrpt REPLICAT testrpt SETENV (ORACLE_HOME = "/u01/app/oracle/product/10.2.0.1/db1") USERID ogg@honcho,PASSWORD ogg ASSUMETARGETDEFS HANDLECOLLISIONS REPERROR (DEFAULT, DISCARD) DDLERROR DEFAULT DISCARD DDLOPTIONS REPORT DISCARDFILE /u01/app/oracle/ogg/repsz.dsc,append,megabytes 100 MAP test.*, TARGET test.*;
6.启动extract和replicat 进程：
GGSCI (honcho) 4> start testrpt Sending START request to MANAGER ... REPLICAT TESTRPT starting GGSCI (honcho) 5> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING TESTRPT 00:00:00 00:00:09 GGSCI (xhz1) 10> start testext Sending START request to MANAGER ... EXTRACT TESTEXT starting GGSCI (xhz1) 11> start testpump Sending START request to MANAGER ... EXTRACT TESTPUMP starting GGSCI (xhz1) 12> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING TESTEXT 00:00:00 00:00:00 EXTRACT RUNNING TESTPUMP 00:00:00 00:00:04
7.测试
在节点1上创建测试表： SQL > select instance_name from v $ instance ; INSTANCE_NAME -------------- -- honcho1 SQL > conn test / test Connected . SQL > create table honcho ( id number , name varchar2 ( 20 )) ; Table created . SQL > begin 2 for i in 1..100 loop 3 insert into honcho values ( i , ' honcho ' ) ; 4 end loop ; 5 commit ; 6 end ; 7 / PL / SQL procedure successfully completed . SQL > select count ( * ) from honcho ; COUNT ( * ) -------- -- 100
在目标上查看：
SQL > select instance_name from v $ instance ; INSTANCE_NAME -------------- -- honcho SQL > conn test / test Connected . SQL > select count ( * ) from honcho ; COUNT ( * ) -------- -- 100
在节点2上添加新数据：
SQL > select instance_name from v $ instance ; INSTANCE_NAME -------------- -- honcho2 SQL > conn test / test Connected . SQL > begin 2 for i in 101..200 loop 3 insert into honcho values ( i , ' honcho ' ) ; 4 end loop ; 5 commit ; 6 end ; 7 / PL / SQL procedure successfully completed . SQL > select count ( * ) from honcho ; COUNT ( * ) -------- -- 200
在目标库上查看数据：
SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- honcho SQL> conn test/test Connected. SQL> select count(*) from honcho; COUNT(*) ---------- 200
从测试数据可以查到在节点1和节点2上变更数据都可以同步到目标端。
总结：rac环境下配置ogg，基本同单实例无异！需要注意的地方有以下几点!
1：只需要在一个节点部署ogg即可，但是需要保证ogg可以识别到其他节点的redo或者archivelog，如果归档实在本地，可以通过NFS来实现访问； 2：使用ASM存储，需要先配置ASM实例的静态注册，同时在tnsnames.ora文件中配置连接串； 3：在配置exttract进程中，注意需要配置TRANLOGOPTIONS 参数，输入连接asm实例的凭证； 4：在配置exttract进程中，添加tranlog的时候，需要写thread n，n代表节点数； 5：注意进程的启动顺序，先是source和target端的mgr进程，其次是source端的extract进程，target端的replicat进程，最后是source端的extract pump进程。