诊断：v$archived_log.applied未能及时更新

v$archived_log.applied is not Being Updated by Data Guard

环境

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Primary RAC主库 --->>> Standby 单实例

现象

Standby应用日志后，Primary库中，线程2的日志应用的信息v$archived_log.applied未更新。

Primary

SQL> select name,open_mode,database_role from v$database;

NAME        OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
<dbname> READ WRITE PRIMARY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
2241

Standby

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
<dbname> READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
2240

原因

v$ARCHIVED_LOG.APPLIED='NO' for all archivelogs on primary despite the fact that primary and standby databases are in sync.

解决方案

The ARCH-RFS Heartbeat Ping between the Primary and Standby Database is responsible for updating the APPLIED-Column of v$archived_log on the Primary Database.

There is a designated Heartbeat ARCn-Process on the Primary Database to perform this Ping. If this Process starts to hang, it does not communicate with the remote RFS-Process any more and so it cannot update the Primary accordingly.

Toggling log_archive_max_processes down to 1 and back to original value (example 4) will get the process restarted.

SQL>archive log list;
SQL>show parameter log_archive_max_processes;
SQL>alter system set log_archive_max_processes=1 scope=memory;
SQL>alter system set log_archive_max_processes=4;
SQL>alter system archive log current;
SQL> select thread#, sequence#, applied from v$archived_log where sequence# = <sequence just archived>;

参考

1. v$archived_log.applied is not Being Updated by Data Guard Causing RMAN-08120 (Doc ID 2071445.1)