How to Drop or Remove or Decommission a Database in Oracle

How to Drop or Remove or Decommission a Database in Oracle

Database Decommission steps for Oracle 10G and 11g databases

Note:

~~~~~~~~

Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application.

Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP).

1)  Please get the required approval from Business/Customer to proceed for database decommissioning.

2)  Send the Notification to Business/Customer that we are going to decommission the database. (if required)

3)  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.

4)  If you wish then you can take the list of Data files/Control files/Redo log files and Parameter files that constitute the database.

5)  Shutdown the database.

6)  Shutdown the listener (if required.)

7)  Take the complete database backup and make sure this backup is a valid backup.

8)  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.

9)  Remove the monitoring jobs entry from crontab and also remove the monitoring jobs (if any) running from third party tool(s) in the database.

10)  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.

Disconnected from Oracle Database

…………………………………………………………

…………………………………………………………..

Check if the instance is running or not, shutdown the instance if required.

11)  Remove the archive log files, trace files, dump files, backup files and respective Database directories. (if required)

Be careful before deleting the physical files.

12)  Remove the Backup schedule job or any other job applied on the database.

13)  Remove the database entry from /etc/oratab or comment out the entry.

14)  Send the final notification to Application/Customer that DB has been decommissioned. You can also share the backup retention details to the Business/Customer.

15)  Remove the database details from inventory sheet or any other record(s). (if required)

Note:

Any or All steps mentioned above can be applied during database decommissioning under your organization’s SOP.

It may also happen that SOP may include some extra steps as I already mention it is at some part enterprise dependent.