This has been tested in 11gR2 running under 11.2.0.2 CRS. This can be run for any number of nodes.
1.login as oracle into a first node
2. Set env
. oraenv <sid>
3. srvctl stop database -d <dbname>
4. start the first instance in mount mode
srvctl start instance -i <instance_name> -d <dbname>
5. set cluster_database= false and shutdown the database
sqlplus /nolog
conn / as syssdba
alter system set cluster_database=false;
shutdown immediate;
6. start the database in restrict mode
sqlplus /nolog
conn / as syssdba
startup mount restrict;
5. connect through rman
rman target /
rman> drop database including backups noprompt;
Please note we can use drop database alone incase if we want to retain the archivelog and backup of the database ;
o Drop database deletes "Datafiles,logfiles,controlfiles and spfiles"
o Drop database includeing backups deletes "it additionally deletes archivelogs and backup peices generated by rman"
2. Update OCR
1. login as oracle in the same server
. oraenv <sid>
2. srvctl remove database –d <dbname>
< will prompt for confirmation>
NB:
1. if oracle has been already removed and remove database is throwing error, please create $ORACLE_HOME/bin folder and touch a file touch $ORACLE_HOME/bin/oracle and then run the srvctl remove command
2. if you still facing issue to drop the database details from OCR due to unknon state in the ocr,login as Grid Home owner and remove the resource using crsctl delete resource <resource_name> ( for ex crsctl delete resource ora.lngaf.db
2. Cleanup of ASMs
Ideally drop database should remove all files from the ASM storage.if you find some files are not removed.Please follow the below steps otherwise skip this step.
1. login as Grid Home owner in the same server and connect to ASM through ASMCMD interface
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 4194304 37847040 37844460 2703360 17570550 0 N DATA01/
MOUNTED NORMAL N 512 4096 4194304 53104128 53101068 3793152 24653958 0 N FRA01/
MOUNTED NORMAL N 512 4096 1048576 4175360 4173369 298240 1937564 0 Y SYSTEMDG/
2. verify the space has been released .otherwise manually
remove the folders.
ASMCMD> cd DATA01/PROD1_SB/
ASMCMD> pwd
+DATA01/PROD1_SB
ASMCMD> rm –fr +DATA01/PROD1_SB
NB:
Sometime ASM is not removing all files/directories. Files can be removed manually through asmcmd.folders . Sometimes it can happen that the ASM contains empty system directories which cannot be removed .It is due to a bug Ref: How To Remove An Empty ASM System Directory [ID 444812.1]
Solution:
· Create a new database with the DBCA having the same name as the old directory and subsequently drop the database with the DBCA.
o Or:
· Create a dummy tablespace having a datafile within the directory to be dropped, drop the tablespace and then remove the datafile using the ASMCMD tool. The old directory will be removed automatically.
