Using Flashback in a Data Guard Environment

If Logical Mistakes happen, we can address them with the Flashback techniques, introduced in Oracle Database 10g already, even if in an Data Guard Environment. In case of “Flashback Table To Timestamp” or “Flashback Table To Before Drop”, there is nothing special to take into account regarding the Standby Database. It will simply replicate these actions accordingly.

If we do “Flashback Database” instead, that needs a special treatment of the Standby Database. This posting is designed to show you how to do that:

DGMGRL> show configuration
Configuration
 Name:                mycf
 Enabled:             YES
 Protection Mode:     MaxAvailability
 Databases:
 prima - Primary database
 physt - Physical standby database
Fast-Start Failover: DISABLED
Current status for "mycf":
SUCCESS

This is an 11g Database, but the shown technique should work the same with 10g also. Prima & Physt are both creating Flashback Logs:

SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> select database_role,flashback_on from v$database;
DATABASE_ROLE    FLASHBACK_ON
---------------- ------------------
PRIMARY          YES
SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> select database_role,flashback_on from v$database;
DATABASE_ROLE    FLASHBACK_ON
---------------- ------------------
PHYSICAL STANDBY YES

I will now introduce the “Logical Mistake” on the Primary Database:

SQL> select * from scott.dept;
 DEPTNO DNAME          LOC
---------- -------------- -------------
 10 ACCOUNTING     NEW YORK
 20 RESEARCH       DALLAS
 30 SALES          CHICAGO
 40 OPERATIONS     BOSTON
SQL> drop user scott cascade;
User dropped.

The Redo Protocol gets transmitted with SYNC to the Standby Database and is applied there with Real-Time Apply. In other words: The Logical Mistake has already reached the Standby Database. We could have configured a Delay in the Apply there to address such scenarios. But that is somewhat “old fashioned”; the modern way is to go with flashback. The background behind that is, that in case of a Disaster, hitting the Primary Site, a Delay would cause a longer Failover time. I will now flashback the Primary to get back Scott:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1299624 bytes
Variable Size             230689624 bytes
Database Buffers           75497472 bytes
Redo Buffers                6373376 bytes
Database mounted.
SQL> flashback database to timestamp systimestamp - interval '15' minute;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from scott.dept;
 DEPTNO DNAME          LOC
---------- -------------- -------------
 10 ACCOUNTING     NEW YORK
 20 RESEARCH       DALLAS
 30 SALES          CHICAGO
 40 OPERATIONS     BOSTON

There he is again! Until now, that was not different from a Flashback Database Operation without Data Guard. But now my Standby Database is no longer able to do Redo Apply, because it is “in the future of the Primary Database”. We are in step 2) of the below picture now that I added to illustrate the situation.

Picture showing Flashback in a Data Guard Environment

Now I need to put the Standby it into a time, shortly before the present time of the Primary, in order to restart the Redo Apply successfully:

DGMGRL> show configuration
Configuration
 Name:                mycf
 Enabled:             YES
 Protection Mode:     MaxAvailability
 Databases:
 prima - Primary database
 physt - Physical standby database
Fast-Start Failover: DISABLED
Current status for "mycf":
Warning: ORA-16607: one or more databases have failed
DGMGRL> show database physt statusreport
STATUS REPORT
 INSTANCE_NAME   SEVERITY ERROR_TEXT
 *      ERROR ORA-16700: the standby database has diverged
                         from the primary database
 *      ERROR ORA-16766: Redo Apply is stopped

Please notice that the show statusreport clause is a new feature of 11g. In 10g, you need to look into the Broker Logfile to retrieve that problem.

SQL> connect sys/oracle@prima as sysdba
Connected.
SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
 294223
SQL> connect sys/oracle@physt as sysdba
Connected.
SQL> flashback database to scn 294221;
Flashback complete.

I subtracted 2 from the Resetlogs Change No. above to make sure that we get the Standby close before the present time of the Primary. Now we need to restart the Redo Apply again:

DGMGRL> show configuration
Configuration
 Name:                mycf
 Enabled:             YES
 Protection Mode:     MaxAvailability
 Databases:
 prima - Primary database
 physt - Physical standby database
Fast-Start Failover: DISABLED
Current status for "mycf":
Warning: ORA-16607: one or more databases have failed
DGMGRL> edit database physt set state=apply-on;
Succeeded.
DGMGRL> show configuration
Configuration
 Name:                mycf
 Enabled:             YES
 Protection Mode:     MaxAvailability
 Databases:
 prima - Primary database
 physt - Physical standby database
Fast-Start Failover: DISABLED
Current status for "mycf":
SUCCESS

That was it!
Conclusion: Flashback is the natural counterpart of Real-Time Apply. You can address Logical Mistakes easily with it and you do not need to Delay the actualization of the Standby Database.

About these ads

, ,

  1. #1 by Kamran Agayev A. on August 6, 2010 - 13:49

    Nice article Uwe. Thanks for sharing!

  2. #2 by Emre Baransel on August 6, 2010 - 13:59

    Hi Uwe,
    It’s a good point that delay configuration in dataguard is old-fashioned. Just necessary disk space (FRA) will be enough to benefit from “flashback on”.
    Thanks for the useful post!

  3. #3 by Mohamed Azar on August 6, 2010 - 14:00

    Nice article Mr.Uwe. Thanks for sharing !!!

  4. #4 by Uwe Hesse on August 6, 2010 - 18:40

    Thank you guys for the nice feedback! @Mohamed: Uwe is my first name. You may use it without the Mr. prefix :-)

  5. #5 by Nitin on August 6, 2010 - 19:30

    Thanks Uwe. I enjoy reading your posts !! They are presented well and are easy to understand.

  6. #6 by robinma on August 7, 2010 - 16:24

    Nice,Very Thanks for sharing this article

  7. #7 by Marko on August 7, 2010 - 22:14

    Thanks for sharing. Very nice written.

    Regards!

  8. #8 by Ulfet on August 8, 2010 - 14:02

    Hi Uwe, I have read, unfortunately could not implement it. Very very necessary information for DBA. Thank you for sharing.

  9. #9 by jason arneil on August 9, 2010 - 09:34

    Hi,

    I wonder if a better way of recovering this type of situation, is to actually flashback the standby and extract the required information, as opposed to having to flashback the entire primary copy, and losing transactions that may be of interest.

    Though I guess this is just for demonstration purposes only!

    jason.

  10. #10 by Uwe Hesse on August 9, 2010 - 09:47

    Jason,
    yes, that would also be a possible resolution: Just
    1) flashback the standby
    2) open it READ ONLY
    3) export anything lost on the primary
    4) Import that to the primary
    5) restart standby to MOUNT and let it recover up to the primarys state

    But again, there maybe Logical Mistakes that are more complex as my example, where you just cannot take this approach. And finally: Yes, that was for demonstration purpose. But it is not “academically” :-)

  11. #11 by lascoltodelvenerdi on August 9, 2010 - 10:00

    What you think of using the “retention guarantee”?

    In this scenario it would be of help, am I right?

    Bye,
    Antonio

  12. #12 by PAB on August 9, 2010 - 10:54

    Hi Uwe,
    Thanks for this excellent articles.

    I have one question regarding dataguard.

    What steps we have to follow on standby database side after Database Point in time recovery or TSPITR on primary database side?

    Thanks

  13. #13 by Uwe Hesse on August 9, 2010 - 11:31

    Antonio,
    Retention Guarantee is an attribute of an UNDO Tablespace that makes the UNDO_RETENTION specified a directive instead of a wish – wish is default. If you for example set an UNDO_RETENTION=1800, you will be able to do a FLASHBACK QUERY or a FLASHBACK TABLE TO TIMESTAMP for 30 minutes into the past guaranteed. If necessary, new DML is not possible to satisfy this UNDO_RETENTION, if space in the undo tablespace is scarce. In short: Yes, you can address some logical mistakes with that for a guaranteed period of time. There is no explicit relation to Data Guard, though.

    PAP,
    the steps on the standby are exactly as in the article after a DBPITR on the primary. You need flashback logs on the standby for that also. Else you have to recreate it. I have not thought yet about TSPITR on the primary – should be similar steps as if you create a new tablespace on primary with STANDBY_FILE_MANAGEMENT=MANUAL, I guess.

  14. #14 by Hashmi on August 11, 2010 - 07:48

    Very nice demo Sir.

  15. #15 by Unbearable cone-eater on October 18, 2011 - 14:40

    That’s what i was looking for! Thanks.

  16. #16 by Alee Raza Memon on December 16, 2013 - 10:15

    Sir! Its great. Thanks alottt.

  1. En garde, with Active Data Guard « dropUser

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

%d bloggers like this: