Oracle备库GV$ARCHIVED_LOG.APPLIED的最新归档日志状态为"IN-MEMORY"对应主库的状态为"NO"
问题
公司数据库每天的0,6,12,18这几个时间点均进行归档备份并删除操作，在OEM查看RMAN的备份状态总是"COMPLETED WITH WARNINGS"。
具体查看备份日志，发现的报错如下：
在主库归档删除策略为：CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
则报错为：RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
在主库归档删除策略为：CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
则报错为：RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
[oracle@XXXXXX1 2021-03-03]$ ll -ltrh *.log -rw-r--r-- 1 oracle oinstall 7.2K Mar 2 23:56 rman_archbak_20210303000001.log -rw-r--r-- 1 oracle oinstall 29K Mar 3 01:44 rman_db1_20210303.log -rw-r--r-- 1 oracle oinstall 6.1K Mar 3 05:56 rman_archbak_20210303060001.log -rw-r--r-- 1 oracle oinstall 7.2K Mar 3 11:57 rman_archbak_20210303120001.log -rw-r--r-- 1 oracle oinstall 7.2K Mar 3 17:57 rman_archbak_20210303180001.log [oracle@XXXXXX1 2021-03-03]$ grep RMAN- *.log rman_archbak_20210303000001.log:RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process rman_archbak_20210303060001.log:RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process rman_archbak_20210303120001.log:RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process rman_archbak_20210303180001.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_db1_20210303.log:RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process[oracle@XXXXXX1 2021-03-03]$ [oracle@XXXXXX1 2021-03-03]$ cd ../2021-03-04 [oracle@XXXXXX1 2021-03-04]$ ll -ltrh *.log -rw-r--r-- 1 oracle oinstall 7.5K Mar 3 23:56 rman_archbak_20210304000001.log -rw-r--r-- 1 oracle oinstall 32K Mar 4 01:45 rman_db1_20210304.log -rw-r--r-- 1 oracle oinstall 7.1K Mar 4 05:56 rman_archbak_20210304060001.log -rw-r--r-- 1 oracle oinstall 7.9K Mar 4 11:56 rman_archbak_20210304120001.log -rw-r--r-- 1 oracle oinstall 7.2K Mar 4 17:57 rman_archbak_20210304180001.log [oracle@XXXXXX1 2021-03-04]$ grep RMAN- *.log rman_archbak_20210304000001.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_archbak_20210304060001.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_archbak_20210304060001.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_archbak_20210304060001.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_archbak_20210304060001.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_archbak_20210304060001.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_archbak_20210304120001.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_archbak_20210304180001.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_db1_20210304.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_db1_20210304.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_db1_20210304.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_db1_20210304.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby rman_db1_20210304.log:RMAN-08120: WARNING: archived log not deleted, not yet applied by standby
探究
数据库架构为双节点RAC+单节点ADG。
每次查看主库GV$ARCHIVED_LOG.APPLIED的值为"NO"的记录，总是存在并且只有一条。
而且该条记录对应的归档总是其中一个节点（有时候是1节点有时候是2节点都有可能）的最新产生的归档日志序列号。
如下，查看APPLIED='NO'的记录此刻为日志序列号40031的归档日志，并且该归档日志为当前1节点最新的归档日志。
14:53:32 SYS@XXXXXX1(1862)> select recid, dest_id, thread#, sequence#, first_time, completion_time, creator, registrar, archived, applied, deleted, status from v$archived_log where standby_dest='YES' and status='A' and applied='NO'; RECID DEST_ID THREAD# SEQUENCE# FIRST_TIME COMPLETION_TIME CREATOR REGISTRAR ARCHIVED APPLIED DELETED STA ---------- ---------- ---------- ---------- ------------------- ------------------- --------------------- --------------------- --------- --------------------------- --------- --- 83754 2 1 40031 2021-03-05 14:00:16 2021-03-05 14:30:15 LGWR LGWR YES NO NO A Elapsed: 00:00:00.08 14:53:36 SYS@XXXXXX1(1862)> 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; THREAD# Last Primary Seq Generated ---------- -------------------------- 1 40031 2 27724 Elapsed: 00:00:00.08 14:54:27 SYS@XXXXXX1(1862)> select * from v$log order by THREAD#,SEQUENCE#; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- ------------ ------------------- 3 1 40029 1073741824 512 2 YES INACTIVE 2.2413E+11 2021-03-05 13:00:15 2.2413E+11 2021-03-05 13:30:17 1 1 40030 1073741824 512 2 YES INACTIVE 2.2413E+11 2021-03-05 13:30:17 2.2413E+11 2021-03-05 14:00:16 4 1 40031 1073741824 512 2 YES INACTIVE 2.2413E+11 2021-03-05 14:00:16 2.2414E+11 2021-03-05 14:30:15 2 1 40032 1073741824 512 2 NO CURRENT 2.2414E+11 2021-03-05 14:30:15 2.8147E+14 13 2 27722 1073741824 512 2 YES INACTIVE 2.2413E+11 2021-03-05 13:00:15 2.2413E+11 2021-03-05 13:30:14 11 2 27723 1073741824 512 2 YES INACTIVE 2.2413E+11 2021-03-05 13:30:14 2.2413E+11 2021-03-05 14:00:13 14 2 27724 1073741824 512 2 YES INACTIVE 2.2413E+11 2021-03-05 14:00:13 2.2414E+11 2021-03-05 14:30:15 12 2 27725 1073741824 512 2 NO CURRENT 2.2414E+11 2021-03-05 14:30:15 2.8147E+14 8 rows selected. Elapsed: 00:00:00.01
在主库创建测试表，备库立马可以看到新建的表，同步时间基本保持在毫秒级别内不存在GAP和大的延迟。
从备库的警告日志看，40031的日志在14:26:53秒就在备库归档成功，但是从上边查询记录看，14:53:32主库的查看结果为APPLIED='NO'。
查询备库的应用情况，40031的状态为"IN-MEMORY"，当前的MRP进程应用日志为主库V$LOG.STATUS='CURRENT'的40032（表示实时同步）：
14:52:53 SYS@xxxxxxstb(1308)> select sequence# from gv$archived_log where applied ='IN-MEMORY'; SEQUENCE# ---------- 40031 Elapsed: 00:00:00.14 14:52:54 SYS@xxxxxxstb(1308)> select process, pid, status, thread#, sequence#, block# from v$managed_standby where process='MRP0'; PROCESS PID STATUS THREAD# SEQUENCE# BLOCK# --------------------------- ---------- ------------------------------------ ---------- ---------- ---------- MRP0 15085 APPLYING_LOG 1 40032 643116 Elapsed: 00:00:00.00
到这里就有点奇怪了，40031已经在备库应用了，只不过备库的400031的APPLIED='IN-MEMORY'。
但确实是应用了，主库40031的APPLIED状态没实时更新为YES。
原因
根据文档The Latest Archivelog On Standby Database Keep The Status Of In-Memory (文档 ID 1384371.1)中指出（右键翻译中文），
备库最新日志状态为"IN-MEMORY"，表示已完全应用存档的日志，但恢复检查点尚未移出日志。
从Broker的角度来看，“ IN-MEMORY”等效于“ YES”，因为Broker关心是否已通过恢复应用日志，而不关心日志是否已被检查点。
从RMAN的角度来看，“ IN-MEMORY”等效于“ NO”，因为RMAN只关心是否可以删除日志。幸运的是，此txn不需要更改RMAN，因为今天查看此列的所有RMAN代码都会检查该值是“ YES”或者不是“ YES”。
通常，只有在运行备用恢复时（或在备用实例崩溃后），才能在备用数据库上看到此值。
当备用恢复会话结束时，只要实例不死，任何“内存中”状态都将更改为“是”或“否”。如果恢复正常，恢复将检查所有内容，从而使所有处于“ IN-MEMORY”状态的归档日志都变为“ YES”。如果异常结束并且恢复无法将检查点移到前面，则所有未完全检查点的具有“ IN-MEMORY”状态的日志都将更改为“ NO”。如果备用实例崩溃，则不会执行此操作。
状态“IN-MEMORY”来自Oracle 11g恢复概念的变化。 在以前的版本中，一旦完全读取了日志文件（已到达日志文件的最后一个SCN），我们将执行完整的恢复检查点，即。 此日志序列中的所有更改都将写入数据库文件中，并且标题和控制文件也将更新。
从Oracle 11g开始，我们执行某种“延迟检查点”，实际上我们在以后的某个时间执行该检查点，以提高恢复和整体性能。 因此，当状态为“内存中”时，这意味着在实例中更改了相应的块，但是这些更改尚未写入数据库文件中。 因此，我们必须使那些ArchiveLogs保持可用，因为在发生崩溃的情况下，我们仍然需要那些ArchiveLogs从数据库文件中的最后一个SCN恢复恢复。
因此，这是您案例中的预期行为。
也就是说，这是正常的。
那么要解决RMAN的报错，可以直接忽略该报错。
要么就是先备份归档日志，再删除半个小时前的已经被备份的归档日志了（数据库强制每半个小时归档一次）。
参考
The Latest Archivelog On Standby Database Keep The Status Of In-Memory (文档 ID 1384371.1)
v$archived_log: applied column is not marked if recovery applied the redo from SRL (文档 ID 1481927.1)