Dropping a Database via RMAN

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


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


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


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.

Leave a Reply

Your email address will not be published. Required fields are marked *