Tablespace Point in time recovery(TSPITR)

How TSPITR Works With an RMAN-Managed Auxiliary Instance

RMAN performs the following steps:

1. If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
2. Takes the tablespaces to be recovered offline in the target database
3. Restores a backup controlfile from a point in time before the target time to the auxiliary instance
4. Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance. Files are restored to the AUXILIARY DESTINATION specified in RMAN script
4. Recovers the restored datafiles in the auxiliary instance to the specified time
6. Opens the auxiliary database with the RESETLOGS option
7. Exports the dictionary metadata about objects in the recovered tablespaces to the target database
8. Shuts down the auxiliary instance
9. Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
10. Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
11. Deletes all auxiliary set files.

At that point the TSPITR process is complete. The recovery set datafiles are returned to their contents at the specified point in time, and belong to the target database.

Lets simulate a test case.

I created a user rohit whose default tablespace is TEST. 

I'll create a table by user rohit and will insert values into that table. I'll also create a table by user scott  and will insert data into that table to check whether there would be any impact on other's schema data in the process of TSPITR.

SQL> conn rohit/rohit
Connected.
SQL> select count(*) from test;
  COUNT(*)
----------
     14014

SQL> begin
  2  for i in 1..10 loop
  3  insert into test
  4  select * from test;
  5  end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> select count(*) from test;
  COUNT(*)
----------
  14350336

SQL> conn scott/tiger
Connected.
SQL> select count(*) from test;
  COUNT(*)
----------
     11200

SQL> begin
  2  for i in 1..3 loop
  3  insert into test
  4  select * from test;
  5  end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed.

SQL> select count(*) from test;
  COUNT(*)
----------
     89600

RMAN> backup current controlfile;

Starting backup at 26-JUN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=151 device type=DISK

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 26-JUN-13

channel ORA_DISK_1: finished piece 1 at 26-JUN-13

piece handle=/u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_26/o1_mf_ncnnf_TAG20130626T193928_8woxjstd_.bkp tag=TAG20130626T193928 comment=NONE

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

Finished backup at 26-JUN-13

Starting Control File and SPFILE Autobackup at 26-JUN-13

piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_26/o1_mf_s_819142771_8woxjvh4_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 26-JUN-13

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name DB1

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

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

1    770      SYSTEM               ***     /u01/app/oracle/oradata/db1/system01.dbf

2    900      SYSAUX               ***     /u01/app/oracle/oradata/db1/sysaux01.dbf

3    700      TEST                 ***     /u01/app/oracle/oradata/test01.dbf

4    140      USERS                ***     /u01/app/oracle/oradata/db1/users01.dbf

5    446      UNDOTBS2             ***     /u01/app/oracle/oradata/db1/undotbs02.dbf

List of Temporary Files

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

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

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

1    1        TEMP1                1           /u01/app/oracle/oradata/db1/temp02.dbf

2    500      TEMP1                500         /u01/app/oracle/oradata/db1/temp03.dbf

Now Lets drop the tablespace.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

Excerpts from alert log.

Wed Jun 26 19:47:44 2013

drop tablespace test including contents and datafiles

Deleted file /u01/app/oracle/oradata/test01.dbf

Completed: drop tablespace test including contents and datafiles

Wed Jun 26 19:48:04 2013

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name DB1

List of Permanent Datafiles

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

File Size(MB) Tablespace           RB segs Datafile Name

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

1    770      SYSTEM               ***     /u01/app/oracle/oradata/db1/system01.dbf

2    900      SYSAUX               ***     /u01/app/oracle/oradata/db1/sysaux01.dbf

4    140      USERS                ***     /u01/app/oracle/oradata/db1/users01.dbf

5    446      UNDOTBS2             ***     /u01/app/oracle/oradata/db1/undotbs02.dbf

List of Temporary Files

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

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

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

1    1        TEMP1                1           /u01/app/oracle/oradata/db1/temp02.dbf

2    500      TEMP1                500         /u01/app/oracle/oradata/db1/temp03.dbf

At this time Tablespace 'TEST' is completely lost, lets Recover that tablespace through RMAN.

RMAN> run

2> {

3> recover tablespace "TEST"

4> until time "to_date('26-06-2013:19:47:44','DD-MM-YYYY:HH24:MI:SS')"

5> auxiliary destination '/u01/aux'; <<-- prefer the enough disk space location for Aux Loc-->>

6> }

Starting recover at 26-JUN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=153 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS2

Creating automatic instance, with SID='khDn'

initialization parameters used for automatic instance:

db_name=DB1

db_unique_name=khDn_tspitr_DB1

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/u01/aux

log_archive_dest_1='location=/u01/aux'

#No auxiliary parameter file used

starting up automatic instance DB1

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1344512 bytes

Variable Size                100666368 bytes

Database Buffers             184549376 bytes

Redo Buffers                   6373376 bytes

Automatic instance created

List of tablespaces that have been dropped from the target database:

Tablespace TEST

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('26-06-2013:19:47:44','DD-MM-YYYY:HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 26-JUN-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=59 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_26/o1_mf_s_819142771_8woxjvh4_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_26/o1_mf_s_819142771_8woxjvh4_.bkp tag=TAG20130626T193931

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/aux/DB1/controlfile/o1_mf_8wozzwpf_.ctl

Finished restore at 26-JUN-13

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('26-06-2013:19:47:44','DD-MM-YYYY:HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  5 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

set newname for clone tempfile  2 to new;

set newname for datafile  3 to

 "/u01/app/oracle/oradata/test01.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 5, 2, 3;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/aux/DB1/datafile/o1_mf_temp1_%u_.tmp in control file

renamed tempfile 2 to /u01/aux/DB1/datafile/o1_mf_temp1_%u_.tmp in control file

Starting restore at 26-JUN-13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/aux/DB1/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/aux/DB1/datafile/o1_mf_undotbs2_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/aux/DB1/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_26/o1_mf_nnndf_TAG20130626T110649_8wnzhl9f_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_26/o1_mf_nnndf_TAG20130626T110649_8wnzhl9f_.bkp tag=TAG20130626T110649

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:35

Finished restore at 26-JUN-13

datafile 1 switched to datafile copy

input datafile copy RECID=14 STAMP=819145404 file name=/u01/aux/DB1/datafile/o1_mf_system_8wp004y5_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=15 STAMP=819145404 file name=/u01/aux/DB1/datafile/o1_mf_undotbs2_8wp004y7_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=16 STAMP=819145404 file name=/u01/aux/DB1/datafile/o1_mf_sysaux_8wp004xo_.dbf

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('26-06-2013:19:47:44','DD-MM-YYYY:HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  5 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  3 online";

# recover and open resetlogs

recover clone database tablespace  "TEST", "SYSTEM", "UNDOTBS2", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  3 online

Starting recover at 26-JUN-13

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_13_8wnzm5fg_.arc

archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_14_8woglwgv_.arc

archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_15_8wotoxo1_.arc

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_16_8wotp5k2_.arc

archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_17_8wotqhf4_.arc

archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_18_8wotsglc_.arc

archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_19_8wottg7z_.arc

archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_20_8wottvv4_.arc

archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_21_8wotznyo_.arc

archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_22_8wov2xbp_.arc

archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_23_8wow69q7_.arc

archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_24_8wow6jo2_.arc

archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_25_8wow6tk5_.arc

archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_26_8wow7yjt_.arc

archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_27_8wow8o1k_.arc

archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_28_8wow903f_.arc

archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_29_8wowb3yn_.arc

archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_30_8wowbx62_.arc

archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_31_8wowd6xy_.arc

archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_32_8wowg8pd_.arc

archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_33_8wowhfhy_.arc

archived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_34_8wowjrwc_.arc

archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_35_8wowl6vg_.arc

archived log for thread 1 with sequence 36 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_36_8wowmftx_.arc

archived log for thread 1 with sequence 37 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_37_8wownbqf_.arc

archived log for thread 1 with sequence 38 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_38_8wowopqd_.arc

archived log for thread 1 with sequence 39 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_39_8wowq3fd_.arc

archived log for thread 1 with sequence 40 is already on disk as file /u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_40_8wp002hk_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_13_8wnzm5fg_.arc thread=1 sequence=13

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_14_8woglwgv_.arc thread=1 sequence=14

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_15_8wotoxo1_.arc thread=1 sequence=15

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_16_8wotp5k2_.arc thread=1 sequence=16

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_17_8wotqhf4_.arc thread=1 sequence=17

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_18_8wotsglc_.arc thread=1 sequence=18

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_19_8wottg7z_.arc thread=1 sequence=19

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_20_8wottvv4_.arc thread=1 sequence=20

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_21_8wotznyo_.arc thread=1 sequence=21

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_22_8wov2xbp_.arc thread=1 sequence=22

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_23_8wow69q7_.arc thread=1 sequence=23

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_24_8wow6jo2_.arc thread=1 sequence=24

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_25_8wow6tk5_.arc thread=1 sequence=25

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_26_8wow7yjt_.arc thread=1 sequence=26

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_27_8wow8o1k_.arc thread=1 sequence=27

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_28_8wow903f_.arc thread=1 sequence=28

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_29_8wowb3yn_.arc thread=1 sequence=29

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_30_8wowbx62_.arc thread=1 sequence=30

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_31_8wowd6xy_.arc thread=1 sequence=31

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_32_8wowg8pd_.arc thread=1 sequence=32

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_33_8wowhfhy_.arc thread=1 sequence=33

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_34_8wowjrwc_.arc thread=1 sequence=34

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_35_8wowl6vg_.arc thread=1 sequence=35

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_36_8wowmftx_.arc thread=1 sequence=36

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_37_8wownbqf_.arc thread=1 sequence=37

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_38_8wowopqd_.arc thread=1 sequence=38

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_39_8wowq3fd_.arc thread=1 sequence=39

archived log file name=/u01/app/oracle/fast_recovery_area/DB1/archivelog/2013_06_26/o1_mf_1_40_8wp002hk_.arc thread=1 sequence=40

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

Finished recover at 26-JUN-13

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  "TEST" read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/aux''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/aux''";

}

executing Memory Script

sql statement: alter tablespace  "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_khDn":

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   EXPDP> Master table "SYS"."TSPITR_EXP_khDn" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_khDn is:

   EXPDP>   /u01/aux/tspitr_khDn_47210.dmp

   EXPDP> ******************************************************************************

   EXPDP> Datafiles required for transportable tablespace TEST:

   EXPDP>   /u01/app/oracle/oradata/test01.dbf

   EXPDP> Job "SYS"."TSPITR_EXP_khDn" successfully completed at 20:27:38

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

Performing import of metadata...

   IMPDP> Master table "SYS"."TSPITR_IMP_khDn" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_khDn":

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   IMPDP> Job "SYS"."TSPITR_IMP_khDn" successfully completed at 20:28:18

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace  "TEST" read write';

sql 'alter tablespace  "TEST" offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script

sql statement: alter tablespace  "TEST" read write

sql statement: alter tablespace  "TEST" offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/aux/DB1/datafile/o1_mf_temp1_8wp07c8t_.tmp deleted

auxiliary instance file /u01/aux/DB1/datafile/o1_mf_temp1_8wp07dnp_.tmp deleted

auxiliary instance file /u01/aux/DB1/onlinelog/o1_mf_3_8wp075s2_.log deleted

auxiliary instance file /u01/aux/DB1/onlinelog/o1_mf_2_8wp07452_.log deleted

auxiliary instance file /u01/aux/DB1/onlinelog/o1_mf_1_8wp070pr_.log deleted

auxiliary instance file /u01/aux/DB1/datafile/o1_mf_sysaux_8wp004xo_.dbf deleted

auxiliary instance file /u01/aux/DB1/datafile/o1_mf_undotbs2_8wp004y7_.dbf deleted

auxiliary instance file /u01/aux/DB1/datafile/o1_mf_system_8wp004y5_.dbf deleted

auxiliary instance file /u01/aux/DB1/controlfile/o1_mf_8wozzwpf_.ctl deleted

Finished recover at 26-JUN-13

Tablespace TEST is Recovered now.

Now immediately take the Recovered Tablespace backup and then change its status to ONLINE as the recovered tablespace status is OFFLINE.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='TEST';

TABLESPACE_NAME                STATUS

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

TEST                           OFFLINE

RMAN> backup tablespace "TEST";

Starting backup at 26-JUN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=28 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/test01.dbf

channel ORA_DISK_1: starting piece 1 at 26-JUN-13

channel ORA_DISK_1: finished piece 1 at 26-JUN-13

piece handle=/u01/app/oracle/fast_recovery_area/DB1/backupset/2013_06_26/o1_mf_nnndf_TAG20130626T203033_8wp0jlkp_.bkp tag=TAG20130626T203033 comment=NONE

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

Finished backup at 26-JUN-13

Starting Control File and SPFILE Autobackup at 26-JUN-13

piece handle=/u01/app/oracle/fast_recovery_area/DB1/autobackup/2013_06_26/o1_mf_s_819145859_8wp0kf1b_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 26-JUN-13

RMAN> sql'alter tablespace TEST online';

sql statement: alter tablespace TEST online

Lets crosscheck the rows returns from the tables to check whether there is any data loss during and after TSPITR process.

SQL> select count(*) from rohit.test;

  COUNT(*)

----------

  14350336

SQL> select count(*) from scott.test;

  COUNT(*)

----------

     89600

Reference.

http://docs.oracle.com/cd/B12037_01/server.101/b10734/rcmtspit.htm