How to Drop or Remove or Decommission a Database in Oracle.
- Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application.
- Please get the required approval from Business/Customer to proceed for database decommissioning.
- Send the Notification to Business/Customer that we are going to decommission the database. (if required).
- Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can be on disk or tape. Backup can be of database only or it can also include the non-Database files like parameter file, dump file etc.
- If you want, you can take the list of Data files/Control files/Redo log files and Parameter files that constitute the database.
- Shutdown the database. also shutdown listener (if required).
- Take the complete database backup and make sure this backup is a valid backup.
- Make sure Storage team has taken the backup as specified in the raised ticket against them and has set the retention period correctly. You can take a Signoff mail from Storage/Backup team in case it is required.
- Remove the monitoring jobs entry from crontab and also remove the monitoring jobs (if any) running from third party tool(s) in the database.
- Startup the database in restrict mode and give drop database command.
SQL> conn / as sysdba Connected to an idle instance. SQL> startup restrict mount ORACLE instance started. Total System Global Area xxxxxxxxxx bytes Fixed Size xxxxxxx bytes Variable Size xxxxxxxxxx bytes Database Buffers xxxxxxxxxx bytes Redo Buffers xxxxxxx bytes Database mounted. SQL> drop database; Database dropped.
11. Check if the instance is running or not, shutdown the instance if required.
12. Remove the archive log files, trace files, dump files, backup files and respective Database directories. (if required)
Be careful before deleting the physical files.
13. Remove the Backup schedule job or any other job applied on the database.
14. Remove the database entry from /etc/oratab.
15. Remove the database details from inventory sheet or any other record(s). (if required)