Install and Configure Goldengate on RAC 10g
以前写过几篇关于OGG(Oracle GlodenGate)部署的文章,是关于单实例到单实例,具体内容可参考:
Install and simple configure Oracle GoldenGate
Configure Oracle GoldenGate Active-to-Active
今天测试了一下从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.169
target 端:
操作系统版本:rhel4.8 32bit
数据库版本:10.2.0.1 32bit
ogg 版本:fbo_ggs_Linux_x86_ora10g_32bit.tar
Database Name:honcho
IP:192.168.0.99
1.在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 10g 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 (0x00321000)
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 (0x00c26000)
libnnz10.so => /u01/app/oracle/product/10.2.0/db_1/lib/libnnz10.so (0x00482000)
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进程。