DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, March 19, 2016

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;






No comments: