> 本文章不討論歸檔可恢復情況，討論歸檔已經完全丟失，無法找回的情況下，恢復整合模式OGG Extract抽取的情況。

透過本文你可以獲取如下資訊：

1. 整合模式抽取本身為什麼無法跳過歸檔。
2. 整合模式抽取跳過歸檔可以採用的兩種辦法。
3. 避免整合模式抽取歸檔丟失無法跳過可以採用的方法。

# 1. 模擬歸檔丟失

> 由於本文件基於測試環境，所以需要模擬歸檔丟失，如果維護的OGG歸檔已經丟失，報錯ORA-01291: missing logfile，且歸檔無法恢復，則直接跳轉到第二章檢視本文件即可。

## 1.1 停止extract

```shell
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捕獲需要歸檔日誌

```shell
# 切換幾次歸檔日誌
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，檢視報錯

```shell
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，則無法在不切換模式情況下跳過歸檔，整合模式跳過歸檔有如下前提條件：

1. 如果是新register的extract，則register extract到當前redo log之間不能有歸檔斷檔，所以即使要跳過的歸檔也必須存在。
2. 如果是執行了很久的extract，需要跳過歸檔的前提是當前捕獲的SCN之後所有歸檔必須物理存在。

綜上：如果整合模式跳過歸檔，在生產環境如果不切換為經典模式情況下，基本不可能實現，所以我們透過如下兩種方式可以換個方式在丟失歸檔情況下，整合模式抽取實現跳過歸檔。

## 2.1  檢視register extract時歸檔日誌

```shell
# 由於一般情況下，生產環境刪除歸檔一般是歸檔日誌所在空間無法保留太多需要刪除，所以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  查詢當前存在的歸檔日誌

```shell
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兩種方法：

1. register extract會自動建立。
2. DBMS_LOGMNR_D.BUILD

### 2.3.1 重新register方式說明

```shell
# 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說明

```shell
# 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。

```shell
# 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 模擬丟失歸檔

```shell
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 切換為經典模式

```shell
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 註釋掉整合模式引數

```shell
# 下面引數可根據自己需要新增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從現存歸檔開始即可

```shell
# 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. 下游程式以後後續資料處理

1. 抽取啟動之後可以rollover，生成新的trail檔案。
2. 修改投遞extract pump程式從新檔案開始投遞。
3. 修改應用replicat程式從新檔案開始應用。
4. 後續資料比對可以透過OGG Veridata工具進行比對修復。
5. 如果斷檔資料量較大，可以重新初始化。

# 4. 整合模式任意跳過歸檔的方法

可以設定每日定時任務，在刪除歸檔後立即執行下面語句，即可最大化跳過丟失歸檔，在不切換模式情況下重新register extract，跳過丟失歸檔，從現有歸檔重新抽取：

```sql
SQL> execute DBMS_LOGMNR_D.BUILD( options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
```

OGG整合抽取模式丟失歸檔處理

相關文章