在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
在目標庫建立表。

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

相關文章