The Oracle (tm) Users' Co-Operative FAQ
How to Recover in a situation where there is no datafile backup
| Author's name: Allan W. Tham
Author's Email: allanwtham@hotmail.com |
Date written: Feb 26 2002 Oracle version(s): 8.1.7.0.0 |
| I accidentally delete a datafile and I didn't do a backup on it. How can I recover it? |
Initially if the database is up, if you try to do a
shutdown immediate, you will get ora-01116, ora-01110 and
ora-27041. If initially you are down,
trying to start it up will result in Ora-01157 and Ora-01110
1. Do a shutdown abort
2. Startup mount
3. Get the Path and Size of the missing datafile -
select df.file#, df.status, df.enabled,
df.create_bytes, df.name
from v$recover_file rf, v$datafile df
where rf.file#=df.file# and rf.error =
"FILE NOT FILE"
(note the path and size of the missing
file)
4. Create the datafile - alter database create datafile
'/path/filename.dbf' as '/path/filename.dbf' size xxx reuse
(Make sure that the path and size are the
same as step 4)
5. If the file is offline, then bring in online - alter database
datafile '/path/filename.dbf' online;
6. Recover the database - recover database
7. Open the database - alter database open
Further reading: Metalink Note:1060605.6
