一、环境描述
|
I P |
实例 |
数据库版本 |
O GG | |
|
生产端 |
1 92.168.238.195/196 |
orcl |
1 1204 |
/ |
|
D G |
1 92.168.238.56 |
o rcldg |
1 1204 |
1 2.3 |
|
O GG 备库 |
1 92.168.238.56 |
ora |
1 1204 |
1 2.3 |
二、经典模式测试 2.1ADG模式 创建挖掘进程
add extract adg_ext tranlog threads 2 begin now add exttrail ./dirdat/ad extract adg_ext extract adg_ext setenv (NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK") setenv (ORACLE_SID="orcldg") userid odc,password odc exttrail ./dirdat/ad tranlogoptions minefromactivedg --tranlogoptions dblogreader fetchoptions fetchpkupdatecols ddl include objname test.* exclude objtype 'TRIGGER' --DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10 table test.*;
创建应用进程
GGSCI (ggs as odc@orcl1) 5> dblogin userid odc password odc Successfully logged into database. GGSCI (ggs as odc@ora) 6> add replicat adg_rep exttrail ./dirdat/ad REPLICAT added. replicat adg_rep setenv (NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK") setenv (ORACLE_SID="ora") userid odc, password odc --handlecollisions assumetargetdefs allownoopupdates dboptions deferrefconst handletpkupdate --batchsql ddlerror 955 ignore ddlerror 1917 ignore ddlerror 24344 ignore ddlerror 1031 ignore ddl include mapped discardfile ./dirrpt/adg.dsc, append megabytes 20 discardrollover on sunday --discardrollover --ddlerror 942 ignore map test.* target test.*;
2.1.1测试二节点正常关机
[oracle@rac11gn2 ~]$ export ORACLE_SID=orcl2 SQL> shutdown immediate OGG正常，数据可以正常传输 重启挖掘也正常
2.1.2测试二节点异常宕机
二节点宕机 [root@rac11gn2 ~]# reboot OGG正常，数据可以正常传输 重启挖掘也正常
2.2DG模式 创建挖掘进程
add extract dg_ext tranlog threads 2 begin now add exttrail ./dirdat/dg extract dg_ext extract dg_ext setenv (NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK") setenv (ORACLE_SID=orcldg) userid odc@orcl,password odc exttrail ./dirdat/dg tranlogoptions archivedlogonly tranlogoptions altarchivelogdest /oracle/app/arch/ora fetchoptions fetchpkupdatecols ddl include objname test.* exclude objtype 'TRIGGER' DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10 table test.*;
创建应用进程
dblogin userid odc password odc Successfully logged into database. add replicat dg_rep exttrail ./dirdat/dg REPLICAT added. replicat dg_rep setenv (NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK") setenv (ORACLE_SID="ora") userid odc, password odc --handlecollisions assumetargetdefs allownoopupdates dboptions deferrefconst handletpkupdate --batchsql ddlerror 955 ignore ddlerror 1917 ignore ddlerror 24344 ignore ddlerror 1031 ignore ddl include mapped discardfile ./dirrpt/dg.dsc, append megabytes 20 discardrollover on sunday --discardrollover --ddlerror 942 ignore map test.* target test.*;
2.2.1测试二节点正常关机
[oracle@rac11gn2 ~]$ export ORACLE_SID=orcl2 SQL> shutdown immediate
检查进程状态
GGSCI (ggs as odc@orcl1) 2> info dg* EXTRACT DG_EXT Last Started 2025-01-13 17:30 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:07 ago) Process ID 3754 Log Read Checkpoint Oracle Redo Logs 2025-01-13 17:34:48 Thread 1, Seqno 152, RBA 77840 SCN 0.411203 (411203) Log Read Checkpoint Oracle Redo Logs 2025-01-13 17:31:03 Thread 2, Seqno 114, RBA 1024 SCN 0.411196 (411196) SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 158 2 114
挖掘进程不能在1节点继续挖下去了，也没报错，重启挖掘后报错
2025-01-13T17:44:29.796+0800 ERROR OGG-00868 Oracle GoldenGate Capture for Oracle, dg_ext.prm: Failed to validate configuration on alternative log format and destination. User must either include thread specifier in the log format or specify unique destination for each and every thread.
解决方案
解决：修改成dblogreader --tranlogoptions archivedlogonly --tranlogoptions altarchivelogdest /oracle/app/orcldgarch tranlogoptions dblogreader
2.2.2测试二节点异常关机
[root@rac11gn2 ~]# reboot
检查进程： 不重启的情况下，可以正常同步，但是重启挖掘后，出现上述错误，
OGG-00868 Oracle GoldenGate Capture for Oracle, dg_ext.prm: Failed to validate configuration on alternative log format and destination. User must either include thread specifier in the log format or specify unique destination for each and every thread.
猜测： 不重启挖掘进程能正常同步，应该是挖掘进程odc连接的节点1节点的原因，如果在reboot之前挖掘进程连接的是2节点，那么会出现2.2.1的问题 后续测试： 特地把挖掘进程中odc@orcl（连接的scan-ip）改成odc@orcl1（连接一节点），然后正常关掉二节点（shutdown），不重启挖掘进程之前是正常同步的，重启后就依旧会出现上述错误。所以就是odc连接生产不同节点的原因，会出现上述问题。 解决方案与2.2.1一致
解决：修改成dblogreader --tranlogoptions archivedlogonly --tranlogoptions altarchivelogdest /oracle/app/orcldgarch tranlogoptions dblogreader
三、集成模式测试 创建挖掘进程
GGSCI (ggs) 21> dblogin userid odc@orcl password odc Successfully logged into database. GGSCI (ggs as odc@orcl2) 22> REGISTER EXTRACT jc_ext DATABASE 2025-01-13 18:35:06 INFO OGG-02003 Extract JC_EXT successfully registered with database at SCN 442270. ADD EXTRACT jc_ext, INTEGRATED TRANLOG, BEGIN NOW ADD EXTTRAIL ./dirdat/jc, EXTRACT jc_ext extract jc_ext setenv (NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK") userid odc@orcl password odc LOGALLSUPCOLS UPDATERECORDFORMAT COMPACT --ENCRYPTTRAIL AES192 exttrail ./dirdat/jc tranlogoptions altarchivelogdest /oracle/app/orcldgarch/ FETCHOPTIONS FETCHPKUPDATECOLS --ddl include objname test1.* exclude objtype 'TRIGGER' DDL INCLUDE MAPPED DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10 table test.*;
注意：集成模式不支持在线挖掘日志的模式tranlogoptions minefromactivedg以及DBLOGREADER 创建应用进程
dblogin userid odc password odc add replicat jc_rep exttrail ./dirdat/jc replicat jc_rep setenv (NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK") setenv (ORACLE_SID="ora") userid odc, password odc --handlecollisions assumetargetdefs allownoopupdates dboptions deferrefconst handletpkupdate --batchsql ddlerror 955 ignore ddlerror 1917 ignore ddlerror 24344 ignore ddlerror 1031 ignore ddl include mapped discardfile ./dirrpt/jc.dsc, append megabytes 20 discardrollover on sunday --discardrollover --ddlerror 942 ignore map test.* target test.*;
启动挖掘进程报错
2025-01-13T18:48:18.214+0800 ERROR OGG-02912 Oracle GoldenGate Capture for Oracle, jc_ext.prm: Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later. 2025-01-13T18:48:18.214+0800 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, jc_ext.prm: PROCESS ABENDING.
解决： 在生产库中
SQL> @prvtlmpg.plb Enter Integrated Capture mining user: odc
3.1测试正常与异常关机二节点 正常同步 重启挖掘也正常 四、结论 经典模式使用dblogreader配置，或者采用集成模式挖掘，那么进程在dg端挖掘，生产多个节点中有部分节点异常，对于dg端的挖掘不会有影响，最多进程重启就能恢复。
