Flashback database was introduced in Oracle 10g and offer’s a simple way for performing a point in time recovery. To enable this, your database must be running in archivelog mode and flash recovery must be enabled.
In 10g We would have had to issue the following commands:
|
1 2 3 4 |
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 day SQL> ALTER DATABASE FLASHBACK ON; |
But with 11gR2, We can now turn flashback on / off, when the database is OPEN
|
1 2 3 4 5 6 7 8 |
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production |
We can check the current status of flashback.
|
1 2 3 4 |
<span style="line-height: 1.5em;">SQL> select flashback_on, status from v$database, v$instance;</span> FLASHBACK_ON STATUS ------------------ ------------ NO OPEN |
Lets set the flashback retention target
|
1 |
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 day |
Now with the database open, we will set the flashback on.
|
1 2 3 4 5 6 7 |
SQL> alter database flashback on; Database altered. SQL> select flashback_on, status from v$database, v$instance; FLASHBACK_ON STATUS ------------------ ------------ YES OPEN |
Now with the database open, we will set the flashback off.
|
1 2 3 4 5 6 7 |
<span style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px;">SQL> alter database flashback off;</span> Database altered. SQL> select flashback_on, status from v$database, v$instance; FLASHBACK_ON STATUS ------------------ ------------ NO OPEN |
Lets enable the flashback.
|
1 2 3 4 5 6 7 |
SQL> alter database flashback on; Database altered. SQL> select flashback_on, status from v$database, v$instance; FLASHBACK_ON STATUS ------------------ ------------ YES OPEN |
Check the flashback retention
|
1 2 3 4 |
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 |
After a while if you run the following you can see how far we can flashback the database.
|
1 2 3 4 5 6 |
SQL> alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"; SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI -------------------- ------------------- 5034013702 2016-02-12 16:33:35 |