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.- 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.
- 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 FRADoc ID 1471471.1: V$Flash_recovery_area_usage is not being updated
Running the following command on standby also fix this issue.
ReplyDelete'CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;'
Thank you for your comment, but that setting does not solve the problem caused by the bugs.
DeleteIn 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.