ORACLE RAC 11.2.0.4 FOR LINUX TO ORACLE 11.2.0.3 OGG部署巨坑集锦

    在ORACLE RAC 11.2.0.4 FOR LINUX TO ORACLE 11.2.0.3 OGG部署过程中有很多需要注意的点，
如果注意不到就会引起很多问题。这里记录ORACLE 11G数据库部署OGG同步工具过程遇到的一些坑。
    环境：   
    源端操作系统：ORACLE LINUX 6.6
    目标端操作系统：Windows server2008R2
    源端数据库版本：ORACLE 11.2.0.4 RAC
    目标端数据库版本：ORACLE 11.2.0.3 单实例
    源端OGG版本：12.3.0.1.0
    目标端OGG版本：12.2.0.2.3
    客户需求：只在RAC单个节点上部署OGG工具，异地远程同步到oracle 11.2.0.3单实例

    坑1、由于异构平台，并且目标端操作系统是WINDOWS，目标端MGR进程需要开启接受特定的IP访问，
否则源端起动抽取传递进程报错（报错日志是在源端OGG安装目录下的ggserr）：
2018-02-27T10:03:30.767+0800  WARNING OGG-01223  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  TCP/IP error 110 ( Connection timed out), endpoint: 192.168.3.13:7809.
2018-02-27T10:05:39.922+0800  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ogg): start extract EXT_SC0.
2018-02-27T10:05:57.997+0800  ERROR   OGG-01201  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  Error reported by MGR : Access denied.
2018-02-27T10:05:58.005+0800  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  PROCESS ABENDING.

    调整方法：
    a、目标端mgr参数文件修改：
GGSCI (WIN-6ABO3IQOF3U as GOLDENGATE@orcl) 341> view param mgr
PORT 7809
ACCESSRULE, PROG *, IPADDR 192.168.*.*, ALLOW
PURGEOLDEXTRACTS C:\OGG\dirdat, USECHECKPOINTS
    b、确定目标端windows的防火墙关闭
    调整后启动初始化抽取投递进程正常：
2018-02-27T10:14:26.554+0800  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ogg): start extract EXT_SC0.
2018-02-27T10:14:26.554+0800  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host [192.168.3.11]:48461 (START EXTRACT EXT_SC0 ).
2018-02-27T10:14:26.554+0800  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT EXT_SC0 starting.
2018-02-27T10:14:26.662+0800  INFO    OGG-01017  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
2018-02-27T10:14:26.663+0800  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  EXTRACT EXT_SC0 starting.
2018-02-27T10:14:26.663+0800  INFO    OGG-03059  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  Operating system character set identified as UTF-8.
2018-02-27T10:14:26.663+0800  INFO    OGG-02695  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2018-02-27T10:14:26.663+0800  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  Successfully set environment variable NLS_LANG=AMERICAN_AMERICA.ZHS16GBK.
2018-02-27T10:14:26.671+0800  INFO    OGG-01360  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  EXTRACT is running in Initial Load mode.
2018-02-27T10:14:26.689+0800  INFO    OGG-01889  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  Flush size (max message size) is set to 27,985.
2018-02-27T10:14:26.815+0800  INFO    OGG-03522  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  Setting session time zone to source database time zone 'GMT'.
2018-02-27T10:14:26.815+0800  INFO    OGG-01851  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  filecaching started: thread ID: 140732522297088.
2018-02-27T10:14:26.815+0800  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  Virtual Memory Facilities for: COM
                                  anon alloc: mmap(MAP_ANON)  anon free: munmap
                                  file alloc: mmap(MAP_SHARED)  file free: munmap
                                  target directories:
                                  /OGG/dirtmp.
2018-02-27T10:14:26.882+0800  INFO    OGG-06509  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  Using the following key columns for source table SCOTT.EMP: EMPNO.

    坑2、由于异构平台，并且目标端操作系统是WINDOWS，目标端需要设置GLOBALS全局参数文件并且需要配置如下参数：
GGSCI (WIN-6ABO3IQOF3U as GOLDENGATE@orcl) 340> view param ./GLOBALS
ALLOWOUTPUTDIR  C:\OGG\dirdat  
如果没有设置该参数，源端启动传输进程时，抱错：
2018-02-27T11:27:54.613+0800  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ogg): info all.
2018-02-27T11:27:56.002+0800  INFO    OGG-01888  Oracle GoldenGate Capture for Oracle, pup_sc1.prm:  TCP network is configured as                              
                                                OS DEFAULT          SPECIFIED           ACTUAL VALUE        
                                       IP_DSCP    0                   N/A                 0                 
                                        IP_TOS    0                   N/A                 0                 
                                   TCP_NODELAY    0                   N/A                 0                 
                                  TCP_QUICKACK    1                   N/A                 1                 
                                      TCP_CORK    0                   N/A                 0                 
                                     SO_SNDBUF    8192                N/A                 8192              
                                     SO_RCVBUF    43690               N/A                 43690.
2018-02-27T11:28:00.909+0800  WARNING OGG-06591  Oracle GoldenGate Capture for Oracle, pup_sc1.prm:  Reading the output trail file C:\OGG\dirdat\p1000000 encounters an error from position 0, rescan from the file header to recover .
2018-02-27T11:28:00.913+0800  ERROR   OGG-01031  Oracle GoldenGate Capture for Oracle, pup_sc1.prm:  There is a problem in the communication with the Collector/Receiver Server. (Reply received is 'Output file C:\OGG\dirdat\p1000000 is not in any allowed output directories.').
2018-02-27T11:28:00.913+0800  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, pup_sc1.prm:  PROCESS ABENDING.  注意修改完目标端的MGR进程后要重庆MGR进程，调整完毕后PUP_SC1工作正常

    

    坑3、oracle ogg同步，当OGG高版本向低版本OGGT同步数据时，需要制定源端初始化抽取传输进程写入文件的兼容版本，
否则，启动初始化抽取进程时报错：
2018-02-27T10:14:26.902+0800  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  EXTRACT EXT_SC0 started.
2018-02-27T10:14:39.766+0800  WARNING OGG-01194  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  EXTRACT task REP_SC0 abended : File InitialLoad, with compatibility level 6, is not compatible with the current software version's compatibility level of 5 . Modify the file writer's parameter file to generate the appropriate format using the FORMAT LEVEL 5 option.
2018-02-27T10:14:39.766+0800  ERROR   OGG-01203  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  EXTRACT abending.
2018-02-27T10:14:39.766+0800  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  PROCESS ABENDING.

    根据报错提示，需要在源端队列写入进程参数文件中加入版本兼容参数： FORMAT LEVEL 5 ，具体操作就是：
edit params EXT_SC0
EXTRACT EXT_SC0
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID GOLDENGATE, PASSWORD GOLDENGATE
RMTHOST 192.168.3.128, MGRPORT 7809
RMTTASK REPLICAT, GROUP REP_SC0,FORMAT LEVEL 5
TABLE scott.emp;
TABLE scott.dept;
    
    坑4、虽然orace OGG支持源端零停机同步数据，但是需要在目标端事先创建好要同步的表的结构，否则OGG会报错：
2018-02-27T10:35:17.370+0800  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  EXTRACT EXT_SC0 started.
2018-02-27T10:35:28.808+0800  INFO    OGG-02911  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  Processing table SCOTT.EMP.
2018-02-27T10:35:29.291+0800  WARNING OGG-01194  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  EXTRACT task REP_SC0 abended : Table ZHUL.EMP does not exist in target database.
2018-02-27T10:35:29.291+0800  ERROR   OGG-01203  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  EXTRACT abending.
2018-02-27T10:35:29.291+0800  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, ext_sc0.prm:  PROCESS ABENDING.
    根据报错提示，修正方法很明确，就是在目标端创建要同步表的表结构，并影射到目标端的rep进程即可

    巨坑5、oracle初始化应用进程添加成功并且编辑好参数文件后，需要手工在ggsci控制台执行命令：
add replicat REP_SC0,specialrun   ----注意REP_SC0是初始化应用进程的名字，如果不执行该命令，启动源端初始化进程会报错，
说目标端dsc文件找不到：
2018-02-27T10:35:29.291+0800   WARNING OGG-01194  EXTRACT task REP_SC0 abended : Could not open checkpoint file C:\ogg\dirchk\REP_SC0.cpr, mode 1 (error 2, No such file or directory). 
报错提示也就是下图中的 REP_SC0 .cpr在ggsci控制台手动执行命令 add replicat REP_SC0,specialrun前是不存在的。

    该异常的处理方法就是： ggsci控制台手动执行命令 add replicat REP_SC0,specialrun

    巨坑6、oracle rac部署ogg与单实例不同，在添加源端抽取进程时需要制定集群模式，否则启动源端抽取进程时报错：
2018-02-27T10:52:01.041+0800  INFO    OGG-01635  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  BOUNDED RECOVERY: reset to initial or altered checkpoint.
2018-02-27T10:52:01.049+0800  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  Virtual Memory Facilities for: BR
                                  anon alloc: mmap(MAP_ANON)  anon free: munmap
                                  file alloc: mmap(MAP_SHARED)  file free: munmap
                                  target directories:
                                  /OGG/BR/EXT_SC1.
2018-02-27T10:52:01.052+0800  INFO    OGG-01851  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  filecaching started: thread ID: 140725520799488.
2018-02-27T10:52:01.053+0800  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  Virtual Memory Facilities for: COM
                                  anon alloc: mmap(MAP_ANON)  anon free: munmap
                                  file alloc: mmap(MAP_SHARED)  file free: munmap
                                  target directories:
                                  /OGG/dirtmp.
2018-02-27T10:52:01.110+0800  INFO    OGG-06604  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  Database ORCL CPU info: CPU Count 4, CPU Core Count 4, CPU Socket Count 1.
2018-02-27T10:52:01.608+0800  INFO    OGG-03522  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  Setting session time zone to source database time zone 'GMT'.
2018-02-27T10:52:02.155+0800  INFO    OGG-01971  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  The previous message, 'INFO OGG-03522', repeated 1 times.
2018-02-27T10:52:02.155+0800   ERROR   OGG-00868   Oracle GoldenGate Capture for Oracle, ext_sc1.prm:   The number of Oracle redo threads (2) is not the same as the number of checkpoint threads (1). EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT <group name>, TRANLOG, THREADS 2, BEGIN...).
2018-02-27T10:52:02.155+0800   ERROR   OGG-01668   Oracle GoldenGate Capture for Oracle, ext_sc1.prm:   PROCESS ABENDING.

    根据提示，删除当前的抽取进程，重新添加抽取进程并制定集群模式(其实就是告诉ogg要监控集群素有的redo组)：
delete extract ext_sc1
ADD EXTRACT ext_sc1  TRANLOG, threads 2 , BEGIN NOW
    日志信息：
2018-02-27T10:59:58.772+0800  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ogg): delete extract ext_sc1.
2018-02-27T11:00:13.908+0800  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ogg): ADD EXTRACT ext_sc1  TRANLOG,threads 2, BEGIN NOW.

    坑7、oracle rac如果使用的是ASM作为存储，需要oracle用户在TNS文件中配置ASM，并在抽取进程中显示指定ASM登陆信息，
以方便OGG进程抓取日志，否则会报错：
2018-02-27T11:09:22.151+0800  INFO    OGG-03522  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  Setting session time zone to source database time zone 'GMT'.
2018-02-27T11:09:23.076+0800  ERROR   OGG-00868  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  Attaching to ASM server ASM: (12154) ORA-12154: TNS:could not resolve the connect identifier specified.
2018-02-27T11:09:23.076+0800  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, ext_sc1.prm:  PROCESS ABENDING.
    ASM的TSN问题解决
    a、源端oracle用户配置ASM的TNS
[oracle@rac2 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-FEB-2018 14:03:13
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                27-FEB-2018 09:04:14
Uptime                    1 days 4 hr. 58 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/app/grid/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/oracle/app/grid/base/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.3.201)(PORT=1521)))
Services Summary...
Service " +ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$ 
[oracle@rac2 admin]$ pwd
/u01/oracle/app/oracle/product/11.2.0.4/db/network/admin
[oracle@rac2 admin]$ ls
samples  shrept.lst  tnsnames.ora
[oracle@rac2 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/oracle/app/oracle/product/11.2.0.4/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = racscanip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

+ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )
[oracle@rac2 admin]$ 
    b、修改抽取进程参数文件
edit params ext_sc1
EXTRACT EXT_SC1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID GOLDENGATE, PASSWORD GOLDENGATE
TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD redhat5
EXTTRAIL /OGG/dirdat/e1,FORMAT LEVEL 5
TABLE scott.emp;
TABLE scott.dept;

    坑8、用于oracle OGG同步的表不能用create table tablee_name as select 方式创建，这样创建的表会丢失主外健信息，
ogg数据同步的时候，目标端初始化应用进程REP_SC0会报错：

2018-02-27 15:23:54  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  REPLICAT REP_SC0 started.
2018-02-27 15:23:54  INFO    OGG-03522  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  Setting session time zone to source database time zone 'GMT'.
2018-02-27 15:23:55  WARNING OGG-02760  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  ASSUMETARGETDEFS is ignored because trail file  contains table definitions.
2018-02-27 15:23:55  INFO    OGG-06505  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  MAP resolved (entry SCOTT.EMP): MAP "SCOTT"."EMP",TARGET ZHUL.EMP.
2018-02-27 15:23:55  WARNING OGG-06439  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  No unique key is defined for table EMP. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2018-02-27 15:23:55  INFO    OGG-02756  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  The definition for table SCOTT.EMP is obtained from the trail file.
2018-02-27 15:23:55  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  Using following columns in default map by name: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO.
2018-02-27 15:23:55  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  Using the following key columns for target table ZHUL.EMP: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO.
2018-02-27 15:23:55  INFO    OGG-06505  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  MAP resolved (entry SCOTT.DEPT): MAP "SCOTT"."DEPT",TARGET ZHUL.DEPT.
2018-02-27 15:23:55  WARNING OGG-06439  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  No unique key is defined for table DEPT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2018-02-27 15:23:55  INFO    OGG-02756  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  The definition for table SCOTT.DEPT is obtained from the trail file.
2018-02-27 15:23:55  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  Using following columns in default map by name: DEPTNO, DNAME, LOC.
2018-02-27 15:23:55  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  Using the following key columns for target table ZHUL.DEPT: DEPTNO, DNAME, LOC.
2018-02-27 15:24:00  INFO    OGG-00994  Oracle GoldenGate Delivery for Oracle, REP_SC0.prm:  REPLICAT REP_SC0 stopped normally.
     OGG-02760 处理方法比较简单，ogg初始化数据前，到源端备份同步表的metadata，然后导入目标库或者获取同步表的DDL
在目标库创建表。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29357786/viewspace-2151364/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29357786/viewspace-2151364/