Enhancing Oracle 19C with Active Data Guard DML Redirection

   Oracle 19C introduces a powerful new feature known as Active Data Guard DML Redirection. This innovation allows DML operations (such as INSERT, UPDATE, and DELETE) on a standby database to be automatically redirected to the primary database for execution. The changes are then propagated back to the standby database, ensuring data consistency and redundancy without disrupting the standby database's read-only status.

Understanding Active Data Guard DML Redirection

Traditionally, attempting to execute DML operations on a standby database would result in an error, as the standby is primarily meant for read-only operations. With Active Data Guard DML Redirection, these operations are instead redirected to the primary database where they are executed. The resultant data changes are then transmitted back to the standby database, keeping it in sync with the primary.

Performance Considerations

While this feature adds significant flexibility, it's important to use it judiciously. Since the DML operations are executed on the primary database, an excessive number of DML operations can impact its performance. Therefore, this feature is best suited for applications that are predominantly read-heavy with occasional DML operations. For such applications, Active Data Guard DML Redirection provides the benefit of seamless updates without compromising the standby database’s availability for read operations.

Steps for Implementing DML Redirection on a Standby Database

To leverage this feature, follow these steps:

• Execute DML on Standby: The user executes a DML statement on the open standby database.
• Redirect to Primary: The DML operation is redirected to the primary database.
• Apply Changes on Primary: The primary database processes the DML operation.
• Stream Changes to Standby: The resultant data changes are streamed back to the standby database.
• Data Visibility: The updated data becomes visible to the client.

Configuring DML Redirection

DML Redirection can be configured either at the system level or the session level. The session-level setting takes precedence over the system-level setting, allowing for flexible and granular control.

System-Level Configuration

To enable DML Redirection at the system level, use the following SQL command:

ALTER SYSTEM SET ADG_REDIRECT_DML=TRUE SCOPE=BOTH;

Session-Level Configuration

For session-specific configuration, execute the following command:

ALTER SESSION ENABLE ADG_REDIRECT_DML;

Conclusion

Oracle 19C's Active Data Guard DML Redirection is a significant enhancement for database administrators and developers, offering the ability to perform necessary updates on a standby database without compromising its read-only operations. By carefully managing the DML load, organizations can take full advantage of this feature to enhance data availability and consistency across their database environments.

Thank you for reading! 

Regards,

Nikhil