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:

Date written: Feb 26 2002

Oracle version(s):

I accidentally delete a datafile and I didn't do a backup on it. How can I recover it?

Back to index of questions

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,
    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

Back to top

Back to index of questions