Oracle备库GV$ARCHIVED_LOG.APPLIED的最新归档日志状态为"IN-MEMORY"(已经应用成功)对应主库的状态为"NO"

 

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)

posted @ 2021-03-05 15:59  PiscesCanon  阅读(2663)  评论(0编辑  收藏  举报