Active Standby Database DML Redirection for Reporting Consistency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Physical standby databases face limitations due to their read-only mode, which restricts changes and severely limits the capabilities of reporting applications, as they need to maintain data consistency with the primary database to ensure immediate availability in case of disasters, but this restricts the ability to make modifications.
Innovation Solution
Implementing an active standby database with read-only functionality, where DML statements are redirected to the primary database, and changes are communicated back to the standby database, allowing for query execution with either committed or uncommitted changes based on session association, ensuring data consistency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a physical standby database is set to read-only mode to maintain data consistency with the primary database, then data consistency and disaster recovery capability are improved, but the capability to execute DML statements and flexibility for reporting applications deteriorate
Solution Approach 1:
The patent implements dynamic DML redirection where the standby database can dynamically redirect DML statements to the primary database based on configuration settings. This allows the system to transition between read-only and read-write modes, enabling reporting applications to execute DML statements against the standby database while maintaining data consistency through automatic redirection to the primary database for actual data modifications.
Solution Approach 2:
The patent introduces an intermediary mechanism (DML redirection layer) between the reporting applications and the standby database. This intermediary captures DML statements from reporting applications, redirects them to the primary database for execution, and applies the changes to the standby database through redo log application. This mediator enables reporting applications to function with full DML capabilities while the standby database maintains its role as a consistent replica for disaster recovery.
2Adaptability or versatility
If DML statements are allowed on the standby database to enhance reporting application capabilities, then adaptability and versatility are improved, but data consistency with the primary database deteriorates
Solution Approach 1:
The patent implements a feedback mechanism where DML statements executed on the standby database are automatically redirected to the primary database, and the results are fed back to maintain consistency. The system monitors DML operations, redirects them to the primary database for authoritative execution, and applies the changes back to the standby database through redo log application, ensuring the standby remains a consistent replica.
Solution Approach 2:
The patent applies preliminary action by pre-configuring the standby database with DML redirection capabilities before reporting applications attempt to execute DML statements. The redirection mechanism is already in place and configured to route DML operations to the primary database, preventing data consistency issues before they can occur. This preliminary setup ensures that reporting applications can operate with full DML capabilities while maintaining data integrity.
3Reliability
If the standby database is kept in read-only mode to ensure immediate availability for disaster recovery, then reliability and speed of failover are improved, but productivity and resource utilization deteriorate
Solution Approach 1:
The patent makes the standby database multi-functional by enabling it to serve both as a disaster recovery replica and as an active reporting database. Through DML redirection, the standby database can accept and process DML statements from reporting applications, transforming it from a single-function read-only replica into a universal database that supports both disaster recovery and reporting workloads, thereby improving resource utilization without compromising reliability.
Solution Approach 2:
The patent implements self-service by allowing reporting applications to directly execute DML statements against the standby database without requiring separate connections to the primary database. The standby database automatically handles DML redirection and consistency maintenance, enabling reporting applications to serve themselves from the standby database resources while the system automatically manages the complexity of maintaining data consistency and coordinating with the primary database.
Data Source
AI summary
A database session in an active standby server on which an active standby database resides receives a DML statement. The session is suspended while the statement is redirected over a database link to a primary database on which the statement is executed. Information associated with execution of the statement is communicated to the session in the active standby server. Redo records describing changes to the contents of the primary database are applied to the active standby database and control is returned to the session. Prior to commitment of a transaction including the statement, a query directed to data to which the statement was directed is received at the active standby server from a client and executed on the active standby database absent use of a database link based on whether information associated with a database session associated with the client matches the information associated with execution of the statement.