How to drop a database

Introduction

The drop database command is the recommended command to drop an Oracle database. Though one can use operating system commands to remove database files instead of the "drop database" command, the latter is compulsory if one wants to drop a database using Oracle ASM (Automatic Storage Management).

DROP DATABASE command

It may be very rare that one is asked to drop a database. But this is usually the case when one wants to refresh existing test/development databases to clone them again from the latest production database backups. One may have to drop production databases too in the event of say, the front end application is retired/decomissioned  or the existing database data is migrated and merged to a different database etc. One needs to exercise a lot of caution and get the necessary approvals prior to dropping a production database. Furthermore, take a full database backup prior to dropping the database. 

1. sqlplus '/ as sysdba'
2. shutdown  immediate;
3. startup mount restrict;
4. exit
5. rman
6. connect target  /
7. drop database;

Do you really want to  drop the database (enter YES or NO)?

The drop database commands deletes all the datafiles, tempfiles, online redo logfiles, spfile (server parameter file) and the control files.

DROP DATABASE command restrictions

The drop database command does not completely remove all the dropped database references in the server. Some of the dropped database related files such as the diagnostic files (trace files, alert logfile etc) are not deleted by the drop database command. Additionally, the Flash Recovery area files are not deleted too (incase the dropped database was using the Flash Recovery Area feature). 

DROP DATABASE command is a must for ASM

The operating system remove commands (for example, the rm command in Linux/Unix) can work only in databases that are stored on filesystems, but not for database files that are stored in ASM disks.  When an ASM diskgroup/disk contains database files from multiple databases, the drop database command "selectively" drops only the files associated with the database to be dropped from the ASM disks.

DROP DATABASE command works for ASM and non-ASM based databases

One can use drop database command for all databases which use the traditional filesystem storage as well as the ASM storage.

Dropping (removing) a database using operating system commands

Though drop database command is the recommended approach, one can also drop a database that uses traditional filesystem storage using operating system commands such as the rm command in Linux/Unix. Because this is a manual approach, one has to ensure that all the files that are part of the database are removed manually

1. Datafiles
2. Tempfiles
3. Online redo logfiles
4. Archived redo logfiles (if any)
5. Flash Recovery area files(if any)
6. pfile,spfile (if any)
7. diagnostic files
8. control files