Deleted Oracle DataFile? Database Not Starting?

The other day whilst doing a backup of my Windows PC running Oracle 10G, I noticed a large datafile which was too big for the External Hard Drive (as it was greater than 4GB in size for a FAT drive) I also did not require it anymore, so with the database shutdown I deleted it.

Today, I tried starting the PC and database and TOAD told me that I could not log on as the the database instance was not available or was in the process of being shutdown, straight away I thought of the DBF file I had deleted, and I should have really removed it from insideOEM.

Anyway, after looking on the internet I found a way around the problem:

Only do this if you no longer want the datafile!

First off all login as sysdba:

This is what I have in a .bat file, this enable me to run the file on the host PC and log in as sysdba

cd oracle-rdi-db\BIN
sqlplus “/ as sysdba”

Then issue “alter database datafile 6 offline drop;” – where the number is the number of the datafile you deleted.

If you don’t know the number then issue this SQL to find out the number:

select df.file#, df.status, df.enabled, df.create_bytes,
from v$recover_file rf, v$datafile df

Then issue the commands:

alter database open;

startup force;

My database then started up ok.

Deleted Oracle Datafile by mistake?

Leave a Reply

Your email address will not be published. Required fields are marked *

20 + = twenty nine