Wednesday, September 19, 2012
STANDBY did not release space in FRA, despite large reclaimable space
At customer site, we have following configuration: 3-node ASM RAC and 1-node ASM standby, both with FRA, also in ASM, 184.108.40.206 version.
Few days ago, we get error, that archiving on standby was stopped.
In alert.log on primary site, on log_archive_dest_2 (to standby) was error:
ORA-07286: sksagdi: cannot obtain device information
In alert.log, on standby site:
Did you see that ? 0.00% used , cca 200GB available. FRA is 250GB size.
I did not want to delete archivelogs, on this site there is not tape to backup archivelogs with RMAN to tape, or disk (there is no space). After I check ASM group that is used for FRA, I saw that there is space to expand FRA:
SO, to quick resolve error, I increase FRA:
and immediatelly after that, archivelogs start comming and applying.
But, it is bothered me, that, with archivelog delete policy set "TO APPLIED ON ALL STANDBY;", and RMAN regular backups on primary, archivelogs must be deleted and set to reclaimable space immediately after they applied on standby db.
When I query v$recovery_file_dest (before increasing):
Well, Oracle should be now release reclaimable space and start use this new free space . But, it is not.
Here are, how you can check is archivelogs became reclaimable:
The following query will list all the archivelogs included in v$flash_recovery_area_usage, those with rectype of 11 are the ones that are reclaimable (I reduced to last 3 with rownum, to compare with max values in next 2 queries):
which archivelogs are applied:
which archivelogs are tranfered to standby side:
This tells me that space should be free, because, obviously, archivelogs was marked as reclaimable soon as they applied on standby (max values from last queries is the same) and I opened SR on Metalink, and try investigate further...
In the note Archiver Hung On Ora-15041, FRA not Cleaning Files Automatically. [ID 316281.1], I found the same simptoms as I have, but, that case is if you have log_archive_dest_1 parameter set directly to some mount point or ASM group, files will get generated but will not be considered by FRA algorithm for cleaning up when space pressure mounts up.
You must set log_archive_dest_1='location=use_db_recovery_file_dest', then Oracle will check check for files whose space is eligible for reclaiming.
But, I have this already, and in the Note, you have a little guery to verify this:
All 3 locations is YES on IS_RECOVERY_DEST_FILE column, v$archived_log.IS_RECOVERY_DEST_FILE=YES, so it should be automatically managed.
After a few reviews and uploads with Oracle support, we find that this is BUG !!! :-)
Yes, and this is bug from version 220.127.116.11 :-) (Bug 14227959: STANDBY DID NOT RELEASE SPACE IN FRA)
So, here are the workarround:
First, you can set this parameter, to provide more detailed information about the space pressure:
and solution (for me that worked):
after that, I decreased FRA from 450 GB to 370 (used files was about 360GB), to check how Oracle will free the space.
Than, I watch alert.log 2-3 days, and space in FRA, and now, it is always something like that:
And, I asked for recomended value, how often I must execute this refresh function for aged files in FRA, and answer was to execute dbms_backup_restore.refreshAgedFiles on standby at regular intervals (once in 1 hour).
At the end, here are all files that I reference for this error:
Bug 14227959: STANDBY DID NOT RELEASE SPACE IN FRA
FRA: Flashback logs are not being deleted when space is needed for archive logs [ID 1481739.1]
V$Flash_recovery_area_usage is not being updated [ID 1471471.1]
Space issue in Flash Recovery Area( FRA ) [ID 829755.1]
Database Crashed With ORA-19815 ORA-19809 ORA-16038 [ID 829254.1]
How is the space pressure managed in the Flash Recovery Area - An Example. [ID 315098.1]
Hope this helps someone.