Email: service@parnassusdata.com 7 x 24 online support!

Home

DOWNLOAD DBRECOVER

Recover A Lost Oracle Datafile With No Backup

Recover A Lost Oracle Datafile With No Backup

Posted by PDSERVICE on Apr 06, 2020 In
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