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

Comments

  1. Shine2/3/13 11:21

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

    ReplyDelete
    Replies
    1. Patrik9/3/13 18:30

      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

Post a Comment

Popular posts from this blog

Set up CrashPlan on QNAP NAS using QPKG, the easy way

Image
Introduction to CrashPlan Description Here's how Wikipedia describes CrashPlan backup software: CrashPlan is backup software that allows Windows, Mac, Linux and Solaris users to back up their data to an offsite data center, computers belonging to friends and family, as well as to attached drives / shared folders. There is a free version (for non-datacenter backups) and CrashPlan+ which is for backing up to CrashPlan's servers. Both versions allow users to back up data automatically. How it can be used on your NAS If you are the owner of a NAS from QNAP, you can install CrashPlan and use it to backup the contents of your NAS to the CrashPlan data center, to other computers and/or external harddisks. You can also allow incoming backups, so that you and your friends and family can back up valuable documents and files to your NAS. Installation instructions I will not go into any more detail about what CrashPlan is. It's time to get it installed and set up on a
Read more

Samsung Account "processing failed" after firmware upgrade

How to resolve the "Processing failed" error logging in to Samsung Account after upgrade A Samsung Account can be used to remotely control your phone. From your web browser you can locate your phone on a map, trigger an alarm so that you can find it in your home, wipe the contents of the phone if it's been stolen, and more. Simply put; it may prove to be very useful when something bad has happened. After upgrading my Samsung Galaxy S2 phone to Ice Cream Sandwich (ICS), I got the error message "Processing failed" when trying to log in to my Samsung Account. Searching the web I found that I'm definitely not the only one. The solution is very easy, once you know about it.
Read more

How to remove Vlingo/Voice Command from your Android phone without rooting

Here's an easy way to remove/uninstall "Voice Command" from Vlingo, the annoying app that starts every time the home button is double-tapped on Samsung Galaxy S2. And sometimes when it's just pressed once... Since "Voice Command" is a system application, it's not easily uninstalled and used to require the phone to be rooted and thus warranty voided. Unlike the other ways of removing the app, this method works on Android phones that have been upgraded to "Ice-Cream Sandwich" (ICS) and does not require the phone to be "rooted" . Please note that there may not be a way to install it again, should you change your mind later. Maybe a factory reset brings it back, maybe it doesn't. I don't know, and have not tried to get it back. Follow these steps to remove "Voice Command" from your Galaxy S2 with ICS without root
Read more