Flashback Database puts Physical Standby into MOUNT from READ-ONLY OPEN

Another bit of discovery from my present Data Guard course in Munich: If you have a Physical Standby opened READ-ONLY, you can do Flashback Database in that state without first shutting down and going to status MOUNT.

I think that this is an 11g New Feature, but I didn’t check it with 10g yet. Let’s have a look first at the ordinary behavior:

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

DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON
---------------- -------------------- ------------------
PRIMARY          READ WRITE           YES

SQL> flashback database to timestamp systimestamp - interval '5' minute;
flashback database to timestamp systimestamp - interval '5' minute
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

This is not a Standby Database and the error is completely expectable. Everybody knows that Flashback Database can only be done in status MOUNT, right? Well not always:

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 database_role,open_mode,flashback_on from v$database;

DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON
---------------- -------------------- ------------------
PHYSICAL STANDBY READ ONLY            YES

SQL> flashback database to timestamp systimestamp - interval '5' minute;

Flashback complete.

It did the Flashback Database! And very fast also. But silently, the instance is now placed in status MOUNT:

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

DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON
---------------- -------------------- ------------------
PHYSICAL STANDBY MOUNTED              YES

In fact, I did not discover this behavior myself, but an attendee did and later on asked me, why Real-Time Query is now no longer in place on the Standby – that is because it is now in status MOUNT. I would not even had try to do that Flashback Database, because I ‘knew’ that it can’t work :-)

Now did you know that?

By the way, since 11gR2, you can turn on Flashback Database for an ordinary Database with it in status OPEN, as I have already posted here.

About these ads

, ,

  1. #1 by joel garry on December 23, 2010 - 02:04

    Does the standby keyword make a difference in the flashback command? (I don’t have one to test). I would think changing the database status without telling would be a bug. Or maybe it is on purpose because with realtime apply and flashback Oracle can’t tell what you want to wind up with.

  2. #2 by Uwe Hesse on December 23, 2010 - 07:34

    Joel,
    there is no ‘standby keyword’ in the game here – the command is the same in both cases. The difference is that the second Database IS a Physical Standby. And as you could see, Flashback Database is not possible in status OPEN with an ordinary (Primary) Database. Even not if opened READ-ONLY, by the way. Furthermore, Real-Time Apply was not on. You need to interrupt it first. But you do not need to shutdown and startup mount then. I wouldn’t call that a bug but a time saver, because the Database needs to be in MOUNT if Flashback Database is done anyway. It just gets there faster with the shown behavior.

  3. #3 by kiranjeet kaur on August 3, 2012 - 14:48

    [oracle@localhost ~]$ dgmgrl
    DGMGRL for Linux: Version 10.2.0.1.0 – Production

    Copyright (c) 2000, 2005, Oracle. All rights reserved.

    Welcome to DGMGRL, type “help” for information.
    DGMGRL> connect sys@kiran;
    Password:
    Connected.
    Error:
    ORA-16525: the Data Guard broker is not yet available
    ORA-06512: at “SYS.X$DBMS_DRS”, line 191
    ORA-06512: at line 1
    please help me sir
    kiran
    thank you

  4. #4 by Uwe Hesse on August 3, 2012 - 15:35

    Looks like you forgot to set DG_BROKER_START=true

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,889 other followers

%d bloggers like this: