Recover your corrupt datafiles in oracle – ora-00376
October 21, 2011
Everyone in softwareland has been there, you're at home and people start calling you about the application shooting error messages with no clear reason. you go through all your logfiles, Windows application server, eventviewer and end up in your database logs, and then find out your tables give an error when trying to retrieve the data. This just happened to me, so i wrote this small tutorial for people encountering this unexpected ora-00376 and ora-01110 'file cannot be read at this time', we can fairly easy recover the corrupt datafiles without having to take the database offline.
use the statement: select * from v$datafile; to read the status on your database files. In our case it says some files are in recovery mode and are therefore unreadable. A possible cause for this could be backup software locking the database files or a linked server job from mssql keeps them occupied.
In my case, I found 5 files in recover mode:
Now that we found the cause for some tables and views in the database not being available, it's time to solve this. We fire up the sqlPlus toolie and connect to the database ( you could also use cmd, if sqlplus is not installed).
run: recover datafile 'E:\ORADATA\DAT02DATA.DBF';
Sqlplus will then ask you for a filename, auto or cancel. We choose to specify the filename, since auto option won't work, since it can't find the archive files in the place it suggested, we give in the location and filename of the archive file that it needs in order to do a recovery. As you can see in the example below, it's looking for the archive files in a different location then where the archive files actually are located. enter the correct location. E:\orabck\oraarch\arc20637_0639339860.001
After you have entered all the correct file locations suggested for the archive files, the message: log applied media recovery complete will be displayed.
we run the statement select * from v$datafile; again and you'll see the status of the file is now offline.
All we need to do now is bring the file back online, to achieve this we run the statement:
alter database datafile 'E:\ORADATA\DAT02DATA.DBF' online;
Repeat the steps above for all the files in recovery status and the database is succesfully recovered again and running.
The advantage of this recovery is that we don't have to bringt the database offline and depending on what files are in recovery mode, people can still access the database and even parts of the application.