In Oracle Database 10gR1 Oracle introduced the RMAN command DROP DATABSE. This one simple statement has the ability to completely remove a database including all RMAN backups with the optional INCLUDING BACKUPS clause.
DROP DATABASE removes all data files, control files, online redo logs. The DROP DATABASE command can optionally delete backups including backup sets, proxy copies, image copies and archive redo logs from the target database from all configured device types.
If the target database is connected to a recovery catalog at the time DROP DATABASE is issued the target database will be unregistered as well as drop delete any backups known to catalog. If the command is issued in NOCATALOG mode then any backups known to the catalog will not be dropped but they will not be in the target database control file either.
The DROP DATABASE must be executed in RMAN while connected to the target database that is mounted and not open and started in RESTRICT SESSION.
Below is a demonstration of the DROP DATABASE command that will drop the database and all backups associated with the database. The Database in the example was not registered with a recovery catalog.
Using RMAN connect to the database in which to drop (target database.) [oracle@odlinux ~]$ rman Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 27 09:58:45 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connect target / connected to target database: ODLIN11G (DBID=2070621120) RMAN>
Next shutdown the database and bring it backup in mount mode and RESTRICT SESSION.
RMAN> shutdown immediate using target database control file instead of recovery catalog database closed database dismounted Oracle instance shut down RMAN> startup mount; connected to target database (not started) Oracle instance started database mounted Total System Global Area 1653518336 bytes Fixed Size 2213896 bytes Variable Size 989857784 bytes Database Buffers 654311424 bytes Redo Buffers 7135232 bytes RMAN> sql 'alter system enable restricted session'; sql statement: alter system enable restricted session RMAN>
Now that the database is in both mounted and in RESTRICTED SESSION we can issue the DROP DATABASE INCLUDING BACKUPS command.
RMAN> drop database including backups; database name is "ODLIN11G" and DBID is 2070621120 Do you really want to drop all backups and the database (enter YES or NO)? yes
Note that the default behavior is to prompt for verification. If you would like to avoid the prompt you can include the optional NOPROMPT clause.
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK /u01/app/oracle/oradata/odlin11g/backup/02lm8hu7_1_1
2 2 1 1 AVAILABLE DISK /u01/app/oracle/oradata/odlin11g/backup/03lm8hu9_1_1
3 3 1 1 AVAILABLE DISK /u01/app/oracle/oradata/odlin11g/backup/04lm8i1i_1_1
4 4 1 1 AVAILABLE DISK /u01/app/oracle/oradata/odlin11g/backup/c-2070621120-20100825-00
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/odlin11g/backup/02lm8hu7_1_1 RECID=1 STAMP=727992263
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/odlin11g/backup/03lm8hu9_1_1 RECID=2 STAMP=727992265
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/odlin11g/backup/04lm8i1i_1_1 RECID=3 STAMP=727992370
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/odlin11g/backup/c-2070621120-20100825-00 RECID=4 STAMP=727992373
Deleted 4 objects
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ODLIN11G
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
3 1 77 A 25-AUG-10
Name: /u01/app/oracle/oradata/odlin11g/arch/1_77_727737088.dbf
4 1 78 A 27-AUG-10
Name: /u01/app/oracle/oradata/odlin11g/arch/1_78_727737088.dbf
deleted archived log
archived log file name=/u01/app/oracle/oradata/odlin11g/arch/1_77_727737088.dbf RECID=3 STAMP=728122132
deleted archived log
archived log file name=/u01/app/oracle/oradata/odlin11g/arch/1_78_727737088.dbf RECID=4 STAMP=728143634
Deleted 2 objects
database name is "ODLIN11G" and DBID is 2070621120
database dropped
RMAN>
While the DROP DATABASE command efficiently drops the database it does leave some remnants of the database on the systems that should be cleaned up for example: entries in the /etc/oratab, password and configuration files in the $ORACLE_HOME/dbs and the file location directory structure.