Drop Oracle Rac Database

Here is an example of how to drop a 4 node rac database manually.

First stop the database

$ srvctl stop database -d gaer4_01

Now remove the database entry  from crs

-d is the db_unuique_name of the database
-i  is the name of the instance

$ srvctl remove instance -d gaer4_01 -i GAER41
Remove instance from the database gaer4_01? (y/[n]) y
$ srvctl remove instance -d gaer4_01 -i GAER42
Remove instance from the database gaer4_01? (y/[n]) y
$ srvctl remove instance -d gaer4_01 -i GAER43
Remove instance from the database gaer4_01? (y/[n]) y
$ srvctl remove instance -d gaer4_01 -i GAER44
Remove instance from the database gaer4_01? (y/[n]) y
PRKO-3147 : Instance GAER44 cannot be removed because it is the only preferred instance for service(s) SRV0GAER401,SRVGAER4RMN for database gaer4_01

Oeps  yes we must remove the service_name of the database first before we can remove the database.

$ srvctl remove service -d gaer4_01 -s SRV0GAER401
$ srvctl remove service -d gaer4_01 -s SRVGAER4RMN

now we can remove the last instance from CRS.

$ srvctl remove instance -d gaer4_01 -i GAER44
Remove instance from the database gaer4_01? (y/[n]) y

Now we can remove the database

$ srvctl remove database -d gaer4_01
Remove the database gaer4_01? (y/[n]) y
Now we start the first instance so we can adjust some parameters.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup

we now set the cluster_database parameter to false.

SQL>alter system set cluster_database=false scope=spfile;

System altered

and we set the cluster_database_instance to 1

SQL>alter system set cluster_database_instances=1 scope=spfile;

System altered.
Now we diable the thread from all instance's execpt the active instance.

SQL> alter database disable thread 2;
Database altered.
SQL> alter database disable thread 3;
Database altered.
SQL>alter database disable thread 4;
Database altered.

Delete the unwanted thread and redo logfiles. Thread 1 is for active instance and other is for another instance. Drop all redo group of other thread. Ex: Group 4,5,6 are for other thread then drop as follows.

SQL>select thread#, group# from v$log;

   THREAD#     GROUP#
---------- ----------
         1          1
         1          2
         1          3
         2          4
         2          5
         2          6
         3          7
         3          8
         3          9
         4         10
         4         11
         4         12
12 rows selected.

so drop group 4 till 12 beloging to thread 2 till 4

SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
SQL> alter database drop logfile group 8;
Database altered.
SQL> alter database drop logfile group 9;
Database altered.
SQL> alter database drop logfile group 10;
Database altered.
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> select thread#, group# from v$log;
   THREAD#     GROUP#
---------- ----------
         1          1
         1          2
         1          3

Now drop all undo tablespace execpt the one from the active instance

SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace UNDOTBS3 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace UNDOTBS4 including contents and datafiles;
Tablespace dropped.

Now we cretae pfile from spfile

SQL> create pfile from spfile;
shutdown database and start it again in mount fase restrict

SQL>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount restrict
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size            1241517520 bytes
Database Buffers          352321536 bytes
Redo Buffers                7344128 bytes
Database mounted.

Now we can drop the database.

SQL> drop database;

And that's it.