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

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016