June 28, 2012

Applied archivelogs not reclaimable on Oracle 11.2.0.3 physical standby database

Bug stops applied archivelogs from being reclaimable

A bug in Oracle 11.2.0.3 prevents archivelogs that have been applied on a physical standby database from being listed as reclaimable in the view v$flash_recovery_area_usage. It may also cause the FRA to fill up and cause the standby database to halt (while waiting to archive a logfile), possibly impacting primary.


Two different bugs?

There is some confusion whether or not the bug will prevent archivelogs from being deleted from the Flash Recovery Area when there is space pressure. It seems that for some setups the standby will not delete archivelogs, and causes a big problem. For others, the view v$flash_recovery_area_usage simply isn't updated correctly - until there is space pressure in the FRA.

In my case archivelogs are being deleted as expected when the FRA is full, and v$flash_recovery_area_usage will temporarily be updated to show some logs as being reclaimable. After some time has passed, it will look as though no logs are reclaimable. The alert.log will have warnings about the Flash Recovery Area being full, but immediately following the warning there is a message stating that another log has been archived.

The standard places to check when troubleshooting a Data Guard configuration, such as broker logfiles, alert.log, and views such as v$archived_log, v$managed_standby, v$archive_gap all show that the Data Guard configuration is working properly.

Workaround

The workaround below can be used no matter which of the following problems you are experiencing.
  1. The view v$flash_recovery_area_usage is not updated properly, but when there is space pressure it will calculate accurate reclaimable space and delete log files to free up space.
  2. Archived log files are not listed as reclaimable and logs will not be deleted, causing the Flash Recovery Area to fill up on the standby.

The workaround is to execute the following statement:

SQL> exec dbms_backup_restore.refreshagedfiles ;

If you are experiencing the first type of bug, this statement will simply update the view v$flash_recovery_area_usage, to accurately show archivelogs as being reclaimable. This really isn't necessary unless you need the view to show the correct figures because of a script that relies on that information, or some other reason.

For the second version of the bug, where the standby database does not delete the archivelogs that in reality are reclaimable, but are not deleted during space pressure, you MUST schedule this command to run often enough for the FRA to not fill up!

Example

Here is some output showing the problem. I'm sure you understand what information is from the alert.log, RMAN, SQL*Plus...

RMAN> configure archivelog deletion policy to applied on standby;


Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE 


SQL> select sequence#, applied from v$archived_log order by 1;

 SEQUENCE# APPLIED
---------- ---------
      3065 YES
      3066 YES
      3067 YES
      3068 YES
      3069 YES
      3070 YES
      3071 YES
      3072 YES
      3073 IN-MEMORY

9 rows selected.



SQL> select process, status, sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING            3072
ARCH      CLOSING            3073
MRP0      APPLYING_LOG       3074
RFS       IDLE               3074
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0

9 rows selected.



SQL> select FILE_TYPE, PERCENT_SPACE_USED, PERCENT_SPACE_RECLAIMABLE
from v$flash_recovery_area_usage 
where FILE_TYPE IN('ARCHIVED LOG', 'FLASHBACK LOG');

FILE_TYPE      PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE -------------- ------------------ -------------------------

ARCHIVED LOG                 84.8                         0
FLASHBACK LOG                  15                       .98




SQL> exec dbms_backup_restore.refreshagedfiles ;


SQL> select FILE_TYPE, PERCENT_SPACE_USED, PERCENT_SPACE_RECLAIMABLE
from v$flash_recovery_area_usage 
where FILE_TYPE IN('ARCHIVED LOG', 'FLASHBACK LOG');

FILE_TYPE      PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE -------------- ------------------ -------------------------

ARCHIVED LOG                 84.8                      83.5
FLASHBACK LOG                  15                       .98



Read more on My Oracle Support

Bug 14227959: STANDBY DID NOT RELEASE SPACE IN FRA
Doc ID 1471471.1: V$Flash_recovery_area_usage is not being updated

2 comments:

  1. Running the following command on standby also fix this issue.
    'CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;'

    ReplyDelete
    Replies
    1. Thank you for your comment, but that setting does not solve the problem caused by the bugs.

      In some situations it is the configuration setting that you want to use - but the bugs will prevent the setting from working as intended, because, well... they are bugs.

      Delete