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

Version 6

    Introduction

    We have seen few series of articles on Data Guard + Oracle cloud [See footnote references], in this article we will see how to perform failover on standby database to convert into primary and then reinstating the old primary database to physical standby database using the beautiful “dbaascli” utility on Oracle cloud, With the Oracle Cloud adoption the DBA job will be easier because no need to crosscheck everything, instead minimal check is sufficient. We will see step by step procedure on how to perform.

     

    Switchover Vs Failover

    Both of them falls under the role transitions but their nature is different. The major logical difference is Switchover is planned outage and failover is unplanned outage. In case of switchover the database roles will change. In case of Failover the standby will be activated to Read-Write mode for the production purpose and where the standby old primary database is no more useful. However this is assumption and now the case is different with new methods.  In this article we are going to discuss purely on failover and reinstate. Below is the view after the failover in Data Guard Configuration.

     

    Failover on Standby database using dbaascli

    Failover can be performed through various methods like with manual failover and automatic failover i.e. Fast Start Failover where the observer will monitor continuously both primary and standby database(s) and whenever the primary is inaccessible then based on the threshold duration the failover will be initiated on standby by observer which is running on different or same server. In his article we will see how to initiate failover.

              Failover plan.png

    Manual failover – Manual failover again can be achieved by various methods.

    1) SQL level

    • SQL> flashback database to before resetlogs;
    • SQL> alter database convert to physical standby;

    2) Data Guard Broker

    3) Dbaasci – Oracle Cloud utility

    4) EM

     

    In this article we will perform failover using dbaascli as the other utilities we are already familiar and been used since years. Below is the current configuration which is hosted on Oracle Cloud, They are running on Oracle EE 12.1.0.2 with Maximum Performance Mode.

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

     

    Synchronization Check

    Synchronization check comes into picture whenever we use the manual method, to ensure all the available archive/redo applied on standby database.

     

    [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" : [

          {

             "PROTECTION_LEVEL" : "MAXIMUM PERFORMANCE",

             "ADDITIONAL_MESSAGES" : "",

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

             "DATABASE_NAME" : "ORC1_01",

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

             "ACTIVE_SESSIONS" : "2",

             "HOST_NAME" : "canada-dg01",

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

             "PROTECTION_MODE" : "MAXIMUM PERFORMANCE",

             "DATABASE_TYPE" : "PHYSICAL STANDBY",

             "OPEN_MODE" : "MOUNTED"

          },

          {

             "PROTECTION_LEVEL" : "MAXIMUM PERFORMANCE",

             "ADDITIONAL_MESSAGES" : "",

             "DATABASE_NAME" : "ORC1_02",

             "ACTIVE_SESSIONS" : "1",

             "HOST_NAME" : "canada-dg02",

             "DATABASE_TYPE" : "PRIMARY",

             "PROTECTION_MODE" : "MAXIMUM PERFORMANCE",

             "PENDING_APPLY_LOG_CNT" : 0,

             "OPEN_MODE" : "READ WRITE"

          }

       ]

    }[oracle@canada-dg01 ~]$

     

    Faiover – dbaascli

    We’ve checked the Data Guard status above and we will initiate the failover using dbaascli as below, like wise with switchover method we have to provide the sys password used during the Data Guard Configuration.

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

    DBAAS CLI version 1.0.0

    Executing command dataguard failover --password Oracle123#

    Performing failover NOW, please wait...

    Failover succeeded, new primary is "ORC1_01"

    SUCCESS : Successfully failed over to Standby

    [oracle@canada-dg01 ~]$

     

    Alert Log Info

    Sat Feb 11 10:39:28 2017

    Data Guard Broker: A failover will now be attempted

    ALTER DATABASE FAILOVER TO ORC1_01

    Sat Feb 11 10:39:28 2017

    TMI: adbdrv BEGIN 97 2017-02-11 10:39:28.330538

    Terminal Recovery requested in process 12219

    TMI: adbdrv termRecovery BEGIN 2017-02-11 10:39:28.332031

    TMI: adbdrv krsm[ers|wrs]2 BEGIN  TR: Post Cancel RTA 2017-02-11 10:39:28.333323

    Terminal Recovery: Stopping real time apply

    Sat Feb 11 10:39:28 2017

    MRP0: Background Media Recovery cancelled with status 16037

    . . .

    . .

    .

    Opening pdb PSOFT (3) with no Resource Manager plan active

    Start service : psoft_dg

    Pluggable database PSOFT opened read write

    Completed: ALTER PLUGGABLE DATABASE ALL OPEN

    Start service : ORC1_dg

    TMI: adbdrv kktexeevt1 END Fire DB open triggers 2017-02-11 10:40:29.062517

    TMI: adbdrv kktexeevt2 BEGIN Fire role change triggers 2017-02-11 10:40:29.150662

    TMI: adbdrv kktexeevt2 END Fire role change triggers 2017-02-11 10:40:29.151248

    Starting background process CJQ0

    TMI: adbdrv END 10 2017-02-11 10:40:29.194373

    Completed: ALTER DATABASE OPEN

    Sat Feb 11 10:40:29 2017

    CJQ0 started with pid=7, OS id=1254

    ALTER PLUGGABLE DATABASE ALL OPEN

    Completed: ALTER PLUGGABLE DATABASE ALL OPEN

     

     

    Post Failover Check

     

    Now we will check the latest status of the Data Guard configuration like below.

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

    DBAAS CLI version 1.0.0

    Executing command dataguard status --details yes

    dbcore: Error: query return ORA error message

    ERROR: Dataguard not 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"

          },

          {

             "DATABASE_TYPE" : "REINSTATE",

             "PROTECTION_MODE" : "UNKNOWN",

             "PROTECTION_LEVEL" : "UNKNOWN",

             "ADDITIONAL_MESSAGES" : "ORA-16661: the standby database needs to be reinstated",

             "DATABASE_NAME" : "ORC1_02",

             "HOST_NAME" : "canada-dg02",

             "OPEN_MODE" : "DOWN"

          }

       ]

    }[oracle@canada-dg01 ~]$

     

    We can see the new primary is ORC1_01 and where the ORC1_02 is down and the status as the standby needs to be reinstated if required. We’ve seen how to perform manual failover using the dbaascli on Oracle Cloud.

     

    Reinstating/Re-enabling disabled standby database after failover

     

    This is different part of the article like rescue operation to the Data Guard configuration to reinstate the disabled physical standby database, this task is considered when the inaccessible server is available now. We will see what we are actually going to perform. We see the new primary database is in EMEA location. Due to  failover process the  database in North America  shows  disabled standby database status . We will reinstate disabled standby database as below.

    After reinstate.png

    Initially we will check Data Guard Broker status to check the current configuration.

    DGMGRL> show configuration

     

    Configuration - fsc

     

      Protection Mode: MaxPerformance

      Members:

      ORC1_01 - Primary database

       ORC1_02 - Physical standby database (disabled)

          ORA-16661: the standby database needs to be reinstated

     

    Fast-Start Failover: DISABLED

     

    Configuration Status:

    SUCCESS   (status updated 44 seconds ago)

     

    DGMGRL> show database 'ORC1_02' statusreport

    Error: ORA-16548: database not enabled

     

    DGMGRL>

     

    Reinstating

    As usual the dbaascli is easy to manage any database administrative options, in order to reinstate we have to provide the sys password which used during Data Guard Configuration as below.

     

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

    DBAAS CLI version 1.0.0

    Executing command dataguard reinstate --password Oracle123#

    Successfully reinstated dataguard instances

    Detail : Successfully reinstated database : ORC1_02

     

    [oracle@canada-dg01 ~]$

     

    Alert Log Info

    Sat Feb 11 10:48:58 2017

    RFS[2]: Assigned to RFS process (PID:2765)

    RFS[2]: Database mount ID mismatch [0x5d6f4edf:0x5d6eed8a] (1567575775:1567550858)

    RFS[2]: Not using real application clusters

    FLASHBACK DATABASE TO SCN 4258023

    Sat Feb 11 10:49:00 2017

    Flashback Restore Start

    Flashback Restore Complete

    Flashback Media Recovery Start

    Started logmerger process

    Sat Feb 11 10:49:01 2017

    Parallel Media Recovery started with 2 slaves

    Sat Feb 11 10:49:01 2017

    Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0

      Mem# 0: /u04/app/oracle/redo/redo01.log

    Sat Feb 11 10:49:01 2017

    Incomplete Recovery applied until change 4258024 time 02/11/2017 10:40:08

    Sat Feb 11 10:49:01 2017

    Flashback Media Recovery Complete

    Completed: FLASHBACK DATABASE TO SCN 4258023

    alter database convert to physical standby

    Sat Feb 11 10:49:02 2017

    ALTER DATABASE CONVERT TO PHYSICAL STANDBY (ORC1)

    Clearing standby activation ID 1566953532 (0x5d65d03c)

    The primary database controlfile was created using the

    'MAXLOGFILES 16' clause.

     

    We are done with all the steps of reinstating and we will check the latest status of the Data Guard Configuration if any issues.

    [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 2 sec before",

             "PROTECTION_MODE" : "MAXIMUM PERFORMANCE",

             "DATABASE_TYPE" : "PHYSICAL STANDBY",

             "OPEN_MODE" : "MOUNTED"

          }

       ]

    }[oracle@canada-dg01 ~]$

     

    Summary

    We discussed a bit about difference between planned and unplanned outage with various methods available to perform failover the standby database to convert to read/write mode. We also performed failover on standby database with the detailed steps with monitoring the alert logs. Finally we reinstated the disabled standby database so that the disable standby database is again part of the Data Guard configuration and all these operations we have issued using the Oracle cloud utility “dbaascli”

     

    Related Articles

    Deploying Data Guard on Oracle Cloud - #DRaaS

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

     

    Author

    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.