Menu Close

Oracle DataGuard 归档日志列表在备库上为0

lcc0556

参见：Doc ID 2041137.1：
https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=1apbrsg4u5_4&_afrLoop=527169096727071

1. 故障现象（SYMPTOMS）

数据库版本：Oracle 18c
故障现象：ARCHIVE LOG LIST 命令在备库上显示 0，但备库与主库完全同步。

2. 变化（CHANGES）

Upgraded to 12.1.0.2

3. 故障原因（CAUSE）

On a standby database:
########### 11gR2:

SQL> SELECT group#,status FROM v$log;

GROUP# STATUS
---------- ----------------
1 CLEARING
2 CLEARING_CURRENT
3 CLEARING
8 CLEARING
5 CLEARING
6 CLEARING
7 CLEARING
4 CLEARING

################In 12cR1: Status is UNUSED

SQL> SELECT group#,status FROM v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
2 UNUSED
3 UNUSED
8 UNUSED
5 UNUSED
6 UNUSED
7 UNUSED
4 UNUSED

4. 解决方案（SOLUTION）

不要在备用端使用 ARCHIVE LOG LIST。
使用下面的查询来确定数据库是否同步

Primary: SQL>
SELECT thread#, max(sequence#) "Last Primary Seq Generated"
  FROM v$archived_log val, v$database vdb
 WHERE val.resetlogs_change# = vdb.resetlogs_change#
 GROUP BY thread#
 ORDER BY 1;

PhyStdby:SQL>
SELECT thread#, max(sequence#) "Last Standby Seq Received"
  FROM v$archived_log val, v$database vdb
 WHERE val.resetlogs_change# = vdb.resetlogs_change#
 GROUP BY thread#
 ORDER BY 1;

PhyStdby:SQL>
SELECT thread#, max(sequence#) "Last Standby Seq Applied"
  FROM v$archived_log val, v$database vdb
 WHERE val.resetlogs_change# = vdb.resetlogs_change#
   AND val.applied in ('YES','IN-MEMORY')
 GROUP BY thread#
 ORDER BY 1;

5. 总结（SUMMARY）

验证最后一个序列是否已接收，最后一个序列是否应用于备用数据库：

SELECT x.thread#,
       x.last_seq_recieved,
       y.last_seq_applied
  FROM (SELECT al.thread#,
               MAX(al.sequence#) AS last_seq_recieved
         FROM v$archived_log al
        GROUP BY al.thread# ) x
  LEFT JOIN
       (SELECT lh.thread#,
               MAX(lh.sequence#) AS last_seq_applied
          FROM v$log_history lh
         GROUP BY lh.thread#) y
    ON x.thread# = y.thread#;
Post Views: 501
Tagged , ,