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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.