How to Recover from a Lost or Deleted Datafile with Different Scenarios [ID 198640.1]

冷不防 2022-02-25 19:35 107阅读 0赞

Applies to:  
Oracle Server - Personal Edition - Version 10.2.0.1 and later  
Oracle Server - Enterprise Edition - Version 10.2.0.1 and later  
Oracle Server - Standard Edition - Version 10.2.0.1 and later  
Information in this document applies to any platform.  
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.  
   
References  
NOTE:755620.1 - How to relocate database files using RMAN  
NOTE:1060605.6 - Recover A Lost Datafile With No Backup  
NOTE:1149946.1 - Recreating a missing datafile with no backups  
NOTE:115424.1 - How to Rename or Move Datafiles and Logfiles  
NOTE:184327.1 - ORA-1157 Troubleshooting  
NOTE:453153.1 - Recovering the primary database's datafile using the physical standby, and vice versa

发表评论取消回复

表情：

评论列表（有 0 条评论，107人围观）

还没有评论，来说两句吧...

相关阅读

相关 How to delete iscsi target from initiator ( CentOS / RHEL 7 ) Linux

ow to delete iscsi target from initiator ( CentOS / RHEL 7 ) Linux How to delete isc...

野性酷女/ 2024年04月19日 09:28/ 0 赞/ 75 阅读

相关 How to use DISM to install a hotfix from within Windows

How to use DISM to install a hotfix from within Windows [https://blogs....

你的名字/ 2024年04月18日 21:06/ 0 赞/ 99 阅读

相关 How to implement an algorithm from a scientific paper

How to implement an algorithm from a scientific paper 2012 JANUARY 18 tags: academic

小灰灰/ 2023年07月10日 05:16/ 0 赞/ 14 阅读

相关 How to Monitor User Activity with psacct or acct Tools

psacct or acct both are open source application for monitoring users activities on the s

绝地灬酷狼/ 2023年01月13日 01:58/ 0 赞/ 45 阅读

相关 html export to excel,How to export a range of data from Excel to HTML or web page file?

How to export a range of data from Excel to HTML or web page file? If you need to expor

「爱情、让人受尽委屈。」/ 2022年10月07日 12:51/ 0 赞/ 50 阅读

相关 How to create a manifest file with Maven

This tutorial will show you how to use the `maven-jar-plugin` to create a manifes

﹏ヽ暗。殇╰゛Y/ 2022年05月26日 07:43/ 0 赞/ 250 阅读

相关 How to Shrink the Datafile of Temporary Tablespace

In this Document [Goal][] [Solution][] -------------------- Applies to:

我不是女神ヾ/ 2022年03月31日 05:40/ 0 赞/ 178 阅读

相关 How Delete File with Readonly Permission?

class Program { static void Main(string[] args) {

快来打我*/ 2022年03月28日 07:54/ 0 赞/ 143 阅读

相关 How to Recover from a Lost or Deleted Datafile with Different Scenarios [ID 198640.1]

Applies to: Oracle Server - Personal Edition - Version 10.2.0.1 and later Oracle Ser

冷不防/ 2022年02月25日 19:35/ 0 赞/ 108 阅读

相关 How to DEBUG a trigger or procedure

DEBUGGING STORED PROCEDURES Over the past several weeks, we’ve been working on debugg

落日映苍穹つ/ 2021年12月17日 15:01/ 0 赞/ 180 阅读