Enabling / Disabling Flashback Database in 11gR2

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:

SQL&gt; SHUTDOWN IMMEDIATE;
SQL&gt; STARTUP MOUNT;
SQL&gt; ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 day
SQL&gt; ALTER DATABASE FLASHBACK ON;

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

SQL&gt; 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

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.

<span style="line-height: 1.5em;">SQL&gt; select flashback_on, status from v$database, v$instance;</span>
FLASHBACK_ON STATUS
------------------ ------------
NO OPEN

<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

SQL&gt; ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 day

1	SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440; # 1 day

Now with the database open, we will set the flashback on.

SQL&gt; alter database flashback on;
Database altered.

SQL&gt; select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
 ------------------ ------------
 YES OPEN

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.

<span style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px;">SQL&gt; alter database flashback off;</span>
Database altered.

SQL&gt; select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
 ------------------ ------------
 NO OPEN

<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.

SQL&gt; alter database flashback on;
 Database altered.

SQL&gt; select flashback_on, status from v$database, v$instance;
FLASHBACK_ON STATUS
 ------------------ ------------
 YES OPEN

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

SQL&gt; show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 db_flashback_retention_target integer 1440

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.

SQL&gt; alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";

SQL&gt; 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

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