Vishal Gupta's Blog

Turning Flashback ON – Incorrect Documentation

Posted by Vishal Gupta on Jul 18, 2011

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
About these ads

4 Responses to “Turning Flashback ON – Incorrect Documentation”

  1. Horia said

    Nice post!
    However at this stage the documentation is corrected.
    As now am preparing for the 11g OCP exam, the exam documentation states that the database shall be in mount exclusive, also preparation tests as recommended on official Oracle page give as correct solution that the database shall be in “mount exclusive” when trying to enable flashback.
    Too bad the information is not up to date in the exam paper, as the exam is against 11gR2.

    • Horia,

      This is the problem one faces when things keep on changing in various versions. One can not be tell for sure in which version behaviour changed. Thats why i maintain Oracle versions 9.2.0.4, 9.2.0.6, 9.2.0.7, 9..2.0.8, 10.1.0.[3-5], 10.2.0.[1-5], 11.1.0[6-7], 11.2.0.[1-3] on my laptop. This setup comes in very very handy to verify what was the change in behaviour and in which version.

      Long live my laptop lab setup.

      Vishal

    • Horia said

      Hi Vishal,

      You have a pretty valuable setup on your laptop. Indeed it sustains a mission critical knowledge center!
      Keep it up, and good luck!

      Regards,
      Horia

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: