DBA Tips Archive for Oracle

  


Drop a Target Database using RMAN

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. The DROP DATABASE Command
  3. DROP DATABASE Example



Overview

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.

  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

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



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


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 18-Sep-2010 17:49:48 EDT
Page Count: 9015