We help IT Professionals succeed at work.

Recovery of Oracle database.

bencouve2
bencouve2 asked
on
Medium Priority
2,075 Views
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.
Comment
Watch Question

Senior Oracle DBA
CERTIFIED EXPERT
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.