HOW TO RETRIEVE THE DROPPED TABLE IN ORACLE USING RMAN AND FLASHBACK TECHNOLOGY




Step 1: Check whether DB has recyclebin on or off


SYS@xxxx> sho parameter recyclebin;


NAME TYPEVALUE
———————————— ———– ——————————
recyclebin   string  ON




SELECT * FROM RECYCLEBIN;


ALTER SESSION SET recyclebin = OFF; 


ALTER SYSTEM SET recyclebin = OFF;


ALTER SESSION SET recyclebin = ON;


ALTER SYSTEM SET recyclebin = ON;






Step 2:  drop table oracle;


Table dropped.


SQL> select original_name from dba_recyclebin; or show recyclebin;


ORIGINAL_NAME 
——————————– 
oracle 


SQL> flashback table oracle to before drop;


Flashback complete.


SQL> select * from oracle;


ID 
———- 

6


————————————————————————————-


Recover the tables PMP and DEPTER using the following clauses in the RECOVER command: DATAPUMP DESTINATION, DUMP FILE, REMAP TABLE, and NOTABLEIMPORT.
The following RECOVER command recovers the PMP and DEPTER tables.( Here i am using SYSDATE 1 day before ) You can also use Until Sequence )


RECOVER TABLE SCOTT.PMP, SCOTT.DEPTER
UNTIL TIME ‘SYSDATE-1’
AUXILIARY DESTINATION ‘/tmp/oracle/recover’
DATAPUMP DESTINATION ‘/tmp/recover/dumpfiles’
DUMP FILE ‘pmp_depter_exp_dump.dat’
NOTABLEIMPORT;










  • March 19, 2016 | 17 views
  • Comments