Search My Oracle Blog

Custom Search

16 June, 2013

DROP A Tablespace After a Backup


What happens if you drop a tablespace after it is backed up ?

Here's the backup :

RMAN> backup as compressed backupset database;

Starting backup at 16-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf
channel ORA_DISK_1: starting piece 1 at 16-JUN-13
channel ORA_DISK_1: finished piece 1 at 16-JUN-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_nnndf_TAG20130616T080419_8vvnzn37_.bkp tag=TAG20130616T080419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:36
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-JUN-13
channel ORA_DISK_1: finished piece 1 at 16-JUN-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_ncsnf_TAG20130616T080419_8vvo4k8w_.bkp tag=TAG20130616T080419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-JUN-13

RMAN>

Here's the subsequent DROP TABLESPACE :

SQL> drop tablespace APEX_2614203650434107 including contents and datafiles;

Tablespace dropped.

SQL> 

What happens when I try to LIST the BACKUP of the datafile / tablespace ?

RMAN> list backup of datafile 10;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 06/16/2013 08:07:44
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 10

RMAN> 
RMAN> list backup of tablespace APEX_2614203650434107;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 06/16/2013 08:12:12
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "APEX_2614203650434107"

RMAN> 


What does a full LIST BACKUP OF DATABASE show ?
RMAN> list backup of database ;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    799.58M    DISK        00:02:30     16-JUN-13      
        BP Key: 4   Status: AVAILABLE  Compressed: YES  Tag: TAG20130616T080419
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_16/o1_mf_nnndf_TAG20130616T080419_8vvnzn37_.bkp
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/system01.dbf
  2       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/users01.dbf
  5       Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  10      Full 14093203   16-JUN-13 
  11      Full 14093203   16-JUN-13 /home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_hemant_8pnowslc_.dbf

RMAN> 

Datafile 10 appears as a NULL entry. It cannot be restored as it no longer belongs to the database.

A RESTORE obviously fails :
RMAN> restore datafile 10;

Starting restore at 16-JUN-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/16/2013 08:15:32
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 10

RMAN> 

So, there you have it.  Once a datafile doesn't belong to the database it cannot be restored.

.
.
.

2 comments:

Anju Garg said...

The tablespace backup is not listed as current controlfile does not have its information. Tablespace can be recovered after restoring a controlfile from the time when tablespace existed. Incomplete recovery will have to be made till the point in time when tablespace existed.

Hemant K Chitale said...

Anju,
You will either have to do
a) Incomplete Recovery of the whole database -- meaning that you lose changes that may have been done to other tablespaces !! -- generally not acceptable !
OR
b) TSPITR using an auxiliary instance


Hemant K Chitale

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com