v$archive_gap查不出东西来怎么办

260阅读 0评论2021-07-27 brjl
分类:Oracle

原因可能是:
当备用数据库具有不同的化身( 查V$DATABASE_INCARNATION )时,可能会发生这种情况。
每当使用 RESETLOGS 选项(例如闪回、故障转移)打开数据库时,数据库都会创建一个新的化身

例如:

解决方法1:

  1. --v$archive_gap 的替代SQL

  2. select USERENV('Instance'), high.thread#, low.lsq, high.hsq
  3.   from (select a.thread#, rcvsq, min(a.sequence#) - 1 hsq
  4.           from v$archived_log a,
  5.                (select thread#, resetlogs_change#, max(sequence#) rcvsq
  6.                   from v$log_history
  7.                  where (thread#, resetlogs_change#) in
  8.                        (select thread#, max(resetlogs_change#)
  9.                           from v$log_history
  10.                          group by thread#)
  11.                  group by thread#, resetlogs_change#) b
  12.          where a.thread# = b.thread#
  13.            and a.resetlogs_change# = b.resetlogs_change#
  14.            and a.sequence# > rcvsq
  15.          group by a.thread#, rcvsq) high,
  16.        (select srl_lsq.thread#, nvl(lh_lsq.lsq, srl_lsq.lsq) lsq
  17.           from (select thread#, min(sequence#) + 1 lsq
  18.                   from v$log_history, v$datafile
  19.                  where checkpoint_change# <= next_change#
  20.                    and checkpoint_change# >= first_change#
  21.                    and enabled = 'READ WRITE'
  22.                  group by thread#) lh_lsq,
  23.                (select thread#, max(sequence#) + 1 lsq
  24.                   from v$log_history
  25.                  where (select min(checkpoint_change#)
  26.                           from v$datafile
  27.                          where enabled = 'READ WRITE') >= next_change#
  28.                  group by thread#) srl_lsq
  29.          where srl_lsq.thread# = lh_lsq.thread#(+)) low
  30.  where low.thread# = high.thread#
  31.    and lsq < = hsq
  32.    and hsq > rcvsq;

解决方法2:
如果方法 1 的替代 SQL 仍然没有显示 GAP,请重新创建备用控制文件。这将清除 v$archived_log 中的当前归档日志。

在主库上:
alter database create standby controlfile as '/tmp/ctl_for_standby';
scp到备库上
备库上:
shu abort
改名
mv ctl_for_standby control01.dbf
startup mount
recover managed standby database disconnect;

参考
V$ARCHIVE_GAP doesn't show a existing GAP (Doc ID 974730.1)
Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1)
上一篇:dg归档gap的处理
下一篇:Dataguard 健康检查