Hi,
We have a 10.2.0.3.0 on Sun OS database which is usign FRA. The database went into hang state few days back with error
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Apr 18 00:34:04 2011
ORACLE Instance idckf - Archival Error
Mon Apr 18 00:34:04 2011
ORA-16038: log 5 sequence# 26635 cannot be archived
ORA-19809: limit exceeded for recovery files
We have increased FRA size and problem got resolved. Looking at the alert log, I can see two warnings.
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 85 %
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 99.34%
After the second alert , database hangs.
I am thinking at ways to monitor and make custom alert using shell scripts. I used following query to monitor this.
select round((SPACE_USED/SPACE_LIMIT)*100) SPACE_PER from V$RECOVERY_FILE_DEST
if this is above 80 % , alert DBAs. But it looks like even if it reaches 99 % , Oracle clears the OMF and SPACE_RECLAIMABLE comes nearly to SPACE_LIMIT. There was one instance where , my query returns SPACE_RECLAIMABLE as 1 GB and after some while SPACE_RECLAIMABLE becomes nearly equal to SPACE_LIMIT.which means Oracle cleared some files.
I would like to know how I can monitor this as I see the SPACE_USED/SPACE_LIMIT and SPACE_RECLAIMABLE at a time cannot be used to actually determine the FRA usage.
Thanks,
SSN