Most of the time we generally use DBCA to delete the database because it is easy and simple . But , there are some scenario's where we have to drop database without using the graphics i,e, without DBCA . In such case , we can delete or drop the database either by manually or by using sql*plus or RMAN prompt . As compare to sql*plus , rman is much more effective because it consume less time and secondly we can delete the archivelogs and backups also . Starting with Oracle 10gR1 onwards, we can drop a database and remove all its records from the rman catalog .
There are basically 4 syntax available to drop the database using RMAN
1.) Drop Database : This command deletes the datafiles, logfiles, Controlfiles and Spfile. If we do not want to delete backups then we can use this command.
2.) Drop Database Noprompt : When “NOPROMPT” is specified RMAN does not prompt for the confirmation before deleting the database. It delete the above files as in case of drop database.
3.)DROP DATABASE INCLUDING BACKUPS : This command delete the datafiles, ogfiles,Controlfiles and Spfile plus the archivelogs and backup pieces generated by RMAN.
4.) DROP DATABASE INCLUDING BACKUPS NOPROMPT : When “NOPROMPT” is specified RMAN does not prompt for the confirmation before deleting the database.
Let's have a look on the following steps to drop the Database using RMAN
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
C:\> rman target /
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 defaults it prompts)
allocated channel: ORA_DISK_1channel 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.
Enjoy JJJ
2 comments:
Good Post
I used this Command on SQL , but never try this on RMAN.
Thanks for Sharing
Good Post
I try this on SQL but never try this on RMAN.
Thanks for Sharing
Post a Comment