Turning Flashback Database on & off with Instance in Status OPEN

I have tought an Oracle Database 11gR2 Administration Workshop II this week in Düsseldorf. During that course, I got aware of a new feature of 11gR2 that may not be very known yet, probably: You can turn Flashback Database on and off now 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.

Funny thing about this feature is, that apparently the Enterprise Manager (11gR2 Database Control) is still not aware of that and gives you a warning if you check the box to turn on Flashback Database, reading that this is going to restart the instance. It doesn’t, though (even not if you try with OEM), but maybe you get scared off by the warning already :-)

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 now turn on Flashback Logging before that operation and turn it off afterwards without limiting the availability of your Database!

About these ads

,

  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

    Interesting!

  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

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

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

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

  13. #13 by Uwe Hesse on July 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 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.
    http://forums.oracle.com/forums/message.jspa?messageID=4465876#4465876

  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 :-)

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

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

  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 :
    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 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;
    STATUS
    ————
    OPEN
    SQL>

    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,
    Andrew

  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
    Uwe

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

  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

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

Follow

Get every new post delivered to your Inbox.

Join 2,604 other followers

%d bloggers like this: