O: Dataguard Broker

Steps to Configure DataGuard Broker

SELECT DB_UNIQUE_NAME, DATABASE_ROLE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

On primary

NAME	DB_UNIQUE_NAME	DATABASE_ROLE	SWITCHOVER_STATUS	OPEN_MODE
orcl	orcl	TO STANDBY	PRIMARY	READ WRITE

On the Standby database

NAME	DB_UNIQUE_NAME	DATABASE_ROLE	SWITCHOVER_STATUS	OPEN_MODE
orcl	alpha	NOT ALLOWED	PHYSICAL STANDBY	READ ONLY

Set below on PRIMARY AND STANDBY

alter system set DG_BROKER_START=TRUE SCOPE=BOTH;

Run below command on alpha standby database on. In my setup it is on 192.168.56.102, oraclelinux.beta server.

alter system set LOG_ARCHIVE_DEST_2='';

Connect DGMGRL Session on Primary

dgmgrl sys/password as sysdba

DGMGRL> show configuration;
DGMGRL> create configuration 'dgb1' as primary database is 'orcl' connect identifier is orcl;
DGMGRL> add database 'alpha' as connect identifier is 'alpha' maintained as physical;
DGMGRL> show configuration;
DGMGRL> edit database orap set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1522)(HOST=WIN19PRIMARY))(CONNECT_DATA=(SERVICE_NAME=orcl)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))';
DGMGRL> edit database oras set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1522)(HOST=WIN19STANDBY))(CONNECT_DATA=(SERVICE_NAME=alpha)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))';
DGMGRL> edit database orap set property ApplyLagThreshold=0;
DGMGRL> edit database orap set property TransportLagThreshold=0;
DGMGRL> edit database oras set property ApplyLagThreshold=0;
DGMGRL> edit database oras set property TransportLagThreshold=0;
DGMGRL> enable configuration;
DGMGRL> show configuration;

Once the configuration is enabled you can see the confiuration details with the following command

DGMGRL> show configuration

Configuration - orcldgb

  Protection Mode: MaxPerformance
  Members:
  orcl  - Primary database
    alpha - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 59 seconds ago)

If the configuration is showing a success, we can test it by doing a switchover to the standby database.

DGMGRL> switchover to 'alpha';

The output of the switchover command should be as below and should not show any errors.

2024-10-02T12:10:02.179+05:30
Performing switchover NOW, please wait...

2024-10-02T12:10:03.278+05:30
Operation requires a connection to database "alpha"
Connecting ...
Connected to "ALPHA"
Connected as SYSDBA.

2024-10-02T12:10:03.609+05:30
Continuing with the switchover...

2024-10-02T12:10:24.768+05:30
New primary database "alpha" is opening...

2024-10-02T12:10:24.768+05:30
Operation requires start up of instance "orcl" on database "orcl"
Starting instance "orcl"...
Connected to an idle instance.

Connected to "orcl"
ORACLE instance started.
Connected to "orcl"
Database mounted.

Database opened.

Connected to "orcl"
2024-10-02T12:11:37.140+05:30
Switchover succeeded, new primary is "alpha"

2024-10-02T12:11:37.150+05:30
Switchover processing complete, broker ready.

You can verify the switchover by issuing the following command

SELECT NAME, DB_UNIQUE_NAME, DATABASE_ROLE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

On New primary

NAME	DB_UNIQUE_NAME	DATABASE_ROLE	SWITCHOVER_STATUS	OPEN_MODE
orcl	alpha	TO STANDBY	PRIMARY	READ WRITE

On New Standby database

NAME	DB_UNIQUE_NAME	DATABASE_ROLE	SWITCHOVER_STATUS	OPEN_MODE
orcl	orcl	NOT ALLOWED	PHYSICAL STANDBY	READ ONLY WITH APPLY

Data Guard - Fast Start Failover Configuration

Oracle dataguard fast start failover is a feature that automatically transfers a failed primary database to a standby database without any manual intervention.

In a normal dataguard setup, in case the primary database fails, a notification is sent to the DBA team. The DBA has to login, investigate the logs for possible causes of the database failure and initiate an appropriate failover procedure. The turn around time (TAT) for this whole activity can range from a few minutes to a few hours and in any application where continuous uptime is critical this loss of time is unacceptable. This is where Fast Start Failover (FSFO) comes into place. The observer continuously monitors the dataguard configuration. In the case of an unhealthy primary, it initiates an automatic failover to the standby database reducing the recovery time tremendously. Additionally the failed primary will automatically be reinstated and taken as a standby into the dataguard configuration.

From the DGMGRL command prompt set the LogXptMode to 'SYNC' on both the primary and the standby database and set the protection mode to maxavailability

edit database orcl set property 'LogXptMode'='sync';
edit database alpha set property 'LogXptMode'='sync';
edit configuration set protection mode as maxavailability;

We then enable the configuration and the fast-start failover.

enable  configuration;
enable fast_start failover;

Now if we will see the configuration it will show as follows

DGMGRL> show configuration;

Configuration - orcldgb

  Protection Mode: MaxAvailability
  Members:
  alpha - Primary database
    Warning: ORA-16819: fast-start failover observer not started

    orcl  - (*) Physical standby database 

Fast-Start Failover: Enabled in Zero Data Loss Mode

Configuration Status:
WARNING   (status updated 39 seconds ago)

Fast-start failover configuration status can be seen as below

DGMGRL> show fast_start failover;

Fast-Start Failover: Enabled in Zero Data Loss Mode

  Protection Mode:    MaxAvailability
  Lag Limit:          0 seconds

  Threshold:          30 seconds
  Ping Interval:      3000 milliseconds
  Ping Retry:         0 
  Active Target:      orcl
  Potential Targets:  "orcl"
    orcl       valid
  Observer:           (none)
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

  Oracle Error Conditions:
    (none)

In this configuration you will see that the observer is not set. To start the observer run the below command on the linux command prompt.

$ nohup dgmgrl sys/oracle123@orcl "start observer" &

You can verify that the observer is running by issuing the jobs command on the command prompt.

Now the show configuration command for fast-start failover will show as below

DGMGRL> show fast_start failover

Fast-Start Failover: Enabled in Zero Data Loss Mode

  Protection Mode:    MaxAvailability
  Lag Limit:          0 seconds

  Threshold:          30 seconds
  Ping Interval:      3000 milliseconds
  Ping Retry:         0 
  Active Target:      orcl
  Potential Targets:  "orcl"
    orcl       valid
  Observer:           oraclelinux.alpha
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

  Oracle Error Conditions:
    (none)

To test fast-start failover we kill pmon on the primary database. After switchover alpha is our new primary. So on the alpha database server we grep the pmon process and kill as follows

$ ps -ef | grep pmon  
$ kill -9 {pid}

It will take sometime for the dataguard to failover to orcl as the primary database. Show configuration will show as follows.

DGMGRL> show configuration

Configuration - orcldgb

  Protection Mode: MaxAvailability
  Members:
  alpha - Primary database
    Error: ORA-1034: ORACLE not available

    orcl  - (*) Physical standby database 

Fast-Start Failover: Enabled in Zero Data Loss Mode

Configuration Status:
ERROR   (status updated 0 seconds ago)

Exit from DGMGL. Reconnect and run the configuration command again.

DGMGRL> show configuration

Configuration - orcldgb

  Protection Mode: MaxAvailability
  Members:
  orcl  - Primary database
    alpha - (*) Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: Enabled in Zero Data Loss Mode

Configuration Status:
SUCCESS   (status updated 99 seconds ago)

And again

DGMGRL> show configuration

Configuration - orcldgb

  Protection Mode: MaxAvailability
  Members:
  orcl  - Primary database
    Warning: ORA-16824: multiple warnings, including fast-start failover-related warnings, detected for the database

    alpha - (*) Physical standby database 
      Warning: ORA-16657: reinstatement of database in progress

Fast-Start Failover: Enabled in Zero Data Loss Mode

Configuration Status:
WARNING   (status updated 15 seconds ago)

Finally

DGMGRL> show configuration

Configuration - orcldgb

  Protection Mode: MaxAvailability
  Members:
  orcl  - Primary database
    alpha - (*) Physical standby database 

Fast-Start Failover: Enabled in Zero Data Loss Mode

Configuration Status:
SUCCESS   (status updated 18 seconds ago)

Now if we run the SELECT command on primary and stanby database we should get output as follows.

SELECT DB_UNIQUE_NAME, DATABASE_ROLE, SWITCHOVER_STATUS, OPEN_MODE FROM V$DATABASE;

On primary

NAME	DB_UNIQUE_NAME	DATABASE_ROLE	SWITCHOVER_STATUS	OPEN_MODE
orcl	orcl	TO STANDBY	PRIMARY	READ WRITE

On the Standby database

NAME	DB_UNIQUE_NAME	DATABASE_ROLE	SWITCHOVER_STATUS	OPEN_MODE
orcl	alpha	NOT ALLOWED	PHYSICAL STANDBY	READ ONLY WITH APPLY

References

Dataguard Broker - Setup

Thought for the day

Trust in the Lord with all your heart, and do not rely on your own understanding.

Proverbs3:5

Navigation Bar

Monday, October 28, 2024

Dataguard Broker - Steps

No comments:

Post a Comment