Steps to drop a RAC Database

Issue Description:

Recently in one of our projects we had to drop a RAC database and then recreate the database.

The normal steps to drop a database are as below

SQL> shutdown immediate;

SQL> startup mount restrict exclusive;

SQL> drop database;

When we issued these statements we got the below error

SQL> drop database
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

Background of the DROP DATABASE command:

Prior to Oracle 10g in order to drop a database it was required to manually remove all the data files, control files, redo logfiles and init.ora, password file etc but with oracle 10g dropping a database is a single command.

Once the drop database command is executed successfully the Oracle Database drops the database and deletes all control files and data files listed in the control file. If the database used a server parameter file (spfile), it is also deleted.

Solution:

Our environment was a 4 node RAC. TSCP1, TSCP2, TSCP3 and TSCP4 were the instances and TSCP was the database

Below are the steps to drop the database cleanly otherwise you can just follow the steps marked in asterisk.

1. * Shut down database and listeners cleanly

2. If it is registered in the CRS in our case 11g Grid, first we had to de-register it from CRS

i. De-register the listener

                        srvctl remove listener –l Listener_TSCP1_node1

                        De-register all the listeners configured for that database on all the nodes

ii.      De-register the instances

srvctl remove instance –d TSCP -i TSCP1

De-register all the instances configured for that database.

iii.         De-register the database 

srvctl remove database –d TSCP

3. Make the below changes in the init.ora of the instance 1 and start the database

cluster_database=false

Now then disable the redo threads

SQL> startup

SQL> alter database disable thread 2;

SQL> alter database disable thread 4;

SQL> alter database disable thread 4;

SQL> shutdown immediate

4. During RAC creation we add redo thread and undo tablespace for each instance. So for clean drop we need to drop them as well.

Start the instance 1 only in mount state

Drop the undo tablespaces for all the other instance except the first.

SQL> startup mount

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

SQL> drop tablespace UNDOTBS3 including contents and datafiles;

SQL> drop tablespace UNDOTBS4 including contents and datafiles;

Then drop the redo groups

SQL> alter database drop logfile group 2;

SQL> alter database drop logfile group 3;

SQL> alter database drop logfile group 4;

Then shutdown the database cleanly and do not do a abort.

SQL> shut immediate;

5. Drop the database

SQL> startup mount restrict exclusive;

SQL> drop database;

Tip:

If your system is cooked file system then the drop database statement will drop the control files and data files listed in the control file physically. Then the database cannot be recovered at all.

But if your system is raw file system, and if you have taken a backup of the control file just before dropping your database, you can recover the database.

If you have taken backup of control file to trace then you can use that file and start the database as we do in cloning.

If you have taken backup of control file in binary format then you can use the dd command and copy it to the raw volumes of the control file and start the database.

You will not loose the data since the drop database command does not drop or delete contents of the raw volumes.

Please do not try this production, you can try and validate this on your test systems first.

--Tanveer Madan