How do I delete an entire database (UNIX only)?


The following shows the steps to drop a database in Unix enviroment. In order to delete a database, there are few things need to be taken care of. First, all the database related files eg *.dbf, *.ctl, *.rdo, *.arc need to be deleted. Then, the entry in listener.ora and tnsnames.ora need to be removed. Third, all the database links need to be removed since it will be invalid anyways.

It depends how you login to oracle account in Unix, you should have environment set for the user oracle. To confirm that the environment variable is set, do a env|grep ORACLE and you will notice that your ORACLE_SID=SOME_SID and ORACLE_HOME=SOME_PATH. If you do not already have the ORACLE_SID and ORACLE_HOME set, do it now.

Make sure also, that you set the ORACLE_SID and ORACLE_HOME correct else you will end up deleting other database. Next, you will have to query all the database related files from dictionaries in order to identify which files to delete. Do the following:

01. Login as connect / as sysdba at svrmgrl
02. startup the database if it’s not already started. The database must at least mounted.
03. spool /tmp/deletelist.lst
04. select name from v$datafile; (This will get all the datafiles; alternatively, you can select file_name from dba_data_files)
05. select member from v$logfile;
06. select name from v$controlfile;
07. archive log list (archive_log_dest is where the archived destination is)
08. locating ifile by issuing show parameter ifile (alternatively, check the content of init.ora)
09. spool off
10. Delete in O/S level the files listed in /tmp/deletelist.lst
11. remove all the entries which refer to the deleted database in tnsnames.ora and listener.ora (located in $ORACLE_HOME/network/admin)
12. remove all database links referring to the deleted database.
13. check “/var/opt/oracle/oratab” to make sure there is no entry of the database deleted. If yes, remove it.
14. DONE