7 x 24 在线支持!
Recover A Lost Oracle Datafile With No Backup
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