Oracle 18C,19C standby CHECKPOINT_CHANGE# 不更新問題

問題：

因為電力問題，一臺Oracle 19.3 standby沒有正常shutdown immediate關機。

來電 後standby機器開啟startup DB時發現要做media介質恢復，且archive log seq號要從 512號開始找，512號seq是幾個月前一次正常關機時的archive log，alertlog如下：

2021-05-17T15:23:14.172329+08:00

PR00 (PID:25418): Managed Standby Recovery starting Real Time Apply

2021-05-17T15:23:14.254023+08:00

Parallel Media Recovery started with 4 slaves

2021-05-17T15:23:14.276650+08:00

stopping change tracking

PR00 (PID:25418): Media Recovery Waiting for T-1.S-512

PR00 (PID:25418): Fetching gap from T-1.S-512 to T-1.S-543

2021-05-17T15:23:15.134844+08:00

Completed: alter database recover managed standby database using current logfile disconnect from session

2021-05-17T15:25:06.572517+08:00

PR00 (PID:25418): FAL: Failed to request gap sequence

PR00 (PID:25418):  GAP - thread 1 sequence 512-543

PR00 (PID:25418):  DBID 837094307 branch 1058193251

PR00 (PID:25418): FAL: All defined FAL servers have been attempted

PR00 (PID:25418): -------------------------------------------------------------------------

PR00 (PID:25418): Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization

PR00 (PID:25418): parameter is defined to a value that's sufficiently large

PR00 (PID:25418): enough to maintain adequate log switch information to resolve

PR00 (PID:25418): archived redo log gaps.

PR00 (PID:25418): -------------------------------------------------------------------------

分析：

為什麼異常關機後要，從上一次正常shutdown時的archive log scn 開始找進行recovery 呢？

查詢恢復時相關SCN值均是幾個月前的時間

--資料檔案頭SCN

select CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile_header

--控制檔案的

select CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile

查詢了其它幾臺Oracle19.3版本 standby發現全部存在scn 不更新的問題，但是資料卻是實時和主庫同步的， 模擬shutdown abort，發現確實存在需要到上一次正常shutdown時的archive log scn開始找進行recovery。難道是BUG？查詢MOS確實存在這樣BUG：

問題發生在18.1及以上版本，備庫MRP恢復時，資料檔案的checkpoint scn and time沒有被更新，在19.4, 18.8, 20.1版本中修復

Bug 29056767  STANDBY: Datafiles Checkpoint not Updated at Standby Database when Media Recover is running

 This note gives a brief overview of bug 29056767.

 The content was last updated on: 15-OCT-2019

 Click here for details of each of the sections below.

Affects:

Product (Component) Oracle Server (Rdbms)

Range of versions believed to be affected (Not specified)

Versions confirmed as being affected • 18.1.0

Platforms affected Generic (all / most platforms affected)

Fixed:

The fix for 29056767 is first included in • 20.1.0

• 19.4.0.0.190716 (Jul 2019) Database Release Update (DB RU)

• 18.8.0.0.191015 (Oct 2019) Database Release Update (DB RU)

Interim patches may be available for earlier versions - click here to check.

Symptoms: Related To:

• Code Improvement

• Recovery

• Physical Standby Database / Dataguard

• "_time_based_rcv_ckpt_target"

Description

On a Physical Standby database, media recovery not regularly updating the checkpoint scn and time stored in each datafile header. 

This problem only happens in oracle version 18.1 onwards.

 

Standby media recovery is running, and it's applying successive log seq#'s,

but the checkpoint scn and time stored each datafile header doesn't change.

 

NOTE: the checkpoint in the datafile headers can be monitored by:

 

  select to_char(sysdate,'HH24:MI:SS'), file#, checkpoint_change#,

      to_char(checkpoint_time,'HH24:MI:SS') from v$datafile_header;

 

A level 1 incremental backup on the standby may skip all the datafiles.

 

Another impact of this bug is that if media recovery suddenly aborts for some other reason (eg due to a "shutdown abort" of the instance)

then the next media recovery session may try to start scanning redo from much further back in time than necessary, and if that redo is unavailable, V$MANAGED_STANDBY would show MRP0 status is WAIT_FOR_GAP, alert log file will show 'FAL: Failed to request gap sequence'

 

Workaround

On the standby, 

 set:  alter system set "_time_based_rcv_ckpt_target"=0;

 

NOTE: restart media recovery for it to pick up the new parameter value.

NOTE: this parameter can also be proactively set on the primary, so it is ready in case it becomes the standby in the future

 

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:29056767 (This link will only work for PUBLISHED bugs)

Note:245840.1 Information on the sections in this article

________________________________________________________________________________

REFERENCES

BUG:29056767 - DATAFILES CHECKPOINT NOT UPDATED AT STANDBY DATABASE WHEN MRP IS ENABLED

解決：

方法1. 在備庫設定_time_based_rcv_ckpt_target引數並重啟恢復程式MRP:

alter system set "_time_based_rcv_ckpt_target"=0 scope=both;

修改重啟後，在主庫使用alter system switch logfile模擬觸發checkpoint發現 CHECKPOINT_CHANGE#已可以正常被更新

方法2. DB升級版本

以上，發現相關版本普遍 存在此BUG問題，希望能幫到大家~