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.
DROP DATABASE must be executed in RMAN while connected to the target database that is mounted and not open and started in
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 220.127.116.11.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
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
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>
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.