I have seen people using Flashback Technology more often to restore tables to an earlier point in time. Automatic tablespace point in time recovery is an other option which we can use to recover one or more tablespaces to an earlier point in time at the same time retaining other tablespaces and objects as it is now.

Thanks to my friend Rajeshh who notified me on this feature.


1)    Create a tablespace TS_AUTO
create tablespace ts_auto datafile ‘+AUTO_DATA’ size 500m;

2)    Crate a database user called AUTO_USER with default tablespace as TS_AUTO
create user auto_user identified by auto_user default tablespace ts_auto temporary tablespace temp quota unlimited on ts_auto;

3)    Grant required privileges to the user created in previous step
grant connect,resource to auto_user;

4)    Create a database session as auto_user
conn auto_user/auto_user

5)    Create a table called T_TEST
create table t_test(t_dt date);

6)Insert some values in to T_TEST
insert into  t_test values (sysdate);

7)Check the data which we had inserted in previous step
select * from auto_user.t_test;
8)Using RMAN backup the database and arvhivelogs

rman target /

backup database plus archivelog;

9)Get the latest log sequence number

SQL> select sequence#,thread# from  v$log;

———- ———-
720          1
718          1
719          1

9) Create a small parameter file

cat /u01/dba/temp/auto_tsptir_check/initEEeE.ora


**note Automated TSPITR creates its own parameter file but the SGA_TARGET in that is only 200m which will cause the whole procedure to fail due to insufficient memory, That is the reason we created our own parameter file and will pass the same during ATSPIR

RMAN> set auxiliary INSTANCE parameter file to “/u01/dba/temp/auto_tsptir_check/initEEeE.ora”;

executing command: SET auxiliary parameter file
using target database control file instead of recovery catalog

10) Perform Automated Tablespace point in time recovery.
RMAN> RECOVER TABLESPACE ts_auto      UNTIL LOGSEQ 720 THREAD 1       AUXILIARY DESTINATION ‘/u01/dba/temp/auto_tsptir_check’;

**AUXILIARY DESTINATION is where rman stores all the database related files for the auxiliary instance.
11) Change the status of the tablespace to online

alter tablespace ts_auto online;


Viewers of this post also viewed...

  • No Related Post