In previous releases point in time recovery of a table or table partition was only possible by manually creating a point in time clone of the database.
For TSPITR, we need to specify the auxiliary destination where RMAN would create a temporary database by restoring the controlfile, SYSTEM
tablespace, UNDO tablespace, SYSAUX tablespace and finally the tablespace that needs to be recovered.
Once the temporary database is restored and recovered, RMAN automatically exports the contents of tablespace to be recovered from the
temproary tablespace to a dump file and imports this dump into the Target(Main) database where the table was dropped.
rman target /
RMAN> run
2> {
3> recover tablespace MYTS until time "to_date('2013-05-04:11:38:00','YYYY-MM:DD:HH24:MI:SS')" auxiliary destination '+FRA';
4> }
Once the import is done successfully, RMAN automatically deletes the temporary database that it had created earlier.
Now, lets connect to the main database and check if we are able to access the dropped table. But, before that, you need to bring the tablespace online.
recovering a dropped table with flashback:The recyclebin feature introduced in Oracle 10g allows you to recover dropped tables using the flashback table...to before drop command. With recyclebin, Oracle does not automatically delete dropped tables. Instead, Oracle renames dropped and their associated objects, giving them system-generated recyclebin names that begin with BIN$.
SQL> select object_name, original_name, type from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ ----------------------- -------
BIN$ABCSD5 TESTING TABLE
select * from "BIN$ABCSD5" ;
SQL> flashback table testing to before drop;
SQL> select index_name from user_indexes where table_name = 'testing';
alter index "recyclebin_name" rename to original_name;
New in Oracle 12c is the Multitenant Architecture, as well as the ability to restore one table from an RMAN backup.
RECOVER TABLE TEST.T1 UNTIL SCN 1853267 AUXILIARY DESTINATION '/u01/aux' REMAP TABLE 'TEST'.'T1':'T1_PREV';
recover table hari.emp until time "to_date('2018-08-14 21:53:42','yyyy-mm-dd:hh24:mi:ss')" auxiliary destination '/u01/fra';
recover table hari.emp until time "to_date('2018-08-14 21:53:42','yyyy-mm-dd:hh24:mi:ss')" auxiliary destination '/u01/fra';
run {
recover table example.test_restore of pluggable database PDB2
until time "to_date('12-01-2016 17:16:00','mm/dd/yyyy hh24:mi:ss')"
auxiliary destination '/oradata/CDB2/aux'
;
}
RMAN> run {
recover table example.test_restore of pluggable database PDB2
until time "to_date('12-01-2016 17:16:00','mm/dd/yyyy hh24:mi:ss')"
auxiliary destination '/oradata/CDB2/aux'
;
}
=================
1.
Flashback features:
SHOW RECYCLEBIN;
flashback table <table> to before drop;
FLASHBACK TABLE HR.REG_HIST TO BEFORE DROP;
2.
Database Point-in-Time Recovery:
3.
Tablespace point-in-time recovery (TSPITR) :
4.
Oracle 12c Table Point-in-time Recovery:
rman target /
RMAN> recover table emp.emp until time "to_date('2010-05-16 02:21:27','yyyy-mm-dd:hh24:mi:ss')" auxiliary destination '/u02/RC_BKP';
RMAN> recover table mm.test until time "to_date('00/17/2013 21:01:15','mm/dd/yyyy hh24:mi:ss')" auxiliary destination '/u03/app/oracle/ux' remap table mm.test:test_temp;
