Recover A Lost Datafile With No Backup (Doc ID 1060605.6)

rongshiyuan發表於2014-05-14

Recover A Lost Datafile With No Backup (Doc ID 1060605.6)


***Checked for relevance on 16-Apr-2014***
 Problem Description: 
==================== 
 
You have inadvertantly lost a datafile at the OS level and there are no current 
backups. 
You are in archivelog mode.
You have ALL Archivelogs available since the datafile was created initially (creation date). 


Problem Explanation: 
==================== 

Since there are no backups, the database cannot be opened without this file 
unless dropped and the tablespace dropped.  If this is an important file and 
tablespace, this is not a valid option.

 
Problem References: 
=================== 

Oracle 7 Backup and Recovery Workshop Student Guide, Failure Scenario 14 


Search Words: 
============= 
 
ORA-1110, lost datafile, file not found.



Solution Description: 
===================== 
 
This files have to be recreated and recovered. Do the following:
 
1) Go to svrmgrl and connect internal.

2) SVRMGR>shutdown immediate. (If this hangs, issue shutdown abort)

3) SVRMGR>startup mount 

4) SVRMGR> select * from v$recover_file;


  SAMPLE:

  FILE#      ONLINE  ERROR              CHANGE#    TIME                
  ---------- ------- ------------------ ---------- --------------------   
  11 OFFLINE FILE NOT FOUND              0 01/01/88 00:00:00   

  (Noting the file number that was reported in the error)


5) SVRMGR> select * from v$datafile where FILE#=11;

  SAMPLE:

  FILE#      STATUS  ENABLED    CHECKPOINT BYTES      CREATE_BYT NAME             
  ---------- ------- ---------- ---------- ---------- ---------- --------
  11 RECOVER READ WRITE 4.9392E+12          0      10240 /tmp/sample.dbf

  (Note the status is RECOVER and the CREATE_BYTE size)
  (Note the NAME)


6) Recreate the datafile.

	SVRMGR> alter database create datafile '/tmp/sample.dbf'
		as '/tmp/sample.dbf' size 10240 reuse.

	(Note that the file "created" and the file created "as" are
	 the same file. The "size" needs to be the same size as it
	 was when it was created.)

7) Check to see that it was successful.

	SVRMGR> select * from v$datafile where FILE#=11;

8) Bring the file online.

	SVRMGR> alter database datafile '/tmp/sample.dbf' online;

9) Recover the datafile.

	SVRMGR> Recover database;

Note: During recovery, all archived redo logs written to since the original 
datafile was created must be applied to the new, empty version of the 
lost datafile." 


10) Enjoy!!

	SVRMGR> alter database open;


Solution Explanation: 
===================== 
 
Recreating the file and recovering it rewrites it to the OS and brings it up to 
date.   

 
Solution References: 
==================== 

Oracle 7 Backup and Recovery Workshop Student Guide, Failure Scenario 14 

References

NOTE:29430.1 - How to Recover a Database Having Added a Datafile Since Last Backup

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-1160651/,如需轉載,請註明出處,否則將追究法律責任。

相關文章