Turning Flashback Database on & off with Instance in Status OPEN


Since 11g, you can turn Flashback Database on and off without having to restart the Instance. It can stay in status OPEN, whereas in 10g, you had to go in Status MOUNT to change the Flashback mode, similar as it is still necessary to go to MOUNT in order to change the Archivelog mode.

Here is a short demo on the command line:

SQL> select * from v$version;
Oracle Database 11g Enterprise Edition Release - Production
PL/SQL Release - Production
CORE      Production
TNS for Linux: Version - Production
NLSRTL Version - Production
SQL> select log_mode,flashback_on from v$database;
------------ ------------------
SQL> select status from v$instance;

I am already in Archivelog mode, but Flashback (Database) is not turned on yet. Instance is OPEN – and stays there:

SQL> alter database flashback on;
Database altered.
SQL> select log_mode,flashback_on from v$database;
------------ ------------------
SQL> alter database flashback off;
Database altered.
SQL> select log_mode,flashback_on from v$database;
------------ ------------------

Conclusion: If you are about to do a critical operation with your database that you may want to rewind in case, you can turn on Flashback Logging before that operation and turn it off afterwards without limiting the availability of your Database!

Watch me on YouTube explaining the above:

Keep in mind that this affects only your ability to do FLASHBACK DATABASE, not the other forms of Flashback like FLASHBACK QUERY or FLASHBACK TABLE because they do not need Flashback Logs and work regardless of what you see in the column FLASHBACK_ON from V$DATABASE as explained here.


  1. #1 by Kamran Agayev A. on June 25, 2010 - 19:59

    That’s one of the new Flashback features of Oracle 11g

    Thanks for reminding

  2. #2 by Ulfat on June 26, 2010 - 07:20


  3. #3 by Asif Momen on June 26, 2010 - 11:20

    To be honest, I was not aware of this feature. Thanks for sharing.

  4. #4 by Surachart Opun on June 26, 2010 - 13:12

    Thank You 🙂
    This is a great feature and nice when we don’t need shutdown DB.

  5. #5 by Svetoslav Gyurov on June 29, 2010 - 08:03

    Actually I missed this one, great feature. Thanks for sharing!

  6. #6 by Uwe Hesse on July 8, 2010 - 16:11

    Thanks for your feedback, Kamran, Ulfat, Asif, Surachart & Svetoslav! Very much appreciated 🙂 It’s what keeps bloggers blogging, I suppose.

  7. #7 by Kamran Agayev A. on July 8, 2010 - 19:53

    Thank you too Uwe for finding time from your busy schedule and posting what you learn and teach on Oracle.

    Keep blogging!

  8. #8 by Hashmi on July 28, 2010 - 08:59

    Thats a great improvement in flashback.
    I wonder when would oracle allow turning archive log mode on when the database is open.
    Also can we flashback the database while its open in 11g?

  9. #9 by Uwe Hesse on July 28, 2010 - 09:12

    there is no announcement yet that changing the Archivelog Mode will be possible with an open instance in the future.
    Also, it is still not possible to do a Flashback Database in any other state as mounted. And it seems to be unlikely to me that this might change in the future, because the whole Data Dictionary takes part in that Flashback Database procedure – which can hardly be done online, similar and for the same reason as a Database Point In Time Recovery can’t be done online.

  10. #10 by Rajeshkumar Govindarajan on July 28, 2010 - 11:59

    thanks for sharing.

  11. #11 by zaigam dilshad on July 28, 2010 - 12:43

    even i am also not aware of this feature…

  12. #12 by Oviwan on July 29, 2010 - 15:35

    I noticed this too last week, even the documentation of 11gR2 says:
    “The database must be mounted, but not open, with a current control file.”


  13. #13 by Uwe Hesse on July 29, 2010 - 15:45

    just checked it. You are right: They did not (at this point you quoted) update the documentation. Will file a service request for that one. Thank you for pointing it out!

  14. #14 by Rajeshkumar Govindarajan on July 30, 2010 - 07:48

    Hi Uwe Hesse,

    I would like to know why we are changing archivelog mode in mount stage?

    Looking for you reply.

    Thank you,

    i also posted this question in forum. if you answer in that it will be helpful for everyone like me.

  15. #15 by Hashmi on July 30, 2010 - 07:54

    May be because we are starting a new ARC process which is a background process. And all backgorund process starts at mount mode.
    Which backgorund process is responsible for generating flashback logs?

  16. #16 by Uwe Hesse on July 30, 2010 - 15:16

    Rajeshkumar Govindarajan,
    I see no obvious reason for that. It was just always so. Must ask our development for it somewhen 🙂

    that can’t be the reason: We do start new background processes without leaving status OPEN. For example RVWR writes Flashback Logs or CTWR writes block change tracking file. Both are introduced in status OPEN. You may investigate this by checking v$bgprocess before and after the command ALTER DATABASE FLASHBACK ON.

  17. #17 by SYSDBA on February 24, 2011 - 20:03

    On 2011-02-24, the documentation still says: “You can specify this clause when the database is mounted but not open.” http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_1004.htm#i2155821
    hmmmm … anyway tks for the info. ;] PS: By the way I really enjoyed your posts Oracle Exadata.

  18. #18 by oracleman on May 25, 2011 - 21:22

    great info

  19. #19 by Fernando on May 30, 2012 - 17:11

    Maybe they should update this note with your posts


  20. #20 by Uwe Hesse on June 1, 2012 - 08:50

    Fernando, thank you for pointing this out! Will file a request to update that MOS note accordingly 🙂

  21. #21 by Venkat on July 23, 2012 - 22:27

    Hesse, It’s really good catch, most of the people does not know this.
    I like you responses.. thx… happy blogging.

  22. #22 by bdrouvot on November 23, 2012 - 15:00

    On remark if you need to generate less flashback logs:

    You don’t need to put the database in flashback mode to create a guarantee restore point. You just need to create the guarantee restore point with the database in the mount state : That way the database will be able to flashback only to this restore point (No other point in time possible because flashback database is off) and it will generate less flashback logs.

    Source :

    I know it is a little bit far from the initial subject (keep the database open 😉 )


  23. #23 by Uwe Hesse on November 28, 2012 - 20:56

    Thank you for mentioning, Bertrand 🙂

  24. #24 by Uwe Hesse on November 28, 2012 - 20:58

    Venkat, thank you for the nice comment!

  25. #25 by Kunle on December 1, 2012 - 00:04

    Waoh…It’s interesting to know this. Thanks

  26. #26 by Ron on January 2, 2013 - 11:30

    Even even better no need to drop down to mount state for guarantee restore point.

    SQL> create restore point NEWYEAR guarantee FLASHBACK database;
    Restore point created.
    SQL> select status from v$instance;

    Happy New Year

  27. #27 by Uwe Hesse on January 6, 2013 - 11:08

    Ron, thank you for pointing that out! It is a side benefit of the feature ‘FLASHBACK ON/OFF in status OPEN’ – previously, if you did not turn on flashback already, the guaranteed restore point did that and it could only be done in status MOUNT 🙂

  28. #28 by Andrew Reid on October 11, 2013 - 08:56

    Dear Uwe,
    Thank you for this. I have translated it into Spanish at this URL.
    Kind Regards,

  29. #29 by Uwe Hesse on October 11, 2013 - 09:33

    Hi Andrew, you’re welcome! Thank YOU for the translation. The URL didn’t make it into the comment, though.
    Kind regards

  30. #30 by Andrew Reid on October 11, 2013 - 18:46

    Dear Uwe,
    Sorry, I didn’t make it clear. If you click on my name at the start of the comment, it takes you to my post.
    Here is the URL:
    … and here is a shortened version for anybody who prefers these:
    Kind Regards,

  31. #31 by Danny on July 31, 2014 - 12:19

    Dear Uwe,
    Thanks for your demo, I had a confuse very long. Do any setting effect after it change successfully in oracle database . I know that were a hard task to verify it . To hope every body discuss its experience .

  32. #32 by Hayat Khan on December 8, 2014 - 07:30

    Is there any way: if we restore the restore-point, our Standby Site don’t needs to be recreated

  33. #33 by Uwe Hesse on December 12, 2014 - 16:12

    Hayat Khan, if you resetlogs the primary respectively restore an older version of the primary, you need to do the same with the standby. This post show the effect of doing flashback database with the primary and what to do than with the standby without having to recreate it: https://uhesse.com/2010/08/06/using-flashback-in-a-data-guard-environment/

  34. #34 by Srinivas on February 6, 2015 - 22:04

    Excellent this document. Thank you very much.

  35. #35 by PaulaWalters on May 5, 2015 - 20:45

    Need to delete Flashback. Please show me how.

  36. #36 by clusterclounds on August 17, 2015 - 09:53

    Do you know significance of having a restore point with flashback database OFF (flashback_on = NO) ?

    The restore point gets created but flashback is disabled. What can be the purpose.

  37. #37 by Uwe Hesse on August 17, 2015 - 13:29

    A restore point can also be used for other operations besides flashback database. E.g. for flashback table to restore point.

  38. #38 by Mikhail on September 26, 2015 - 07:22

    FYI, this feature (enabling Flashback database while db is OPEN) is not supported in RAC.

    Also sometimes it works on RAC too, especially when load is not high.

    They updated documentation, and “Oracle Database Backup and Recovery User’s Guide” for 11.2 allows us to enable flashback logging while db is OPEN.

  39. #39 by Oscar on November 24, 2016 - 14:02

    nice demo, also a funny message when you try to do this on a pdb;

    SQL> connect / as sysdba
    SQL> select * from v$version;

    ——————————————————————————– ———-
    Oracle Database 12c Enterprise Edition Release – 64bit Production 0
    PL/SQL Release – Production 0
    CORE Production 0
    TNS for Linux: Version – Production 0
    NLSRTL Version – Production 0

    SQL> select name from v$pdbs;


    SQL> archive log list;
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 6
    Next log sequence to archive 8
    Current log sequence 8
    SQL> select flashback_on from v$database;


    SQL> alter session set container=pdb3;

    Session altered.

    SQL> select flashback_on from v$database;


    SQL> alter database flashback off;
    alter database flashback off
    ERROR at line 1:
    ORA-03001: unimplemented feature


  40. #40 by Uwe Hesse on November 26, 2016 - 12:42

    Oscar, actually you can do flashback pluggable database in 12cR2 as I have shown here: https://uhesse.com/2016/11/09/flashback-pluggable-database-now-available-in-oracle-12cr2/ But it is turned on and off on the CDB layer.

  1. Flashback Database puts Physical Standby into MOUNT from READ-ONLY OPEN « The Oracle Instructor
  2. Improved Flashback with Oracle 11g | dbinterest.com
  3. Top 10 postings in 2012 « The Oracle Instructor
  4. Happy New Year 2017! | Uwe Hesse

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: