Wednesday, April 05, 2006

DROP DATABASE INCLUDING BACKUPS with RMAN in Oracle 10g Release 2

"In the old times" it could be a problem to get rid of all the Backup information in the RMAN CATALOG after you had deleted a database with dbca for instance.
You had to manually remove all the records from the catalog.

Starting with Oracle 10gR1 we can now use RMAN to drop a database and remove all its records from the RMAN CATALOG.

Here is a demo how it works:
$ rman target / catalog rman_user/oracle@hugo
RMAN> startup mount
RMAN> drop database including backups;

database name is "ORCL" and DBID is 1114494726
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of drop db & bck command at 04/05/2006 07:00:59
RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

RMAN> sql 'alter system enable restricted session';
RMAN> drop database including backups;

database name is "ORCL" and DBID is 1114494726
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=157 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1402 1399 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586867060_234d2o9y_.bkp
1872 1858 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/backupset
/2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrj9_.bkp
1873 1859 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzs0w_.bkp
1874 1860 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrpt_.bkp
1875 1865 1 1 AVAILABLE DISK /u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586886037_234ymxfc_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/autobackup/
2006_04_04/o1_mf_s_586867060_234d2o9y_.bkp recid=38 stamp=586867060
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrj9_.bkp recid=48 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzs0w_.bkp recid=49 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrpt_.bkp recid=50 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586886037_234ymxfc_.bkp recid=55 stamp=586886044
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/backupset/
2006_04_04/o1_mf_nnndf_TAG20060404T150910_234vzrpt_.bkp recid=50 stamp=586883352
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/orcl/autobackup/
2006_04_04/o1_mf_s_586886037_234ymxfc_.bkp recid=55 stamp=586886044
Deleted 5 objects
...
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - ------------------- ---------- ------------------- ----
1228 5 A 04-04-2006:10:34:35 950407 04-04-2006:10:32:56
/u01/app/oracle/oradata/orcl/example01.dbf

List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - ------------------- ----
1557 1 6 A 04-04-2006:11:02:00 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_6_234gj4c1_.arc
1558 1 7 A 04-04-2006:11:19:00 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_7_234gs7rf_.arc
1559 1 8 A 04-04-2006:11:23:51 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_8_234hdf3t_.arc
1560 1 1 A 04-04-2006:11:34:05 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_1_234hpx9w_.arc
1561 1 2 A 04-04-2006:11:39:40 /u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_2_234hqjto_.arc
...
deleted archive log
archive log filename=/u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_6_234gj4c1_.arc recid=46 stamp=586869540
deleted archive log
archive log filename=/u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_7_234gs7rf_.arc recid=47 stamp=586869831
deleted archive log
archive log filename=/u01/app/oracle/oradata/orcl/archivelog/
2006_04_04/o1_mf_1_8_234hdf3t_.arc recid=48 stamp=586870445
deleted archive log
...
datafile copy filename=/u01/app/oracle/oradata/orcl/example01.dbf
recid=11 stamp=586866875
Deleted 1 objects
...
database name is "ORCL" and DBID is 1114494726
database dropped
database name is "ORCL" and DBID is 1114494726
database unregistered from the recovery catalog
RMAN> exit


Rman will not delete the entry in the /etc/oratab, so we have to do this either by editing /etc/oratab or by using dbca to delete the service





For further information pls also see Metalink Note: 251412.1

0 Comments:

Post a Comment

<< Home