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 10.2.0.4.1 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:
- Perform media recovery of the file: recover datafile ‘+DATB….’
- Check the file status-it should be offline: select name,status from v$datafile where name like ‘%undotdbs1%’;
- Online the dataFILE (not tablespace): alter database datafile ‘+DATB/…’ online;
Done, and the database opened.
