How to drop 10g database using DROP DATABASE command

Sometimes databases come and go. Earlier, dropping a database was done manually by removing all files belonging to the database. This can be quite easy by find the controlfile, datafiles, redo log files, archived log files, etc. But, what if the files reside in ASM file system. The task of removing files can be risky or too much work is involved to get the deletion done. In such situations, DROP DATABASE command comes very handy...

I came across a situation where an already shut down database was not allowing to start and complained that memory was insufficient. The spfile of this database resided on the ASM file system. Somehow the database has to be started. So I created the temporary pfile. The steps involved were finding the location of spfile in ASM and creating a temporary pfile on normal file system :

$ cat $ORACLE_HOME/dbs/initorcl.ora 
$ . oraenv 
$ asmcmd 
ASMCMD> pwd 
ASMCMD> ls -l  
Type          Redund  Striped  Time            Sys  Name 
                                                Y   CONTROLFILE/ 
                                                Y   PARAMETERFILE/ 
                                                N   spfileorcl.ora =>+DATA/ORCL/PARAMETERFILE/spfile.265.718738149 
asmcmd[+]> cp +DATA/orcl/spfileorcl.ora /users/apps/dba/oracle/temp_spfileorcl.ora 
asmcmd[+]> exit

Note that even though the spfileorcl.ora is a binary file, temp_spfileorcl.ora created using ASMCMD cp command is an ASCII text file. Now, edit /users/apps/dba/oracle/temp_spfileorcl.ora with below settings :

orcl.sga_target = 1g;

Start the database in MOUNT EXCLUSIVE RESTRICT mode

$ . oraenv 
$ sqlplus /nolog 
SQL> connect / as sysdba 
SQL> startup pfile='/users/apps/dba/oracle/temp_spfileorcl.ora' mount exclusive restrict 

Get the list of all datafiles, controlfiles, redo log files

SQL> select name from v$datafile;

SQL> select name from v$controlfile;

SQL> select member from v$logfile;

Drop the database

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options
SQL> exit

Dropping the database leaves the below directories in ASM disk group

ASMCMD> pwd 
and also all archived redo logs