There are few ways, we can drop the database in oracle. This article is tested in oracle10gR2. Let us discuss these options here.
Option 1. We can use DBCA utility to drop the database. This approach will be a clean approach for dropping database. Since DBCA tool drop all the data files, control files, redolog files, spfile, password file and background dump trace files. This approach cleans up the tnsentry in tnsnames.ora file if we have tnsnames.ora file in server. It also deletes the entire folders relevant to that database. But this approach will not delete RMAN backup files, archived redo log files. If the database is running in RMAN and it is registered with catalog database, then DBCA utility will not unregister from catalog database automatically. So before we drop the database by using DBCA utility, we need to unregister the database by using RMAN UNREGISTER DATABASE command. If the database is using only control file for RMAN metadata, then this command is not necessary.
Option 2 In oracle10g, we have a command to drop the database. All the data files, control files, redo log files will be deleted automatically when we use drop database command in oracle10g. But it is not deleting the folders. It is not deleting dump files, alert log files, archived redo log files, RMAN backup files and password files relevant to the database. Also it is not cleaning the tnsentry in tnsnames.ora file if we have tnsnames.ora file in server. We need to clean these files and folders manually after we drop the database by using drop database command. If the database is running in RMAN with catalog database, we need to unregister the database manually from catalog before we drop the database. This option would not remove backup info in catalog DB.
Here are the steps to drop the database in oracle10g.
1. Connect as sys user
2. shutdown the database
3. Mount the database with restrict mode
4. Drop the database
5. Clean up the files manually(trace files, password file, folders, cleaning the tnsentry, archive redo log files, RMAN backup files)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict mount;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> drop database;
Database dropped.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
Option 3 When the database is running in RMAN, the database must be registered with catalog database. The above two options will not unregister the database from catalog DB. It will not remove the backup information from catalog database. If we use the above options, we need to unregister the database from catalog manually before we drop the database. Oracle10g introduced new feature that we can now use RMAN to drop a database and remove all its records from the RMAN CATALOG. Dropping database from RMAN has a additional benefit of dropping all backup copies and archived log files for the database. For further information pls also see Metalink Note: 251412.1
But still it is not deleting the dump files, alert log files, password file. It is not removing tnsentry in tnsnames.ora file if we have tnsnames.ora file in server. But it cleans data file, control file, redo log file, archive log file, RMAN backup files and removes backup info in CATALOG Database.
C:\>rman target / catalog rman/rman@catdb
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 20 15:11:25 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
RMAN> sql 'alter system enable restricted session';
sql statement: alter system enable restricted session
RMAN> drop database including backups;
database name is "ORCL" and DBID is 1215054467
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=156 devtype=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
36 34 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_01KF
EGUK_1_1_%S_%P
37 35 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_02KF
EGUN_1_1_%S_%P
54 49 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_03KF
EGVS_1_1_%S_%P
64 62 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-00
133 130 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-01
261 255 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-02
328 324 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_07KF
EQ6L_1_1_%S_%P
329 325 1 1 AVAILABLE DISK C:\RMANBACKUP\BACKUPORCL_DB_08KF
EQ7P_1_1_%S_%P
356 350 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\DB_1\DA
TABASE\C-1215054467-20090519-03
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_01KFEGUK_1_1_%S_%P recid=1 stamp
=687293397
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_02KFEGUN_1_1_%S_%P recid=2 stamp
=687293400
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_03KFEGVS_1_1_%S_%P recid=3 stamp
=687293438
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-00 recid=4 stamp=687293441
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-01 recid=5 stamp=687297981
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-02 recid=6 stamp=687301176
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_07KFEQ6L_1_1_%S_%P recid=7 stamp
=687302870
deleted backup piece
backup piece handle=C:\RMANBACKUP\BACKUPORCL_DB_08KFEQ7P_1_1_%S_%P recid=8 stamp
=687302906
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\C-1215054467-20090519
-03 recid=9 stamp=687302924
Deleted 9 objects
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
26 1 3 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_3_1_687273734.ARC
33 1 4 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_4_1_687273734.ARC
103 1 5 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_5_1_687273734.ARC
104 1 6 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_6_1_687273734.ARC
128 1 7 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_7_1_687273734.ARC
129 1 8 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_8_1_687273734.ARC
164 1 9 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_9_1_687273734.ARC
165 1 10 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_10_1_687273734.ARC
166 1 11 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_11_1_687273734.ARC
167 1 12 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_12_1_687273734.ARC
249 1 13 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_13_1_687273734.ARC
287 1 1 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_1_1_687301150.ARC
288 1 2 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_2_1_687301150.ARC
289 1 3 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_3_1_687301150.ARC
290 1 4 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_4_1_687301150.ARC
319 1 5 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_5_1_687301150.ARC
384 1 6 A 19-MAY-09 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\
LOG_6_1_687301150.ARC
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_6872
73734.ARC recid=1 stamp=687293394
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6872
73734.ARC recid=2 stamp=687293436
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_6872
73734.ARC recid=3 stamp=687297267
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_6_1_6872
73734.ARC recid=4 stamp=687297285
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_7_1_6872
73734.ARC recid=5 stamp=687298507
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_8_1_6872
73734.ARC recid=6 stamp=687298515
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_9_1_6872
73734.ARC recid=7 stamp=687300567
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_10_1_687
273734.ARC recid=8 stamp=687300576
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_11_1_687
273734.ARC recid=9 stamp=687300608
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_12_1_687
273734.ARC recid=10 stamp=687300618
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_13_1_687
273734.ARC recid=11 stamp=687301152
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_1_1_6873
01150.ARC recid=12 stamp=687301663
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_2_1_6873
01150.ARC recid=13 stamp=687301679
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_3_1_6873
01150.ARC recid=14 stamp=687301699
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_4_1_6873
01150.ARC recid=15 stamp=687301706
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_5_1_6873
01150.ARC recid=16 stamp=687302866
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCHIVE\LOG_6_1_6873
01150.ARC recid=17 stamp=687340879
Deleted 17 objects
database name is "ORCL" and DBID is 1215054467
database dropped
database name is "ORCL" and DBID is 1215054467
database unregistered from the recovery catalog
RMAN>
Conclusion: What is best choice to delete the database? Based on my experience, best choice would be, use DBCA to remove the Database. But before remove, unregister the database in CATALOG database if database is using RMAN with catalog.
In case, if you don't have DBCA, then we can go for removing DB in RMAN as below.
RMAN> drop database including backups;
After successfully dropped the database, we might need to remove dump files, password file manually.
Another option would be, using DROP DATABASE command. Again, you need to manually remove the files, backup info in CATALOG database.
No comments:
Post a Comment