Tablespace Point-In-Time Recovery(TSPITR) in RMAN step by step

How to recover a table using Tablespace Point-In-Time Recovery(TSPITR) in RMAN

1.       SETUP A TEST ENVIRONMENT

SQL> create tablespace TSPITR_TBS datafile '/u01/app/oracle/oradata/ORCL/tspitr_tbs.dbf' size 20M;

Tablespace created.

SQL> grant connect, create table to tspitr_user identified by ts;

Grant succeeded.

SQL> alter user tspitr_user default tablespace TSPITR_TBS quota unlimited on TSPITR_TBS;

User altered.

[oracle@alizertest ~]$ sqlplus tspitr_user/ts

SQL> create table test (id number);

Table created.

SQL> begin

  2  for i in 1 .. 100 loop

  3  insert into test values (i);

  4  end loop;

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)

----------

       100

SQL> commit;

Commit complete.

2.       Take full database backup

RMAN> list backup of tablespace tspitr_tbs;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

28      Full    1006.99M   DISK        00:02:08     30-NOV-15

        BP Key: 28   Status: AVAILABLE  Compressed: NO  Tag: TAG20151130T171057

        Piece Name: /u02/rman_bkp/database_bkp/ORCL_ts_0vqniu12_1_1.bkp

  List of Datafiles in backup set 28

  File LV Type Ckp SCN    Ckp Time  Name

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

  6       Full 960254     30-NOV-15 /u01/app/oracle/oradata/ORCL/tspitr_tbs.dbf

3.       LOGICAL ERROR OCCURS

SQL> truncate table test;

Table truncated.

SQL> select count(*) from test;

  COUNT(*)

----------

         0

SQL> commit;

Commit complete.

4.       Switch a logfile

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/arc_dest

Oldest online log sequence     12

Next log sequence to archive   14

Current log sequence           14

5.       Analyze logfiles to detect the scn and reasons with Logminer

SQL> begin

  2  dbms_logmnr.add_logfile(logfilename=> '/u01/arc_dest/1_21_896447297.dbf');

  3  end;

  4  /

PL/SQL procedure successfully completed.

SQL> begin

  2  dbms_logmnr.start_logmnr();

  3  end;

  4  /

PL/SQL procedure successfully completed.

SQL> select scn, sql_redo from v$logmnr_contents where seg_owner='TSPITR_USER' and seg_name='TEST';

    SCN SQL_REDO

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

 965500 truncate table test;

6.       Now recover the tablespace

Here you can get below errors.

RMAN-06026: some targets not found - aborting restore

RMAN-06024: no backup or copy of the control file found to restore

Find the solutions at below link : 

How to fix RMAN-06026 & RMAN-06024...

RMAN> run

2> {

3> recover tablespace tspitr_tbs

4> until scn 965496

5> auxiliary destination '/u01/aux_dest';

6> }

Starting recover at 30-NOV-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='shta'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=shta_tspitr_ORCL

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/u01/aux_dest

log_archive_dest_1='location=/u01/aux_dest'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes

Variable Size                100666596 bytes

Database Buffers             184549376 bytes

Redo Buffers                   6381568 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 965496;

# 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 30-NOV-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 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 /u02/rman_bkp/control_bkp/c-1423639881-20151130-04.ctl

channel ORA_AUX_DISK_1: piece handle=/u02/rman_bkp/control_bkp/c-1423639881-20151130-04.ctl tag=TAG20151130T231314

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_dest/ORCL/controlfile/o1_mf_c5s427mf_.ctl

Finished restore at 30-NOV-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 965496;

plsql <<<-- tspitr_2

declare

  sqlstatement       varchar2(512);

  offline_not_needed exception;

  pragma exception_init(offline_not_needed, -01539);

begin

  sqlstatement := 'alter tablespace '||  'TSPITR_TBS' ||' offline immediate';

  krmicd.writeMsg(6162, sqlstatement);

  krmicd.execSql(sqlstatement);

exception

  when offline_not_needed then

    null;

end; >>>;

# 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  6 to

 "/u01/app/oracle/oradata/ORCL/tspitr_tbs.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, 6;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TSPITR_TBS offline immediate

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_dest/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 30-NOV-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 /u01/aux_dest/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux_dest/ORCL/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/aux_dest/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCL/tspitr_tbs.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u02/rman_bkp/database_bkp/ORCL_13qnjj3l_1_1.bkp

channel ORA_AUX_DISK_1: piece handle=/u02/rman_bkp/database_bkp/ORCL_13qnjj3l_1_1.bkp tag=TAG20151130T231045

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:08

Finished restore at 30-NOV-15

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=897176362 file name=/u01/aux_dest/ORCL/datafile/o1_mf_system_c5s42ldj_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=897176362 file name=/u01/aux_dest/ORCL/datafile/o1_mf_undotbs1_c5s42lh6_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=897176362 file name=/u01/aux_dest/ORCL/datafile/o1_mf_sysaux_c5s42ldz_.dbf

contents of Memory Script:

{

# set requested point in time

set until  scn 965496;

# 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  6 online";

# recover and open resetlogs

recover clone database tablespace  "TSPITR_TBS", "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  6 online

Starting recover at 30-NOV-15

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 20 is already on disk as file /u01/arc_dest/1_20_896447297.dbf

archived log for thread 1 with sequence 21 is already on disk as file /u01/arc_dest/1_21_896447297.dbf

archived log file name=/u01/arc_dest/1_20_896447297.dbf thread=1 sequence=20

archived log file name=/u01/arc_dest/1_21_896447297.dbf thread=1 sequence=21

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

Finished recover at 30-NOV-15

database opened

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  TSPITR_TBS read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/aux_dest''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/aux_dest''";

}

executing Memory Script

sql statement: alter tablespace  TSPITR_TBS read only

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

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

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_shta":

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

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

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

   EXPDP> Dump file set for SYS.TSPITR_EXP_shta is:

   EXPDP>   /u01/aux_dest/tspitr_shta_20606.dmp

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

   EXPDP> Datafiles required for transportable tablespace TSPITR_TBS:

   EXPDP>   /u01/app/oracle/oradata/ORCL/tspitr_tbs.dbf

   EXPDP> Job "SYS"."TSPITR_EXP_shta" successfully completed at 23:42:39

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

# drop target tablespaces before importing them back

sql 'drop tablespace  TSPITR_TBS including contents keep datafiles';

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

sql statement: drop tablespace  TSPITR_TBS including contents keep datafiles

Performing import of metadata...

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

   IMPDP> Starting "SYS"."TSPITR_IMP_shta":

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   IMPDP> Job "SYS"."TSPITR_IMP_shta" successfully completed at 23:43:41

Import completed

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace  TSPITR_TBS read write';

sql 'alter tablespace  TSPITR_TBS offline';

# enable autobackups after TSPITR is finished

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

}

executing Memory Script

sql statement: alter tablespace  TSPITR_TBS read write

sql statement: alter tablespace  TSPITR_TBS offline

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

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/aux_dest/ORCL/datafile/o1_mf_temp_c5s478w3_.tmp deleted

auxiliary instance file /u01/aux_dest/ORCL/onlinelog/o1_mf_3_c5s472fx_.log deleted

auxiliary instance file /u01/aux_dest/ORCL/onlinelog/o1_mf_2_c5s47050_.log deleted

auxiliary instance file /u01/aux_dest/ORCL/onlinelog/o1_mf_1_c5s46x2t_.log deleted

auxiliary instance file /u01/aux_dest/ORCL/datafile/o1_mf_sysaux_c5s42ldz_.dbf deleted

auxiliary instance file /u01/aux_dest/ORCL/datafile/o1_mf_undotbs1_c5s42lh6_.dbf deleted

auxiliary instance file /u01/aux_dest/ORCL/datafile/o1_mf_system_c5s42ldj_.dbf deleted

auxiliary instance file /u01/aux_dest/ORCL/controlfile/o1_mf_c5s427mf_.ctl deleted

Finished recover at 30-NOV-15

In this case, RMAN will create additional temporary datafiles, controlfile & online redo logfiles in auxiliry destination. Using these files, RMAN will start a auxiliary instance. You can check...

7.       Check the data is updated in Table or not

SQL> conn tspitr_user/ts

Connected.

SQL> select count(*) from test;

select count(*) from test

                     *

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/tspitr_tbs.dbf'

SQL> conn / as sysdba

Connected.

SQL> alter TABLESPACE TSPITR_TBS ONLINE;

Tablespace altered.

SQL> conn tspitr_user/ts

Connected.

SQL> select count(*) from test;

  COUNT(*)

----------

       100

Hope It will help u, friendss.....
Thanks..