This chapter describes how to create and schedule Microsoft Transaction Server-related Oracle transaction recovery.
This chapter contains these topics:
You must configure the Microsoft Transaction Server and Oracle Database environments after installing or migrating Oracle Services for Microsoft Transaction Server (OraMTS).
Configuration is not required on the Windows computer if a Microsoft Transaction Server is installed on a computer.
To configure the Microsoft Transaction Server, perform the following tasks on the computer where the Oracle Database is installed:
oramtsadmin.sql script against the database to create the Microsoft Transaction Server administrative user account (the default username is
Schedule automatic transaction recovery.
If you have an Oracle Fail Safe configuration, modify the registry values before or after running the
Distributed transaction capabilities are required to use Microsoft Transaction Server with Oracle database. Microsoft Transaction Server-related Oracle transactions become in-doubt transactions when any of the following fail:
Microsoft Transaction Server application
An Oracle MTS Recovery Service resolves in-doubt transactions on the computer that started the failed transaction.
Typically, an Oracle MTS Recovery Service is automatically created and started with Oracle Services For Microsoft Transaction Server. However, for Oracle Database releases 18.104.22.168 through 11.2.0.x, depending on the options you choose during the install, Oracle MTS Recovery Service may be created, but not started at the end of the installation. In this case, it must be started manually.
Only one Oracle MTS Recovery Service can be installed for each computer. A scheduled recovery job on each Microsoft Transaction Server-enabled database permits the Oracle MTS Recovery Service to resolve in-doubt transactions.
The Oracle MTS Recovery Service resolves an in-doubt Microsoft Transaction Server transaction in the following order:
The DBMS recovery job detects an in-doubt MTS-related transaction.
The DBMS recovery job extracts the recovery service's endpoint address from the XID of the in-doubt transaction and requests the recovery service for the outcome of the MTS/MS DTC transaction.
The recovery service requests its MS DTC for transaction outcome.
The recovery service reports transaction outcome to the DBMS job process.
The DBMS recovery job commits or terminates the in-doubt transaction.
OraMTS uses server-based recovery to resolve in-doubt transactions originated by MSDTC. To do this, the OraMTS administrator must be able to access the Windows middle-tier node through
oramtsadmin.sql grants execute privileges on
UTL_HTTP to the OraMTS administrator, as shown in "Configuring Automatic Transaction Recovery"
Note:Starting with Oracle version 11g, the DBA needs to create an access control list (ACL) as shown in "Creating an Access Control List (ACL)".
For Oracle database version 11g and later, the DBA must create an access control list (ACL) that grants the OraMTS administrator the privilege to make out-bound HTTP connections. Example 3-1 demonstrates this:
BEGIN -- Create the new ACL, naming it "OraMTSadmin.xml", with a description. -- This provides the OraMTS administrative user e.g. MTSADMIN user FOO -- the privilege to connect DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('OraMTSadmin.xml', 'Allow usage to the UTL network packages', 'FOO', TRUE, 'connect'); -- Now grant privilege to resolve DNS names to the OraMTS administrative user DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('OraMTSadmin.xml' , 'FOO', TRUE,'resolve'); -- Specify which hosts this ACL applies to, in this case we are allowing -- access to all hosts. if one knew the list of all Windows middle-tier, -- these could be added one by one. DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('OraMTSadmin.xml','*'); END;
Automatic transaction recovery is performed by scheduling a database job. A database job for in-doubt transactions must be scheduled for each database participating in Microsoft Transaction Server transactions.
Transaction recovery is configured by running the
oramtsadmin.sql script, which triggers
prvtoramts.plb scripts to create the PL/SQL package
utl_oramts. The database view
oramts_2pc_pending is also created to show in-doubt transactions related to Microsoft Transaction Server transactions.
Creates the Microsoft Transaction Server administrator user account.
Automatically schedules database jobs for transaction recovery every one minute.
When the database job is run, it checks for unresolved global transactions in the database that are related to Microsoft Transaction Server. Information in the transaction identifiers (XIDs) of the in-doubt transactions identifies the computer on which the transaction was started. The Oracle MTS Recovery Service on that computer resolves the transaction.
Schedules post-recovery cleanup every half hour.
Schedule automatic transaction recovery in the database by performing these tasks:
JOB_QUEUE_PROCESSES initialization parameter specifies the maximum number of job slaves started on an instance.
To set and start up job-queue processes:
Ensure that you have
Go to the computer on which the Oracle Database is installed.
C:\> sqlplus /NOLOG
Connect to the database as
SQL> CONNECT / AS SYSDBA
JOB_QUEUE_PROCESSES initialization parameter:
The default value for this parameter is
0. Set this parameter to a value greater than
1 if there are many destinations to which to propagate the messages.
Shut down the Oracle Database:
Restart the Oracle Database:
oramtsadmin.sql script creates the Microsoft Transaction Server administrator user account with the default username
mtssys. The Microsoft Transaction Server transaction recovery jobs run under the administrator user account.
To create and schedule automatic transaction recovery:
Ensure that you have
Log on to the computer where the Oracle Database is installed.
C:\> sqlplus /NOLOG
Connect to the database as
SQL> CONNECT / AS SYSDBA
You are prompted for the Microsoft Transaction Server administrator username and password. You can accept the default username of
mtssys and password of
mtssys, or change them.
If you did change the password in step 5, you can change it using this script:
SQL> ALTER USER USERNAME IDENTIFIED BY new_password;
To change the username after completing this task, drop the user, rerun the
oramtsadmin.sql script, and specify a different username when prompted.
Use this procedure to view Microsoft Transaction Server in-doubt transactions in the database. This procedure uses the
dbms_output package to display results.
SQL> SET SERVEROUTPUT ON SQL> EXECUTE utl_oramts.show_indoubt;
The following information appears:
========================================================= currently indoubt transactions ========================================================= formatid : 21255235 gtrid : C2229A505904974D81FB7316B147325900000000 bqual : 5BAB6A6B55CD294AA20335839110829C0100000000901944700050 local txid : 142.11.202 tx state : prepared protocol : HTTP endpoint : firstname.lastname@example.org:2030 ========================================================= formatid : 21255235 gtrid : 259DF9C8DFC5574F8876F0DF4E15CCAD00000000 bqual : 2C8DCED5B9816244BA2B73CC013EEB870100000000901944700050 local txid : 2.18.185 tx state : prepared protocol : HTTP endpoint : email@example.com:2030
This procedure is run by the transaction recovery job. An automatic database job is scheduled for
utl_oramts.recover_automatic. When the job is run, it checks for unresolved global transactions in the database that are related to Microsoft Transaction Server. Information in the XIDs of the in-doubt transactions identifies the computer on which the transaction started. The Oracle MTS Recovery Service is contacted and resolves the transactions.
Use this procedure to request the transaction manager (MS DTC) to forget resolved transactions. This procedure is run by the post-recovery cleanup job.
oramts_2pc_pending is created by executing
oramts_2pc_pending shows in-doubt transactions in the database. This view consists of the following columns:
global_transaction_id This is the transaction identifier of the Oracle global transaction corresponding to the Microsoft Transaction Server transaction. In fact, this is the globally unique identifier (GUID) of the Microsoft Transaction Server transaction.
branch_id This shows the branch identifier of the Oracle transaction. A single Microsoft Transaction Server transaction can have multiple Oracle global transactions. This depends on the number of Microsoft Transaction Server/COM+ components that span the same Microsoft Transaction Server transaction. All these transactions have the small global transaction identifier, but different branch identifiers.
To view Microsoft Transaction Server–related in-doubt transactions in the database, use SQL*Plus to query the view
Start SQL*Plus with the Microsoft Transaction Server administrator user account:
C:\> sqlplus mtsadmin_user/ mtsadmin_password
Enter the following command:
SQL> SELECT * FROM oramts_2pc_pending;
This displays the computer on which the in-doubt transaction originated.
In typical configurations, the MS DTC and Oracle MTS Recovery Service run on the same computer. This ensures that the required information for transaction recovery is available to the Oracle-Microsoft Transaction Server integration layer.
In configurations where the Microsoft Transaction Server application is part of a Windows cluster (for example, the application can fail over to another node or host in the cluster), the MS DTC runs as a cluster-wide resource. All cluster nodes use a single instance of the MS DTC running on any cluster node.
If you have an Oracle Fail Safe configuration, make sure the following registry information is replicated on all nodes in the cluster participating in Microsoft Transaction Server transactions:
To modify registry values for Oracle Fail Safe configurations:
Go to the computer on which the MS DTC and Oracle MTS Recovery Service are installed.
Start the registry from the command prompt:
The Registry Editor window appears.
Copy the registry information appearing here to all nodes in the cluster.
Reboot the computer on which you added the key.