Oracle: Distributed transaction timeouts

Last update: 6 February 2019 (cf)

Summary

When using an Oracle database in conjunction with the Fabasoft Folio Distributed Transaction Manager (DTM), while having incorrectly configured distributed transaction timeouts, there may be database errors on commit or other actions concerning transactions.

Information

When using the Fabasoft Folio DTM with an Oracle database, there are rare conditions that may lead to a number of pending transactions on your database. Due to this commit and rollback may be impaired, leading to errors on usage of the affected domain.

Example

       Data Access Error: Database system error: ORA-02089: COMMIT is not allowed in a subordinate session

These errors are potentially caused by incorrectly configured distributed transaction timeouts:

• DISTRIBUTED_LOCK_TIMEOUT - configured on the Oracle database
  This value determines the timeout for distributed transactions in the Oracle Database.
• SesTm - configured in the Windows registry
  This value determines the timeout for database sessions in the Fabasoft DTM.
• FSCDTM_TXLOG_TIMEOUT - configured in the Windows registry
  This value determines the timeout for distributed transactions in the Fabasoft DTM.

Solution

Warning: Please consider that both solutions provided below require a complete domain restart!

Verifying distributed transaction timeout settings

In order to verify the currently active timeout settings for your domain please execute the following steps on your backend servers :

• Check the values of these Fabasoft Folio DTM registry-keys. If no value is defined the default values will be used.
• HKEY_LOCAL_MACHINE\SOFTWARE\Fabasoft\Fabasoft Components Server\Domain x.y\Service z\Datasources\Default\SesTm (default: 99)
• HKEY_LOCAL_MACHINE\SOFTWARE\Fabasoft\Fabasoft Components Server\Domain x.y\Service z\Datasources\Default\FSCDTM_TXLOG_TIMEOUT (default: 90)
• (Domain x.y is your domain id, Service z are all entries of the COO Services)
• Check the value of the DISTRIBUTED_LOCK_TIMEOUT with the following query:

       select value from v$parameter where upper(name) = 'DISTRIBUTED_LOCK_TIMEOUT'

Configuring the distributed transaction timeouts

In order to change the distributed transaction timeout settings for your domain please execute the following steps:

• Define values for all settings according to the formula:
  "FSCDTM_TXLOG_TIMEOUT " (default: 90) < " SesTm " (default: 99) < " DISTRIBUTED_LOCK_TIMEOUT " (recommended: 300).
• Stop all kernel instances
• Stop all COO-services of the affected domain.
  Note: Before stopping the COO-services ensure that all kernel instances have been shut down.
• Check and if necessary change the value of the " DISTRIBUTED_LOCK_TIMEOUT " setting on the Oracle database.
• Check and if necessary change the value of the " SesTm " and " FSCDTM_TXLOG_TIMEOUT " setting in the registry of each connected service
  Note: When using Linux the registry path is mapped to a directory structure in /etc/fabasoft/settings. For more information about managing registry keys under Linux consult the white paper „Fabasoft Folio Environment Variables“.

HKEY_LOCAL_MACHINE\SOFTWARE\Fabasoft\Fabasoft Components Server\ Domain x.y\Service z \Datasources\Default\SesTm

HKEY_LOCAL_MACHINE\SOFTWARE\Fabasoft\Fabasoft Components Server\ Domain x.y\Service z \Datasources\Default\FSCDTM_TXLOG_TIMEOUT

• Start all stopped COO-services
• Start the stopped kernel instances and thoroughly test the domain

Clearing all domain transactions

After ensuring that the settings described above are correctly configured

• Stop all COO-services of the affected domain.
  Note: Before stopping the COO-services ensure that all kernel instances have been stopped.
• Start all COO-services into recovery mode in order to clear all DTM-logs .
• Once again stop all COO-services
• Verify the registry settings of all COO-services
• Check if there are any pending transactions left on the Oracle database.

       select * from sys.dba_pending_transactions

select * from dba_2pc_pending

• If there are any transactions left, execute a rollback according to http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txnman005.htm#A...
• Restart all stopped COO-services in normal mode
• Restart the stopped kernel instances and thoroughly test the domain

References

For further information regarding the use of the Fabasoft Folio Distributed Transaction Manager please refer to White Paper - Fabasoft Folio Distributed Transaction Manager

Applies to

• Fabasoft Folio (all versions)
• Fabasoft eGov-suite (all versions)

while using an Oracle database