FLASHBACK Database

============== How enable Flashback database =================================
shutdown immediate;
startup mount exclusive;
alter database archivelog;
alter database flashback on;
alter database open;

SELECT flashback_on, log_mode
FROM v$database;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%flashback%';

-- 2 days
alter system set DB_FLASHBACK_RETENTION_TARGET=2880;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%flashback%';

SELECT estimated_flashback_size FROM gv$flashback_database_log;

===================== How to use Flashback Database==================================
SELECT current_scn FROM v$database;

--oldest flashback log
SELECT oldest_flashback_scn,oldest_flashback_time FROM gv$flashback_database_log;

GRANT flashback any table TO uwclass;

CREATE RESTORE POINT bef_damage;



=======================Action==========================================
shutdown immediate;
startup mount exclusive;

-- check the flashback of incarnation is possible with restore point;
SELECT SCN,DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,TIME,NAME FROM  V$RESTORE_POINT;

       SCN DATABASE_INCARNATION# GUA TIME                                     NAME
---------- --------------------- --- ---------------------------------------- ---------------
   9400175                     4 NO  24-JAN-12 11.53.19.000000000 AM          BEF_DAMAGE
   9400227                     4 NO  24-JAN-12 11.54.57.000000000 AM          AFTER_INSERT
   9400855                     6 NO  24-JAN-12 12.05.22.000000000 PM          LAST_POINT
   9330796                     8 NO  24-JAN-12 12.16.48.000000000 PM          BEF_IMAGE
   9330823                     8 NO  24-JAN-12 12.17.40.000000000 PM          AFTER_INSERT2
   9331462                     9 NO  24-JAN-12 12.30.02.000000000 PM          AFTER_INSERT3
   9331119                    10 NO  24-JAN-12 12.32.38.000000000 PM          AFTER_INSERT4



SELECT INCARNATION#,PRIOR_INCARNATION#,RESETLOGS_TIME,STATUS,FLASHBACK_DATABASE_ALLOWED from V$DATABASE_INCARNATION;

INCARNATION# PRIOR_INCARNATION# RESETLOGS STATUS  FLASHBACK_DATABASE_ALLOWED
------------ ------------------ --------- ------- --------------------------
           1                  0 30-JUN-05 PARENT  NO
           2                  1 30-MAR-11 PARENT  NO
           3                  2 20-JAN-12 PARENT  NO
           4                  3 20-JAN-12 PARENT  NO
           5                  4 24-JAN-12 ORPHAN  NO
           6                  4 24-JAN-12 ORPHAN  NO
           7                  4 24-JAN-12 PARENT  YES
           8                  7 24-JAN-12 PARENT  YES
           9                  8 24-JAN-12 ORPHAN  YES
          10                  8 24-JAN-12 ORPHAN  YES
          11                  9 24-JAN-12 ORPHAN  YES

INCARNATION# PRIOR_INCARNATION# RESETLOGS STATUS  FLASHBACK_DATABASE_ALLOWED
------------ ------------------ --------- ------- --------------------------
          12                  8 24-JAN-12 CURRENT YES


-- be sure to substitute your SCN
FLASHBACK DATABASE TO SCN 19513917;
or
FLASHBACK DATABASE TO RESTORE POINT bef_damage;


/*
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);
FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00';
FLASHBACK DATABASE 
TO TIMESTAMP to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');
*/

-- this will fail
alter database open;

-- this will succeed
alter database open resetlogs;