Step by Step Drop Database Manually in Oracle
Step by Step Drop Database Manually in Oracle
Sometimes we need to drop/delete the database, especially for the test and development databases. Usually, we drop test or development database once the application team’s testing completed and this database seems not to be in use.
Key Points to remember before dropping the databases.
=> Follow organization-level policy to drop the database like to keep database shutdown for a specific number of days or retention then drop, one full DB’s backup retention, etc.
=> Capture all datafiles. redolog files, control files, etc of the database, Follow the article here How to find all physicals files in Oracle.
=> Make sure no any application connected sessions/remote sessions to the database, click here for How to Find Sessions in Oracle All in One
=> Take one full database backup and keep it for some days or follow the retention period to keep backup.
=> Delete/disable all database’s dependents job in crontab or other tools.
=> Delete target database in OEM if you have active.
We drop databases with the following steps.
1. Set the environment.
In this steps, we have set the database environment or export database which we need to drop.
[oracle@localhost dbhome_1]$ . oraenv ORACLE_SID = [test] ? ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1 The Oracle base remains unchanged with value /u01/app/oracle
2. Verify Database.
Login into sqlplus and verify the database and shutdown clean database.
[oracle@localhost dbhome_1]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 02:18:48 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- TEST READ WRITE SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down.
3. login into the database.
Open the database in mount exclusive mode.
[oracle@localhost dbhome_1]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 02:21:56 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup restrict mount; ORACLE instance started. Total System Global Area 754971128 bytes Fixed Size 8901112 bytes Variable Size 478150656 bytes Database Buffers 264241152 bytes Redo Buffers 3678208 bytes Database mounted.
4. Drop database.
Now we are dropping the database.
SQL> drop database; Database dropped. Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
5. Post Validate
Validate all datafiles. redolog files, control files, etc of the database as captured before drop database, it should not be available on OS level. If available delete it using the command line.
Check listener service, it should not be active.
This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.
441 total views, 2 views today
Hello and welcome to DBsGuru,
DBsGuru is a group of experienced DBA professionals and serves databases and its related community by providing technical blogs, projects, training.
Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.
Thanks for the visits!
Share Learn Grow!
