ORA-16014: Log 1 serial number 242 Unarchived, no available destination

ORA-16014: Log 1 serial number 242 Unarchived, no available destination

SQL> alter database open;

*
Row 3 has an error:
ORA-16014: Log 1 serial number 242 Unarchived, no available destination
ORA-00312: Online log 1 thread 1:
'C: \ Oracle \ PRODUCT \ 10.2.0 \ ORADATA \ THRS \ redo01.log'

 

SQL> show parameter db_recovery

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string C: \ oracle \ product \ 10.2.0 \ db_1/
Flash_recovery_area
Db_recovery_file_dest_size big integer 2G

 

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.

Computing space occupied by 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:

Alter system set db_recovery_file_dest_size = 3G scope = both;

 

You can also delete unnecessary backups in the flash recovery area in rman to release the flash recovery area space to solve this problem:

Open command line

Set oracle_sid = thrs

Rman target dw/* as sysdba

Delete archivelog all completed before 'sysdate-7'; (delete archived logs 7 days ago)

Or

Delete archivelog until time "sysdate-7 ";