Drop Database using RMAN

Its very truly said “Learning is a Continuous Process“.Never the late,today i learned something new “Dropping Database using RMAN”.What was the reason for my learning!!!!!????

I had dropped the database using “drop database” command in SQL*Plus and later i had to manually delete all the archivelogs and backups of that particular database.It consumed my time.So, a friend of mine suggested using RMAN to drop the database,though little late.Immediately i started searching for it on google (don’t know what will happen if its down for even 1min) and got few good links.

The Drop Database command, makes a fast and efficient cleanup of all datafiles, redologs and control files in one command.It requires to mount the database in exclusive restricted mode.So even if you execute it on WRONG database(DO NOT TRY!!!!!), no problems…you can stay alive 🙂

But as said , this command cleaned up only the datafiles,redologs and control files.The archivelogs and the database backup (taken through RMAN) still remain and needs to be deleted manually.

So,lets try and see “DROPPING DATABASE USING RMAN”

There are 4 syntax available to drop the database using RMAN :-

1. DROP DATABASE;

2. DROP DATABASE NOPROMPT;

3. DROP DATABASE INCLUDING BACKUPS;

4. DROP DATABASE INCLUDING BACKUPS NOPROMPT;

When “NOPROMPT” is specified RMAN does not prompt for the confirmation before deleting the database.

The “DROP DATABASE” command deletes the

Datafiles
Logfiles
Controlfiles
Spfile

The ” DROP DATABASE INCLUDING BACKUPS ” command in addition to the above files also removes

Archivelogs
Backup pieces generated by RMAN

Here is a demo on a test database:-

Session 1 (DB INFO):-

23:11:48 SYS @ oracle >@db_info

 DBID NAME      LOG_MODE     VERSION_T OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS    FOR                           
---------- --------- ------------ --------- ---------- ---------------- -------------------- ---                           
1574601275 ORACLE    ARCHIVELOG   29-OCT-09 READ WRITE PRIMARY          SESSIONS ACTIVE      YES                           

Elapsed: 00:00:00.15

INSTANCE_NAME    INSTANCE_NUMBER HOST_NAME                        VERSION           STARTUP_T STATUS       DATABASE_STATUS
---------------- --------------- ---------------------------------------------------------------- ----------------- ---------                          
oracle                         1 ANAND                            10.2.0.4.0        18-FEB-10 OPEN         ACTIVE         

Elapsed: 00:00:00.01

NAME                      VALUE                            
-------------------- ----------                            
Fixed Size              1295896                            
Variable Size         142608872                            
Database Buffers       58720256                            
Redo Buffers            7090176                            

Elapsed: 00:00:00.03

Controlfile                  Status                        
------------------------------------------------------------ -------                       
D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL01.CTL                UNKNOWN                       
D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL02.CTL                UNKNOWN                       
D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL03.CTL                UNKNOWN                       

Elapsed: 00:00:00.01

Datafile                     Status  Enabled    Size (M)   
------------------------------------------------------------ ------- ---------- ----------
D:\ORACLE\PRODUCT\10.2.0\ORACLE\ANDY_01.DBF                  ONLINE  READ WRITE       10.2
D:\ORACLE\PRODUCT\10.2.0\ORACLE\EXAMPLE01.DBF                ONLINE  READ WRITE      102.4
D:\ORACLE\PRODUCT\10.2.0\ORACLE\LOGMNRTBS_01.DBF             ONLINE  READ WRITE       51.2
D:\ORACLE\PRODUCT\10.2.0\ORACLE\STREAMTBS_01.DBF             ONLINE  READ WRITE      102.4
D:\ORACLE\PRODUCT\10.2.0\ORACLE\SYSAUX01.DBF                 ONLINE  READ WRITE      307.2
D:\ORACLE\PRODUCT\10.2.0\ORACLE\SYSTEM01.DBF                 SYSTEM  READ WRITE      512.0
D:\ORACLE\PRODUCT\10.2.0\ORACLE\UNDOTBS01.DBF                ONLINE  READ WRITE       56.3
D:\ORACLE\PRODUCT\10.2.0\ORACLE\USERS01.DBF                  ONLINE  READ WRITE      142.0 

8 rows selected.

Elapsed: 00:00:00.18

 Group Logfile                      Status             
---------- ------------------------------------------------------------ -------            
 1 D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO01.LOG                   UNKNOWN            
 2 D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO02.LOG                   UNKNOWN             
 3 D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO03.LOG                   UNKNOWN            

Elapsed: 00:00:00.03
23:17:07 SYS @ oracle >select PIECE#,TAG,STATUS,START_TIME,COMPLETION_TIME,ELAPSED_SECONDS,BYTES,HANDLE from v$backup_piece where DELETED='NO';

 PIECE# TAG                              S START_TIM COMPLETIO ELAPSED_SECONDS      BYTES HANDLE                        
---------- -------------------------------- - --------- --------- --------------- ---------- --------------------          
 1 TAG20100217T232722               A 17-FEB-10 17-FEB-10             104  715161600 D:\RMAN\ORACLE_1              
 1 TAG20100217T234213               A 17-FEB-10 17-FEB-10              58  492507648 D:\RMAN\ARCH_ORACLE_1

23:22:30 SYS @ oracle >shu immediate;
23:27:42 SYS @ oracle >startup mount;

Session 2 (RMAN SESSION) :-

C:\Documents and Settings\user>rman target sys/xxxxxxx

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Feb 18 23:28:18 2010

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

connected to target database: ORACLE (DBID=1574601275, not open)

RMAN> drop database including backups;

database name is "ORACLE" and DBID is 1574601275
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of drop db & bck command at 02/18/2010 23:28:24
RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

As said earlier, Database has be in EXCLUSIVE RESTRICTED MODE.

Lets put the database in exclusive restricted mode from RMAN session only,

RMAN> sql 'alter system enable restricted session';

using target database control file instead of recovery catalog
sql statement: alter system enable restricted session

During this time the alert log updates with the below information :-

Thu Feb 18 23:28:43 2010
Stopping background process MMNL
Thu Feb 18 23:28:44 2010
Stopping background process MMON
Starting background process MMON
Starting background process MMNL
MMON started with pid=11, OS id=1740
Thu Feb 18 23:28:45 2010
ALTER SYSTEM enable restricted session;
MMNL started with pid=12, OS id=2204

Now,lets Drop the Database

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "ORACLE" and DBID is 1574601275

Do you really want to drop all backups and the database (enter YES or NO)? yes (BY DEFAULT ITS PROMPT)

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
18      18      1   1   AVAILABLE   DISK        D:\RMAN\ORACLE_1
19      19      1   1   AVAILABLE   DISK        D:\RMAN\ARCH_ORACLE_1
deleted backup piece
backup piece handle=D:\RMAN\ORACLE_1 recid=18 stamp=711242843
deleted backup piece
backup piece handle=D:\RMAN\ARCH_ORACLE_1 recid=19 stamp=711243739
Deleted 2 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
2       A 27-DEC-09       2754635    27-DEC-09       D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
202     1    132     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1
203     1    133     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1
204     1    134     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1
205     1    135     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1
206     1    136     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1
207     1    137     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1
208     1    138     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1
209     1    139     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1
210     1    140     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1
211     1    141     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1
212     1    142     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1
213     1    143     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1
214     1    144     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1
215     1    145     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1
216     1    146     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1
217     1    147     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1
218     1    148     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1
219     1    149     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1
220     1    150     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1
221     1    151     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1
222     1    152     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1
223     1    153     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1
224     1    154     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1
225     1    155     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1
226     1    156     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1
227     1    157     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1
228     1    158     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1
229     1    159     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1
230     1    160     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1
231     1    161     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1
232     1    162     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1
233     1    163     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1
235     1    164     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1
234     1    165     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1
236     1    166     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1
237     1    167     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1
238     1    168     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1
239     1    169     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1
240     1    170     A 27-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1
241     1    171     A 28-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1
242     1    172     A 30-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1
243     1    173     A 31-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1
244     1    174     A 01-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1
245     1    175     A 03-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1
246     1    176     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1
247     1    177     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1
248     1    178     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1
249     1    179     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1

deleted control file copy
control file copy filename=D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL recid=2 stamp=706679151
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1 recid=202 stamp=708474283
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1 recid=203 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1 recid=204 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1 recid=205 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1 recid=206 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1 recid=207 stamp=708477174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1 recid=208 stamp=708477177
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1 recid=209 stamp=708477957
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1 recid=210 stamp=708478193
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1 recid=211 stamp=708523128
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1 recid=212 stamp=708523129
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1 recid=213 stamp=708523194
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1 recid=214 stamp=708527072
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1 recid=215 stamp=708527201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1 recid=216 stamp=708527278
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1 recid=217 stamp=708527459
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1 recid=218 stamp=708527641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1 recid=219 stamp=708527711
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1 recid=220 stamp=708531955
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1 recid=221 stamp=708532250
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1 recid=222 stamp=708532331
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1 recid=223 stamp=708537657
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1 recid=224 stamp=708537715
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1 recid=225 stamp=708537936
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1 recid=226 stamp=708538012
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1 recid=227 stamp=708538174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1 recid=228 stamp=708538195
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1 recid=229 stamp=708538201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1 recid=230 stamp=708539593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1 recid=231 stamp=708555515
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1 recid=232 stamp=708555516
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1 recid=233 stamp=708555536
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1 recid=235 stamp=708648593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1 recid=234 stamp=708648592
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1 recid=236 stamp=708648853
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1 recid=237 stamp=709329114
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1 recid=238 stamp=709330932
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1 recid=239 stamp=709423232
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1 recid=240 stamp=709515047
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1 recid=241 stamp=709678641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1 recid=242 stamp=709728779
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1 recid=243 stamp=709857486
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1 recid=244 stamp=710029358
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1 recid=245 stamp=710118379
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1 recid=246 stamp=710118402
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1 recid=247 stamp=710118425
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1 recid=248 stamp=710118565
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1 recid=249 stamp=710248827
Deleted 49 objects

database name is "ORACLE" and DBID is 1574601275
database dropped

RMAN>

Alert Log has the following information :-

Thu Feb 18 23:39:22 2010
DROP DATABASE
Thu Feb 18 23:39:22 2010
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\SYSTEM01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\UNDOTBS01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\SYSAUX01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\USERS01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\EXAMPLE01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\STREAMTBS_01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\ANDY_01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\LOGMNRTBS_01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO01.LOG
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO02.LOG
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\REDO03.LOG
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\TEMP01.DBF
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE_BLK_CHG.LOG
Thu Feb 18 23:39:39 2010
Instance terminated by USER, pid = 3456
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL01.CTL
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL02.CTL
Deleted file D:\ORACLE\PRODUCT\10.2.0\ORACLE\CONTROL03.CTL
Completed: DROP DATABASE
Thu Feb 18 23:39:45 2010
Shutting down instance (abort)
License high water mark = 4

So,as it can been seen from the alert log and the rman session window that all the ” Datafiles,Redo Logfiles ,Controlfiles ,Archivelogs and the Backup pieces” are deleted.

Its cool, isn’t it 🙂

NOTE:- Starting with Oracle 10gR1 we can use RMAN to drop a database.

4 thoughts on “Drop Database using RMAN”

Pingback: Blogroll Report 12/02/2009 – 19/02/2010 « Coskan’s Approach to Oracle
www.rmanbackup.com says:

September 12, 2010 at 4:24 PM

Thank you for this precious information about RMAN Technology.

1. Anand says:
  
  September 13, 2010 at 10:52 AM
  
  Thanks for the comments. 🙂
  
  Regards,
  Anand
  
askraks says:

August 4, 2011 at 10:17 PM

Its an Nice article, can you add more information with dropping the RAC database and removing from OCR so that it will not try to resart the DB during the server boot;

srvctl remove instance -d BRNPSBR11 -i BRNPSBR11
srvctl remove instance -d BRNPSBR1 -i BRNPSBR11
srvctl remove database -d BRNPSBR1
srvctl remove servies -d BRNPSBR1 servies name
Hope this help you 🙂

Learn. Share. Repeat.

Drop Database using RMAN

So,lets try and see “DROPPING DATABASE USING RMAN”

Session 1 (DB INFO):-

Session 2 (RMAN SESSION) :-

4 thoughts on “Drop Database using RMAN”

Leave a Reply Cancel reply

So,lets try and see “DROPPING DATABASE USING RMAN”

Session 1 (DB INFO):-

Session 2 (RMAN SESSION) :-

Share this:

Like this:

Related

4 thoughts on “Drop Database using RMAN”

Leave a Reply Cancel reply