ORA-16014: log 3 serial number 187 Unarchived, no available destination issue resolution and Solution

This problem is actually caused by the full archiving log.

Solution:

1. delete old archived logs;

2. Increase the size of archived logs;

The following detailed cases are for reference only;

Microsoft Windows XP [version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.

C: \ Documents ents and Settings \ c0000> sqlplus/As sysdba

SQL * Plus: Release 10.2.0.1.0-production on Friday January 4 11:01:18 2013

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options

SQL> startup
ORA-01081: unable to start Oracle that is already running-Close it first
SQL> alter database open
2;
Alter database open
*
Row 3 has an error:
ORA-16014: log 3 serial number 187 Unarchived, no available destination
ORA-00312: Online log 3 thread 1:
'D: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ redo03.log'
ORA-00312: Online log 3 thread 1: 'd: \ demo \ redo3_2.log'

SQL> show parameter db_recovery_file;

Name type value
-----------------------------------------------------------------------------
Db_recovery_file_dest string D: \ oracle \ product \ 10.2.0/flash
_ Recovery_area
Db_recovery_file_dest_size big integer 8g
SQL> alter system archive log current;
Alter system archive log current
*
Row 3 has an error:
ORA-01109: the database is not open

SQL> alter system switch logfile;
Alter system switch logfile
*
Row 3 has an error:
ORA-01109: the database is not open

SQL> shutdown immediate;
ORA-01109: the database is not open

The database has been detached.
The Oracle routine has been disabled.
SQL> startup
The Oracle routine has been started.

Total system global area 603979776 bytes
Fixed size 1250380 bytes
Variable Size 104860596 bytes
Database buffers 490733568 bytes
Redo buffers 7135232 bytes
The database has been loaded.
ORA-16038: log 3 serial number 187 cannot be archived
ORA-19809: exceeds the limit on the number of recovery files
ORA-00312: Online log 3 thread 1:
'D: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ redo03.log'
ORA-00312: Online log 3 thread 1: 'd: \ demo \ redo3_2.log'

SQL> atler database open;
SP2-0734: Unknown command starting with "atler data..."-ignore the remaining lines.
SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-16014: log 3 serial number 187 Unarchived, no available destination
ORA-00312: Online log 3 thread 1:
'D: \ oracle \ product \ 10.2.0 \ oradata \ oracle \ redo03.log'
ORA-00312: Online log 3 thread 1: 'd: \ demo \ redo3_2.log'

SQL> alter system set db_recovery_file_dest_size = 10g scope = both;

The system has been changed.

SQL> alter database open;

The database has been changed.

SQL> select * from V $ instance;

Instance_number instance_name
-------------------------------
Host_name
----------------------------------------------------------------
Version startup_time status par thread # Archive
---------------------------------------------------------------
Log_switch_wait logins Shu database_status instance_role active_st
------------------------------------------------------------------------
BlO
---
1 Oracle
PCB-CIMER
10.2.0.1.0 month-13 open No 1 started

Instance_number instance_name
-------------------------------
Host_name
----------------------------------------------------------------
Version startup_time status par thread # Archive
---------------------------------------------------------------
Log_switch_wait logins Shu database_status instance_role active_st
------------------------------------------------------------------------
BlO
---
Allowed no active primary_instance normal
No

At this point, the database has been up and recovered to normal.