Recovery of Oracle database.

Posted on 2006-03-27
Medium Priority
Last Modified: 2012-08-14
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.
Question by:bencouve2
1 Comment
LVL 35

Accepted Solution

johnsone earned 2000 total points
ID: 16301785
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.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question