Sunday, 23 September 2018

How To Drop An Oracle Database (Complete Guide)

Dropping a database is considered as a rare/dangerous/easy task, because of this it's not well documented as I think.
In general, there are two ways of dropping a database, using dbca and the manual way.
I usually don't trust GUI tools which dbca is one of them, this is why I'll explain the manual way :-)

I'll explain the steps for dropping a STAND ALONE & RAC databases.

Before you drop any DB make sure you're 100% conscious, not in a middle of something else and you're not in a hurry :-)

Dropping a STANDALONE DB:
let's suppose the database name to be dropped is orcl.

If the database is registered with Grid Control:
Connect to Grid Control with an admin user (e.g. SYSMAN)
Targets -> Databases -> Select ORCL -> Remove

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


Dropping a RAC DB:

let's suppose the database name to be dropped is orcl.

Remove the database from Grid/Cloud Control if it's already registered with it:
Connect to Grid Control with an admin user (e.g. SYSMAN)
Targets -> Databases -> Select ORCL -> Remove

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 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

No comments:

Post a Comment