Turning Flashback Database on & off with Instance in Status OPEN

keep_it_up

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;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select log_mode,flashback_on from v$database;
LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO
SQL> select status from v$instance;
STATUS
------------
OPEN

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;
LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES
SQL> alter database flashback off;
Database altered.
SQL> select log_mode,flashback_on from v$database;
LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   NO

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 von Kamran Agayev A. am Juni 25, 2010 - 19:59

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

    Thanks for reminding

  2. #2 von Ulfat am Juni 26, 2010 - 07:20

    Interesting!

  3. #3 von Asif Momen am Juni 26, 2010 - 11:20

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

  4. #4 von Surachart Opun am Juni 26, 2010 - 13:12

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

  5. #5 von Svetoslav Gyurov am Juni 29, 2010 - 08:03

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

  6. #6 von Uwe Hesse am Juli 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 von Kamran Agayev A. am Juli 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 von Hashmi am Juli 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 von Uwe Hesse am Juli 28, 2010 - 09:12

    Hashmi,
    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 von Rajeshkumar Govindarajan am Juli 28, 2010 - 11:59

    thanks for sharing.

  11. #11 von zaigam dilshad am Juli 28, 2010 - 12:43

    even i am also not aware of this feature…
    greatttttttttttttttttttttt………….

  12. #12 von Oviwan am Juli 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.“

    http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_9011.htm#SQLRF01801

  13. #13 von Uwe Hesse am Juli 29, 2010 - 15:45

    Oviwan,
    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 von Rajeshkumar Govindarajan am Juli 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.
    http://forums.oracle.com/forums/message.jspa?messageID=4465876#4465876

  15. #15 von Hashmi am Juli 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 von Uwe Hesse am Juli 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 🙂

    Hashmi,
    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 von SYSDBA am Februar 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 von oracleman am Mai 25, 2011 - 21:22

    great info

  19. #19 von Fernando am Mai 30, 2012 - 17:11

    Maybe they should update this note with your posts

    https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=249319.1

  20. #20 von Uwe Hesse am Juni 1, 2012 - 08:50

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

  21. #21 von Venkat am Juli 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 von bdrouvot am 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 :
    http://docs.oracle.com/cd/B19306_01/backup.102/b14192/rpfbdb002.htm

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

    Bertrand

  23. #23 von Uwe Hesse am November 28, 2012 - 20:56

    Thank you for mentioning, Bertrand 🙂

  24. #24 von Uwe Hesse am November 28, 2012 - 20:58

    Venkat, thank you for the nice comment!

  25. #25 von Kunle am Dezember 1, 2012 - 00:04

    Waoh…It’s interesting to know this. Thanks

  26. #26 von Ron am Januar 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;
    STATUS
    ————
    OPEN
    SQL>

    Happy New Year

  27. #27 von Uwe Hesse am Januar 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 von Andrew Reid am Oktober 11, 2013 - 08:56

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

  29. #29 von Uwe Hesse am Oktober 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
    Uwe

  30. #30 von Andrew Reid am Oktober 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:
    http://mi-blog-sobre-oracle.blogspot.co.uk/2013/10/caracteristica-nueva-de-flashback-en.html
    … and here is a shortened version for anybody who prefers these:
    http://goo.gl/DLK66R
    Kind Regards,
    Andrew

  31. #31 von Danny am Juli 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 von Hayat Khan am Dezember 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 von Uwe Hesse am Dezember 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 von Srinivas am Februar 6, 2015 - 22:04

    Excellent this document. Thank you very much.

  35. #35 von PaulaWalters am Mai 5, 2015 - 20:45

    Need to delete Flashback. Please show me how.

  36. #36 von clusterclounds am 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 von Uwe Hesse am 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 von Mikhail am September 26, 2015 - 07:22

    FYI, this feature (enabling Flashback database while db is OPEN) is not supported in RAC.
    Ref: ENABLE FLASHBACK DATABASE IN RAC ENVIRONMENT (Doc ID 416496.1)

    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 von Oscar am November 24, 2016 - 14:02

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

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

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

    SQL> select name from v$pdbs;

    NAME
    ——————————————————————————————————————————–
    PDB$SEED
    PDB1
    PDB2
    PDB3

    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;

    FLASHBACK_ON
    ——————
    YES

    SQL> alter session set container=pdb3;

    Session altered.

    SQL> select flashback_on from v$database;

    FLASHBACK_ON
    ——————
    YES

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

    SQL>

  40. #40 von Uwe Hesse am 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

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..