Data Guard Role Transitions on Oracle Cloud using “dbaascli”  #DRaaS

Version 9

    Introduction

     

    We've seen the Deployment of Data Guard on Oracle Cloud, Now we will see how to perform Switchover using Oracle cloud utility dbaascli. Switchover is a planned role transition to change the database role from primary to standby and vice versa. There are various methods used like traditional SQL level, later with Data Guard Broker and using EM/EMGC. In this article, we will see how to manage Data Guard using the Oracle Cloud utility “dbaascli” and how to perform switchover for the Data Guard hosted on Oracle cloud.

     

    Dbaascli utility for Data Guard

    In early days of Data Guard, the switchover was a lengthy procedure, because we had to crosscheck various things in both primary and standby sites like

     

    • Synchronization
    • Disable job queue
    • Initiate switchover from primary
    • Initiate switchover from standby
    • Check new standby database availability
    • Synchronization

     

    This is considered as a lengthy procedure and this is simplified with Data Guard Broker with a single command “DGMGRL> switchover to EMEA” and the databases is available automatically. As mentioned in the introduction above, the same activities EM can do and again it comes with license.

     

    Question:  We have Broker with the Data Guard on cloud then why we need dbaascli utility ?

    Answer:    dbaascli utility, which can perform all Oracle database cloud service administration operations.

    Example:  With dbaascli we can start, stop the databases, patching, start/stop of listener – This shows we can perform most of the operations with dbaascli and on top of that it allows us to perform switchover, failover, reinstate etc. Therefore, it is better to make good use of this single utility as it can play major part of administration

     

                                                 Switchover - Oracle Cloud.pngb

     

    Switchover Prerequisites

    Prerequisites are not lengthy, because we do not need to do any changes at database level or at parameter level. We will check only the synchronization whether the standby is in synchronized with primary database.  In this setup, Data Guard is running on 12.1.0.2 with Maximum performance mode. 

                             2017-02-11 15_12_00-Oracle Database Cloud Service Details.png

    In our environment of Oracle Cloud to check the synchronization between primary and standby databases, we can follow below few methods.

    1. Data Guard Metrics in Service console [Above Image]
    2. Data Guard Broker
    3. Dbaascli utility

    In this article, we are discussing specifically how to perform role transition by dbaascli. We will use the dbaascli to check whether the standby database is in synchronization or not with the primary one as below:

     

    [oracle@canada-dg01 ~]$ dbaascli dataguard status --details yes

    DBAAS CLI version 1.0.0

    Executing command dataguard status --details yes

    SUCCESS: Dataguard is up and running

    {

       "instances" : [

          {

             "DATABASE_TYPE" : "PRIMARY",

             "PROTECTION_MODE" : "MAXIMUM PERFORMANCE",

             "PROTECTION_LEVEL" : "MAXIMUM PERFORMANCE",

             "ADDITIONAL_MESSAGES" : "",

             "DATABASE_NAME" : "ORC1_01",

             "PENDING_APPLY_LOG_CNT" : 0,

             "ACTIVE_SESSIONS" : "1",

            "HOST_NAME" : "canada-dg01",

             "OPEN_MODE" : "READ WRITE"

          },

          {

             "PROTECTION_LEVEL" : "MAXIMUM PERFORMANCE",

             "ADDITIONAL_MESSAGES" : "",

            "APPLY_LAG" : "00 days 00 hrs 00 min 00 sec",

             "DATABASE_NAME" : "ORC1_02",

             "APPROXIMATE_ROLE_TRANSITION_TIME" : "00 days 00 hrs 00 min 00 sec + 30 sec",

             "ACTIVE_SESSIONS" : "2",

            "HOST_NAME" : "canada-dg02",

             "TRANSPORT_LAG" : "00 days 00 hrs 00 min 00 sec last computed 0 days 0 hrs 0 min 8 sec before",

             "PROTECTION_MODE" : "MAXIMUM PERFORMANCE",

             "DATABASE_TYPE" : "PHYSICAL STANDBY",

            "OPEN_MODE" : "MOUNTED"

          }

       ]

    }

     

    [oracle@canada-dg01 ~]$ 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 - fsc

     

      Protection Mode: MaxPerformance

      Members:

      ORC1_01 - Primary database

        ORC1_02 - Physical standby database

     

    Fast-Start Failover: DISABLED

     

    Configuration Status:

    SUCCESS   (status updated 1 second ago)

     

    DGMGRL> show database 'ORC1_01'

     

    Database - ORC1_01

     

      Role:               PRIMARY

      Intended State:     TRANSPORT-ON

      Instance(s):

        ORC1

     

    Database Status:

    SUCCESS

     

    DGMGRL> show database 'ORC1_02'

     

    Database - ORC1_02

     

      Role:               PHYSICAL STANDBY

      Intended State:     APPLY-ON

      Transport Lag:      0 seconds (computed 12 seconds ago)

      Apply Lag:          0 seconds (computed 12 seconds ago)

      Average Apply Rate: 1.00 KByte/s

      Real Time Query:    OFF

      Instance(s):

        ORC1

     

    Database Status:

    SUCCESS

              DGMGRL>

     

    Switchover with dbaascli

    We have already performed the synchronization and now we will perform actual switchover as below. We need to provide the SYS password, which we use while creating  the Data Guard configuration.

     

    [oracle@canada-dg01 ~]$ dbaascli dataguard switchover --password Oracle123#

    DBAAS CLI version 1.0.0

    Executing command dataguard switchover --password Oracle123#

    Performing switchover NOW, please wait...

    New primary database "ORC1_02" is opening...

    Operation requires start up of instance "ORC1" on database "ORC1_01"

    Starting instance "ORC1"...

    ORACLE instance started.

    Database mounted.

    Switchover succeeded, new primary is "ORC1_02"

    SUCCESS : Switchover to Standby operation completed successfully

     

    Alert Log Info

    Sat Feb 11 09:41:14 2017

    SWITCHOVER VERIFY BEGIN

    SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database

    SWITCHOVER VERIFY COMPLETE

    Sat Feb 11 09:41:17 2017

    RFS[4]: Assigned to RFS process (PID:11515)

    RFS[4]: Identified database type as 'physical standby': Client is ARCH (PID:21251)

    Sat Feb 11 09:41:19 2017

    RFS[5]: Assigned to RFS process (PID:11517)

    RFS[5]: Identified database type as 'physical standby': Client is Foreground (PID:21243)

    RFS[5]: End-Of-Redo archival of thread 1 sequence 21

    Sat Feb 11 09:41:19 2017

    RFS[5]: Begin archive primary thread 1 sequence 21 (ORC1)

    RFS[5]: Selected log 5 for thread 1 sequence 21 dbid 1566911002 branch 935066100

    Sat Feb 11 09:41:20 2017

    RFS[5]: Completed archive log 3 thread 1 sequence 21 (ORC1)

    Sat Feb 11 09:41:20 2017

    ARC1: Evaluating archive log 5 thread 1 sequence 21

    Sat Feb 11 09:41:20 2017

    ARC1: Beginning to archive thread 1 sequence 21 (2646805-4148678) (ORC1)

    Sat Feb 11 09:41:20 2017

    ARC1: Creating local archive destination LOG_ARCHIVE_DEST_1: '/u03/app/oracle/fast_recovery_area/ORC1_02/archivelog/2017_02_11/o1_mf_1_21_%u_.arc' (T-1.S-21) (ORC1)

    Sat Feb 11 09:41:20 2017

    ARC3: Evaluating archive log 5 thread 1 sequence 21

    ARC3: Unable to archive thread 1 sequence 21

          Log actively being archived by another process

    Sat Feb 11 09:41:21 2017

    Resetting standby activation ID 1566922798 (0x5d65582e)

    Sat Feb 11 09:41:22 2017

    Media Recovery End-Of-Redo indicator encountered

    Sat Feb 11 09:41:22 2017

    Media Recovery Continuing

    Media Recovery Waiting for thread 1 sequence 22

    Sat Feb 11 09:41:23 2017

    SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.

     

    The Switchover completed successfully without any errors. We will check the latest status of the Data Guard configuration again as below to check all the databases are available [Especially whether new standby is available?] and the synchronization status.

     

                   2017-02-11 15_45_07-  after switchover console status unchanged.png

    After the switchover, the standby is already mounted and fully synchronized with primary database without any LAG.

     

    Summary

    We have seen which methods are available to perform the role transitions and benefits of using Oracle Cloud administrative operation utility “dbaascli” to manage the databases or Data Guard Configuration. We have also seen how to use the dbaascli utility to perform the switchover including the post checkups.

     

    Related Articles

    Deploying Data Guard on Oracle Cloud - #DRaaS

    Failover & Reinstating using "dbaascli" on Oracle Cloud - #DRaaS

     

    Nassyam Basha is a Database Administrator. He has around 10 years of IT experience as Oracle DBA, currently working as Senior Principal Consultant at Data Intensity. He holds a master's degree in Computer Applications from the University of Madras. He is an Oracle 11g Certified master an Oracle ACE Director. He actively participates in Oracle-related forums such as OTN with a status of Super Hero, Oracle support awarded as "Guru" and acting as OTN Moderator and written numerous articles with OTN and on Toad World. He maintains an Oracle technology-related blog, www.oracle-ckpt.com and can be reached at nassyambasha@gmail.com.