Friday, December 2, 2016

How to drop RAC database manually

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.

Step 1  Verify the instance

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


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


Step 2  Shutdown the both instance


SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
TEST1

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

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
TEST2

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

Step3  Mount the first instance and update the cluster parameter.


 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.

Step 3  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
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> drop database;

Database dropped.

Monitor the alert log while dropping the database

Step 4  Update the OCR


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

Step 5 Verify the instance.


 [oracle@usbenhost01 ~]$  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
[oracle@usbenhost01 ~]$  crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details                                                                                                                                                                   
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       usbenhost01              STABLE
               ONLINE  ONLINE       usbenhost02              STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       usbenhost01              STABLE
               ONLINE  ONLINE       usbenhost02              STABLE
ora.TEST.dg
               ONLINE  ONLINE       usbenhost01              STABLE
               ONLINE  ONLINE       usbenhost02              STABLE
ora.asm
               ONLINE  ONLINE       usbenhost01              Started,STABLE
               ONLINE  ONLINE       usbenhost02              Started,STABLE
ora.net1.network
               ONLINE  ONLINE       usbenhost01              STABLE
               ONLINE  ONLINE       usbenhost02              STABLE
ora.ons
               ONLINE  ONLINE       usbenhost01              STABLE
               ONLINE  ONLINE       usbenhost02              STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       usbenhost02              STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       usbenhost01              STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       usbenhost01              STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       usbenhost01              169.254.59.131 192.                                                                                                                                                             1
                                                             68.1.101,STABLE
ora.cvu
      1        ONLINE  ONLINE       usbenhost01              STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       usbenhost01              Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       usbenhost01              STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       usbenhost02              STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       usbenhost01              STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       usbenhost01              STABLE
ora.usben.db
      1        ONLINE  ONLINE       usbenhost01              Open,STABLE
      2        ONLINE  ONLINE       usbenhost02              Open,STABLE
ora.usbenhost01.vip
      1        ONLINE  ONLINE       usbenhost01              STABLE
ora.usbenhost02.vip
      1        ONLINE  ONLINE       usbenhost02              STABLE
--------------------------------------------------------------------------------
[oracle@usbenhost01 ~]$   

Step 6  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.

No comments: