Tablespace Point-in-Time Recovery(TSPIR) in Oracle 12c non-cdb

Tablespace Point-in-Time Recovery(TSPIR) in Oracle 12c non-cdb

 drop tablespace madhav including contents and datafiles;

 drop user madhav cascade;

 create tablespace madhav datafile '/oracle/app/oracle/oradata/madhav/madhav.dbf' size 10M autoextend on maxsize unlimited ;

 create user madhav identified by madhav default tablespace madhav;



 alter user madhav quota unlimited on madhav;

 alter user madhav temporary tablespace temp;

 grant connect, resource to madhav;

 grant connect, resource to madhav;

 conn madhav/madhav

sho user;

 create table test1 (a number, b number);

 insert into test1 values (1,2);

 insert into test1 values (2,3);

 commit;

select *from test1;



         A          B

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

         1          2

         2          3





select to_char(sysdate,'hh24:mi dd-mm-yyyy') from dual;



TO_CHAR(SYSDATE,

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

01:33 07-10-2016




rman target sys/manager



run

{

allocate channel c1 type disk format '/tmp/madhav/madhav_%U';

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

}





select to_char(sysdate,'hh24:mi dd-mm-yyyy') from dual;



RMAN>

TO_CHAR(SYSDATE,

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

01:35 07-10-2016







 truncate table madhav.test1;

 select *From madhav.test1;


no rows selected










run

{

SQL 'ALTER TABLESPACE madhav OFFLINE IMMEDIATE';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT  '/tmp/madhav/madhav_%U';

recover tablespace madhav until time "to_date('01:34 07-10-2016','hh24:mi dd-mm-yyyy')" auxiliary destination '/tmp/';

SQL "ALTER TABLESPACE madhav ONLINE";

}





RMAN> select *From madhav.test1;



         A          B

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

         1          2

         2          3