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
C:
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, df.name
from v$recover_file rf, v$datafile df
Then issue the commands:
“alter database open;“
“startup force;“
My database then started up ok.