7
Troubleshooting Oracle Services for Microsoft Transaction Server

This chapter provides Oracle Services for Microsoft Transaction Server (MTS) troubleshooting information.

This chapter contains these topics:

• Tracking Oracle Services for Microsoft Transaction Server Performance
  
  
• Correcting Windows NT Explorer Problems
  
  
• Correcting Oracle Net Changes that Impact Connection Pooling
  
  
• Frequently Asked Questions About Oracle Services for Microsoft Transaction Server
  
  
• Dropping the Microsoft Transaction Server Administrative User Account

Tracking Oracle Services for Microsoft Transaction Server Performance

Trace files record information about Oracle Services for Microsoft Transaction Server performance. This information includes:

• Any errors
  
  
• Enlistment requests and outcomes
  
  
• Prepare, commit, and abort requests and their outcomes

There are two registry parameters that handle tracing within oramts.dll. oramts.dll performs the following:

• Implements the API for integrating the Oracle database server with Microsoft Transaction Server
  
  
• Works as a resource dispenser to provide pooled Oracle Call Interface (OCI) connections
  
  
• Enables clients with nonpooled OCI connections to enlist in transactions started by Microsoft Distributed Transaction Coordinator (MS DTC)
  
  
• Communicates with Oracle Services for Microsoft Transaction Server to enlist the database server in MS DTC-started transactions

Table 7-1 describes the registry parameters for handling tracing. If not previously set, both parameters are automatically set in \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID during Oracle Services for Microsoft Transaction Server installation. Use the registry parameters instead of setting these parameters as environment variables. Setting environment variables requires you to restart your computer for the changes to occur.

Table 7-1 Trace Registry Parameter

Parameter	Description	Datatype	Default Value
ORAMTS_CP_TRACE_LEVEL	Traces the resource dispenser layer within oramts.dll. The trace filename uses the following format: oramtscppid.trc where pid is the identification number of the process. New trace information is always added to the bottom of the file.	REG_SZ	0
ORAMTS_CP_TRACE_DIR	Sets the output directory in which trace files are generated.	REG_SZ	ORACLE_BASE\ORACLE_HOME\oramts\trace

Table 7-2 shows the range of ORAMTS_CP_TRACE_LEVEL trace values. Set ORAMTS_CP_TRACE_LEVEL to a value other than 0 only when tracing is necessary.

Table 7-2 Trace Registry Parameter Value

Level	Description
0	Disables tracingFoot 1
1	Traces errors only
2	Traces important events in addition to errors
4	Traces function entry/exit, important events, and errors
5	Traces reference counting function and constructor/destructor entry/exit

1 If the registry parameter is not set in the registry or as an environment variable, then tracing is disabled (the same as setting the level to 0). Note also that Level 3 is not currently supported. If you set this parameter to 3, level 2 tracing is instead enabled.

Correcting Windows NT Explorer Problems

If you experience Windows NT Explorer crashes or other unexpected Windows NT problems when using Microsoft Transaction Server with a database server, install the Windows NT 4.0 Service Pack 5 or greater (available from Microsoft).

Correcting Oracle Net Changes that Impact Connection Pooling

The connection pool provided by the ORAMTS layer (that is, oramts.dll) uses a connection's net service name to identify pooled connections for an application. If changes are made to the net service name, and there are currently pooled connections, the application using the connection pool must be stopped and restarted. These changes can include altering the host or the database system identifier (SID) for the net service name in the tnsnames.ora file.

These changes ensure that all currently pooled connections corresponding to the old net service name are destroyed and any new pooled connections use the changes made to the net service name. This includes any application hosting Microsoft Transaction Server components.

To empty connection pools:

Frequently Asked Questions About Oracle Services for Microsoft Transaction Server

This section presents answers to common questions.

Question: How do I design an application when I have multiple database servers?

Answer: Oracle clients can establish connections to a database server in two ways:

• Typical Oracle clients establish connections to a database server using a dedicated server configuration. In a dedicated server configuration, one client corresponds to one Oracle server process.
  
  
• For scalability under heavy loads, Oracle clients have the option of using a shared server configuration. In a shared server configuration, a single Oracle server process can be shared by more than one client connection.

Microsoft Transaction Server communicates with the database server through distributed transactions. In a dedicated server configuration, you cannot use distributed updates (data manipulation language statements across database links) from other database servers. However, if the original connection to the database server is established using shared server configurations, the distributed updates from other database servers succeed.

To use data manipulation language statements in shared server configurations, set the following parameter in the tnsnames.ora file:

SERVER=dedicated

This forces the Oracle Net listener to provide a dedicated connection.

Figure 7-1 shows this process.

Figure 7-1 Distributed DML statements from MTS applications

Text description of the illustration ntmts009.gif

Question: What are the differences between Oracle Net connection pooling, OCI connection pooling, and Microsoft Transaction Server connection pooling?

Answer: Oracle Net connection pooling is a server-side feature that is implemented only if the database server is configured for shared server support. Oracle Net connection pooling enables you to minimize the number of physical network connections to a shared server. This is achieved by sharing a dispatcher's set of connections among multiple client processes.

Microsoft Transaction Server provides a resource pooling infrastructure that enables certain resources to be pooled, such as memory and database connections. The OCI connection pooling layer works with Microsoft Transaction Server resource pooling to provide pooled Oracle client/server sessions. The OCI connection pooling layer also caches Oracle Net connections to reduce client/server session setup time.

Question: What are in-doubt transactions?

Answer: Oracle uses distributed transactions in the following configurations:

• Distributed database configurations (for example, distributed updates using database links)
  
  
• External transaction monitors (for example, Tuxedo, MS DTC) for coordinating transaction outcome

The two-phase commit protocol completes these transactions. During phase one, the transaction monitor (TM) requests the various resource managers involved in the TM's transaction to prepare the underlying distributed transactions. In phase two, the TM determines whether it commits or aborts the transaction, and requests the resource managers to commit or abort the underlying transaction. If a resource manager fails to receive the phase two notification, the underlying distributed transaction becomes in-doubt.

To integrate Oracle with Microsoft Transaction Server, distributed transactions are used in the database. Distributed transactions correspond to transactions coordinated by the MS DTC. A distributed transaction can become in-doubt when the transaction cannot commit or abort (phase two of the two-phase commit). This occurs when the Microsoft Transaction Server application server process, database server, or network fails.

Dropping the Microsoft Transaction Server Administrative User Account

The Microsoft Transaction Server administrative user account is created by running the oramtsadmin.sql script with Oracle9i release 2 (9.2). If you later change the database server with which Microsoft Transaction Server is coordinating transactions, you can drop the administrative user account schema from the previous database server.

To drop the Microsoft Transaction Server administrative user account:

1. Start SQL*Plus:
   
   

   c:\> sqlplus /NOLOG
   
   

2. Connect to the database server as SYSDBA:
   
   

   SQL> CONNECT / AS SYSDBA
   
   

3. Enter the following command to drop administrative user account schema:
   
   

   SQL> DROP USER mtsadmin_username CASCADE;
   
   

   where mtsadmin_username is the Microsoft Transaction Server administrative user account (default is mtssys).

4. See Chapter 3, "Managing Recovery Scenarios" for information on creating the Microsoft Transaction Server administrative user account for the new database server.