Subscribe YouTube Channel For More Live Tutorials

Active Data Guard DML Redirection

DML Redirection lets a standby database in an Oracle Data Guard setup handle DML operations (INSERT, UPDATE, DELETE) aimed at it. These operations get sent to and run on the primary database, and the changes then come back to the standby database as part of the usual redo apply process. This means you can use the standby database for both reading and writing turning it into a fully updatable standby.

How DML Redirection Works

  1. User Session and Transaction Handling:
    • Active Data Guard catches DML operations that start on the standby database.
    • It wraps the operation in a transaction and sends it to the primary database.
  2. Execution on the Primary Database:
    • The primary database runs the DML operation as if it began there.
    • Oracle keeps data consistent and maintains transaction integrity across both databases.
  3. Redo Data Propagation and Application:
    • The primary database creates redo data that includes the changes from the DML operation.
  • The standby database gets this redo data and applies it. This keeps the standby database in sync with the primary one.
  1. Client Response:
    • After the standby finishes applying the redo, you can see the results of the DML operation.
    • The standby database session gets the DML operation’s outcome just as if it ran .

Configuration and Setup

You need to take multiple steps to set up Active Data Guard with DML Redirection. Your main focus should be on making sure you configure redo transport services and role transitions :

  1. Start Redo Transport Services:
    • Set up and launch the redo transport service on the main database to make sure redo data keeps moving to the backup.
  2. Set Up Standby for Redirection:
    • The backup database needs settings to take in redirected DML transactions. This often means adjusting startup settings to turn on this feature.
  3. Handle Roles:
    • Check that the databases have the right roles (main and backup) and that DML redirection works when the backup is active (read-write).

Advantages of DML Redirection

  1. Better Availability:
    • The backup database can handle read-write tasks giving non-stop access even when the main database needs fixing or stops working.
  2. Better Use of Resources:
    • By letting the backup do some work, the system can spread the load between the main and backup servers making the best use of the hardware.
  3. Stronger Backup Plans:
    • With DML redirection, the backup database stays almost in sync with the main one making backup plans more reliable.

Use Cases

  • Maintenance and Upgrades:
    • Keep the primary database running while you fix or improve it. Just send all the write operations to the standby instead.
  • Load Balancing:
    • When things get busy, you can send some of the heavy write jobs to the standby. This helps manage the load on the primary.
  • Reporting and Queries:
    • Run your big complex queries and reports on the standby. Meanwhile, the primary can keep handling the live stuff by sending DML changes to the standby.