3
Managing Recovery Scenarios

This chapter describes how to create and schedule Microsoft Transaction Server-related Oracle transaction recovery.

This chapter contains these topics:

• Microsoft Transaction Server Configuration Requirements
  
  
• Microsoft Transaction Server Transaction Recovery Overview
  
  
• Scheduling Automatic Microsoft Transaction Server Transaction Recovery
  
  
• Viewing Microsoft Transaction Server In-Doubt Transactions
  
  
• Modifying Registry Values for Oracle Fail Safe Configurations

Microsoft Transaction Server Configuration Requirements

You must configure the Microsoft Transaction Server and Oracle database server environments after installing or migrating Oracle Services for Microsoft Transaction Server (MTS). Review Table 3-1 to identify what you must configure.

Table 3-1 Configuration Requirements

On This Computer...	Is Any Oracle Configuration Required?	See...
Client computer	No	Not applicable
Windows NT computer where Microsoft Transaction Server is installed	No	Not applicable
Computer where the database server is installed	Yes. Perform the following procedures: Run the oramtsadmin.sql script against the database server to: Create the Microsoft Transaction Server administrative user account (the default username is mtssys). Schedule automatic transaction recovery.	"Microsoft Transaction Server Transaction Recovery Overview" "Scheduling Automatic Microsoft Transaction Server Transaction Recovery"
-	For Oracle Fail Safe configurations, you can modify the registry values before or after running the oramtsadmin.sql script.	"Modifying Registry Values for Oracle Fail Safe Configurations" if using Oracle Fail Safe

Microsoft Transaction Server Transaction Recovery Overview

Distributed transaction capabilities are required to use Microsoft Transaction Server with Oracle. Microsoft Transaction Server-related Oracle transactions become in-doubt transactions when any of the following fail:

• Microsoft Transaction Server application
  
  
• Network
  
  
• Microsoft Distributed Transaction Coordinator (MS DTC)

An Oracle MTS Recovery Service resolves in-doubt transactions on the computer that started the failed transaction. An Oracle MTS Recovery Service is automatically installed with Oracle Services for Microsoft Transaction Server. Only one Oracle MTS Recovery Service can be installed for each computer. A scheduled recovery job on each Microsoft Transaction Server-enabled database server permits the Oracle MTS Recovery Service to resolve in-doubt transactions.

Figure 3-1 describes how in-doubt transactions are resolved.

Figure 3-1 Resolution of In-Doubt Microsoft Transaction Server Transactions

Text description of the illustration ntmts010.gif

Scheduling Automatic Microsoft Transaction Server Transaction Recovery

Automatic transaction recovery is performed by scheduling a database job. A database job for in-doubt transactions must be scheduled for each database server participating in Microsoft Transaction Server transactions.

Transaction recovery is configured by running the oramtsadmin.sql script. This script runs the scripts utl_oramts.sql and prvtoramts.plb 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.

The oramtsadmin.sql script does the following:

• 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 server 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 server by performing the following tasks:

• Task 1: Set and Start Up SNP Processes
  
  
• Task 2: Create and Schedule Automatic Transaction Recovery

Task 1: Set and Start Up SNP Processes

The JOB_QUEUE_PROCESSES initialization parameter specifies the number of job queue (SNP) processes started in an instance.

To set and start up SNP processes:

1. Ensure that you have SYSDBA privileges.
   
   
2. Go to the computer on which the database server is installed.
   
   
3. Start SQL*Plus:
   
   

   C:\> sqlplus /NOLOG
   

4. Connect to the database server as SYSDBA:
   
   

   SQL> CONNECT / AS SYSDBA
   
   

5. Review the following table for the location of the initialization parameter file for the database server release:
   
   
   

   If Using This Server...	Go to...
   8.1.x or later database server	ORACLE_BASE\admin\DB_NAME\pfile\init.ora
   8.0.6 database server	ORACLE_HOME\database\initsid.ora

6. Open the initialization parameter file.
   
   
7. Set the following initialization parameter to at least this value:
   
   

   JOB_QUEUE_PROCESSES = 1
   
   

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.

8. Shut down the database server:
   
   

   SQL> SHUTDOWN
   
   

9. Restart the database server:
   
   

   SQL> STARTUP
   
   

10. Exit SQL*Plus:
    
    

    SQL> EXIT
    

Task 2: Create and Schedule Automatic Transaction Recovery

The 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.

The oramtsadmin.sql script runs the utl_oramts.sql script to grant the following privileges and roles to the administrator user account:

• CONNECT role
  
  
• SELECT_CATALOG_ROLE role
  
  
• FORCE_ANY_TRANSACTION privilege
  
  
• DBMS_JOBS package, on which EXECUTE privileges are granted
  
  
• DBMS_TRANSACTION package, on which EXECUTE privileges are granted

To create and schedule automatic transaction recovery:

1. Ensure that you have SYSDBA privileges.
   
   
2. Log on to the computer where the database server is installed.
   
   
3. Start SQL*Plus:
   
   

   C:\> sqlplus /NOLOG 
   
   

4. Connect to the database server as SYSDBA:
   
   

   SQL> CONNECT / AS SYSDBA
   
   

5. Run the oramtsadmin.sql script:
   
   

   SQL> @ORACLE_BASE\ORACLE_HOME\oramts\admin\oramtsadmin.sql;
   
   

   You are prompted individually for the Microsoft Transaction Server administrator username and password. You can accept the default username of mtssys and password of mtssys, or change them.

6. If you do not change the password in step 5, change it for the mtssys user afterward:
   
   

   SQL> ALTER USER mtssys IDENTIFIED BY new_password;
   

   Note: To change the username later, drop the user, rerun the oramtsadmin.sql script, and specify a different username when prompted.

7. Exit SQL*Plus:
   
   

   SQL> EXIT
   
   

A single PL/SQL package, utl_oramts, is created in the Microsoft Transaction Server administrator's schema. utl_oramts exposes these public procedures and creates this view:

• utl_oramts.show_indoubt procedure
  
  
• utl_oramts.recover_automatic procedure
  
  
• utl_oramts.forget_RMs procedure
  
  
• oramts_2pc_pending view

utl_oramts.show_indoubt Procedure

Type

Procedure

Arguments

None

Returns

None

Description

Use this procedure to view Microsoft Transaction Server in-doubt transactions in the database server. This procedure uses the dbms_output package to display results.

This procedure requires SERVEROUTPUT set to ON.

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      : middletier-1@foo.com:2030
=========================================================
formatid      : 21255235
gtrid             : 259DF9C8DFC5574F8876F0DF4E15CCAD00000000
bqual         : 2C8DCED5B9816244BA2B73CC013EEB870100000000901944700050
local txid    : 2.18.185
tx state      : prepared
protocol      : HTTP
endpoint      : middletier-2@foo.com:2030

utl_oramts.recover_automatic Procedure

Type

Procedure

Arguments

None

Returns

None

Description

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 server 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.

utl_oramts.forget_RMs Procedure

Type

Procedure

Arguments

None

Returns

None

Description

Use this procedure to request the transaction monitor (MS DTC) to forget resolved transactions. This procedure is run by the post-recovery cleanup job.

oramts_2pc_pending View

The view oramts_2pc_pending is created by executing oramtsadmin.sql. oramts_2pc_pending shows in-doubt transactions in the database server. This view consists of the following columns:

Formatid

This is the formatid of the global transaction in the database server.

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.

local_tx_id

A local Oracle transaction corresponds to each Microsoft Transaction Server transaction. This column shows the identifier corresponding to this local transaction.

state

This shows the state of the transaction: pending, heuristically committed, heuristically aborted, and so on.

protocol

This is the protocol that the transaction recovery job in the database server uses to communicate with the Oracle MTS Recovery Service.

endpoint

This is the endpoint of the Windows NT/2000 computer on which the Microsoft Transaction Server transaction originated. For HTTP connections, this translates to a hostname and port number.

Viewing Microsoft Transaction Server In-Doubt Transactions

To view Microsoft Transaction Server-related in-doubt transactions in the database server, use SQL*Plus to query the view oramts_2pc_pending.

To view Microsoft Transaction Server-related in-doubt transactions:

1. Start SQL*Plus with the Microsoft Transaction Server administrator user account:
   
   

   C:\> sqlplus mtsadmin_user/mtsadmin_password
   
   

2. Enter the following command:
   
   

   SQL> SELECT * FROM oramts_2pc_pending;
   
   

   This displays the computer on which the in-doubt transaction originated.

Modifying Registry Values for Oracle Fail Safe Configurations

In typical configurations, the MS DTC and Oracle MTS Recovery Service run on the same computer. In this way, 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 NT 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. Using Oracle Fail Safe, the Oracle MTS Recovery Service can also be made into a cluster wide resource.

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:

1. Go to the computer on which the MS DTC and Oracle MTS Recovery Service are installed.
   
   
2. Start the registry from the command prompt:
   
   

   C:\> regedt32
   
   

   The Registry Editor window appears.

3. Go to HKEY_LOCAL_MACHINE\Software\Oracle\OracleMTSRecoveryService.
   
   
4. Copy the registry information appearing here to all nodes in the cluster.
   
   
5. Reboot the computer on which you added the key.