How to Recover from a Lost or Deleted Oracle Datafile with Different Scenarios

Posted by PDSERVICE on Apr 06, 2020 In

PURPOSE

This article explains the various scenarios for ORA-01157 and how to avoid them.

SCOPE & APPLICATION

This article is intended for Oracle Support Analysts, Oracle Consultants and

Database Administrators.

TROUBLESHOOTING STEPS

How to Recover from a Lost Datafile in Different Scenarios

In the event of a lost datafile or when the file cannot be accessed an ORA-01157

is reported followed by ORA-01110.

Besides this, you may encounter error ORA-07360 : sfifi: stat error, unable to

obtain information about file. A DBWR trace file is also generated in the

background_dump_dest directory. If an attempt is made to shutdown the database

normal or immediate will result in ORA-01116, ORA-01110 and possibly ORA-07368.

This article discusses various scenarios that may be causing this error and the

solution/workaround for these.

Throughout this note we refer to "backups" but if you have a valid physical standby database

you may also use the standby database's datafiles to recover the primary database.

Datafile not found by Oracle

- Unintentionally renamed or moved at the Operating System (OS) level.

Simply restore the file to its original location and recover it

- Intentionally moved/renamed at OS level.

You are re-organising the datafile layout across various disks at the OS.

After moving/renaming the file you will have to rename the file at database

level, and recover it.

Note:115424.1 How to Rename or Move Datafiles and Logfiles

Datafile damaged/deleted

If the file is damaged/deleted and an attempt is made to start the database

will result in ORA-01157, ORA-01110. Then depending upon the type of datafile

lost different action needs to be taken. Check for a faulty hard disk. The

file may have gone corrupt due to faulty disk. Replace the bad disk or create

the file on a non-faulty disk.

Lost datafile could be in one of the following:

1. Temporary tablespace

If the datafile belongs to a temporary tablespace, you will have to simply offline

drop the datafile and then drop the tablespace with including contents option.

Thereafter, re-create the temporary tablespace.

Note.184327.1 Common Causes and Solutions on ORA-1157 Error Found in Backup & Recovery

2. Read Only Tablespace

In this case you will have to restore the most recent backup of the read-only

datafile. No media recovery is required as read-only tablespaces are not

modified. Note however that media recovery will be required under the following conditions:

a. The tablespace was in read-write mode when the last backup was taken

and was made read-only afterwards.

b. The tablespace was in read-only mode when last backup was taken and

was made read-write in between and then again made read only

In either of the above cases you will have to restore the file and do a media

recovery using RECOVER DATAFILE statement. Apply all the necessary archived redo

logs until you get the message "Media Recovery Complete".

Note.184327.1 Common Causes and Solutions on ORA-1157 Error Found in Backup & Recovery

3. User Tablespace

Two options are available:

a. Recreate the user tablespace.

If all the objects in the tablespace can be re-created (recent export is

available; tables can be re-populated using scripts; SQL*Loader etc)

Then, offline drop the datafile, drop the tablespace with including

contents option. Thereafter, re-create the tablespace and re-create

the objects in it.

b. Restore file from backup and do a media recovery.

Database has to be in archivelog mode.If the database is in NOARCHIVELOG

mode, you will only succeed in recovering the datafile if the redo to be

applied to it is within the range of your online redo logs.

Note.184327.1 Common Causes and Solutions on ORA-1157 Error Found in Backup & Recovery

4. Index Tablespace

Two options are available:

a. Recreate the Index tablespace

If the index can be easily re-created using script or manual CREATE INDEX

statement, then best option is to offline drop the datafile,drop the

index tablespace, and re-create it and recreate all indexes in it.

b. Restore file from backup and do a media recovery.

If the index tablespace cannot be easily re-created, then restore the

lost datafile from a valid backup and then do a media recovery on it.

Note.184327.1 Common Causes and Solutions on ORA-1157 Error Found in Backup & Recovery

5. System (and/or Sysaux) Tablespace

a. Restore from a valid backup and perform a media recovery on it

b. Rebuild the database.

If neither backup of the datafile nor the full database backup is

available, then rebuild database using full export, user level/table

level export, scripts, SQL*Loader, standby etc. to re-create and

re-populate the database.

Note.184327.1 Common Causes and Solutions on ORA-1157 Error Found in Backup & Recovery

6. Undo Tablespace

While handling situation with lost datafile of an undo tablespace you need to

be extra cautious so as not to lose active transactions in the undo segments.

The preferred option in this case is to restore the datafile from backup and

perform media recovery.

i. If the database was cleanly shutdown.

Ensure that database was cleanly shutdown in NORMAL or IMMEDIATE mode.

Update your init file with "undo_management=manual"

Restart the database

Drop and recreate the undo tablespace

Update your init file with "undo_management=auto"

Restart the database

ii. If the database was NOT cleanly shutdown.

If the database was shutdown aborted or crashed, you may not be able to drop

the datafile as the undo segments may contain active transactions.

You will need to restore the file from a backup

and perform a media recovery.

7. Lost Controlfiles and Online Redo Logs

If the datafiles are in a consistent state, not needing media recovery, but you have lost

all the controlfiles and online redologs, then while

attempting to create controlfile using scripts will complain of missing

redologs. In this case use RESETLOGS option of the create controlfile

script and then open the database with RESETLOGS option.

8. Lost datafile and no backup

If there are no backups of the lost datafile then you can re-create the

datafile with the same size as the original file and then apply all the

archived redologs written since original datafile was created to the new

version of the lost datafile.

Note: Please put the restore and recovery from backup as the first and prefer

option for case 2 - 6.

Note:1060605.6 Lost datafile and no backup.

You are here

How to Recover from a Lost or Deleted Oracle Datafile with Different Scenarios

How to Recover from a Lost or Deleted Oracle Datafile with Different Scenarios