奕新集团--单实例到RAC节点 GG同步

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   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进程。