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:
E:\ORADATA\DAT02DATA.DBF
E:\ORADATA\IDX01.DBF
E:\ORADATA\UNDOTBS01.DBF
E:\ORADATA\SYSAUX01.DBF
E:\ORADATA\DAT02DATA01.DBF
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.
BSD
Need advice how to recover the data base with missing data file:
After a bad blocks on hard disk that corrupt oracle data file, I ran fsck to repair
the problem, but the datafile been deleted from disk by the fsck.
This data file store User table space (tables, packages, etc…)
The database is Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
The OS is Linux
The data base was running on Archive Mode
I use to make Cold backup’s without rman, simply I shutdown the db and then copy the folder that contain the data & control files .
The last two cold backups not worth, it copied the damaged datafile with problem, and take half of it’s size.
The last good backup I have is from January and luckily I keep with it all the archive files ( a few hundreds) from backup day until problem date.
Now after fsck delete the corrupt data file The rest of the database with out the missing datafile seems to be fine, I can connect normally to other users.
There are some options to recover the database, I do not know wich and how ?
recover the last backup from January with the archive files
as different database and then:
a) take the missing datafile into the current database ?
b) use the backup+archive database as the current database ?
what is the best way and how to do it ?
thank you for your advice and help
Yitshak
Hi Yitsjak,
What specific error or message do you get from the oracle database on the missing files?
If you want to take the long but safe road, I recommend you to replace the missing errors it asks for, this takes some time, but rebuilds only the damaged parts of the database and leaves the rest of your data unharmed.
greets,
Melanie