ORA-16014: Log 1 Serial Number 100 Unarchived, no available destination

From http://hi.baidu.com/nayinian/blog/item/a012686072754ed08cb10d6b.html

SQL> startup The Oracle routine has been started. Total system global area 201326592 bytes Fixed size 1248092 bytes Variable Size 88081572 bytes Database buffers 109051904 bytes Redo buffers 2945024 bytes The database has been loaded. ORA-16038: log 2 serial number 27 cannot be archived ORA-19809: exceeds the limit on the number of recovery files ORA-00312: Online log 2 thread 1: 'D:/Oracle/product/10.2.0/oradata/orcl/redo02.log' SQL> alter database open; Alter database open * Row 3 has an error: ORA-16014: log 2 serial number 27 Unarchived, no available destination ORA-00312: Online log 2 thread 1: 'D:/Oracle/product/10.2.0/oradata/orcl/redo02.log' SQL> show parameter db_recovery Name type value ----------------------------------------------------------------------------- Db_recovery_file_dest string D:/Oracle/product/10.2.0/flash _ Recovery_area Db_recovery_file_dest_size big integer 2G SQL> alter system set db_recovery_file_dest_size = 3G scope = both; The system has been changed. SQL> alter database open; The database has been changed. Bytes ------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------- Another solution --------------------------------------

1). Check the usage of Flash recovery area:

SQL> select * from V $ flash_recovery_area_usage;

File_type percent_space_used percent_space_reclaimable number_of_files
----------------------------------------------------------------------
Controlfile. 33 0 1
Onlinelog 7.32 0 3
Archivelog 25.06 0 35
Backuppiece 67.69 0 4
Imagecopy 0 0 0
Flashbacklog 0 0 0

You have selected 6 rows.

. Calculate the space occupied by the flash recovery area:

SQL> select sum (percent_space_used) * 3/100 from V $ flash_recovery_area_usage;

Sum (percent_space_used) * 3/100
-----------------------------
3.012

As you can see, here we have used GB, which indicates that the db_recovery_file_dest_size = 2 GB which we just set at the beginning cannot archive online redo logs. Here, we set the db_recovery_file_dest_size parameter, increase the flash recovery area to solve this problem.

(3). You can also delete unnecessary backups in the Flash recovery area in RMAN to release the flash recovery area space to solve this problem:

(1). Delete Obsolete;

(2). Crosscheck backupset;

Delete expired backupset;

Here, I manually delete a part of the archive file, and then use the following command to reduce the actual space of the flash recovery area. The problem is solved.