Saturday, October 10, 2015

Oracle Data Guard fail over using Broker

This topic  demonstrates how to  fail over on Oracle 11g data guard environment.

The below scenario is tested in version. The following database is used for fail over testing.

Data Guard

The manual fail over is tested from primary(devdb12) to standby(devdb12_dg). 

Things to check prior to fail over :

Make sure, application is down and no application connection on the database.

Verify the data guard replication  and see standby is close to primary. we need to ensure that, all the archive logs are shipped to standby and applied successfully. If not, then there will be data loss after fail over.

If we  have multiple standby, then it is better to choose the standby which has highest log sequence# to  minimize the data loss.

Generally Fail over can be done for the following circumstances
  • Primary  is crashed
  • Primary is not performing well
  • Primary database is in mount mode and not able to open the database  
Prerequisite : The data guard broker should be configured on the database.  Click  here to review the broker configuration steps in case you need.

DGMGRL for Linux: Version - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
DGMGRL> show configuration;

Configuration - devdb12_dg_broker

 Protection Mode: MaxPerformance
    chfsdb43    - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database

    devdb12_dg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DGMGRL> failover to devdb12_dg;
Performing failover NOW, please wait...
Error: ORA-16798: unable to complete recovery during failover

Unable to failover
DGMGRL> failover to devdb12_dg immediate;
Performing failover NOW, please wait...
Failover succeeded, new primary is "devdb12_dg"
DGMGRL> exit

After fail over, we need to rebuild the standby or flash back to prior stage and apply the archive log files.

No comments: