HOW TO RECOVER DELETED DATAFILE IN ORACLE

Suppose you have accidentally removed a datafile from your production database? First thing, DON’T PANIC! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms.

This procedure will even work if your database is in NOARCHIVELOG mode.

First let’s create a tablespace.

SQL> create tablespace test datafile '/data/oracle/app/oracle/oradata/TESTDB/test1.dbf' size 10M;

Tablespace created.

SQL> conn test/test
 Connected.

SQL> create table test_table (key number(1));

Table created.

SQL> insert into test_table values (7);

1 row created.
  
SQL> commit;
 Commit complete.

SQL> select * from test_table;

KEY

----------
7

So we have a schema test with a simple table that was created in the test datafile (this was default tablespace of test user). Now we are going to remove this test datafile that is in the /data/oracle/app/oracle/oradata/TESTDB directory:

[oracle@localhost fd]$ rm –rf /data/oracle/app/oracle/oradata/TESTDB/test1.dbf

It’s really gone. Let’s test we can’t read from that table again:

[oracle@localhost fd]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 23 10:50:23 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> conn test/test

Connected.

SQL> select * from test_table;

KEY
----------
7

SQL> conn / as sysdba

Connected.

SQL> conn test/test

Connected.

SQL> select * from test_table;

select * from test_table
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 5: '/data/oracle/app/oracle/oradata/TESTDB/test1.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

For a simple table already buffered, you can still retrieve the data from cache, but as soon as you are forced to attempt a read from disk you are going to error. Oops! That’s not good. So what are the options? flashback database, was my first thought when this happened for real, but that was not available, backups? In this situation still you can recover you datafile by below steps.

 For ARCHIVELOG DATABASE:

First find a PID of a process we know would have a File Descriptor open for the file we have just removed. Database writer is a good candidate:

[oracle@NVMBD1BZY150D00 TESTDB]$ ps -ef |grep dbw
oracle 43643 1 0 13:39 ? 00:00:00 ora_dbw0_TESTDB
We could do an lsof on this PID to confirm this is the case:

[oracle@NVMBD1BZY150D00 TESTDB]$ /usr/sbin/lsof -p 43643

 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 43643 oracle rtd DIR 253,0 4096 2 /
oracle 43643 oracle DEL REG 0,4 31227916 /SYSV00000000
oracle 43643 oracle mem REG 253,0 5624 8323305 /lib64/libaio.so.1.0.1
oracle 43643 oracle mem CHR 1,5 4014 /dev/zero
oracle 43643 oracle mem REG 253,4 58949 14403358 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libnque11.so
oracle 43643 oracle mem REG 253,4 1544 14395587 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
oracle 43643 oracle mem REG 253,0 65928 8323102 /lib64/libnss_files-2.12.so

oracle 43643 oracle mem REG 253,0 22536 8323474 /lib64/libdl-2.12.so

oracle 43643 oracle mem REG 253,4 153574 21258376 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libocrutl11.so
oracle 43643 oracle mem REG 253,4 3319072 21258375 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libocrb11.so
oracle 43643 oracle mem REG 253,4 1590995 21258374 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libocr11.so
oracle 43643 oracle mem REG 253,4 12755 14402512 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libskgxn2.so
oracle 43643 oracle mem REG 253,4 17319952 21258371 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libhasgen11.so
oracle 43643 oracle mem REG 253,4 161764 14402492 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libdbcfg11.so
oracle 43643 oracle mem REG 253,4 228765 21258373 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libclsra11.so
oracle 43643 oracle mem REG 253,4 7955322 21258383 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.so
oracle 43643 oracle mem REG 253,4 1010297 14403515 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libskgxp11.so
oracle 43643 oracle mem REG 253,4 589359 21258391 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libcell11.so
oracle 43643 oracle mem REG 253,4 12259 14402220 /data/oracle/app/oracle/product/11.2.0/dbhome_1/lib/libodmd11.so
oracle 43643 oracle 0r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 1w CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 2w CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 3r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 4r CHR 1,5 0t0 4014 /dev/zero
oracle 43643 oracle 5r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 6r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 7u REG 253,4 1544 14395587 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
oracle 43643 oracle 8r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 9r CHR 1,3 0t0 4012 /dev/null
oracle 43643 oracle 10r CHR 1,5 0t0 4014 /dev/zero
oracle 43643 oracle 11w REG 253,4 2396 14680217 /data/oracle/app/oracle/admin/TESTDB/bdump/alert_TESTDB.log

oracle 43643 oracle 12u REG 253,4 1544 14395587 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat

oracle 43643 oracle 13r REG 253,4 1101312 32800880 /data/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb

oracle 43643 oracle 14r DIR 0,3 0 16643132 /proc/43643/fd

oracle 43643 oracle 15r CHR 1,5 0t0 4014 /dev/zero

oracle 43643 oracle 16u REG 253,4 1544 14395587 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat

oracle 43643 oracle 17uR REG 253,4 24 14395352 /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/lkTESTDB

oracle 43643 oracle 20u IPv6 16643553 0t0 UDP *:61571

oracle 43643 oracle 21r REG 253,4 1101312 32800880 /data/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb

oracle 43643 oracle 256u REG 253,4 9846784 14532752 /data/oracle/app/oracle/oradata/TESTDB/control01.ctl

oracle 43643 oracle 257u REG 253,4 9846784 14532753 /data/oracle/app/oracle/oradata/TESTDB/control02.ctl

oracle 43643 oracle 258uW REG 253,4 104865792 14532757 /data/oracle/app/oracle/oradata/TESTDB/system.dbf

oracle 43643 oracle 259uW REG 253,4 104865792 14532758 /data/oracle/app/oracle/oradata/TESTDB/user04.dbf

oracle 43643 oracle 260uW REG 253,4 104865792 14532759 /data/oracle/app/oracle/oradata/TESTDB/sysaux.dbf

oracle 43643 oracle 261uW REG 253,4 524296192 14532760 /data/oracle/app/oracle/oradata/TESTDB/undo.dbf

oracle 43643 oracle 262uW REG 253,4 1048584192 14532761 /data/oracle/app/oracle/oradata/TESTDB/temp1.dbf

oracle 43643 oracle 263uW REG 253,4 1073750016 14532816 /data/oracle/app/oracle/oradata/TESTDB/test1.dbf (deleted)

Missed out lots of /dev/shm entries in the above. We can see on the last line, the /tmp/test01.dbf datafile and we see it’s marked as deleted. We can also see it has a file descriptor 263. You can also see this from the following directory:

[oracle@NVMBD1BZY150D00 TESTDB]$ ls -ltar /proc/43643/fd/

total 0

dr-xr-xr-x. 7 oracle oinstall 0 Dec 12 13:39 ..
dr-x------. 2 oracle oinstall 0 Dec 12 13:39 .
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 9 -> /dev/null
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 8 -> /dev/null
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 7 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 6 -> /dev/null
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 5 -> /dev/null
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 4 -> /dev/zero
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 3 -> /dev/null
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 263 -> /data/oracle/app/oracle/oradata/TESTDB/test1.dbf (deleted)
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 262 -> /data/oracle/app/oracle/oradata/TESTDB/temp1.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 261 -> /data/oracle/app/oracle/oradata/TESTDB/undo.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 260 -> /data/oracle/app/oracle/oradata/TESTDB/sysaux.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 259 -> /data/oracle/app/oracle/oradata/TESTDB/user04.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 258 -> /data/oracle/app/oracle/oradata/TESTDB/system.dbf
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 257 -> /data/oracle/app/oracle/oradata/TESTDB/control02.ctl
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 256 -> /data/oracle/app/oracle/oradata/TESTDB/control01.ctl
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 21 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 20 -> socket:[16643553]
l-wx------. 1 oracle oinstall 64 Dec 12 15:20 2 -> /dev/null
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 17 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/lkTESTDB
lrwx------. 1 oracle oinstall 64 Dec 12 15:20 16 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
 lr-x------. 1 oracle oinstall 64 Dec 12 15:20 15 -> /dev/zero
 lr-x------. 1 oracle oinstall 64 Dec 12 15:20 14 -> /proc/43643/fd
 lr-x------. 1 oracle oinstall 64 Dec 12 15:20 13 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
 lrwx------. 1 oracle oinstall 64 Dec 12 15:20 12 -> /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/hc_TESTDB.dat
 l-wx------. 1 oracle oinstall 64 Dec 12 15:20 11 -> /data/oracle/app/oracle/admin/TESTDB/bdump/alert_TESTDB.log
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 10 -> /dev/zero
l-wx------. 1 oracle oinstall 64 Dec 12 15:20 1 -> /dev/null
lr-x------. 1 oracle oinstall 64 Dec 12 15:20 0 -> /dev/null


Where we have used the PID of the dbw process.

Again we see the file is marked as deleted and again it’s FD 263. Now this is the idea Frits mentioned and we can start actually recovering the data:

[oracle@NVMBD1BZY150D00 TESTDB]$ cd /proc/43643/fd/

From the /proc/43643/fd/ directory I ran a cat on the File Descriptor number and sent it to another filename test1_tmp.dbf. Now you have recovered the data, you can switch the tablespace to use this datafile:

SQL>alter database datafile '/data/oracle/app/oracle/oradata/TESTDB/test1.dbf' offline;
Database altered.

SQL> alter database rename file '/data/oracle/app/oracle/oradata/TESTDB/test1.dbf' to '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf';

Database altered.

SQL> alter database datafile '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf' online;

alter database datafile '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf'

SQL> recover datafile '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf'
Media recovery complete.

SQL> alter database datafile '/data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf' online;

Database altered.

SQL> select * from test.test_table;

KEY 
----------
7

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

How to Recover Drop tablespace

If a tablespace has been dropped, the Tablespace Point In Time Recovery (TSPITR) method cannot be used.

When you drop a tablespace, the controlfile will then no longer have any records of the tablespace which has been dropped. Attempts to use the RMAN RECOVER TABLESPACE command will return the RMAN error RMAN-06019 – “could not translate tablespace name” as shown below.

STEP 1: LET’S CREATE A TABLESPACE TEST_TBS FIRST:

SQL> create tablespace TEST_TBS DATAFILE '/u02/app/oracle/oradata/DEV/testtbs.dbf' size 200m;

 Tablespace created.

STEP 2: DELETE THIS TABLESPACE INCLUDING CONTENTS AND DATAFILES:

SQL> drop tablespace TEST_TBS including contents and datafiles;

Tablespace dropped.

STEP 3: NOW TRY TO RECOVER THE DROPPED TABLESPACE USING RMAN:

RMAN> recover tablespace TEST_TBS;

Starting recover at 18-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/18/2013 20:43:24
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name " TEST_TBS "

RMAN>

See the error - RMAN-20202: Tablespace not found in the recovery catalog

STEP 4: SO TO RECOVER THE DROPPED TABLESPACE, WE HAVE TWO OPTIONS:

1) Do a point in time recovery of the whole database until the time the tablespace was dropped.
2) Create a clone of the database from a valid backup, export the required tables from the tablespace which has been dropped, recreate the tablespace and then import the tables from the clone.

The first option will require the outage of the entire database and the entire database will be rolled back in tine in order to recover the tablespace.The second option can be peformed online, but we will need to factor in the disk space requirements to create a clone of the database from which the tablespace has been dropped.

STEP 5: LET’S CREATE THE SAME TABLESPACE AGAIN AND TAKE A FULL BACKUP OF DATABASE INCLUDING CONTROLFILE:

            SQL> create tablespace TEST_TBS DATAFILE  

      '/u02/app/oracle/oradata/DEV/testtbs.dbf' size 200m;

            Tablespace created.

STEP 6: NOW TAKE FULL DATABASE BACKUP:

RMAN> backup database format='/u02/rman_bkp/full_%u_%s_%p' plus archivelog;

Starting backup at 14-SEP-14

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=5 RECID=1 STAMP=858270813

input archived log thread=1 sequence=6 RECID=2 STAMP=858271143

input archived log thread=1 sequence=7 RECID=3 STAMP=858272123

input archived log thread=1 sequence=8 RECID=4 STAMP=858272525

input archived log thread=1 sequence=9 RECID=5 STAMP=858274111

channel ORA_DISK_1: starting piece 1 at 14-SEP-14

channel ORA_DISK_1: finished piece 1 at 14-SEP-14

piece handle=/u02/app/oracle/fast_recovery_area/DEV/backupset/2014_09_14/o1_mf_annnn_TAG20140914T172832_b1c0q897_.bkp tag=TAG20140914T172832 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

Finished backup at 14-SEP-14

Starting backup at 14-SEP-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u02/app/oracle/oradata/DEV/system01.dbf

input datafile file number=00002 name=/u02/app/oracle/oradata/DEV/sysaux01.dbf

input datafile file number=00005 name=/u02/app/oracle/oradata/DEV/testtbs.dbf

input datafile file number=00003 name=/u02/app/oracle/oradata/DEV/undotbs01.dbf

input datafile file number=00004 name=/u02/app/oracle/oradata/DEV/users01.dbf

channel ORA_DISK_1: starting piece 1 at 14-SEP-14

channel ORA_DISK_1: finished piece 1 at 14-SEP-14

piece handle=/u02/rman_bkp/full_09pigeab_9_1 tag=TAG20140914T172842 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:09:35

channel ORA_DISK_1: starting 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 14-SEP-14

channel ORA_DISK_1: finished piece 1 at 14-SEP-14

piece handle=/u02/rman_bkp/full_0apigesa_10_1 tag=TAG20140914T172842 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 14-SEP-14

Starting backup at 14-SEP-14

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=10 RECID=6 STAMP=858274706

channel ORA_DISK_1: starting piece 1 at 14-SEP-14

channel ORA_DISK_1: finished piece 1 at 14-SEP-14

piece handle=/u02/app/oracle/fast_recovery_area/DEV/backupset/2014_09_14/o1_mf_annnn_TAG20140914T173826_b1c19tt6_.bkp tag=TAG20140914T173826 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 14-SEP-14

RMAN> backup current controlfile;

Starting backup at 14-SEP-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 14-SEP-14

channel ORA_DISK_1: finished piece 1 at 14-SEP-14

piece handle=/u02/app/oracle/fast_recovery_area/DEV/backupset/2014_09_14/o1_mf_ncnnf_TAG20140914T174305_b1c1llw1_.bkp tag=TAG20140914T174305 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-SEP-14

STEP 7: DROP THE TABLESPACE TEST_TBS AGAIN AND WE WILL RECOVER IT AS BELOW:

SQL> drop tablespace TEST_TBS including contents and datafiles;

Tablespace dropped.

STEP 8: START THE DATABASE IN NOMOUNT STAGE AND RESTORE THE CONTROLFILE FROM BACKUP:
           

      SQL> startup nomount
             ORACLE instance started.

                Total System Global Area  631914496 bytes
                Fixed Size                  1338364 bytes
                Variable Size             394265604 bytes
                Database Buffers          230686720 bytes
                Redo Buffers                5623808 bytes

STEP 9: NEXT WE NEED TO RESTORE CONTROLFILE FROM BACKUP WHICH CONTAINS METADATA OF TABLESPACE TEST_TBS:

            [oracle@dev rman_bkp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Sep 14 18:02:51 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEV (not mounted)

RMAN> restore controlfile from '/u02/app/oracle/fast_recovery_area/DEV/backupset/2014_09_14/o1_mf_ncnnf_TAG20140914T174305_b1c1llw1_.bkp';

RMAN>

Starting restore at 14-SEP-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:08

output file name=/u02/app/oracle/oradata/DEV/control01.ctl

output file name=/u02/app/oracle/fast_recovery_area/DEV/control02.ctl

Finished restore at 14-SEP-14

Now restored controlfile contains the metadata of dropped Tablespace TEST_TBS.

            RMAN> report schema ;

Report of database schema for database with db_unique_name DEV

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    710      SYSTEM               ***     /u02/app/oracle/oradata/DEV/system01.dbf

2    560      SYSAUX               ***     /u02/app/oracle/oradata/DEV/sysaux01.dbf

3    85       UNDOTBS1             ***     /u02/app/oracle/oradata/DEV/undotbs01.dbf

4    5        USERS                ***     /u02/app/oracle/oradata/DEV/users01.dbf

5    200      TEST_TBS             ***     /u02/app/oracle/oradata/DEV/testtbs.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    29       TEMP                 32767       /u02/app/oracle/oradata/DEV/temp01.dbf

The alert log will also show the time when the tablespace was dropped. We can also see that a controlfile autobackup has taken place after the tablespace was dropped

Sun Sep 14 17:43:36 2014

drop tablespace test_tbs including contents and datafiles

Deleted file /u02/app/oracle/oradata/DEV/testtbs.dbf

Completed: drop tablespace test_tbs including contents and datafiles.

STEP 10: NOW THAT WE KNOW THE TIME THE TABLESPACE WAS DROPPED, WE CAN DO A POINT IN TIME   RECOVERY OF THE DATABASE IN ORDER TO RECOVER THE TABLESPACE WHICH HAS BEEN DROPPED:

            RMAN> run

{

set UNTIL TIME "to_date('2014-09-14 17:38:25','YYYY-MM-DD HH24:MI:SS')";

restore database;

recover database;

}2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 14-SEP-14

Starting implicit crosscheck backup at 14-SEP-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

Crosschecked 8 objects

Finished implicit crosscheck backup at 14-SEP-14

Starting implicit crosscheck copy at 14-SEP-14

using channel ORA_DISK_1

Finished implicit crosscheck copy at 14-SEP-14

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u02/app/oracle/fast_recovery_area/DEV/backupset/2014_09_14/o1_mf_ncnnf_TAG20140914T174305_b1c1llw1_.bkp

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/app/oracle/oradata/DEV/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/DEV/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/DEV/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/DEV/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/DEV/testtbs.dbf

channel ORA_DISK_1: reading from backup piece /u02/rman_bkp/full_09pigeab_9_1

channel ORA_DISK_1: piece handle=/u02/rman_bkp/full_09pigeab_9_1 tag=TAG20140914T172842

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:10:46

Finished restore at 14-SEP-14

Starting recover at 14-SEP-14

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file /u02/app/oracle/fast_recovery_area/DEV/archivelog/2014_09_14/o1_mf_1_10_b1c19t3z_.arc

archived log file name=/u02/app/oracle/fast_recovery_area/DEV/archivelog/2014_09_14/o1_mf_1_10_b1c19t3z_.arc thread=1 sequence=10

media recovery complete, elapsed time: 00:00:03

Finished recover at 14-SEP-14

RMAN>
RMAN> alter database open resetlogs;

database opened

STEP 11: NOW CHECK THE RESTORED TABLESPACE:

            SQL> select file_name,bytes from dba_data_files where

          tablespace_name='TEST_TBS’

FILE_NAME                                        LESPACE     STATUS

---------------------------------------------------------------------------------------------------------

/u02/app/oracle/oradata/DEV/testtbs.db             TEST_TBS   AVAILABLE 

     

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!