Oracle Data Guard switchover with DG Broker on oracle database 19C

 Dear All,

In this post i am going to share oracle Data Guard switchover  activity using  data guard  broker.

perform an Oracle Data Guard switchover with broker, you can follow below steps:

Prepare for the switchover

Before starting the switchover process, make sure that both the primary and standby databases are synchronized and that all required logs have been applied to the standby database. Also, ensure that the broker is configured and running on both the primary and standby sites.

Manual Switchover Read Here

Step 1: Check Archvie Gap

Before starting the switchover activity let's verify the archive gap between primary and standby.

On Primary:

SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d

where v.resetlogs_change# = d.resetlogs_change#

group by thread# order by 1;

On Standby:

SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d

where v.resetlogs_change# = d.resetlogs_change#

group by thread# order by 1; 

Step 2: Connect to the broker
dgmgrl sys/Welcome_123

Step 3: Verify the configuration

DGMGRL> show configuration

Step 4: Prepare for the switchover

Validate Primary Database

DGMGRL> validate database prod;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:

    prod:  Off

  Managed by Clusterware:

    prod:  NO

    Validating static connect identifier for the primary database prod...

    The static connect identifier allows for a connection to database "prod".

Validate Standby Database

DGMGRL> validate database stby;

  Database Role:     Physical standby database

  Primary Database:  prod

  Ready for Switchover:  Yes

  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:

    prod:  Off

    stby :  Off

  Managed by Clusterware:

    prod:  NO

    stby :  NO

    Validating static connect identifier for the primary database prod...

    The static connect identifier allows for a connection to database "prod".

  Current Log File Groups Configuration:

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status

              (prod)               (stby)

    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status

              (stby)                (prod)

    1         3                       0                       Insufficient SRLs

    Warning: standby redo logs not configured for thread 1 on prod

Step 5: Initiate the switchover

Initiate the switchover by running the "switchover to " command in dgmgrl. This will start the switchover process and automatically failover the primary database to the standby database.

DGMGRL> switchover to stby;

Performing switchover NOW, please wait...

Operation requires a connection to database "stby"

Connecting ...

Connected to "stby"

Connected as SYSDBA.

New primary database "stby" is opening...

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

Starting instance "prod"...

Connected to an idle instance.

ORACLE instance started.

Connected to "prod"

Database mounted.

Database opened.

Connected to "prod"

Switchover succeeded, new primary is "stby"

Step 6: Verify the switchover

After the switchover, verify that the new primary database is running by running the "show database" command in dgmgrl.

Current Primary stby

DGMGRL> show database stby

Database - stby

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    stby

Database Status:

SUCCESS

Current standby prod

DGMGRL> show database prod

Database - prod

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 0 seconds ago)

  Apply Lag:          0 seconds (computed 0 seconds ago)

  Average Apply Rate: 0 Byte/s

  Real Time Query:    ON

  Instance(s):

    prod

Database Status:

SUCCESS

Database Warnings

Sometimes you will face the below error, then you have to check the log_archive_dest_1 and log_archive_dest_2 parameter on the standby side and configure it properly.

DGMGRL> show database prod

Database - prod

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      (unknown)

  Apply Lag:          (unknown)

  Average Apply Rate: (unknown)

  Real Time Query:    ON

  Instance(s):

    prod

  Database Warning(s):

    ORA-16854: apply lag could not be determined

    ORA-16856: transport lag could not be determined

    ORA-16858: last communication time from redo source could not be determined

Database Status:

WARNING

DGMGRL> show database stby;

Database - stby

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

   

Database Status:

ERROR

Step 7: Start Recovery

After the switchover start the MRP process using the broker or manually.

Enable with Broker:

DGMGRL> EDIT DATABASE prod SET STATE='APPLY-ON';

Succeeded.

Check MRP status using SQL command, if no row is selected that means MRP is disabled.

SQL> select inst_id, process, status from gv$managed_standby where process='MRP0';

   INST_ID PROCESS   STATUS

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

         1 MRP0      APPLYING_LOG

Disable with Broker

DGMGRL> EDIT DATABASE prod SET STATE='APPLY-OFF';

Enable/Disable MRP process manually:

stop MRP :

SQL> alter database recover managed standby database cancel;

start MRP:

SQL> alter database recover managed standby database disconect from session;

Thanks,

Srini