OGG集成模式下归档无法删除的特殊场景

1. 说明

在OGG 12.3.0.1版本上,OGG基本上是配置成集成模式了,OGG配置过程我就不再多说了,extract添加完成后,为了让RMAN删除归档时保留OGG抽取进程需要的归档,我们需要向数据库注册一下OGG的抽取进程,脚本如下:

GGSCI> register extract ex_tt, DATABASE

这其实就向以前Oracle Stream用的DBA_CAPTURE视图里添加一行记录,OGG抽取进程时时向数据库更新当前抽取的SCN位置,RMAN在删除归档时会参考DBA_CAPTURE视图里的SCN信息,OGG未抽取的归档会保留不删除。
原理就到这里了,今天测一个场景时,碰到了个问题,OGG一般默认情况下会开BR特性,BR以前碰到过默名其妙的问题,需要START EXTNAME, BRRESET重置才会恢复,今天想着将BR禁用下,看看RMAN删除归档会有什么问题,试了这个场景,还真碰到问题了。

2. 禁用BR过程

未关闭BR前,查看OGG抽取进程的检查点:

GGSCI (it12ctest01) 13> info ex_tt, showch

EXTRACT    EX_TT    Last Started 2017-10-18 16:15  Status STOPPED
Checkpoint Lag      00:00:04 (updated 00:03:54 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                    2017-10-19 09:27:47
                    SCN 0.44818438 (44818438)


Current Checkpoint Detail:

Read Checkpoint #1

  Oracle Integrated Redo Log

  Startup Checkpoint (starting position in the data source):
    Timestamp: 2017-09-16 11:54:25.000000
    SCN: Not available

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
    Timestamp: 2017-10-19 09:27:47.000000
    SCN: 0.44818438 (44818438)

  Current Checkpoint (position of last record read in the data source):
    Timestamp: 2017-10-19 09:27:47.000000
    SCN: 0.44818438 (44818438)

  BR Previous Recovery Checkpoint:                                 ------------------------------> BR INFO BEGIN
    Timestamp: 2017-10-18 16:15:49.043216
    SCN: Not available

  BR Begin Recovery Checkpoint:
    Timestamp: 2017-10-19 08:17:26.000000
    SCN: 0.44804031 (44804031)

  BR End Recovery Checkpoint:
    Timestamp: 2017-10-19 08:17:26.000000
    SCN: 0.44804031 (44804031)                                     -------------------------------> BR INFO END

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):
    Sequence #: 8
    RBA: 83494
    Timestamp: 2017-10-19 09:27:51.994829
    Extract Trail: ./dirdat/tt
    Seqno Length: 9
    Flip Seqno Length: No
    Trail Type: EXTTRAIL

Header:
  Version = 2
  Record Source = A
  Type = 18
  # Input Checkpoints = 1
  # Output Checkpoints = 1

File Information:
  Block Size = 2048
  Max Blocks = 100
  Record Length = 2048
  Current Offset = 0

Configuration:
  Data Source = 3
  Transaction Integrity = 1
  Task Type = 0

Status:
  Start Time = 2017-10-18 16:15:52
  Last Update Time = 2017-10-19 09:27:51
  Stop Status = G
  Last Result = 520

数据库的归档信息如下:

SQL> select name, thread#, sequence#, first_change#, first_time from v$archived_log where dest_id = 1 order by first_time desc;

NAME                                                                                THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TIME
-------------------------------------------------------------------------------- ---------- ---------- ------------- -----------
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_16.626.957779199                          2        16      44822108 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_20.635.957779197                          1        20      44822091 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_15.642.957779193                          2        15      44818700 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_19.644.957779189                          1        19      44818689 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_14.652.957778145                          2        14      44818611 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_18.651.957778141                          1        18      44818576 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_17.649.957778095                          1        17      44810921 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_13.650.957778109                          2        13      44810912 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_12.647.957776121                          2        12      44804181 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_16.648.957776123                          1        16      44697435 2017/10/19
+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_11.641.957773897                          2        11      44662121 2017/10/19
......

将OGG的BR给禁用,禁用过程如下:
停掉抽取进程ex_tt
然后在ex_tt的参数文件中添加:
BR, BROFF
再启动抽取进程ex_tt
查看ex_tt的检查点,已经没有BR检查点信息了

GGSCI (it12ctest01) 34> info ex_tt,showch

EXTRACT    EX_TT    Last Started 2017-10-19 09:44  Status STOPPED
Checkpoint Lag      00:00:00 (updated 00:00:58 ago)
Log Read Checkpoint  Oracle Integrated Redo Logs
                    2017-10-19 09:45:20
                    SCN 0.44821859 (44821859)


Current Checkpoint Detail:

Read Checkpoint #1

  Oracle Integrated Redo Log

  Startup Checkpoint (starting position in the data source):
    Timestamp: 2017-09-16 11:54:25.000000
    SCN: Not available

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
    Timestamp: 2017-10-19 09:45:20.000000
    SCN: 0.44821859 (44821859)

  Current Checkpoint (position of last record read in the data source):
    Timestamp: 2017-10-19 09:45:20.000000
    SCN: 0.44821859 (44821859)

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):
    Sequence #: 9
    RBA: 1446
    Timestamp: 2017-10-19 09:45:26.394022
    Extract Trail: ./dirdat/tt
    Seqno Length: 9
    Flip Seqno Length: No
    Trail Type: EXTTRAIL

Header:
  Version = 2
  Record Source = A
  Type = 18
  # Input Checkpoints = 1
  # Output Checkpoints = 1

File Information:
  Block Size = 2048
  Max Blocks = 100
  Record Length = 2048
  Current Offset = 0

Configuration:
  Data Source = 3
  Transaction Integrity = 1
  Task Type = 0

Status:
  Start Time = 2017-10-19 09:44:47
  Last Update Time = 2017-10-19 09:45:26
  Stop Status = G
  Last Result = 400

3. 测试RMAN删除归档

禁用OGG的BR后,RMAN删除归档时,发现归档无法删除了

RMAN> delete noprompt archivelog until SCN 44821859;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2654 instance=tdb1 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_16.648.957776123 thread=1 sequence=16
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_17.649.957778095 thread=1 sequence=17
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_18.651.957778141 thread=1 sequence=18
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_1_seq_19.644.957779189 thread=1 sequence=19
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_11.641.957773897 thread=2 sequence=11
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_12.647.957776121 thread=2 sequence=12
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_13.650.957778109 thread=2 sequence=13
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_14.652.957778145 thread=2 sequence=14
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+GIRMDG/TDB/ARCHIVELOG/2017_10_19/thread_2_seq_15.642.957779193 thread=2 sequence=15

thread_1_seq_16.648.957776123、thread_2_seq_11.641.957773897这两个归档就包含了禁用BR前的SCN:44804031
我切换了多次归档,发现从这最旧的这两个归档开始,RMAN就一直无法删除了,而检查OGG的检查点,Recovery Checkpoint是一直往下走的,
所以这里我感觉是OGG的BR检查点信息,保留在DBA_CAPTURE里了,并且一直不更新,导致了RMAN无法将归档删除。
用以下SQL查询:

SQL> SELECT CAPTURE_NAME,
  2        CAPTURE_TYPE,
  3        STATUS,
  4        to_char(REQUIRED_CHECKPOINT_SCN, '999999999999999') as REQ_SCN,
  5        to_char(OLDEST_SCN, '999999999999999') as OLDEST_SCN
  6    FROM DBA_CAPTURE;

CAPTURE_NAME            CAPTURE_TYPE STATUS  REQ_SCN          OLDEST_SCN
------------------------ ------------ -------- ---------------- ----------------
OGG$CAP_EX_TT            LOCAL        DISABLED        44821858         44821858

这个DBA_CAPTURE中的OGG检查点,一直不走了,RMAN删除归档就考虑这两个信息的,看下这两个字段的描述

为了验证这个猜测,我多次切换了归档日志,上面检查DBA_CAPTURE的脚本多次执行,发现这两个SCN是一直不动的,重启抽取进程也无效,RMAN归档一直无法删除。
然后我找了找,发现了如下OGG参数:

TRANLOGOPTIONS INTEGRATEDPARAMS(_CKPT_RETENTION_CHECK_FREQ 300)

将这个参数添加到OGG的抽取进程中,重启抽取进程后,发现DBA_CAPTURE里的这两个SCN信息终于变化了,RMAN也可以正常删除归档。

4. 其它

当然,最后说一下,将这个OGG的注册信息从DBA_CAPTURE中删除,也是可以解决问题的。

关于紫砂壶

感悟技术人生
此条目发表在GoldenGate分类目录,贴了标签。将固定链接加入收藏夹。