Oracle 19c Database Roles in Data Guard

Oracle Data Guard in Oracle 19c offers robust mechanisms to ensure data protection and availability. Understanding the various database roles in Data Guard is crucial for database administrators to configure their systems effectively to meet business requirements. This post will explore the different database roles in Oracle Data Guard, detailing their functionalities, benefits, and how to manage them.

Introduction to Data Guard Roles

Oracle Data Guard provides two primary roles for databases: the Primary role and the Standby role. Administrators can dynamically alter these roles through planned or unplanned transitions to ensure data protection and high availability.

Primary Role

The Primary database is the active database that handles all the transactions and updates. It sends redo data to the Standby databases to ensure they are in sync.

Key Characteristics:

Handles all read and write operations.
Sends redo data to the Standby databases.
Must be highly available and performant.

Transition to Standby Role:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Standby Role

Standby databases are replicas of the Primary database and can take over the Primary role in case of a planned or unplanned transition. There are three types of Standby databases: Physical, Logical, and Snapshot.

Key Characteristics:

Receives redo data from the Primary database.
Can be used for read-only operations or reporting (in case of Physical Standby).
Can become the Primary database if needed.

Transition to Primary Role:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;

Types of Data Guard Role Transitions

Switchover

A Switchover is a planned role transition from Primary to Standby and vice versa. It is typically used for maintenance operations.

Steps for Switchover:

Ensure Standby redo logs are configured.
Disconnect active sessions.
Validate the Standby database.
Perform the Switchover.

DGMGRL> VALIDATE DATABASE VERBOSE prd_stdby;
DGMGRL> SWITCHOVER TO prd_stdby;

Failover

A Failover is an unplanned role transition, usually due to an emergency where the Primary database is no longer available. It ensures minimal or no data loss depending on the protection mode.

Steps for Failover:

Initiate failover from the Standby database.
Broker determines the necessity and initiates the failover.
Recreate the original Primary database to reintegrate it into the configuration.

DGMGRL> FAILOVER TO prd_stdby;

Situations Preventing Switchover

Certain conditions can prevent a successful Switchover:

Archived redo logs are unavailable.
Point-in-time recovery is necessary.
Primary database is not open or cannot be opened.

Detailed DG Role Transition Procedures

Switchover Steps Using Broker

Verify/configure Standby Redo logs on Primary and Standbys.
Check for any synchronization errors between Primary and Standby.
Disconnect active application sessions if possible.
Validate the database.
Execute the Switchover.

DGMGRL> VALIDATE DATABASE VERBOSE london_stdy;
DGMGRL> SWITCHOVER TO prd_stdby;

Checking the Last Switchover

SQL> COLUMN STANDBY_BECAME_PRIMARY_SCN FORMAT 9999999999999999999;
SQL> SELECT STANDBY_BECAME_PRIMARY_SCN FROM V$DATABASE;
SQL> SELECT SCN_TO_TIMESTAMP(16966320784612) FROM DUAL;

Switchover Steps Using SQL Commands

Verify/configure Standby Redo logs on Primary and Standbys.
Properly configure each database for the role it will assume.
Check for redo transport errors or gaps in the Standby.
Verify that the temp file exists and points to a valid location.
For a faster role transition, stop Active DataGuard and switch to mount state.

SQL> ALTER DATABASE SWITCHOVER TO 'prd_stdy' VERIFY;
SQL> ALTER DATABASE SWITCHOVER TO 'prd_stdby';

Post Switchover:

ALTER DATABASE OPEN;
ALTER DATABASE MOUNT/OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Switchover Data Guard to Logical Standby

Considerations:

The switchover does not cause the current Primary to shut down.
It is recommended to finalize user sessions, though not necessary.
Logical Standby might not have all the data up to date.
Switchover to Logical Standby invalidates and disables all Physical and Snapshot Standbys in the configuration. Re-creation is required.
Other Logical Standbys in the configuration become Standbys of the new Primary.

Types of Failover – Manual

Performed by the DBA:

Complete: Minimizes data loss by applying all available redos on the Standby. Broker avoids disabling any Standby that is not the failover target.
Immediate: The Standby does not apply any additional redo data. This is a faster failover but requires recreating all Standbys that were not the failover target (including the old Primary).

DGMGRL> FAILOVER TO 'standby' [IMMEDIATE];

Fast-Start Failover – Automatic

Performed automatically by the Data Guard Broker:

Requires enabling Flashback and configuring the Fast Recovery Area on Primary and Standby for Fast-Start Failover.

Data Guard Role Switch- Reenabling Disabled Databases Using DGMGRL

Considerations:

Reenable broker management by reinstating or recreating disabled databases.
If a database cannot be reinstated, recreate it from a copy of the Primary.

DGMGRL> REINSTATE DATABASE boston;
DGMGRL> ENABLE DATABASE boston;

Ensuring Optimal Configuration

To ensure that the Data Guard configuration operates optimally, consider the following best practices:

Regular Monitoring:

Use Data Guard Broker commands to monitor redo transport and apply operations.
Resolve redo gaps promptly to maintain data protection.

Test Switchover and Failover:

Regularly test these operations to confirm smooth performance.
Verify all standby databases are synchronized and ready to take over the primary role.

Optimize Network Configuration:

Ensure the network between the primary and standby databases is optimized for low latency and high throughput.
Use dedicated network links for redo transport to avoid congestion and ensure timely delivery of redo data.

Conclusion

Understanding and managing the database roles in Oracle Data Guard is essential for ensuring the safety and availability of your data. By choosing the appropriate role transitions and following best practices for configuration and monitoring, you can achieve the right balance between performance and data protection for your specific environment.

See more on Oracle’s website!

Be Oracle Dataguard Certified Professional, this world is full of opportunities for qualified DBAs!