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!
Thinking Out Loud 