Recovering by Oracle Flashback

Oracle Flashback Technology provides the DBA the ability to recover  to a specified point in time in the past very quickly, easily, and without taking any part of the database offline.

Flashback Table: Row movement must be enabled on the table.

SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;Table altered.

FLASHBACK TABLE EMP TO SCN 123456; -- The EMP table is restored to its state when the database was at the time specified by the SCN.

FLASHBACK TABLE EMP TO TIMESTAMP  --  target point in time 
      TO_TIMESTAMP('2010-12-11 09:30:00', 'YYYY-MM-DD HH:MI:SS')
 

Recovering at the Row Level
In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:27 AM, an employee 'IMAD' had been updated in your EMP table, and you know that at 12:26 AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

 SELECT * FROM scott.EMP   -- here you see the current row , observe ename in upper case
      WHERE empno = 1111;
EMPNO    ENAME    JOB    MGR    HIREDATE    SAL    COMM    DEPTNO
1111    IMAD    CLERK    7902    12/17/1980    800    null    20

SELECT * FROM scott.EMP AS OF TIMESTAMP -- you see here ename was in lower case 
   TO_TIMESTAMP('2010-12-11 12:26:00', 'YYYY-MM-DD HH24:MI:SS')
   WHERE empno = 1111--'IMAD';
 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1111 imad CLERK 7902 12/17/1980 800 null 20