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.

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more