Database must be enabled FLASHBACK DATABASE to use the FLASHBACK for recovery a deleted or updated data.
a) How to
check FLASHBACK DATABASE;
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
FLASHBACK_ON
------------------
YES
SQL>
b) How to
enable FLASHBACK DATABASE
i) Start
SQL*Plus and ensure that the database is mounted, but not open. For example:
SQL>
SHUTDOWN IMMEDIATE;
SQL>
STARTUP MOUNT;
ii) Optionally,
set theDB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback
window in minutes:
SQL> ALTER SYSTEM SET
DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
By default
DB_FLASHBACK_RETENTION_TARGET is set to one day (1440 minutes).
iii) Enable the
Flashback Database feature for the whole database:
SQL> ALTER DATABASE
FLASHBACK ON;
Recovery
Preparation & Steps
Note: This is
pre-plan scenario. Therefore I have taken current SCN number and system times-tamp
using following query. But, if unexpected scenario we can find SCN number from
Alert log and time can be used according to an incident happened.
Preparation
Step 1) How to
find CURRENT_SCN, SYSTIMESTAMP
SQL> SELECT
CURRENT_SCN, SYSTIMESTAMP FROM V$DATABASE;
Step 2) Check an available data in
table which we suppose to recover.
SQL> SELECT
* FROM RMAN.EMP2 AS OF SCN 5968829510027;
Step 3)
Delete one record and update one record.
SQL> DELETE FROM RMAN.EMP2 WHERE ID=103;
SQL> UPDATE RMAN.EMP2 SET SALARY=10000 WHERE ID IN (100,101);
SQL> COMMIT;
Step 4)
Check an available data after above changes.
SQL> SELECT * FROM RMAN.EMP2;
Recovery Steps
There are
two methods to recover a deleted or updated records.
Method 1:- using
SCN Number
Method 2:- using TIMESTAMP
Method 1:
SQL> FLASHBACK TABLE RMAN.EMP2 TO SCN 5968829510027;
Flashback
complete.
SQL>
Method 2:
SQL> ALTER TABLE RMAN.EMP2 ENABLE ROW MOVEMENT;
Table
altered.
SQL>
SQL> FLASHBACK TABLE RMAN.EMP2 TO TIMESTAMP(TO_DATE('09-SEP-2015 11:12:4','DD-MON-YYYY HH24: MI: SS'));
Flashback
complete.
SQL>
SQL> SELECT * FROM RMAN.EMP2;
