Pages

Saturday, May 5, 2012

The drop database command is the recommended command to drop an Oracle database. Though one can use operating system commands to remove database files instead of the "drop database" command, the latter is compulsory if one wants to drop a database using Oracle ASM (Automatic Storage Management).

DROP DATABASE command

It may be very rare that one is asked to drop a database. But this is usually the case when one wants to refresh existing test/development databases to clone them again from the latest production database backups. One may have to drop production databases too in the event of say, the front end application is retired/decomissioned or the existing database data is migrated and merged to a different database etc. One needs to exercise a lot of caution and get the necessary approvals prior to dropping a production database. Furthermore, take a full database backup prior to dropping the database.

sqlplus '/ as sysdba'

shutdown immediate;

startup mount restrict;

exit

rman

connect target /

drop database;

Do you really want to drop the database (enter YES or NO)?

The drop database commands deletes all the datafiles, tempfiles, online redo logfiles, spfile (server parameter file) and the control files.

DROP DATABASE command restrictions

The drop database command does not completely remove all the dropped database references in the server. Some of the dropped database related files such as the diagnostic files (trace files, alert logfile etc) are not deleted by the drop database command. Additionally, the Flash Recovery area files are not deleted too (incase the dropped database was using the Flash Recovery Area feature).

DROP DATABASE command is a must for ASM

The operating system remove commands (for example, the rm command in Linux/Unix) can work only in databases that are stored on filesystems, but not for database files that are stored in ASM disks. When an ASM diskgroup/disk contains database files from multiple databases, the drop database command "selectively" drops only the files associated with the database to be dropped from the ASM disks.

DROP DATABASE command works for ASM and non-ASM based databases

One can use drop database command for all databases which use the traditional filesystem storage as well as the ASM storage.

Dropping (removing) a database using operating system commands

Though drop database command is the recommended approach, one can also drop a database that uses traditional filesystem storage using operating system commands such as the rm command in Linux/Unix. Because this is a manual approach, one has to ensure that all the files that are part of the database are removed manually

Introduction Have you seen your VNC Viewer showing a black screen intermittently ? We have a fix for it. This issue has been occurrin...

Copyright and Disclaimer Notice

Copyright 2007 - 2017 DBA University, Inc. All Rights Reserved. No content of this website may be reprinted or otherwise reproduced without DBA University's permission. The posts and comments in this blog are on an "AS IS" basis without warranties. Always test your changes before pushing them to a real-time system !

Oracle is a registered trademark of Oracle Corporation and/or its affiliates .Other names may be trademarks of their respective owners.