How to drop a rac database safely and update in OCR

This has been tested in 11gR2  running under 11.2.0.2 CRS. This can be run for any number of nodes.

1.    Drop database using RMAN

1.login as oracle  into a first node

2. Set env

   . oraenv <sid>

3. srvctl stop database -d <dbname>

4. start the first instance in mount mode

     srvctl start instance -i <instance_name> -d <dbname>

5. set cluster_database= false and shutdown the database

    sqlplus /nolog

    conn / as syssdba

    alter system set cluster_database=false;

    shutdown immediate;

   

6. start the database in restrict mode

    sqlplus /nolog

    conn / as syssdba

    startup mount restrict;

5. connect through rman

      rman target /

      rman> drop database including backups noprompt;

  Please note we can use drop database alone incase if we want to retain the archivelog and backup of the database ;

o   Drop database deletes "Datafiles,logfiles,controlfiles and spfiles"

o   Drop database includeing backups deletes "it additionally deletes archivelogs and backup peices generated by rman"

 2.    Update OCR

1.         login as oracle in the same server

. oraenv <sid>

2.          srvctl remove database –d <dbname>

< will prompt for confirmation>

    NB:

1.       if oracle has been already removed and remove database is throwing error, please create $ORACLE_HOME/bin folder and touch a file touch $ORACLE_HOME/bin/oracle and then run the srvctl remove command

2.       if you still facing issue to drop the database details from OCR due to unknon state in the ocr,login as Grid Home owner  and remove the resource using crsctl delete resource <resource_name> ( for ex crsctl delete resource ora.lngaf.db

2.    Cleanup of ASMs

Ideally drop database should remove all files from the ASM storage.if you find some files are not removed.Please follow the below steps otherwise skip this step.

1.  login as Grid Home owner in the same server and connect to ASM through ASMCMD interface

ASMCMD> lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB   Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  NORMAL  N         512   4096  4194304  37847040  37844460          2703360        17570550              0             N  DATA01/

MOUNTED  NORMAL  N         512   4096  4194304  53104128  53101068          3793152        24653958              0             N  FRA01/

MOUNTED  NORMAL  N         512   4096  1048576   4175360   4173369           298240         1937564              0             Y  SYSTEMDG/

2.  verify the space has been released .otherwise manually

   remove the folders.

ASMCMD> cd DATA01/PROD1_SB/

ASMCMD> pwd

+DATA01/PROD1_SB

ASMCMD> rm –fr +DATA01/PROD1_SB

NB:

Sometime ASM is not removing all files/directories. Files can be removed manually through asmcmd.folders . Sometimes it can happen that the ASM contains empty system directories which cannot be removed .It is due to a bug  Ref: How To Remove An Empty ASM System Directory [ID 444812.1]

                                Solution:

·         Create a new database with the DBCA having the same name as the old directory and subsequently drop the database with the DBCA.

o   Or:

·         Create a dummy tablespace having a datafile within the directory to be dropped, drop the tablespace and then remove the datafile   using the ASMCMD tool. The old directory will be removed automatically.