Sunday, September 20, 2009

Database Startup fails with error ORA-16038,ORA-19809, ORA-00312

Error Description:
---------------------
Whenever you try to startup the database it fails with error ORA-16038,ORA-19809, ORA-00312.
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 117440552 bytes
Database Buffers 41943040 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-16038: log 3 sequence# 572 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'

Or in mount stage whenever you try to open the database it fails with error ORA-16014, ORA-00312.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 572 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'

Cause of The Problem:
----------------------------------------
There was an attempt to archived the online log 3 but it could not archive the online log in the available archived log destination. The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can't archived new online log.

Solution of The Problem:
-------------------------------------------
Solution A:(Enough space on the drive)
1)One more check the alert log. (Not needed though)
An extra check you can do in alert log which is in background_dump_dest/alert_$ORACLE_SID.log
SQL> show parameter background_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/product/10.
2.0/db_1/admin/dbase/bdump
$less /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump/alert_dbase.log
You may see the same entry is in the alert log.

2)Check the archive destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 572
Next log sequence to archive 572
Current log sequence 580

So archived log destination is DB_RECOVERY_FILE_DEST. You can see the exact destination in OS by,
SQL> show parameter db_recover

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2/flash_recovery_area
db_recovery_file_dest_size big integer 10G

3)Increase the value of db_recovery_file_dest_size
As archive destination is full so increase the size.
SQL> alter system set db_recovery_file_dest_size=20G;
System altered.

4)Open the database now.
SQL> alter database open;
Database altered.

Solution B: Have not enough space on the drive
If you have not enough space in your disk and you have recent backup of your database and archive log is not needed then you can issue
$rman target /
RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';

Solution C: Have not any backup
If you have not any recent backup then backup database to another location and delete archivelogs.

To do this,
$rman target /
RMAN>backup format '/oradata2/%U' archivelog all delete input database;

Solution D: Have recent backup and only need archivelog

In this case backup the archive log to another location and delete archive log from flash recovery area.
You can do this by,

$rman target /
RMAN> backup format '/oradata2/%U' archivelog all delete input;

No comments: