Drop a Oracle RAC Database

I am demonstrating how we can drop the database in RAC environment manually in Oracle12c(12.1.0.2.0) version.

I have two node RAC and the database name is TEST.  The instance names are TEST1 & TEST2.

1. Verify the instance

[oracle@host01 ~]$ srvctl status database -d TEST
Instance TEST1 is running on node host01
Instance TEST2 is running on node host02

[oracle@host01 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE host01 STABLE
ONLINE ONLINE host02 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE host01 STABLE
ONLINE ONLINE host02 STABLE
ora.TEST.dg
ONLINE ONLINE host01 STABLE
ONLINE ONLINE host02 STABLE
ora.asm
ONLINE ONLINE host01 Started,STABLE
ONLINE ONLINE host02 Started,STABLE
ora.net1.network
ONLINE ONLINE host01 STABLE
ONLINE ONLINE host02 STABLE
ora.ons
ONLINE ONLINE host01 STABLE
ONLINE ONLINE host02 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE host02 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE host01 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE host01 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE host01 169.254.59.131 192.1
68.1.101,STABLE
ora.cvu
1 ONLINE ONLINE host01 STABLE
ora.mgmtdb
1 ONLINE ONLINE host01 Open,STABLE
ora.oc4j
1 ONLINE ONLINE host01 STABLE
ora.scan1.vip
1 ONLINE ONLINE host02 STABLE
ora.scan2.vip
1 ONLINE ONLINE host01 STABLE
ora.scan3.vip
1 ONLINE ONLINE host01 STABLE
ora.test.db
1 ONLINE ONLINE host01 Open,STABLE
2 ONLINE ONLINE host02 Open,STABLE
ora.usben.db
1 ONLINE ONLINE host01 Open,STABLE
2 ONLINE ONLINE host02 Open,STABLE
ora.host01.vip
1 ONLINE ONLINE host01 STABLE
ora.host02.vip
1 ONLINE ONLINE host02 STABLE
--------------------------------------------------------------------------------

2. Stop the entire cluster environment

[oracle@host01 ~]$ srvctl stop database -d TEST
[oracle@host01 ~]$ srvctl status database -d TEST
Instance TEST1 is not running on node host01
Instance TEST2 is not running on node host02

3. Start only one instance to edit the cluster_database parameter to FALSE

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
TEST1
SQL> startup mount
ORACLE instance started.
Total System Global Area  524288000 bytes
Fixed Size                  2926320 bytes
Variable Size             436209936 bytes
Database Buffers           79691776 bytes
Redo Buffers                5459968 bytes
Database mounted.
SQL>
SQL> sho parameter cluster_data
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
SQL>  alter system set cluster_database=FALSE scope=spfile;
System altered.

4. Mount the first instance in restrict mode and drop the database.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict exclusive;
ORACLE instance started.
Total System Global Area  524288000 bytes
Fixed Size                  2926320 bytes
Variable Size             415238416 bytes
Database Buffers          100663296 bytes
Redo Buffers                5459968 bytes
Database mounted.
SQL> sho parameter cluster_data
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
SQL>  select logins,parallel from v$instance;
LOGINS     PAR
---------- ---
RESTRICTED NO
SQL> drop database;
Database dropped.

5. Update the OCR

[oracle@host01 ~]$ srvctl status database -d TEST
Instance TEST1 is not running on node host01
Instance TEST2 is not running on node host02
[oracle@host01 ~]$
[oracle@host01 ~]$ srvctl remove database -d TEST
Remove the database TEST? (y/[n]) y
[oracle@host01 ~]$

6. Verify the instance

[oracle@host01 ~]$  srvctl status database -d TEST
PRCD-1120 : The resource for database TEST could not be found.
PRCR-1001 : Resource ora.test.db does not exist

7. To drop the database including the backup, we can go for the below option

RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;

8. Go to OS and clean if there is any files related to the database. Go to ASM disk and clean if there is any relevant files to  this database.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

Anuradha’s LinkedIn: https://www.linkedin.com/in/dbaanuradhamudgal/

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp