Oracle : How to delete a database with RMAN

Oracle : How to delete a database with RMAN

If you have an environment in which you have to constantly delete databases, or even if you just want to decommission one Database, the most secure way to remove the corresponding files instead of using the rm command is to use the RMAN command DROP DATABASE

If you are using a RAC environment, the first thing you have to do is set the cluster_database parameter to FALSE, once you have done that, shutdown the database

oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 22:30:21 2012

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

TESTDB1 >alter system set cluster_database=FALSE scope=spfile;

System altered.

TESTDB1 >exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ srvctl stop database -d TESTDB

oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ srvctl status database -d TESTDB
Instance TESTDB1 is not running on node servidor1
Instance TESTDB2 is not running on node servidor2

Now that you have shutdown your Database,make sure you have the correct environment variable set, you don’t want to drop a wrong database, and then we will open it in MOUNT mode and set a restricted session

oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ . $HOME/TESTDB

oracle@servidor1.oracleenespanol.blogspot.com [TESTDB1] /home/oracle
oracle $ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 11 22:36:08 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 2622255104 bytes

Fixed Size 2231232 bytes
Variable Size 637535296 bytes
Database Buffers 1962934272 bytes
Redo Buffers 19554304 bytes

RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

using target database control file instead of recovery catalog
sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION

The only thing pending after this, is to drop the database, I don’t like to do it with the NOPROMPT option, as this will be your last chance to repente before doing dropping it.

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "TESTDB" and DBID is 1743232258

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


allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=82 device type=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_3_788480833
2 2 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_4_788480833
3 3 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_5_788480834
4 4 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_2_788480832
5 5 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_6_788480835
6 6 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_7_788480837
7 7 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_8_788480838
8 8 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_9_788480839
9 9 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_1_788480832
10 10 1 1 AVAILABLE DISK /copy01/TESTDB/full/TESTDB_HOT_07012012_1_10_788480839
11 11 1 1 AVAILABLE DISK /copy01/TESTDB/control/TESTDB_07_01_2012_788480872.ctl
12 12 1 1 AVAILABLE DISK /copy01/TESTDB/control/c-1743232258-20120701-00
13 13 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_16_788480924
14 14 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_17_788480924
15 15 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_18_788480926
16 16 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_15_788480923
17 17 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_13_788480923
18 18 1 1 AVAILABLE DISK /copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_14_788480923
19 19 1 1 AVAILABLE DISK /copy01/TESTDB/control/c-1743232258-20120701-01
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_3_788480833 RECID=1 STAMP=788480834
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_4_788480833 RECID=2 STAMP=788480834
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_5_788480834 RECID=3 STAMP=788480836
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_2_788480832 RECID=4 STAMP=788480833
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_6_788480835 RECID=5 STAMP=788480838
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_7_788480837 RECID=6 STAMP=788480838
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_8_788480838 RECID=7 STAMP=788480839
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_9_788480839 RECID=8 STAMP=788480839
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_1_788480832 RECID=9 STAMP=788480832
deleted backup piece
backup piece handle=/copy01/TESTDB/full/TESTDB_HOT_07012012_1_10_788480839 RECID=10 STAMP=788480840
deleted backup piece
backup piece handle=/copy01/TESTDB/control/TESTDB_07_01_2012_788480872.ctl RECID=11 STAMP=788480875
deleted backup piece
backup piece handle=/copy01/TESTDB/control/c-1743232258-20120701-00 RECID=12 STAMP=788480881
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_16_788480924 RECID=13 STAMP=788480924
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_17_788480924 RECID=14 STAMP=788480924
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_18_788480926 RECID=15 STAMP=788480926
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_15_788480923 RECID=16 STAMP=788480924
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_13_788480923 RECID=17 STAMP=788480923
deleted backup piece
backup piece handle=/copy01/TESTDB/arch/TESTDB_ARCH_07012012_1_14_788480923 RECID=18 STAMP=788480923
deleted backup piece
backup piece handle=/copy01/TESTDB/control/c-1743232258-20120701-01 RECID=19 STAMP=788480947
Deleted 19 objects


released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=82 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 TESTDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
14 2 4 A 11-JUL-12
Name: +ARCH/TESTDB/archivelog/TESTDB_4_2_788478082.dbf

deleted archived log
archived log file name=+ARCH/TESTDB/archivelog/TESTDB_4_2_788478082.dbf RECID=14 STAMP=788481092
Deleted 1 objects


database name is "TESTDB" and DBID is 1743232258
database dropped

Update : A great reminder and precaution given by Leighton Nelson ( TW : @leight0nn) is to do a  backup … keep just as a safe guard before doing any of theses steps

Rene Antunez
[email protected]
No Comments

Sorry, the comment form is closed at this time.