Vishal Gupta's Blog

Oracle documentation states that you can not use the FLASHBACK database clause (i.e turn it ON or OFF) while database is OPEN. Flashback mode can be turned ON only when database is in MOUNT mode. This is true in Oracle version 10.1, 10.2, 11.1. But in 11gR2, Oracle actually allows you to turn on the flashback even in OPEN mode, even though documentation says otherwise. I have raised an Oracle Service Request with Oracle to get this documentation bug fixed.

[Update 19-Jul-2011]

Following bug has been raised by Oracle Support.

Bug 12768808: FLASHBACK DATABASE DO NOT REQUIRE TO BE IN MOUNT MODE FROM 112 ONWARDS

Excerpt from 11gR2 documentation, SQL Reference for “ALTER DATABASE” statement  (as of 18-Jul-2011) – Click Here

flashback_mode_clause

Use this clause to put the database in or take the database out of FLASHBACK mode. You can specify this clause only if the database is in ARCHIVELOG mode and you have already prepared a fast recovery area for the database.You can specify this clause when the database is mounted but not open. This clause cannot be specified on a physical standby database if redo apply is active.

See Also:

Oracle Database Backup and Recovery User’s Guide for information on preparing the fast recovery area for Flashback operations

FLASHBACK ON Use this clause to put the database in FLASHBACK mode. When the database is in FLASHBACK mode, Oracle Database automatically creates and manages Flashback Database logs in the fast recovery area. Users with SYSDBA system privilege can then issue a FLASHBACK DATABASE statement.

FLASHBACK OFF Use this clause to take the database out of FLASHBACK mode. Oracle Database stops logging Flashback data and deletes all existing Flashback Database logs. Any attempt to issue a FLASHBACK DATABASE will fail with an error.

11gR2

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 18 04:56:02 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ --------------------
NO		   ARCHIVELOG	READ WRITE

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ --------------------
YES		   ARCHIVELOG	READ WRITE

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ --------------------
NO		   ARCHIVELOG	READ WRITE

11gR1

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jul 18 05:15:52 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
NO		   ARCHIVELOG	READ WRITE

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size		    2142976 bytes
Variable Size		  159386880 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5296128 bytes
Database mounted.
SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
NO		   ARCHIVELOG	MOUNTED

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
YES		   ARCHIVELOG	MOUNTED

SQL> alter database open;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
YES		   ARCHIVELOG	READ WRITE

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on, log_mode, open_mode from v$database;

FLASHBACK_ON	   LOG_MODE	OPEN_MODE
------------------ ------------ ----------
NO		   ARCHIVELOG	READ WRITE