Saturday, September 02, 2006

Drop a Target Database using RMAN

Starting with Oracle10g Release 1, Oracle provides a method
to completely drop a database with one simple statement. This
method makes use of the DROP DATABASE command in RMAN.

NOTE: Although this should be obvious from the title of the article,
the following will completely drop the target database including
all datafiles, controlfiles, redo log files, SPFILE, and if you
want, all backups and archived redo log files associated with the database. Consider yourself warned.

The DROP DATABASE Command

NOTE: Before the target database can be dropped, it must be mounted in EXCLUSIVE mode with RESTRICTED SESSION enabled.

Before dropping the database, let's take a look at the complete syntax for the RMAN DROP DATABASE command:
DROP DATABASE;
DROP DATABASE NOPROMPT;
DROP DATABASE INCLUDING BACKUPS;
DROP DATABASE INCLUDING BACKUPS NOPROMPT;
When "NOPROMPT" is specified, RMAN will not prompt for confirmation
before deleting the database. The default for RMAN is to prompt
for confirmation.

Restrictions and Usage Notes

  • This command can only be run from RMAN.
  • You must be connected to the target database from RMAN while
    the database is mounted in EXCLUSIVE mode with RESTRICTED SESSION enabled.

  • When using the "DROP DATABASE" command, RMAN drops the target
    database including the following files at the operating system level:

    • Datafiles
    • Online Redo Log Files
    • Controlfiles
    • SPFILE (if it exists)
  • When including the "INCLUDING BACKUPS" clause, RMAN will delete the
    files listed above as well as the following files at the operating system level:

    • Archive Redo Logs
    • Backup pieces generated by RMAN for the target database
  • When using the "DROP DATABASE" command with RMAN connected
    to a recovery catalog, RMAN will unregister the target database.

  • The "DROP DATABASE" command does not delete the following files:

    • init.ora (text version of the Oracle initialization file)
    • password file
  • If the operating system is Windows NT/2000, then it does not delete
    the Windows NT service for the target database instance.

DROP DATABASE EXAMPLE

In this article, I will be dropping a database named orcl. The orcl database is in archivelog mode and has several backups included in the Flash Recovery Area. I want to drop the target database along with all backup files.

$ sqlplus "/ as sysdba"

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 100664912 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> exit

$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Aug 7 17:33:25 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1123903688, not open)

RMAN> drop database including backups;

database name is "ORCL" and DBID is 1123903688

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

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
51 31 1 1 AVAILABLE DISK /u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_annnn_TAG20060807T164635_2fh9of4s_.bkp
52 32 1 1 AVAILABLE DISK /u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_nnndf_TAG20060807T164638_2fh9ogvf_.bkp
53 33 1 1 AVAILABLE DISK /u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_ncsnf_TAG20060807T164638_2fh9vzo0_.bkp
54 34 1 1 AVAILABLE DISK /u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_annnn_TAG20060807T165009_2fh9w2lm_.bkp
deleted backup piece
backup piece handle=/u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_annnn_TAG20060807T164635_2fh9of4s_.bkp recid=51 stamp=597861997
deleted backup piece
backup piece handle=/u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_nnndf_TAG20060807T164638_2fh9ogvf_.bkp recid=52 stamp=597861998
deleted backup piece
backup piece handle=/u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_ncsnf_TAG20060807T164638_2fh9vzo0_.bkp recid=53 stamp=597862207
deleted backup piece
backup piece handle=/u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_annnn_TAG20060807T165009_2fh9w2lm_.bkp recid=54 stamp=597862210
Deleted 4 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
specification does not match any archive log in the recovery catalog

database name is "ORCL" and DBID is 1123903688
database dropped