Skip navigation

The following has been tested running Oracle Database 11.2.0.1 Enterprise Edition under Oracle Linux 5.11.

 

After dropping a tablespace, the RMAN metadata in the controlfile no longer shows the corresponding datafiles:

 

RMAN> list backup;
  6      Full 2302982    14-MAR-16 /u02/oradata/ORCL2/datafile/o1_mf_mydata_cgfy38bq_.dbf

SQL> drop tablespace mydata including contents;

Tablespace dropped.

RMAN> list backup;
  6      Full 2302982    14-MAR-16



 

When you perform a DBPITR using the current controlfile, the tablespace will exist in the database data dictionary, but the datafile will be missing. Also the RMAN metadata will show the datafile as missing.

 

SQL> select * from dba_data_files;

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00006
     6 MYDATA                     

RMAN> list backup;
  6       Full 2303502    14-MAR-16 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00006



 

In order to perform a point in time recovery after dropping a tablespace, the controlfile prior to dropping the tablespace must be restored as well. Note: you cannot use UNTIL SCN when restoring a controlfile from autobackup, but you can use UNTIL TIME.

 

Here is an example demonstrating the complete process:

 

$ export NLS_DATE_FORMAT="dd-MON-YYYY hh24:mi:ss"

RMAN> list backup;
  Control File Included: Ckp SCN: 2303588      Ckp time: 14-MAR-2016 18:50:15
  Control File Included: Ckp SCN: 2303622      Ckp time: 14-MAR-2016 18:56:43



 

RMAN> run {
2> shutdown abort
3> startup nomount
4> restore until time "to_date('2016-03-14:18:50:15','YYYY-MM-DD:HH24:MI:SS')"
5> controlfile from autobackup;
6> startup mount
7> set until scn 2303588;
8> restore database;
9> recover database;
10> alter database open resetlogs;
}

using target database control file instead of recovery catalog
Oracle instance shut down

connected to target database (not started)
Oracle instance started

Total System Global Area     634679296 bytes

Fixed Size                     2255912 bytes
Variable Size                469763032 bytes
Database Buffers             155189248 bytes
Redo Buffers                   7471104 bytes

Starting restore at 14-MAR-2016 19:41:22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=246 device type=DISK

recovery area destination: /u03/fast_recovery_area
database name (or database unique name) used for search: ORCL2
channel ORA_DISK_1: AUTOBACKUP /u03/fast_recovery_area/ORCL2/autobackup/2016_03_14/o1_mf_s_906490215_cgfygqyk_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160314
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u03/fast_recovery_area/ORCL2/autobackup/2016_03_14/o1_mf_s_906490215_cgfygqyk_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u02/oradata/ORCL2/controlfile/o1_mf_b5o6bpbx_.ctl
output file name=/u03/fast_recovery_area/ORCL2/controlfile/o1_mf_b5o6bpcy_.ctl
Finished restore at 14-MAR-2016 19:41:23

database is already started
database mounted
released channel: ORA_DISK_1

executing command: SET until clause

Starting restore at 14-MAR-2016 19:41:28
Starting implicit crosscheck backup at 14-MAR-2016 19:41:28
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=246 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 14-MAR-2016 19:41:29

Starting implicit crosscheck copy at 14-MAR-2016 19:41:29
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 14-MAR-2016 19:41:29

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/fast_recovery_area/ORCL2/autobackup/2015_10_02/o1_mf_s_892064878_c0xjtg55_.bkp
File Name: /u03/fast_recovery_area/ORCL2/autobackup/2015_10_02/o1_mf_s_892063593_c0xhl9wh_.bkp
File Name: /u03/fast_recovery_area/ORCL2/autobackup/2016_03_14/o1_mf_s_906490603_cgfytw0o_.bkp
File Name: /u03/fast_recovery_area/ORCL2/autobackup/2016_03_14/o1_mf_s_906493075_cgg183br_.bkp
File Name: /u03/fast_recovery_area/ORCL2/autobackup/2016_03_14/o1_mf_s_906490215_cgfygqyk_.bkp
File Name: /u03/fast_recovery_area/ORCL2/archivelog/2016_03_14/o1_mf_1_2_cgfytpnq_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/oradata/ORCL2/datafile/o1_mf_system_c0xlc2mp_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/oradata/ORCL2/datafile/o1_mf_sysaux_c0xlc2mr_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/ORCL2/datafile/o1_mf_undotbs1_c0xlc2mz_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/ORCL2/datafile/o1_mf_users_c0xlc2n2_.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/ORCL2/datafile/o1_mf_example_c0xlc2mv_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u02/oradata/ORCL2/datafile/o1_mf_mydata_cgfydmv4_.dbf
channel ORA_DISK_1: reading from backup piece /u03/fast_recovery_area/ORCL2/backupset/2016_03_14/o1_mf_nnndf_TAG20160314T184950_cgfyfytp_.bkp
channel ORA_DISK_1: piece handle=/u03/fast_recovery_area/ORCL2/backupset/2016_03_14/o1_mf_nnndf_TAG20160314T184950_cgfyfytp_.bkp tag=TAG20160314T184950
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 14-MAR-2016 19:41:55

Starting recover at 14-MAR-2016 19:41:55
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u03/fast_recovery_area/ORCL2/archivelog/2016_03_14/o1_mf_1_2_cgfytpnq_.arc
archived log file name=/u03/fast_recovery_area/ORCL2/archivelog/2016_03_14/o1_mf_1_2_cgfytpnq_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-MAR-2016 19:41:56

database opened



 

SQL> select * from dba_data_files;

/u02/oradata/ORCL2/datafile/o1_mf_mydata_cgg1gt96_.dbf
     6 MYDATA               104857600      12800 AVAILABLE
       6 YES 3.4360E+10    4194302          12800  103809024         12672
ONLINE