ORA-01113 Received in Production

[Scott Buckingham]

The above error was encountered about an hour ago. I looked it up and to correct this I need to apply media recovery to the temp02.dbf file. Having never done this and still not an official DBA (but I'm the best we have around here), I'm asking for some assistance. One forum message I saw stated that I can possibly run the 'recover database' command. Is this a valid option? For what it's worth, we just performed a full Omniback backup over the weekend.

[T G Manikandan]

If you are using RMAN features for backing up oracle then the below lines can help you in restoring the database

//*
Backing up the database is the easy part. The real challenge begins when the "ORA-01113 file needs recovery" message flashes on your console. Fortunately, using RMAN for recovery is as simple as backing up in the first place. Just as it takes only a few commands to back up the database, it takes just a few to restore it:
RMAN> run {
2> allocate channel t1 type 'sbt_tape';
3> restore database;
4> recover database;
5> release channel t1;
6> }

When you invoke this script, RMAN identifies the most appropriate backup from which to restore, requests the required tape volumes, and restores the data files. If an incremental-backup strategy is in place, then it will restore a combination of levels 0 and above. RMAN then determines which archive logs are required for rolling forward to the current point in time, requests that they are restored from tape, and recovers the database.

*//

[Tom Geudens]

Hi,
It has been a while since my last day as a DBA, but I think (verify this !!!) that you might have been lucky if the following is true : Is temp02.dbf a file of the temporary tablespace ? If so (again, verify this !!!) it may be possible to startup the database (nomount or mount ... I can't remember exactly which) drop the temp tablespace, recreate it and open the database. I also think this will only work for an 8.i (or higher) version.

Hope this helps,
Tom Geudens

[Andreas D. Skjervold]

Hi

Stop the database.

Restore the datafile from your last backup.

recover the database:

SVRMGR> startup mount
SVRMGR> recover database;

You must have all archived redologs since last backup of the database.

Alt. you might use recover tablespace. Set the tablespace offline first.

Andreas

[Volker Borowski · Accepted solution]

Hi,

check if the datafile ist there:

ls -l ......

If yes, check what is wrong. If you can ensure, the the file is intact (may be left in state backup befor a shutdown), you can simply recover the datafile, while the database is up.

Alter database datafile '....' offline;
recover datafile '.....';
Alter database datafile '....' online;

If the datafile is corrupt, you might need to restore it first. If you can work partly with the database, you can prceed like described above.

The other option is to shutdown and mount the database and do a
recover database;

good luck
Volker

[Scott Buckingham]

Thank you all for your responses! As it turned out, this was a relatively easy fix as I ended up performing the set of commands Volker suggested. The 'recover datafile' worked and I was able to bring it back on-line. The application is accessible again! I guess being in the temporary tablesapce made this easier than if this would have happened elsewhere. Thanks again!

[Volker Borowski]

Hi Scott,

I am lucky it worked for you.

Be sure to check the datafile with the dbv command during the next possible downtime, if you have not been able to verify why this error occured (usually this occurs when doing a shutdown while the tablespace is in backup mode, and this would be a noncritical reason), otherwise you might now be working with a corrupt datafile which would not be good.

Since it is a TEMP-Tablespace you might be more safe to recreate it at the next possible downtime.

Volker