Thursday, December 17, 2009

Dropping a Database in Oracle

In my last post, I documented the syntax for creating a database using as brief a syntax as possible. Beginning in Oracle 10g, it is now possible to merely enter "create database" in sqlplus, and successfully create an instance. Along with this sparse syntax comes a new, even more esoteric concept: The ability to DROP a database in the same manner.

In prior versions of Oracle, removing a database was something of a manual endeavor, and was therefore error-prone and often incomplete. It involved removing files and directories tied to an instance by hand, and might leave tattered evidence of a previous database's existence that Grid Control and other monitoring tools might pick up and report on. In a worst case scenario, I've seen these databases come to life in a zombie-like fashion after certain system events like reboots, simply because someone failed to remove a parameter from a file which was setup as the "root" user during installation, and therefore not addressed during the manual drop.

The new command attempts to address all such issues. By merely saying "drop database" while connected to the appropriate software and with the ORACLE_SID setting to match, a database "goes away". This is, of course, a destructive operation! It requires the database to be in a restricted mode, and it cannot easily be recovered from, once executed (which leaves it a good time to mention that before ANY command is executed against an Oracle database, a recovery plan should always be considered).

Command script:

SQLPLUS> connect / as sysdba
SQLPLUS> shutdown abort;
SQLPLUS> startup mount restrict;
SQLPLUS> drop database;

It's as simple as that!

Check around your system for the last vestiges of the database you just dropped. While this command will address everything directly attached (datafiles, online logs, controlfiles, alert logs, etc), it will only do so for locations defined in the database at the time the command was entered. This might mean that logs or scripts that were relocated at different points of time would not be removed.

While I think this command is an excellent tool for cleaning up, at least a cursory look around the system after execution is certainly a must. And don't forget grid control, if you're using it. Monitor this server after a period of time and all remnants of it should be gone.

1 comment:

  1. These commands will still work in Oracle 11g.

    It might be a good practice to add the following to your init.ora:

    db_file_create_dest=something permanent

    This is because otherwise, the software will use the same mount point as the init file.