Automatic TSPITR in 10G RMAN

RMAN automatic Tablespace Point-In-Time Recovery ( 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.

 When performing fully automated TSPITR, letting RMAN manage the entire process. There are only two requirements which we need to specify manually.

1.  Auxiliary destination for RMAN to use for the auxiliary set datafiles and other files for the auxiliary instance.  

2. Configure any channels required for the TSPITR on the target instance.

Note: The auxiliary instance will use the same channel configuration as the target instance when performing the TSPITR.

Setting up the environment:

 1. Created a tablespace test  and a table in that tablespace
   
   

SQL> Create tablespace test datafile 'D:\BACKUP\TEST.DBF' size 2 M;
Tablespace created

SQL> create table test001 (id number) tablespace test;
Table created.

SQL> insert into test001 values(100);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test001;
ID
----------
100

2. Checked the CURRENT LOG Sequence

SQL> select SEQUENCE# from v$log where status='CURRENT';

SEQUENCE#
----------
135

## 135 is my current log sequence

3. Taken the backup of the database and current controlfile using RMAN.

4. Done few log switch and droped that table.
 

SQL> drop table test001;        Table dropped.

SQL> select SEQUENCE# from v$log where status='CURRENT';

 SEQUENCE#
----------
       139

   ## Table dropped in log sequence 139.
   So TSPITR until log sequence 138 will give that table back.

Note: UNTIL TIME TSPITR syntax is :

RMAN> recover tablespace "TEST" until time "23-SEP-2005 10:00:00','DD-MON-YYYY HH24:MI:SS"
      auxiliary destination 'D:\BAckup\temp';

 

True test starts:
-----------------

1. Connect to RMAN

   C:> rman target / catalog rman/rman

2. Need to Do A TSPITR until log sequence 138.

  Auxiliary destination  is D:\Backup\temp

 RMAN> recover tablespace "TEST"  until logseq 138 thread 1
       auxiliary destination 'D:\Backup\temp';

Starting recover at 20-SEP-05

    starting full resync of recovery catalog

    full resync complete

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: sid=26 devtype=DISK

   

    Creating automatic instance, with SID='ljsA'

   

    initialization parameters used for automatic instance:

    db_name=AMAR

    compatible=10.2.0.1.0

    db_block_size=8192

    db_files=200

    db_unique_name=tspitr_AMAR_ljsA

    large_pool_size=1M

    shared_pool_size=110M

    #No auxiliary parameter file used

    db_create_file_dest=D:\BAckup\temp

    control_files=D:\BAckup\temp/cntrl_tspitr_AMAR_ljsA.f

   

   

    starting up automatic instance AMAR

   

    Oracle instance started

   

    Total System Global Area     201326592 bytes

   

    Fixed Size                     1247044 bytes

    Variable Size                146802876 bytes

    Database Buffers              50331648 bytes

    Redo Buffers                   2945024 bytes

    Automatic instance created

   

    contents of Memory Script:

    {

    # set the until clause

    set until  logseq 138 thread 1;

    # restore the controlfile

    restore clone controlfile;

    # mount the controlfile

    sql clone 'alter database mount clone database';

    # archive current online log for tspitr to a resent until time

    sql 'alter system archive log current';

    # avoid unnecessary autobackups for structural changes during TSPITR

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

    # resync catalog after controlfile restore

    resync catalog;

    }

    executing Memory Script

   

    executing command: SET until clause

   

    Starting restore at 20-SEP-05

    allocated channel: ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: sid=36 devtype=DISK

   

    channel ORA_AUX_DISK_1: starting datafile backupset restore

    channel ORA_AUX_DISK_1: restoring control file

    channel ORA_AUX_DISK_1: reading from backup piece

    +DATA/amar/backupset/2005_09_20/ncnnf0_tag20050920t105610_0.291.569501773

    channel ORA_AUX_DISK_1: restored backup piece 1

    piece handle=+DATA/amar/backupset/2005_09_20/ncnnf0_tag20050920t105610_0.291.569501773

    tag=TAG20050920T105610

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

    output filename=D:\BACKUP\TEMP\CNTRL_TSPITR_AMAR_LJSA.F

    Finished restore at 20-SEP-05

   

    sql statement: alter database mount clone database

    

    sql statement: alter system archive log current

   

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

   

    starting full resync of recovery catalog

    full resync complete

    released channel: ORA_DISK_1

    released channel: ORA_AUX_DISK_1

   

    contents of Memory Script:

    {

    # generated tablespace point-in-time recovery script

    # set the until clause

    set until  logseq 138 thread 1;

    plsql <<-- tspitr_2

    declare

      sqlstatement       varchar2(512);

      offline_not_needed exception;

      pragma exception_init(offline_not_needed, -01539);

    begin

      sqlstatement := 'alter tablespace '||  'TEST' ||' offline for recover';

      krmicd.writeMsg(6162, sqlstatement);

      krmicd.execSql(sqlstatement);

    exception

      when offline_not_needed then

        null;

    end; >>;

    # set an omf destination filename for restore

    set newname for clone datafile  1 to new;

    # set an omf destination filename for restore

    set newname for clone datafile  2 to new;

    # set an omf destination tempfile

    set newname for clone tempfile  1 to new;

    # set a destination filename for restore

    set newname for datafile  6 to

     "D:\BACKUP\TEST.DBF";

    # rename all tempfiles

    switch clone tempfile all;

    # restore the tablespaces in the recovery set plus the auxilliary tablespaces

    restore clone datafile  1, 2, 6;

    switch clone datafile all;

    #online the datafiles restored or flipped

    sql clone "alter database datafile  1 online";

    #online the datafiles restored or flipped

    sql clone "alter database datafile  2 online";

    #online the datafiles restored or flipped

    sql clone "alter database datafile  6 online";
    # make the controlfile point at the restored datafiles, then recover them
    recover clone database tablespace  "TEST", "SYSTEM", "UNDOTBS1" delete archivelog;
    alter clone database open resetlogs;
    # PLUG HERE the creation of a temporary tablespace if export fails due to lack
 

   # of temporary space.
    # For example in Unix these two lines would do that:
    #sql clone "create tablespace aux_tspitr_tmp
    #           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
    }
    executing Memory Script
   
    executing command: SET until clause
   
    sql statement: alter tablespace TEST offline for recover
   
    executing command:

SET NEWNAME
   
    executing command: SET NEWNAME
   
    executing command: SET NEWNAME
   
    executing

command: SET NEWNAME
   
    renamed temporary file 1 to D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
 

  
    Starting restore at 20-SEP-05
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: sid=39 devtype=DISK
 

  
    channel ORA_AUX_DISK_1: starting datafile backupset restore
    channel ORA_AUX_DISK_1: specifying datafile(s) to

restore from backup set
    restoring datafile 00001 to D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_SYSTEM_%U_.DBF
    restoring

datafile 00002 to D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
    restoring datafile 00006 to D:\BACKUP\TEST.DBF
 

   channel ORA_AUX_DISK_1: reading from backup piece
    +DATA/amar/backupset/2005_09_20/nnndf0_tag20050920t105434_0.295.569501677
 

   channel ORA_AUX_DISK_1: restored backup piece 1
    piece handle=+DATA/amar/backupset/2005_09_20/nnndf0_tag20050920t105434_0.295.569501677
 

   tag=TAG20050920T105434
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:06
    Finished restore at 20-SEP-05
 

  
    datafile 1 switched to datafile copy
    input datafile copy recid=4 stamp=569503256
    filename=D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_SYSTEM_1LZ8QZF1_.DBF
 

   datafile 2 switched to datafile copy
    input datafile copy recid=5 stamp=569503256
    filename=D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_UNDOTBS1_1LZ8QZN7_.DBF
 

  
    sql statement: alter database datafile  1 online
   
    sql statement: alter database datafile  2 online
 

  
    sql statement: alter database datafile  6 online
   
    Starting recover at 20-SEP-05
    using channel ORA_AUX_DISK_1
 

  
    starting media recovery
   
    archive log thread 1 sequence 134 is already on disk as file
    +DATA/amar/archivelog/2005_09_20/thread_1_seq_134.292.569501807
 

   archive log thread 1 sequence 135 is already on disk as file
    +DATA/amar/archivelog/2005_09_20/thread_1_seq_135.280.569501919
 

   archive log thread 1 sequence 136 is already on disk as file
    +DATA/amar/archivelog/2005_09_20/thread_1_seq_136.283.569501925
 

   archive log thread 1 sequence 137 is already on disk as file
    +DATA/amar/archivelog/2005_09_20/thread_1_seq_137.297.569501931
 

   archive log filename=+DATA/amar/archivelog/2005_09_20/thread_1_seq_134.292.569501807 thread=1
    sequence=134
    archive

log filename=+DATA/amar/archivelog/2005_09_20/thread_1_seq_135.280.569501919 thread=1
    sequence=135
    archive log filename=+DATA/amar/archivelog/2005_09_20/thread_1_seq_136.283.569501925

thread=1
    sequence=136
    archive log filename=+DATA/amar/archivelog/2005_09_20/thread_1_seq_137.297.569501931 thread=1
 

   sequence=137
    media recovery complete, elapsed time: 00:00:06
    Finished recover at 20-SEP-05
   
    database

opened
   
    contents of Memory Script:
    {
    # export the tablespaces in the recovery set
    host 'exp userid
 

   =\"/@(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ARGV0=oracleljsA)(ARGS=^'(DESCRIPTION=(LO
    AL=YES)(ADDRESS=(PROTOCOL=beq)))^')(ENVS=^'ORACLE_SID=ljsA^'))(CONNECT_DATA=(SID=ljsA))) as
    sysdba\" point_in_time_recover=y tablespaces=
     TEST file=
    tspitr_a.dmp';
    # shutdown clone before import
    shutdown clone immediate
    # import the tablespaces in the recovery set
    host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
    tspitr_a.dmp';
    # online/offline the tablespace imported
    sql "alter tablespace  TEST online";
    sql "alter tablespace  TEST offline";
    # enable autobackups in case user does open resetlogs from RMAN after TSPITR
    sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
    # resync catalog

after tspitr finished
    resync catalog;
    }
    executing Memory Script
   
    Export: Release 10.2.0.1.0 -

Production on Tue Sep 20 11:21:40 2005
   
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
   
   
 

   Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
    tion
    With the Partitioning, OLAP

and Data Mining options
    Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8

character set (possible charset conversion)
    Note: table data (rows) will not be exported
   
    About to export Tablespace

Point-in-time Recovery objects...
    For tablespace TEST ...
    . exporting cluster definitions
    . exporting table

definitions
    . . exporting table                        TEST001
    . exporting referential integrity constraints
 

  . exporting triggers
    . end point-in-time recovery
    Export terminated successfully without warnings.
   
 

  host command complete
   
    database closed
    database dismounted
    Oracle instance shut down
   
 

  Import: Release 10.2.0.1.0 - Production on Tue Sep 20 11:22:17 2005
   
    Copyright (c) 1982, 2005, Oracle.  All rights

reserved.
   
   
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
    tion
 

   With the Partitioning, OLAP and Data Mining options
   
    Export file created by EXPORT:V10.02.01 via conventional path
 

   About to import Tablespace Point-in-time Recovery objects...
    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR

character set
    import server uses AL32UTF8 character set (possible charset conversion)
    . importing SYS's objects into

SYS
    . . importing table                      "TEST001"
    Import terminated successfully without warnings.
   
    host command complete
   
    sql statement: alter tablespace  TEST online
   
    sql statement: alter

tablespace  TEST offline
   
    sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
   
    starting

full resync of recovery catalog
    full resync complete
   
    Removing automatic instance
    Automatic instance

removed
    auxiliary instance file D:\BACKUP\TEMP\CNTRL_TSPITR_AMAR_LJSA.F deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_SYSTEM_1LZ8QZF1_.DBF

deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_UNDOTBS1_1LZ8QZN7_.DBF deleted
    auxiliary instance

file D:\BACKUP\TEMP\TSPITR_A\DATAFILE\O1_MF_TEMP_1LZ8TSLB_.TMP deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\ONLINELOG\O1_MF_1_1LZ8TM4X_.LOG

deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\ONLINELOG\O1_MF_2_1LZ8TMSL_.LOG deleted
    auxiliary instance file D:\BACKUP\TEMP\TSPITR_A\ONLINELOG\O1_MF_3_1LZ8TNCV_.LOG deleted
    Finished recover at 20-SEP-05

TSPITR recovery completed sucessfully.

3. Connect to target database using SQLPLUS and take the tablespace test Online.
 

  SQL>  select * from test001;
         ID
    ----------
         100

Recoverd the lost table using TSPITR.