本文章不讨论归档可恢复情况，讨论归档已经完全丢失，无法找回的情况下，恢复集成模式OGG Extract抽取的情况。
通过本文你可以获取如下信息：
- 集成模式抽取本身为什么无法跳过归档。
- 集成模式抽取跳过归档可以采用的两种办法。
- 避免集成模式抽取归档丢失无法跳过可以采用的方法。
1. 模拟归档丢失
由于本文档基于测试环境，所以需要模拟归档丢失，如果维护的OGG归档已经丢失，报错ORA-01291: missing logfile，且归档无法恢复，则直接跳转到第二章查看本文档即可。
1.1 停止extract
GGSCI (single as ogg@honor1) 55> stop ex_re Sending STOP request to EXTRACT EX_RE ... Request processed. GGSCI (single as ogg@honor1) 56> info ex_re EXTRACT EX_RE Last Started 2022-05-06 23:08 Status STOPPED Checkpoint Lag unknown (updated 00:00:54 ago) Log Read Checkpoint Oracle Integrated Redo Logs 2022-05-07 10:31:31 SCN 5.15113672 (21489950152)
1.2 切几次归档并模拟删除extract捕获需要归档日志
# 切换几次归档日志
SYS@honor1 > alter system archive log current;
System altered.
SYS@honor1 > /
System altered.
SYS@honor1 > /
System altered.
# 根据上述SCN查询当前捕获所在的Oracle Redo Log sequence#
SYS@honor1 > SELECT THREAD#, SEQUENCE#, NAME, FIRST_CHANGE#, NEXT_CHANGE#, STATUS
2 FROM V$ARCHIVED_LOG
3 WHERE FIRST_CHANGE# <= 21489950152
4 AND NEXT_CHANGE# >= 21489950152
5 AND STATUS = 'A';
THREAD# SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE# STATUS
------- --------- ---------------------------------------- ---------------- ---------------- ----------
1 377 +FRADG/honor/archivelog/2022_05_07/threa 21489950070 21489950244 A
d_1_seq_377.390.1104057109
# 删除归档，由于Integrated Capture需要该归档，所以不加force无法删除，删除连续三个归档，确保extract丢失需要的归档文件
RMAN> delete force noprompt archivelog sequence 377 thread 1;
RMAN> delete force noprompt archivelog sequence 378 thread 1;
RMAN> delete force noprompt archivelog sequence 379 thread 1;
1.3 启动extract，查看报错
GGSCI (single as ogg@honor1) 50> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX_RE unknown 00:14:18
GGSCI (single as ogg@honor1) 51> start ex_re
Sending START request to MANAGER ...
EXTRACT EX_RE starting
# tail -200f ggserr.log
2022-05-06T23:16:24.066+0800 ERROR OGG-00868 Oracle GoldenGate Capture for Oracle, ex_re.prm: Error code 1291, error message: ORA-01291: missing logfile
(Missing Log File WAITING FOR DICTIONARY REDO. Read Position SCN: 5.15113672 (21489950152)).
2022-05-06T23:16:24.066+0800 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ex_re.prm: PROCESS ABENDING.
# 通过查询删除状态归档日志，确定被删除归档的sequence#
SYS@honor1 > SELECT THREAD#, SEQUENCE#, NAME, FIRST_CHANGE#, NEXT_CHANGE#, STATUS
2 FROM V$ARCHIVED_LOG
3 WHERE FIRST_CHANGE# <= 21489950152
4 AND NEXT_CHANGE# >= 21489950152
5 AND STATUS = 'D';
THREAD# SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE# STATUS
------- --------- ---------------------------------------- ---------------- ---------------- ----------
1 377 21489950070 21489950244 D
2. 集成模式跳过归档
集成模式不同于经典模式，集成模式对于跳过归档有限制，所以当集成模式丢失归档时，如果没有事先定期重新build logmnr dictionary，则无法在不切换模式情况下跳过归档，集成模式跳过归档有如下前提条件：
- 如果是新register的extract，则register extract到当前redo log之间不能有归档断档，所以即使要跳过的归档也必须存在。
- 如果是运行了很久的extract，需要跳过归档的前提是当前捕获的SCN之后所有归档必须物理存在。
综上：如果集成模式跳过归档，在生产环境如果不切换为经典模式情况下，基本不可能实现，所以我们通过如下两种方式可以换个方式在丢失归档情况下，集成模式抽取实现跳过归档。
2.1 查看register extract时归档日志
# 由于一般情况下，生产环境删除归档一般是归档日志所在空间无法保留太多需要删除，所以register extract时的归档日志肯定不复存在。
# 集成模式重新register extract以及add extract..scn或alter extract...scn需要保证register时dictionary存在的归档到指定的scn之间归档连续，不能断档，才可以在register时到当前redo之间任意选取scn开始抽取，只要有断档，将无法继续
# 下面测试环境由于我只删除了extract捕获所在位置的三个归档文件，所以register时所在归档还存在，这点需要注意。
SYS@honor1 > SELECT thread#,sequence#,first_change#,status
FROM v$archived_log
3 WHERE dictionary_begin = 'YES' AND
4 standby_dest = 'NO' AND
5 name IS NOT NULL AND
6 status = 'A';
THREAD# SEQUENCE# FIRST_CHANGE# STATUS
------- --------- ---------------- ----------
1 359 21489879344 A
# 上面status为A代表Available，一般环境中该状态均为D，即归档已经被删掉，这是可预期的。
2.2 查询当前存在的归档日志
SYS@honor1 > SELECT thread#,sequence#,first_change#,status
2 FROM v$archived_log
3 WHERE dest_id = 1 AND
4 name IS NOT NULL AND
5 status = 'A' AND
6 sequence# > 377;
THREAD# SEQUENCE# FIRST_CHANGE# STATUS
------- --------- ---------------- ----------
1 380 21489950257 A
1 381 21489950264 A
1 382 21489950268 A
1 383 21489950274 A
1 384 21489950278 A
1 385 21489950285 A
1 386 21489950290 A
1 387 21489950296 A
1 388 21489950301 A
1 389 21489950306 A
1 390 21489950313 A
1 391 21489950318 A
1 392 21489966190 A
2.3 重新建立logmnr dictionary
集成模式如果想从任意时间抽取或跳过任意归档，需要logmnr dictionary建立时所在归档的first_scn到当前scn之间不能有归档断档，才能跳过这两个scn之间任意的归档。
建立logmnr dictionary两种方法：
- register extract会自动建立。
- DBMS_LOGMNR_D.BUILD
2.3.1 重新register方式说明
# 1. register extract，默认不指定scn时，会自动从发出register extract时所在scn建立logmnr dictionary并为extract capture注册，如果指定scn的话，需要与下面SQL语句查出的logmnr dictionary所在归档的first_scn一致，不能随便指定scn。
SELECT first_change#
FROM v$archived_log
WHERE dictionary_begin = 'YES' AND
standby_dest = 'NO' AND
name IS NOT NULL AND
status = 'A';
SYS@honor1 > SELECT thread#,sequence#,first_change#,status
2 FROM v$archived_log
3 WHERE dictionary_begin = 'YES' AND
4 standby_dest = 'NO' AND
5 name IS NOT NULL AND
6 status = 'A';
THREAD# SEQUENCE# FIRST_CHANGE# STATUS
------- --------- ---------------- ----------
1 359 21489879344 A
# 重新register需要建立在logmnr dictionary所在的redo log到当前redo log不能有断档，而我们一般不会设定定时任务执行unregister/register建立logmnr dictionary，所以我们的logmnr dictionary一般只有最开始register extract的建立的，而最开始建立的logmnr dictionary所在的归档日志早已随着清理空间或备份而被删除而发生归档断档，一旦发生断档，则集成模式将无法跳过logmnr dictionary建立时的first_scn到当前scn之间任何归档。
# 我们测试环境中看到下面其实是有断档的，所以我们重新register再alter extract或add extract指定scn跳过特定归档方式无法实现，所以集成模式中，重新register无法进行。
SYS@honor1 > SELECT thread#,sequence#,first_change#,status
FROM v$archived_log
WHERE dest_id = 1 AND
name IS NOT NULL AND
5 status = 'A';
THREAD# SEQUENCE# FIRST_CHANGE# STATUS
------- --------- ---------------- ----------
1 352 21489872002 A
..
1 369 21489935694 A # 后面丢失了370
1 371 21489949150 A
..
1 376 21489950049 A # 后面丢失了377,378,379
1 380 21489950257 A
..
2.3.2 手工创建logmnr dictionary说明
# 1. 手工提前创建dictionary，使用下面命令
# 手工建立logmnr dictionary之后，即可从logmnr dictionary所在redo的first_scn开始重新register extract，重新开始抽取。
SQL> execute DBMS_LOGMNR_D.BUILD( options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
SQL> SELECT first_change#
FROM v$archived_log
WHERE dictionary_begin = 'YES' AND
standby_dest = 'NO' AND
name IS NOT NULL AND
status = 'A';
THREAD# SEQUENCE# FIRST_CHANGE# STATUS
------- --------- ---------------- ----------
1 359 21489879344 A
1 398 21489984293 A
2.4 重新register，添加extract恢复
通过上述几个小节可以看出，集成模式如果想跳过归档，在不切换为经典模式情况下，几乎不可能实现，所以只能通过下面方式，重新register，重新添加extract。
# register extract会收集logmnr一些基表统计信息，会造成数据库压力，需要谨慎
alert日志如下：
LOGMINER: Gathering statistics on logminer dictionary. (107)
# 集成模式register时如果指定scn，则必须为1.6.2小节中查询出来存在logmnr dictionary的归档的first_scn，不能任意指定，如果不指定scn，则会自动在当前redo中建立logmnr dictionary
GGSCI (single as ogg@honor1) 104> register extract ex_re database scn 21489984293
2022-05-07 00:54:49 INFO OGG-02003 Extract EX_RE successfully registered with database at SCN 21489984293.
# 集成模式添加时如果指定scn，则必须为上面register的scn之后的到当前归档的任意scn，从register到extract指定的scn中间不能存在归档日志断档，否则无法正常抽取，这点一定要注意，如果后续创建replicat比较缓慢，可以在register之后，直接开始抽取，抽取尽量早点抽取，否则一旦register之后，未开始抽取，而中间归档日志删除无法恢复，则需要再次重新register或build logmnr dictionary，重新开始配置抽取，否则无法正常抽取。
GGSCI (single as ogg@honor1) 105> add extract ex_re integrated tranlog scn 21489984293
EXTRACT (Integrated) added.
GGSCI (single as ogg@honor1) 106> add exttrail ./dirdat/ex,extract ex_re,megabytes 100
EXTTRAIL added.
GGSCI (single as ogg@honor1) 107> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX_RE 00:00:00 00:00:27
GGSCI (single as ogg@honor1) 108> start ex_re
Sending START request to MANAGER ...
EXTRACT EX_RE starting
GGSCI (single as ogg@honor1) 109> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EX_RE 00:00:00 00:01:03
GGSCI (single as ogg@honor1) 111> info ex_re
EXTRACT EX_RE Initialized 2022-05-07 00:55 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:01:07 ago)
Process ID 50669
Log Read Checkpoint Oracle Integrated Redo Logs
First Record
SCN 5.15147813 (21489984293)
GGSCI (single as ogg@honor1) 122> send ex_re,status
Sending STATUS request to EXTRACT EX_RE ...
EXTRACT EX_RE (PID 50669)
Current status: Recovery complete: Processing data
Current read position:
Redo thread #: 1
Sequence #: 402
RBA: 20295304
Timestamp: 2022-05-07 12:09:39.000000
SCN: 5.15176900 (21490013380)
Current write position:
Sequence #: 8
RBA: 1444
Timestamp: 2022-05-07 00:57:47.920815
Extract Trail: ./dirdat/ex
# 可以看到已经恢复正常
# extract参数设置
extract ex_re
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERIDALIAS oggsrc
exttrail ./dirdat/ex
GETUPDATEBEFORES
GETTRUNCATES
DDL &
INCLUDE MAPPED OBJTYPE 'TABLE' &
INCLUDE MAPPED OBJTYPE 'INDEX' &
INCLUDE MAPPED OBJTYPE 'SEQUENCE' &
INCLUDE MAPPED OBJTYPE 'VIEW' &
INCLUDE MAPPED OBJTYPE 'PROCEDURE' &
INCLUDE MAPPED OBJTYPE 'FUNCTION' &
INCLUDE MAPPED OBJTYPE 'PACKAGE' &
INCLUDE MAPPED OBJTYPE 'MATERIALIZED VIEW' &
EXCLUDE OPTYPE COMMENT
DDLOPTIONS ADDTRANDATA REPORT
BR BRINTERVAL 2H
CACHEMGR CACHESIZE 500MB
WARNLONGTRANS 2H,CHECKINTERVAL 5M
NUMFILES 4000
EOFDELAYCSECS 10
LOGALLSUPCOLS
TRANLOGOPTIONS INTEGRATEDPARAMS (parallelism 1)
discardfile ./dirrpt/ex_re.dsc,append, megabytes 200
TABLE hr.*;
2. 切换经典模式跳过归档
可以利用经典模式可以指定scn，将集成模式抽取切换为经典模式后，从指定位置开始重新抽取即可。
2.1 模拟丢失归档
GGSCI (single as ogg@honor1) 130> stop ex_re
Sending STOP request to EXTRACT EX_RE ...
Request processed.
SYS@honor1 > alter system archive log current;
RMAN> delete force archivelog all;
GGSCI (single as ogg@honor1) 134> start ex_re
Sending START request to MANAGER ...
EXTRACT EX_RE starting
# ggserr.log报错
2022-05-07T03:56:23.254+0800 ERROR OGG-00868 Oracle GoldenGate Capture for Oracle, ex_re.prm: Error code 1291, error message: ORA-01291: missing logfile
(Missing Log File WAITING FOR DICTIONARY REDO. Read Position SCN: 5.15223324 (21490059804)).
2022-05-07T03:56:23.254+0800 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ex_re.prm: PROCESS ABENDING.
2.2 切换为经典模式
GGSCI (single) 135> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EX_RE unknown 00:18:53
GGSCI (single) 136> info extract ex_re downgrade
Extract EX_RE is ready to be downgraded from integrated capture. Archive logs corresponding to SCN 21490028815 and higher must be accessible by the downgraded extract.
GGSCI (single) 137> dblogin useridalias oggsrc
Successfully logged into database.
# 如果为RAC环境，则需要指定后面threads n。
GGSCI (single as ogg@honor1) 77> ALTER EXTRACT ex_re, DOWNGRADE INTEGRATED TRANLOG [THREADS 2]
Extract EX_RE successfully downgraded from integrated capture.
GGSCI (single as ogg@honor1) 79> unregister extract ex_re database
2022-05-07 04:11:41 INFO OGG-01750 Successfully unregistered EXTRACT EX_RE from database.
2.3 注释掉集成模式参数
# 下面参数可根据自己需要添加classic模式参数
# TRANLOGOPTIONS DBLOGREADER由于使用异地抽取方式，所以需要添加该参数才能正确读取到归档日志
GGSCI (single) 126> view param ex_re
extract ex_re
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERIDALIAS oggsrc
exttrail ./dirdat/ex
GETUPDATEBEFORES
GETTRUNCATES
TRANLOGOPTIONS DBLOGREADER
--DDL &
--INCLUDE MAPPED OBJTYPE 'TABLE' &
--INCLUDE MAPPED OBJTYPE 'INDEX' &
--INCLUDE MAPPED OBJTYPE 'SEQUENCE' &
--INCLUDE MAPPED OBJTYPE 'VIEW' &
--INCLUDE MAPPED OBJTYPE 'PROCEDURE' &
--INCLUDE MAPPED OBJTYPE 'FUNCTION' &
--INCLUDE MAPPED OBJTYPE 'PACKAGE' &
--INCLUDE MAPPED OBJTYPE 'MATERIALIZED VIEW' &
--EXCLUDE OPTYPE COMMENT
--DDLOPTIONS ADDTRANDATA REPORT
BR BRINTERVAL 2H
CACHEMGR CACHESIZE 500MB
WARNLONGTRANS 2H,CHECKINTERVAL 5M
NUMFILES 4000
EOFDELAYCSECS 10
LOGALLSUPCOLS
--TRANLOGOPTIONS INTEGRATEDPARAMS (parallelism 1)
discardfile ./dirrpt/ex_re.dsc,append, megabytes 200
TABLE hr.*;
2.3 从现存归档开始抽取
从现有归档开始抽取或重新添加extract从现存归档开始即可
# 1. 从指定位置开始
SYS@honor1 > SELECT thread#,sequence#,first_change#,status
2 FROM v$archived_log
3 WHERE standby_dest = 'NO' AND
4 name IS NOT NULL AND
5 status = 'A';
THREAD# SEQUENCE# FIRST_CHANGE# STATUS
------- --------- ---------------- ----------
1 450 21490059909 A
1 451 21490065208 A
1 452 21490065214 A
1 453 21490065219 A
GGSCI (single as ogg@honor1) 84> alter extract ex_re,scn 21490059909
EXTRACT altered.
GGSCI (single as ogg@honor1) 86> start ex_re
Sending START request to MANAGER ...
EXTRACT EX_RE starting
# 2. 第二种方法：重新添加classic extract
GGSCI (single) 92> dblogin useridalias oggsrc
GGSCI (single as ogg@honor1) 92> delete extract ex_re
Deleted EXTRACT EX_RE.
GGSCI (single as ogg@honor1) 96> add extract ex_re tranlog scn 21490059909
EXTRACT added.
GGSCI (single as ogg@honor1) 98> add exttrail ./dirdat/ex,extract ex_re,megabytes 100
EXTTRAIL added.
GGSCI (single as ogg@honor1) 99> start ex_re
GGSCI (single as ogg@honor1) 100 > info ex_re
EXTRACT EX_RE Last Started 2022-05-07 04:22 Status RUNNING
Checkpoint Lag unknown (updated 00:00:02 ago)
Process ID 59633
Log Read Checkpoint Oracle Redo Logs
2022-05-07 15:18:27 Seqno 454, RBA 625168
SCN 5.15229664 (21490066144)
# 后续如有需要，切换回集成模式即可。
3. 下游进程以后后续数据处理
- 抽取启动之后可以rollover，生成新的trail文件。
- 修改投递extract pump进程从新文件开始投递。
- 修改应用replicat进程从新文件开始应用。
- 后续数据比对可以通过OGG Veridata工具进行比对修复。
- 如果断档数据量较大，可以重新初始化。
4. 集成模式任意跳过归档的方法
可以设置每日定时任务，在删除归档后立即执行下面语句，即可最大化跳过丢失归档，在不切换模式情况下重新register extract，跳过丢失归档，从现有归档重新抽取：
SQL> execute DBMS_LOGMNR_D.BUILD( options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
最后修改时间：2022-06-13 17:28:42
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
