ORA-604 when starting the database

Interesting scenario this morning with a development database. There is no specific monitoring in place for development systems so a user phoned us up stating that the database was inaccessible. The last lines in the alert.log showed a problem with a datafile not readable:

Wed Apr 28 10:18:08 2010
Errors in file /u01/app/oracle/admin/devone/bdump/devone_pmon_3416.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '+DATB/DEVONE/datafile/undotbs1.356.703506859'

This is a single instance database (a clone from our production RAC cluster) running on RHEL 5.4 64bit with ASMLib.OK, now that’s an odd problem.  Scrolling back lots I found this error message:

Tue Apr 27 11:12:37 2010
KCF: write/open error block=0xbb6 online=1
 file=2 +DATB/DEVONE/datafile/undotbs1.356.703506859
 error=15186 txt: ''
Automatic datafile offline due to write error on
file 2: +DATB/DEVONE/datafile/undotbs1.356.703506859

Looking up the error was the next step-it’s an ASMLib problem:

> oerr ora 15186
15186, 00000, “ASMLIB error function = [%s],  error = [%s],  mesg = [%s]”
// *Cause:  An error occured during a call to function listed in the error.
// *Action: The detailed message associated with the error is listed along with
//          the error. Correct the error and try again or contact ASMLIB
//          library vendor for support.

Starting the instance didn’t work-Oracle mounted it and then failed with ORA-604:

ORA-00604: error occurred at recursive SQL level 
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 8859
ORA-1092 signalled during: alter database open...

Looking back into the alert log I found a trace, shown below. The undo tablespace 1 was in use by the instance when it crashed so the chances are that it contains information needed for instance recovery. The solution then was simple:

  1. Perform media recovery of the file: recover datafile ‘+DATB….’
  2. Check the file status-it should be offline: select name,status from v$datafile where name like ‘%undotdbs1%’;
  3. Online the dataFILE (not tablespace): alter database datafile ‘+DATB/…’ online;

Done, and the database opened.