28 | 03 | 2024
Latest Articles
Popular Articles

RMAN

Drop a database with RMAN

User Rating:  / 0
PoorBest 

Drop a Database with RMAN

 

When we wanna get rid of a database - which is rarely the case - we can in an easy way

1. remove the database, the database files
2. remove the associated backups

The below procedure is easy, I assume you are aware what you are doing with commands like drop database including backups.

SQL> shutdown immediate;

ORACLE instance shut down.

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2045912 bytes
Variable Size             117442600 bytes
Database Buffers          293601280 bytes
Redo Buffers                6340608 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.


Let us check whether it is really the database we wanna drop.

SQL> show parameter db_name


db_name      string        bk

 

oracle@myhost1:/mnt/app/oracle/OraHome_2/dbs $ rman target /


Recovery Manager: Release 10.2.0.3.0 - Production on Mon Mar 17 15:03:23 2008

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

connected to target database: BK (DBID=589716967, not open)

RMAN> drop database including backups;

database name is "BK" and DBID is 589716967

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=540 devtype=DISK

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=540 devtype=DISK
specification does not match any archive log in the recovery catalog

database name is "BK" and DBID is 589716967
database dropped


This is an extract from the alert file. Note that one of the advantages of OMF - Oracle Managed Files - is that they are automatically removed. It can be wise to manually delete some  files  referring to the removed db in $ORACLE_HOME/dbs ( like the passwordfile ) and in the /etc/oratab file

ALTER SYSTEM enable restricted session;

Mon Mar 17 15:04:06 2008
DROP DATABASE
Mon Mar 17 15:04:06 2008
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_system_3xwhjd07_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_undotbs1_3xwhjd88_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_sysaux_3xwhjdf6_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_users_3xwhjdor_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_ts_my_db_a_3xwhjdtg_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_ts_my_db_l_3xwhjdyr_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_ts_my_db_l_3xwhjfc9_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_ts_my_db_l_3xwhjg2c_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_ts_my_db_l_3xwhlomx_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_ts_my_db_l_3xwhvjy0_.dbf
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_ts_my_db_l_3xwhvko2_.dbf
Deleted Oracle managed file /opt/oracle/BK/onlinelog/o1_mf_4_3xwk1239_.log
Deleted Oracle managed file /opt/oradata/BK/onlinelog/o1_mf_4_3xwk1khh_.log
Deleted Oracle managed file /opt/oracle/BK/onlinelog/o1_mf_5_3xwk1ypf_.log
Deleted Oracle managed file /opt/oradata/BK/onlinelog/o1_mf_5_3xwk2fv0_.log
Deleted Oracle managed file /opt/oracle/BK/onlinelog/o1_mf_6_3xwk2vqc_.log
Deleted Oracle managed file /opt/oradata/BK/onlinelog/o1_mf_6_3xwk3c23_.log
Deleted Oracle managed file /opt/oracle/BK/onlinelog/o1_mf_7_3xwk3vx0_.log
Deleted Oracle managed file /opt/oradata/BK/onlinelog/o1_mf_7_3xwk4cko_.log
Deleted Oracle managed file /opt/oracle/BK/onlinelog/o1_mf_8_3xwk4pdk_.log
Deleted Oracle managed file /opt/oradata/BK/onlinelog/o1_mf_8_3xwk52o1_.log
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_temp_3xwk5mm4_.tmp
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_temp01_3xwk5n1y_.t
mp
Deleted Oracle managed file /mnt/oradata/bk/BK/datafile/o1_mf_temp02_3xwk5nft_.tmp
Termination issued to instance processes. Waiting for the processes to exit
Mon Mar 17 15:04:16 2008
Instance termination failed to kill one or more processes
Instance terminated by USER, pid = 15817
Deleted Oracle managed file /opt/oracle/BK/controlfile/o1_mf_3xwk0q83_.ctl
Deleted Oracle managed file /opt/oradata/BK/controlfile/o1_mf_3xwk0r1s_.ctl
Completed: DROP DATABASE
Mon Mar 17 15:04:21 2008
Shutting down instance (abort)
License high water mark = 3