Recovery Manager (RMAN) automatic tablespace point-in-time recovery

Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the tablespaces and other objects in the database.

How TSPITR Works With an RMAN-Managed Auxiliary Instance

RMAN carries out the following steps:

• If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
• Takes the tablespaces to be recovered offline in the target database
• Restores a backup controlfile from a point in time before the target time to the auxiliary instance
• Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance. Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE)
• Recovers the restored datafiles in the auxiliary instance to the specified time
• Opens the auxiliary database with the RESETLOGS option
• Exports the dictionary metadata about objects in the recovered tablespaces to the target database
• Shuts down the auxiliary instance
• 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.
• Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
• 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.

Deciding When to Use TSPITR

Like a table import, RMAN TSPITR enables you to recover a consistent data set; however, the data set recovered includes an entire tablespace rather than one object.

RMAN TSPITR is most useful for situations such as these:

• Recovering data lost after an erroneous TRUNCATE TABLE statement
• Recovering from logical corruption of a table
• Undoing the effects of an incorrect batch job or other DML statement that has affected only a subset of the database

Note that, as with database point-in-time recovery (DBPITR), you cannot perform TSPITR if you do not have your archived redo logs. For databases running in NOARCHIVELOG mode, you cannot perform TSPITR.

Determining the Recovery Set: Analyzing Data Relationships

Your recovery set starts out including the datafiles for the tablespaces you wish to recover. If, however, objects in the tablespaces you need have relationships (such as constraints) to objects in other tablespaces, you will have to account for this relationship before you can perform TSPITR. You have three choices when faced with such a relationship:

• Add the tablespace including the related objects to your recovery set
• Remove the relationship
• Suspend the relationship for the duration of TSPITR

Identifying and Preserving Objects That Will Be Lost After TSPITR

When RMAN TSPITR is performed on a tablespace, any objects created after the target recovery time are lost. You can preserve such objects, once they are identified, by exporting them before TSPITR using an Oracle export utility (Data Pump Export or Original Export) and re-importing them afterwards using the corresponding import utility.

To see which objects will be lost in TSPITR, query the TS_PITR_OBJECTS_TO_BE_DROPPED view on the primary database.

Example of TSPITR :

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

SQL> show user ;

USER is "SUMIT"

SQL> create table tspitr ( i int , J date) tablespace USERSTBS ;

Table created.

insert some data in tspitr table and commit ;

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Rman target /

backup database plus archivelog ;

SQL> set line 1000

SQL> SELECT * FROM sys.ts_pitr_check WHERE(ts1_name = 'USERSTBS' AND ts2_name != 'USERSTBS') OR (ts1_name != 'USERSTBS' AND ts2_name = 'USERSTBS');

no rows selected

SQL> SELECT owner, name, tablespace_name , creation_time FROM sys.ts_pitr_objects_to_be_dropped WHERE tablespace_name ='USERSTBS' ;

OWNER     NAME     TABLESPACE_NAME       CREATION_TIME

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

SCOTT          PK_DEPT    USERSTBS      17-SEP-2011 10:21:59

SCOTT          DEPT       USERSTBS       17-SEP-2011 10:21:59

SCOTT          EMP         USERSTBS       17-SEP-2011 10:21:59

SCOTT          PK_EMP      USERSTBS       17-SEP-2011 10:21:59

SCOTT          SALGRADE    USERSTBS      17-SEP-2011 10:21:59

SUMIT          TSPITR      USERSTBS       08-JUN-2015 23:15:16

SUMIT          TEST       USERSTBS       08-JUN-2015 23:42:25

SQL> select * from tspitr ;

   I J

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

  1 08-JUN-2015 23:15:24

  2 08-JUN-2015 23:15:38

  3 08-JUN-2015 23:15:46

  3 08-JUN-2015 23:16:57

  3 08-JUN-2015 23:17:01

  3 08-JUN-2015 23:31:01

  3 08-JUN-2015 23:31:08

  3 08-JUN-2015 23:32:25

  4 08-JUN-2015 23:43:26

  5 08-JUN-2015 23:43:45

Now drop few tables or truncate ot purge :

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

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

  COUNT(*)

----------

 14

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

  COUNT(*)

----------

  4

SQL> drop table tspitr ;

Table dropped.

SQL> drop table scott.EMP ;

Table dropped.

SQL> drop table scott.DEPT ;

Table dropped.

SQL>

SQL> select sysdate from dual ;

SYSDATE

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

08-JUN-2015 23:53:42

Suppose we want out all tables back until time : 08-JUN-2015 23:44:00 : ( You can use Logminer tool to find the exact time of drop statement)

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

[oracle@DB11G RMAN]$ cd /data01/RMAN/

[oracle@DB11G RMAN]$ mkdir TSPITR

[oracle@DB11G RMAN]$ chown oracle:oinstall TSPITR/

[oracle@DB11G RMAN]$ chmod 775 TSPITR/

[oracle@DB11G RMAN]$

SQL> select timestamp_to_scn(to_timestamp('08-JUN-2015 23:44:00','DD-MON-YYYY HH24:MI:SS')) as scn from dual ;

       SCN

----------

   1362639

SQL> select scn_to_timestamp(1362639) as timestamp from dual ;

TIMESTAMP

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

08-JUN-15 11.44.00.000000000 PM

RMAN> recover tablespace USERSTBS until scn 1362639  auxiliary destination '/data01/RMAN/TSPITR' ;

Starting recover at 09-JUN-15

using channel ORA_DISK_1

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 UNDOTBS1

Creating automatic instance, with SID='veyq'

initialization parameters used for automatic instance:

db_name=RACDB

db_unique_name=veyq_tspitr_RACDB

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/data01/RMAN/TSPITR

log_archive_dest_1='location=/data01/RMAN/TSPITR'

#No auxiliary parameter file used

starting up automatic instance RACDB

Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2227744 bytes

Variable Size                100663776 bytes

Database Buffers             184549376 bytes

Redo Buffers                   4837376 bytes

Automatic instance created

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:

{

# set requested point in time

set until  scn 1362639;

# 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 09-JUN-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 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 /data01/RMAN/c-878593708-20150608-03

channel ORA_AUX_DISK_1: piece handle=/data01/RMAN/c-878593708-20150608-03 tag=TAG20150608T232715

channel ORA_AUX_DISK_1: restored backup piece 1

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

output file name=/data01/RMAN/TSPITR/RACDB/controlfile/o1_mf_bqdt069y_.ctl

Finished restore at 09-JUN-15

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  scn 1362639;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

set newname for datafile  4 to

 "/data01/app/oracle/datafiles/racdbf/USERSTBS01.dbf";

set newname for datafile  5 to

 "/data01/app/oracle/datafiles/racdb/USERSTBS02.dbf";

# switch all tempfiles

switch clone tempfile all;

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

restore clone datafile  1, 3, 2, 4, 5;

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 /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 09-JUN-15

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 /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /data01/app/oracle/datafiles/racdbf/USERSTBS01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /data01/app/oracle/datafiles/racdb/USERSTBS02.dbf

channel ORA_AUX_DISK_1: reading from backup piece /data01/RMAN/4bq90tjc_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

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

Finished restore at 09-JUN-15

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=881885722 file name=/data01/RMAN/TSPITR/RACDB/datafile/o1_mf_system_bqdt0rhx_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=881885723 file name=/data01/RMAN/TSPITR/RACDB/datafile/o1_mf_undotbs1_bqdt0roo_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=9 STAMP=881885723 file name=/data01/RMAN/TSPITR/RACDB/datafile/o1_mf_sysaux_bqdt0rkg_.dbf

contents of Memory Script:

{

# set requested point in time

set until  scn 1362639;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  5 online";

# recover and open resetlogs

recover clone database tablespace  "USERSTBS", "SYSTEM", "UNDOTBS1", "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  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  5 online

Starting recover at 09-JUN-15

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /data01/arch/arch_1_5_881841121

archived log for thread 1 with sequence 6 is already on disk as file /data01/arch/arch_1_6_881841121

archived log for thread 1 with sequence 7 is already on disk as file /data01/arch/arch_1_7_881841121

archived log for thread 1 with sequence 8 is already on disk as file /data01/arch/arch_1_8_881841121

archived log for thread 1 with sequence 9 is already on disk as file /data01/arch/arch_1_9_881841121

archived log file name=/data01/arch/arch_1_5_881841121 thread=1 sequence=5

archived log file name=/data01/arch/arch_1_6_881841121 thread=1 sequence=6

archived log file name=/data01/arch/arch_1_7_881841121 thread=1 sequence=7

archived log file name=/data01/arch/arch_1_8_881841121 thread=1 sequence=8

archived log file name=/data01/arch/arch_1_9_881841121 thread=1 sequence=9

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

Finished recover at 09-JUN-15

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  USERSTBS read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/data01/RMAN/TSPITR''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/data01/RMAN/TSPITR''";

}

executing Memory Script

sql statement: alter tablespace  USERSTBS read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data01/RMAN/TSPITR''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data01/RMAN/TSPITR''

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_veyq":

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

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

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

   EXPDP> Dump file set for SYS.TSPITR_EXP_veyq is:

   EXPDP>   /data01/RMAN/TSPITR/tspitr_veyq_29688.dmp

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

   EXPDP> Datafiles required for transportable tablespace USERSTBS:

   EXPDP>   /data01/app/oracle/datafiles/racdb/USERSTBS02.dbf

   EXPDP>   /data01/app/oracle/datafiles/racdbf/USERSTBS01.dbf

   EXPDP> Job "SYS"."TSPITR_EXP_veyq" successfully completed at 00:17:47

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

# drop target tablespaces before importing them back

sql 'drop tablespace  USERSTBS including contents keep datafiles';

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

executing Memory Script

database closed

database dismounted

Oracle instance shut down

sql statement: drop tablespace  USERSTBS including contents keep datafiles

Performing import of metadata...

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

   IMPDP> Starting "SYS"."TSPITR_IMP_veyq":

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   IMPDP> Job "SYS"."TSPITR_IMP_veyq" successfully completed at 00:56:05

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace  USERSTBS read write';

sql 'alter tablespace  USERSTBS offline';

# enable autobackups after TSPITR is finished

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

}

executing Memory Script

sql statement: alter tablespace  USERSTBS read write

sql statement: alter tablespace  USERSTBS offline

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

Removing automatic instance

Automatic instance removed

auxiliary instance file /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_temp_bqdw9928_.tmp deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/onlinelog/o1_mf_3_bqdw8t8x_.log deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/onlinelog/o1_mf_2_bqdw8o7v_.log deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/onlinelog/o1_mf_1_bqdw8jkw_.log deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_sysaux_bqdw42n1_.dbf deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_undotbs1_bqdw42r5_.dbf deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/datafile/o1_mf_system_bqdw42kd_.dbf deleted

auxiliary instance file /data01/RMAN/TSPITR/RACDB/controlfile/o1_mf_bqdw3jov_.ctl deleted

Finished recover at 09-JUN-15

SQL> Alter tablespace USERSTBS online ;

Tablespace altered.

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select * from sumit.tspitr ;

   I J

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

  1 08-JUN-2015 23:15:24

  2 08-JUN-2015 23:15:38

  3 08-JUN-2015 23:15:46

  3 08-JUN-2015 23:16:57

  3 08-JUN-2015 23:17:01

  3 08-JUN-2015 23:31:01

  3 08-JUN-2015 23:31:08

  3 08-JUN-2015 23:32:25

  4 08-JUN-2015 23:43:26

  5 08-JUN-2015 23:43:45

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

  COUNT(*)

----------

 14

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

  COUNT(*)

----------

  4