Thinking Out Loud

June 27, 2007

Flashback Database and Data Guard

Filed under: oracle — mdinh @ 7:17 pm

How many people out there actually use Flashback database?

My curiosity led me to discover that flashback database works great with data guard broker because there is to need to rebuild the standby database following a failover.

The following is a very simple scenario demonstrating flashback and data guard.

Here is the current configuration:

DGMGRL> show configuration;

Configuration
Name: dg_o10gr2
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
hawk_o10gr2 – Primary database
kite_o10gr2 – Physical standby database

Current status for “dg_o10gr2”:
SUCCESS

Perform graceful switchover to standby database – kite_o10gr2.

DGMGRL> switchover to kite_o10gr2;
Performing switchover NOW, please wait…
Operation requires shutdown of instance “O10GR2” on database “hawk_o10gr2”
Shutting down instance “O10GR2″…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “O10GR2” on database “kite_o10gr2”
Shutting down instance “O10GR2″…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “O10GR2” on database “hawk_o10gr2”
Starting instance “O10GR2″…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “O10GR2” on database “kite_o10gr2”
Starting instance “O10GR2″…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “kite_o10gr2”

Since the operation is a graceful swithover, the standby database does not need to be rebuilt.

The standby database becomes primary and the primary becomes standby.

DGMGRL> show configuration;

Configuration
Name: dg_o10gr2
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
hawk_o10gr2 – Physical standby database
kite_o10gr2 – Primary database

Current status for “dg_o10gr2”:
SUCCESS

Perform failover to standby database – hawk_o10gr2.

Note: this should be executed at the current standby server.

DGMGRL> failover to hawk_o10gr2;
Performing failover NOW, please wait…
Failover succeeded, new primary is “hawk_o10gr2”

The standby database is now disabled.

DGMGRL> show configuration;

Configuration
Name: dg_o10gr2
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
hawk_o10gr2 – Primary database
kite_o10gr2 – Physical standby database (disabled)

Current status for “dg_o10gr2”:
SUCCESS

At this point, the standby database would need to be recreated.

Using flashback database saves the day as the standby database can be enable with a simple “reinstate” command.

DGMGRL> reinstate database kite_o10gr2;
Reinstating database “kite_o10gr2”, please wait…
Error: ORA-16653: failed to reinstate database

Failed.
Reinstatement of database “kite_o10gr2” failed

Startup former primary database in mount mode.

The former primary database – kite_o10gr2 is still online.

[oracle@kite]
O10GR2:/home/oracle$
sys

SQL*Plus: Release 10.2.0.2.0 – Production on Wed Jun 27 18:02:06 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options

SYS@O10GR2:PRIMARY> startup force mount;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1264968 bytes
Variable Size 964690616 bytes
Database Buffers 104857600 bytes
Redo Buffers 2928640 bytes
Database mounted.
SYS@O10GR2:PRIMARY>

Now go back and reinstate the standby database.

DGMGRL> reinstate database kite_o10gr2;
Reinstating database “kite_o10gr2”, please wait…
Operation requires shutdown of instance “O10GR2” on database “kite_o10gr2”
Shutting down instance “O10GR2″…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “O10GR2” on database “kite_o10gr2”
Starting instance “O10GR2″…
ORACLE instance started.
Database mounted.
Continuing to reinstate database “kite_o10gr2” …
Reinstatement of database “kite_o10gr2” succeeded

DGMGRL> show configuration;

Configuration
Name: dg_o10gr2
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
hawk_o10gr2 – Primary database
kite_o10gr2 – Physical standby database

Current status for “dg_o10gr2”:
SUCCESS

Whoila!

Advertisements

5 Comments »

  1. I didn’t exactly understand at which point you actually used the flashback? During reinstate operation? And why did the first reinstate fail and you had to “startup mount” the database for it to succeed?

    Other than that, it was a good post. Especially since we are planning to start using dataguard soon.

    Comment by prodlife — June 27, 2007 @ 9:36 pm | Reply

  2. prodlife,

    Thanks for stopping by.

    Blogging is still new to me and I am not especially good at it.

    The “reinstate database” will use flashback.

    I got the following error from $BDUMP/drcO10GR2.log when trying to reinstate the standby database without flashback.

    Physical RSM: Reinstatement failed. Flashback is not turned on. Can’t reinstate database without Flashback.

    The first reinstate command failed because the database needs to be mounted and not opened.

    The following error from $BDUMP/drcO10GR2.log tells me this.

    Physical RSM: Reinstatement failed. Old primary database is OPEN, must be MOUNT before database reinstatement can be issued. Re-start database and startup to MOUNT, then re-issue the REINSTATE command.

    Hence, I issued “startup force mount” against the former standby database and reissue “reinstate command”

    HTH

    Comment by mdinh — June 27, 2007 @ 10:32 pm | Reply

  3. Thanks for the clarification.

    Comment by prodlife — June 28, 2007 @ 2:02 am | Reply

  4. Do we have to enable flashback on both primary and standby for reinstate to work? If I only need to enable flashback on primary, does it matter if i decided to keep flashback logs for 24 hrs or for just 1 hr for reinstate to work fine?

    Comment by Fide — April 16, 2008 @ 5:57 pm | Reply

  5. You just showed me that my documentation is lacking the information. I no longer have access to the system because I have since moved on. However, I believe you would have to enable flashback on both primary and standby. My documentation only shows standby though, which does not really makes sense.

    If you reinstate the database within the time frame of the flashback log keep duration, then it should be fine.

    HTH.

    Comment by mdinh — April 18, 2008 @ 9:25 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: