Recovery of Oracle database.

We have a scenario where one of our departments do a cold backup every night of their database. However, they have a problem today with indexes, it would seem. This is the error message that they are getting.

ERROR at line 1:

ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: 'D:\path\to\datafile\DATAFILES\INDXS.DBF'

The datafile INDX.DBF contains database indexes.

The got the following error when issuing the “alter database open resetlogs” command:

ERROR at line 1:
ORA-01152: file 10 was not restored from a sufficiently old backup
ORA-01110: data file 10: 'D:\path\to\datafile\DATAFILES\INDXS.DBF'

The same error was given when they used the backup version from 24/3 and 25/3, which is not surprising as they recovered only this dbf file.
At 02:00 am each night they make a cold backup of the database using the bat file backup_db.txt (which is renamed to txt so that it will not be deleted from the Antivirus program). I think that they are just trying to restore one dbf file though and not all allong with the control file.

Here are some errors from the alert logs

ORA-01110: data file 10: 'D:\path\to\datafile\DATAFILES\INDXS.DBF'
ORA-01114: IO error writing block to file 10 (block # 1)
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.

ORA-01171: datafile 10 going offline due to error advancing checkpoint
ORA-01122: database file 10 failed verification check
ORA-01110: data file 10: 'D:\path\to\datafile\DATAFILES\INDXS.DBF'
ORA-01208: data file is an old version - not accessing current version

Error from smon

SMON: Restarting fast_start parallel rollback
SMON: ignoring slave err,downgrading to serial rollback
ORACLE Instance si2p (pid = 6) - Error 376 encountered while recovering transaction (1, 19) on object 63803.
*** 2006-03-27 02:34:04.304
ksedmp: internal or fatal error
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: 'D:\path\to\datafile\DATAFILES\INDXS.DBF'

Any advice on the above would be well received. Thanks in anticipation.
Who is Participating?
johnsoneSenior Oracle DBACommented:
Is the database in archive log mode?

If so, after restoring the file, you need to recover it to make it consistent with the rest of the database.

If not, then the entire database needs to be restored to be consistent.

As an absolute last attempt, and this should only be used if everything else fails, you should be able to offline drop the one file, open the database, get the index descriptions for the indexes in that tablespace, then drop the tablespace, recreate the tablespace and recreate the indexes.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.