A Simple Switchover

Version 1

    Purpose of Document

     

    To demo a simple switchover using Data Guard Broker.

     

    Preparation

     

    Start Broker, connect and use the “show configuration” to get some details about the primary and standby databases.


       

    /home/oracle:PRIMARY >dgmgrl

    DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

     

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

     

    Welcome to DGMGRL, type "help" for information.

    DGMGRL> connect /

    Connected as SYSDG.

    DGMGRL> show configuration;

     

    Configuration - broker1

     

      Protection Mode: MaxPerformance

      Members:

      PRIMARY - Primary database

       standby - Physical standby database


     

    Fast-Start Failover: DISABLED

     

    Configuration Status:

    SUCCESS (status updated 30 seconds ago)


       

    Note: The "show configuration" returns the member names exacted the way Oracle wants it for use in commands like "show database" or "validate database". You might get other combinations of member names to work, but using single quotes and the name returned by "show configuration" seems to always work.

      

    These versions of the command will fail:

     

    show database primary;

    show database PRIMARY

    show database 'primary';


       

    Switchover

     

    First valid the standby database:


     

    DGMGRL> validate database 'standby'

     

      Database Role: Physical standby database

      Primary Database: PRIMARY

     

      Ready for Switchover: Yes

      Ready for Failover: Yes (Primary Running)

     

      Current Log File Groups Configuration:

       Thread # Online Redo Log Groups Standby Redo Log Groups Status 

    (PRIMARY) (standby)

       1 3 3 Insufficient SRLs

     

      Future Log File Groups Configuration:

       Thread # Online Redo Log Groups Standby Redo Log Groups Status 

    (standby) (PRIMARY)

       1 3 3 Insufficient SRLs


     

    The “Insufficient SRLs” message is troubling, it should be confirmed.

     

    On the Primary side: 

    SQL> select group#, member from v$logfile order by group#;


     

       GROUP# MEMBER

    ---------- ------------------------------------------------------------

       1 /u01/app/oracle/oradata/PRIMARY/redo01.log

       1 /u01/app/oracle/oradata/PRIMARY/redo01a.log

       2 /u01/app/oracle/oradata/PRIMARY/redo02.log

       2 /u01/app/oracle/oradata/PRIMARY/redo02a.log

       3 /u01/app/oracle/oradata/PRIMARY/redo03.log

       3 /u01/app/oracle/oradata/PRIMARY/redo03a.log

       4 /u01/app/oracle/oradata/PRIMARY/redo04.log

       5 /u01/app/oracle/oradata/PRIMARY/redo05.log

       6 /u01/app/oracle/oradata/PRIMARY/redo06.log

       7 /u01/app/oracle/oradata/redo07.log


     

    And then just the standby logs:

     

    SQL> select group#, thread#, status from v$standby_log;


     

       GROUP# THREAD# STATUS

    ---------- ---------- ----------

       4 1 UNASSIGNED

       5 1 UNASSIGNED

       6 1 UNASSIGNED

       7 0 UNASSIGNED


     

    Looks like v$standby and broker disagree.

       

    On the Standby side

     

    SQL> select group#, thread#, status from v$standby_log;


     

       GROUP# THREAD# STATUS

    ---------- ---------- ----------

       4 1 UNASSIGNED

       5 1 ACTIVE

       6 1 UNASSIGNED

       7 0 UNASSIGNED

     
     

    So Broker is not providing the correct SRL information. For more information see this MOS Doc:

     

    Bug 14674237 - VALIDATE DATABASE from BROKER incorrectly reports on standby redo log files (Doc ID 14674237.8)


    Connect to the standby and run the switchover command.

       

    DGMGRL> connect dga/*******@STANDBY

    Connected as SYSDG.

    DGMGRL> switchover to 'standby';

    Performing switchover NOW, please wait...

    New primary database "standby" is opening...

    Operation requires start up of instance "PRIMARY" on database "PRIMARY"

    Starting instance "PRIMARY"...

    ORACLE instance started.

    Database mounted.

    Switchover succeeded, new primary is "standby"

    DGMGRL>

     

    Success!


    Summary

     

    Switchover with Broker is less complex than using SQL. A switchover can be performed in about half the commands needed for an SQL switchover and you can do it all from one server. Using a SYSDG user is also safer than using the SYS user.