Tuesday, August 13, 2019

Drop Oracle Database

There are two ways to drop an Oracle database:

Before starting:
1) It's always wise to take a Full backup of the database before dropping it.
2) If the database is already registered with Grid/Cloud Control, un-register it:
Connect to Grid Control with an admin user (e.g. SYSMAN)
Targets -> Databases -> Select DB_NAME "ORCL" -> Remove

First and best option: Using DBCA:

Why DBCA is the best? Because it will clean up all the database traces properly from the system (datafiles, controlfiles, redologs, archivelogs, trace files and log files) using one command. Whether the database is a Standalone, Oracle Restart or RAC setup:

How:
# dbca -silent -deleteDatabase -sourceDB orcl -forceArchiveLogDeletion -sysDBAPassword abc123 -sysDBAUserName sys

-silent                                        Run dbca in command line mode
-sourceDB                                 Provide the DB_NAME you want to drop (i.e. orcl)
-forceArchiveLogDeletion        Delete archivelogs 
-sysDBAPassword                    Provide SYS user password (i.e. abc123)
-sysDBAUserName                  Provide a user with SYSDBA privilege (i.e. sys)


Second Option: Using RMAN:

Case1: Dropping a STANDALONE DB:

Restart the DB in "RESTRICT EXCLUSIVE" mode:

[By oracle]
# export ORACLE_SID=orcl
# sqlplus "/ as sysdba"
SQL> shu immediate;
SQL> startup mount exclusive restrict;
SQL> select INSTANCE_NAME,HOST_NAME from v$instance;

Drop the database from RMAN:
# export ORACLE_SID=orcl
# rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 10 10:19:48 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1220202490, not open)

RMAN> DROP DATABASE INCLUDING BACKUPS;
...
database name is "ORCL" and DBID is 1220202490
database dropped
RMAN> exit

Note: INCLUDING BACKUPS option will delete RMAN Backups/Datafiles & Controlfiles copies along with the Database files.

Remove the DB from ORACLE RESTART repository:
[By oracle]
# srvctl remove database -d orcl 

Delete the database entry from /etc/oratab: [both Nodes]

# vi /etc/oratab
[Remove the similar line]
orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

[Optional] Delete the instance files from $ORACLE_HOME/dbs:

# cd $ORACLE_HOME/dbs
# ls -l *orcl*

[Optional] Remove database diag directory from $ORACLE_BASE/diag/rdbms:

# adrci
ADR base = "/u01/app/oracle"

adrci> show homes rdbms
ADR Homes:
diag/rdbms/orcl/orcl

adrci> exit

# cd /u01/app/oracle/diag/rdbms
# rm -rf orcl


Case2: Dropping a RAC DB:

Set the cluster_database parameter to false:
SQL> alter system set cluster_database=false scope=spfile;

Shutdown the database on ALL RAC nodes:
# srvctl stop database -d orcl

Startup the DB in "RESTRICT" "EXCLUSIVE" mode: from only one node:

[By oracle]
# export ORACLE_SID=orcl1
# sqlplus "/ as sysdba"
SQL> startup mount exclusive restrict;
SQL> select INSTANCE_NAME,HOST_NAME from v$instance;

Drop the database:
-----------------
# export ORACLE_SID=orcl1
# rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 10 10:17:42 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1675848900, not open)

RMAN> DROP DATABASE INCLUDING BACKUPS;
...
database name is "ORCL" and DBID is 1675848900
database dropped
RMAN> exit

Remove the database from the Clusterware configurations:
[By oracle]
# srvctl remove database -d orcl

Delete the database & instance entry from /etc/oratab: [Both RAC nodes]
# vi /etc/oratab
orcl1:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent
orcl:/u01/app/oracle/product/11.2.0/db_1:N # line added by Agent

[Optional] Delete instance files from $ORACLE_HOME/dbs: [Both RAC nodes]
# cd $ORACLE_HOME/dbs
# ls -l *orcl*

[Optional] Remove the database diag directory from $ORACLE_BASE/diag/rdbms:  [Both RAC nodes]

# adrci
ADR base = "/u01/app/oracle"

adrci> show homes rdbms
ADR Homes:
diag/rdbms/orcl/orcl1
diag/rdbms/orcl/orcl
diag/rdbms/test/test
diag/rdbms/test/test1
diag/rdbms/_mgmtdb/-MGMTDB

adrci> exit

# cd /u01/app/oracle/diag/rdbms
# rm -rf orcl1
# rm -rf orcl