+

Search Tips   |   Advanced Search

Set two-phase commit distributed transactions with Oracle RAC


Real Application Cluster (RAC) configurations for Oracle 10g have an inherent issue with the transaction manager when Oracle attempts to recover two-phase commit distributed transactions that span over multiple Oracle RAC nodes. A problem can occur when one node fails, and Oracle opens up the other surviving nodes for business before the Oracle RAC completes the necessary recovery action for the node that has failed. The appserver's ability to maintain transaction affinity provides you the ability to circumvent this issue.

Errors can occur when the recovery process attempts to commit or rollback a transaction branch through a RAC node that was previously active but later failed. The transaction manager would receive the following exception:

ORA- 24756: transaction does not exist

If this error is encountered, the Oracle database administrator might need to manually resolve the in-doubt transaction by forcing a rollback or commit process. If we do not desire a manual intervention, however, we might want to configure an automatic and transparent strategy for transaction recovery.If the in-doubt transaction is not resolved, any subsequent transactions will receive the following exception:

ORA-01591 lock held by in-doubt distributed transaction
The result is that portions of the database will not be usable.

The key to a transparent recovery strategy is to eliminate the possibility of a global transaction spanning more than one transaction branch over multiple RAC nodes. A transaction branch corresponds to a database connection that is enlisted in a global transaction. If all connections in a global two-phase commit transaction originate from the same node, transaction recovery problems should not arise. Set an Oracle RAC with the appserver to prevent errors with two-phase transactions.

The appserver maintains transaction affinity for incoming connections, and we can take advantage of this feature to configure automatic recovery for Oracle RAC with two-phase commit transactions. If we implement this configuration, all connections from a given application server will be received from the same Oracle node, and the connections will finish on that same node. This configuration will avoid situations in which transactions span multiple nodes, and you should not experience a recovery problem if one or more Oracle nodes go down.

 

 

Results

If we configured an automatic recovery strategy, the DTP service will start automatically on the preferred instance. However, if the database is restarted, the DTP service will not start automatically. We can start the DTP service using this command:

srvctl start service -d  -s

If a RAC node stops working, Oracle will not failover the DTP service until the Oracle RAC cleanup and recovery is complete. Even if the Oracle nodes come back up, the Oracle DTP service will not return to the freshly restarted RAC node. Instead, you will have to manually move the service to the restarted RAC node.

When you configure DTP on the Oracle service, we have transferred load balancing from the Oracle JDBC provider to the appserver. The workload will be distributed by the appserver instead of Oracle, which is why you created services that do not implement load balancing and only use one primary node. This configuration prevents situations in which transaction processes span multiple RAC nodes and alleviates recovery problems that can arise when one or more RAC nodes fail.

 

Related tasks


Set Oracle connection caching in the appserver
Set Oracle Real Application Cluster (RAC) with the appserver
Set a JDBC provider and data source