Pickleball spielen

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.