Wednesday, March 7, 2007

what happens when flash_recovery_area is full

I encountered this error while bringing up the database.

ORA-16014: log 2 sequence# 240 not archived, no available destinations

This was because the log_archive_dest_10 or flash_recovery_area is full. I checked the v$recovery_file_dest and the space allocated and used were equal and are of 7G in size.
Space reclaimable is zero.

Using RMAN i did the following:

RMAN> crosscheck archivelog all;
allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=131 devtype=DISK

validation failed for archived logarchive log filename=D:\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2006_08_25\O1_MF_1_69_2GXRYZ3S_.ARC recid=32 stamp=599419203validation failed for archived log
............similaryly it gave validation error for all the archive log files as I have deleted them from the operating system level.

I then ran the following...

RMAN> delete expired archivelog all;
released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=131 devtype=DISK
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archive logarchive log filename=D:\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2006_08_25\O1_MF_1_69_2GXRYZ3S_.ARC recid=32 stamp=599419203

............ like this it deleted 134 objects I deleted. The space used in the v$recovery_file_dest has come down gradually.

SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
D:\flash_recovery_area 7147483648 7140811776 0 162

SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
D:\flash_recovery_area 7147483648 473303552 0 22