In this post, we will discuss how to drop the Oracle database. The below procedure drops the spfile also but it does not touch Oracle Home. You may want to backup the spfile before dropping the database
sqlplus / as sysdba startup mount (If database not started) create pfile='/tmp/pfile.ora' from spfile;
Dropping an Oracle database involves deleting the database and all its associated data files, control files, and redo logs. It’s a significant action that can’t be undone, so always ensure you have backups and a solid reason for dropping the database.
How to drop the database
Now follow the below procedure to drop the database
- Source the environment
$. oraenv
[ORACLE_SID]? TEST
or
export ORACLE_SID=TEST
export ORACLE_HOME=<ORACLE_HOME PATH>
2- Connect as sysdba
$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 1 17:38:02 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
3- Start The database
SQL> startup ORACLE instance started. Total System Global Area 134045184 bytes Fixed Size 2217952 bytes Variable Size 142871862 bytes Database Buffers 129317222 bytes Redo Buffers 16343040 bytes Database mounted. Database opened.
4- Shutdown the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
5- Start in Exclusive mode
SQL> startup mount exclusive restrict ORACLE instance started. Total System Global Area 134045184 bytes Fixed Size 2217952 bytes Variable Size 142871862 bytes Database Buffers 129317222 bytes Redo Buffers 16343040 bytes Database mounted.
6- Drop the database
SQL> drop database;
Database dropped.
Disconnected from Oracle Database 11g Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
This command will remove the database and all its associated data files, control files, and redo log files. This completes dropping the database.
7. Drop database can be also done using RMAN and it provides additional functionality. We can delete backup also with it now from 11gR2
RMAN> DROP DATABASE;
This is the same as we do from sqlplus, but it will ask for prompt before deleting
RMAN>DROP DATABASE NOPROMPT;
This is the same as we do from sqlplus, it does not ask any thing
RMAN>DROP DATABASE INCLUDING BACKUPS;
This command apart from deleting the database also deletes archivelog and backup taken RMAN in all device type. It ask for prompt before deleting
RMAN>DROP DATABASE INCLUDING BACKUPS NOPROMPT
This command apart from deleting the database also deletes archivelog and backup taken RMAN. It does not ask for prompt before deleting
How to Drop the Oracle RAC Database
If the Database is Oracle RAC, then the below steps need to be executed in the mount state
alter system set cluster_database=FALSE scope=spfile
Then stop and mount in exclusive mode and drop it
Post Drop database Steps
Clean Up Initialization Files and Other Artifacts:
After dropping the database, you should manually delete the initialization parameter files “pfile”, password file, and any archived logs or backups associated with the database.
Update or Remove Database from srvctl
If you’re managing your using srvctl. Consider removing the database from srvctl if not been deleted already
Cleanup Listener and TNSNAMES Configurations:
If you have a listener configuration (listener.ora) or other network configuration files like tnsnames.ora that reference the dropped database, you might want to clean or update them.
Related Articles
Flashback PDB in Oracle Database 12c Release 2
PDB Relocate in Oracle database 12c Release 2
How to remotely clone a PDB or non-CDB in Oracle Database 12.1
Hot PDB cloning in Oracle database 12.2
Refreshable PDB in Oracle Database 12.2
https://docs.oracle.com/database/121/ADMIN/cdb_mon.htm#ADMIN13720