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

  Dear All,

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

Note :::::
Before performing the FAILOVER activity, you should enable the FLASHBACK on standby and primary. If you missed to enable you need to rebuild the standby post failover activity.

Primary Database:

The primary database name is STBY.

Standby Database:
The Standby database name is prod.

Check the MRP process on Standby

Before doing anything let's check the MRP process on the STANDBY machine.

SQL> select sequence#,process,status from v$managed_standby;

As per the above screenshot, MRP and RFS processes are running.

Failover

To start this activity, I'm going to perform a failover on the primary database.

Step 1: Shutdown the PRIMARY Database

SQL> alter system switch logfile;

SQL> shut immediate;

Step 2: Check the MRP process again

After the shutdown of the PRIMARY database, check the MRP processes again on STANDBY.

SQL> select sequence#,process,status from v$managed_standby;

MRP process

 this time RFS is not running on the STANDBY server. after primary stoped RFS not running .

before primary stop see the RFS status 

Step 3: Connect with DG Broker on Standby

Let's connect with DG Broker on the Standby machine using the following command and check the database configuration.

$dgmgrl sys/Welcome_123

DGMGRL> show configuration

In the above screenshot, you can see there is an error "ORA-1034: ORACLE not available" with the PRIMARY database.

Step 4: START FAILOVER

Perform failover using DG broker, use the below query:

DGMGRL> failover to prod;

failover to prod

After failover, now "prod" is the primary database.

DGMGRL> show configuration

show configuration

After failover, you can see the log sequence number is reset and started from 1, as you can check in the below screenshot.

archive log list

Reinstate the old Primary

Let's reinstate the old primary database using the below steps.

Step 5: Start the old Primary till mount

To perform reinstate start the old primary till mount stage.

$sqlplus / as sysdba

SQL> startup mount

start database till mount

After mounting check the database role.

reinstate the old Primary

As per the above screenshot, the STBY database role is PRIMARY. But in the Data Guard environment, we can maintain only one Primary, and right now the prod is our Primary database.

Step 6: Reinstate Database

Now, this is the time to reinstate the database using the following command.

DGMGRL> reinstate database STBY;

reinstate database STBY

Let's check the database configuration now.

DGMGRL> show configuration

DGMGRL> reinstate database STBY;

Reinstating database "stby", please wait...

Reinstatement of database "stby" succeeded

DGMGRL> show configuration

Configuration - prod

  Protection Mode: MaxPerformance

  Members:

  prod - Primary database

    stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:

SUCCESS   (status updated 14 seconds ago)

DGMGRL> show database stby;

Database - stby

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 1 second ago)

  Apply Lag:          0 seconds (computed 1 second ago)

  Average Apply Rate: 6.00 KByte/s

  Real Time Query:    ON

  Instance(s):

    stby

Database Status:

SUCCESS

DGMGRL> show database prod;

Database - prod

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    prod

Database Status:

SUCCESS

DGMGRL>

reinstate the old Primary

Now you can see the error is removed and the STBY database becomes a physical standby database.

SQL> select sequence#,process,status from v$managed_standby;

 SEQUENCE# PROCESS   STATUS

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

         0 ARCH      CONNECTED

         0 DGRD      ALLOCATED

         0 DGRD      ALLOCATED

         0 ARCH      CONNECTED

         1 ARCH      CLOSING

         0 ARCH      CONNECTED

         0 ARCH      CONNECTED

         2 RFS       IDLE

         0 RFS       IDLE

         2 MRP0      APPLYING_LOG

10 rows selected.

Primary Node :

Standby Node :

Thanks,

Srini