How to manually DROP DATABASE in Oracle-RAC Environment.

My Environment Details:-

Oracle11g r2 RAC/GRID 2 Node

Oracle Database Name:DEMO

Instance Name DEMO1 on NODE1

Instance Name DEMO2 on NODE2

Steps for Manual Drop the RAC Database

1) Login to Oracle Node1.

-bash-3.2$ pwd
/home/oracle
-bash-3.2$ export ORACLE_SID=DEMO1

2) Check the current pmon process.

-bash-3.2$ ps -eaf | grep pmon
oracle   19020     1  0 22:24 ?        00:00:00 ora_pmon_DEMO1
oracle   19213 19129  0 22:28 pts/0    00:00:00 grep pmon
-bash-3.2$

-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 26 22:28:28 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SQL> select dbid, name from v$database;

DBID NAME
---------- ---------
3542930991 DEMO

SQL> select INSTANCE_NUMBER,INSTANCE_NAME from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
1 DEMO1

SQL>

3) Shutdown the Database on node-2

-bash-3.2$ hostname
aaarac2
-bash-3.2$
-bash-3.2$ export ORACLE_SID=DEMO2
-bash-3.2$
-bash-3.2$ ps -eaf | grep pmon
oracle    2163  1927  0 22:30 pts/0    00:00:00 grep pmon
oracle    4288     1  0 Nov25 ?        00:00:02 ora_pmon_DEMO2
-bash-3.2$
-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 26 22:31:05 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SQL> select dbid, name from v$database;

DBID NAME
---------- ---------
3542930991 DEMO

SQL> select INSTANCE_NUMBER,INSTANCE_NAME from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
2 DEMO2

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

4) Startup database in restricted mode on node-1


SQL> select dbid, name from v$database;

DBID NAME
---------- ---------
3542930991 DEMO

SQL> select INSTANCE_NUMBER,INSTANCE_NAME from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
1 DEMO1

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount RESTRICT ;
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             201330080 bytes
Database Buffers           54525952 bytes
Redo Buffers                4980736 bytes
Database mounted.

5.) Drop database from node-1

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


To resolve this error we need to change following init parameter and restart the database with pfile.

*.cluster_database=FALSE
SQL> alter system set cluster_database=FALSE scope=spfile;

System altered.

6) Create pfile from spfile

SQL> create pfile from spfile ;

File created.

SQL>

-bash-3.2$ echo $ORACLE_SID=DEMO1
DEMO1=DEMO1
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 26 22:48:38 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP pfile=$ORACLE_HOME/dbs/initDEMO1.ora MOUNT RESTRICT;
ORACLE instance started.

Total System Global Area  263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             201330080 bytes
Database Buffers           54525952 bytes
Redo Buffers                4980736 bytes
Database mounted.
SQL>
SQL> show parameter instance_name from v$database;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      DEMO1
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
Advertisements

2 thoughts on “How to manually DROP DATABASE in Oracle-RAC Environment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s