Flashback Database

SQL> alter system set db_recovery_file_dest='+BACKUPDG' scope=both sid='*';

SQL> alter system set db_recovery_file_dest_size=5000M scope=both sid='*';

SQL> alter system set db_flashback_retention_target= 1440 scope=both sid='*';

--- Verify the parameter setup .

COLUMN NAME FORMAT A40
COLUMN VALUE FORMAT A50

SELECT INST_ID,NAME,VALUE FROM gv$PARAMETER WHERE NAME IN ('db_recovery_file_dest','db_recovery_file_dest_size','db_flashback_retention_target')
/

INST_ID NAME VALUE
---------- ---------------------------------------- --------------------------------------------------
1 db_recovery_file_dest +BACKUPDG
1 db_recovery_file_dest_size 5218762752
1 db_flashback_retention_target 1440
2 db_recovery_file_dest +BACKUPDG
2 db_recovery_file_dest_size 5218762752
2 db_flashback_retention_target 1440

6 rows selected.

-- Shutdown database and startup mount;

SQL> shutdown immediate;
SQL> startup mount;
Database mounted.

--- Ensure database is in archive log mode.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 71
Next log sequence to archive 0
Current log sequence 72

---- Turn the Flashback on;

SQL> alter database flashback on;

-- Verify Flashback in Turned on .

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
YES

SQL> alter database open;

SQL> CREATE RESTORE POINT BEFORE_PATCH;

Restore point created.

---- Flashback Related Views :

SELECT OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME ,FLASHBACK_SIZE FROM v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL FLASHBACK_SIZE
-------------------- --------- --------------
859379 10-DEC-09 159416320


SQL> SELECT SCN,NAME ,TIME FROM V$RESTORE_POINT;

SCN NAME TIME
---------- ---------------------------------------- ---------------------------------------------------------------------------
976812 BEFORE_PATCH 11-DEC-09 03.54.59.000000000 PM


COLUMN NAME FORMAT A50
1* SELECT NAME,LOG#,BYTES,FIRST_TIME FROM V$FLASHBACK_DATABASE_LOGFILE
SQL> /

NAME LOG# BYTES FIRST_TIM
-------------------------------------------------- ---------- ---------- ---------
+BACKUPDG/armynet/flashback/log_1.367.705266421 1 15941632 10-DEC-09
+BACKUPDG/armynet/flashback/log_2.365.705266477 2 15941632 10-DEC-09
+BACKUPDG/armynet/flashback/log_3.361.705276043 3 15941632 10-DEC-09
+BACKUPDG/armynet/flashback/log_4.360.705286825 4 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_5.358.705286845 5 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_6.357.705301241 6 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_7.356.705304845 7 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_8.354.705315645 8 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_9.349.705322797 9 15941632 11-DEC-09
+BACKUPDG/armynet/flashback/log_10.348.705330031 10 15941632 11-DEC-09

10 rows selected.



=======================

---- Flasback to scn:

FLASHBACK [STANDBY] DATABASE TO [BEFORE] SCN ;


---- Flasback to timestamp

FLASHBACK [STANDBY] DATABASE TO [BEFORE] TIMESTAMP ;


---- Flasback to restore_point
FLASHBACK [STANDBY] DATABASE TO [BEFORE] RESTORE POINT ;



Example of Flasback using Restore point (Standby database open read/write and convert back to standby)



CREATE RESTORE POINT before_open;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

ALTER DATABASE OPEN;


--- DATABASE OPEN TO READ/WRITE OPERATION.


----TO CONVERT BACK TO STANDBY;


SHUTDOWN IMMEDIATE;

STARTUP MOUNT;
ALTER DATABASE FALSHBACK TO RESTORE POINT BEFORE_OPEN;

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;