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


I am not a DBA expert, but I had an opportunity to work with DBA for the below problem raised in Oracle database. When I try to backup oracle server my backup fails. When I check with my DBA I come across this below error. I find the solution from the url

http://arjudba.blogspot.in/2008/07/database-startup-fails-with-error-ora.html

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.

Root Cause: 

There was an attempt to archive 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 archive new online log.

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 back up 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;

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: