Link to home
Start Free TrialLog in
Avatar of NBee
NBee

asked on

Microsoft Transaction Server and Oracle

Hi all!
I have two servers: one is Oracle Server and the other used for COMs.
 -Oracle Server:
         Windows NT Server 4.0 SP4
         Oracle 8i 8.1.6
         Windows NT Option Pack 4.0
 -COM Server:
         Windows NT Service 4.0 SP4
         Oracle 8i Client
         Windows NT Option Pack 4.0

I also have a client program call a business COM from COM Server. This COM will call to data COM which will connect to ORACLE Server to retrieve data (connection string="Provider=MSDAORA.1;Password=PWD;User ID=UID;Data Source=DS;Persist Security Info=True").
Both COM and client program are written in Visual Basic 6.0. And COMs are set as follow
 - Business COM: Require new transaction
 - Data COM : Uses transaction

When the program execute, I get a message box like this:
     "Catastrophic failure"
What's wrong?
Help me please!
Avatar of dbalaski
dbalaski

I've run into this before and assisted someone else with the problem.
It turned out to be a missing script installed into the oracle database --  the script was supposed to be obsolete,  however  it is still required.  (bad documentation)
Here is the solution:



=========================================================
There is an oracle script required to utilize Microsoft MTS with oracle -- read the following fully through:
================================
Oracle8i Application Developer's Guide - Fundamentals
Release 2 (8.1.6)   A76939-01

Chapter 16:  Working with Transaction Monitors with Oracle XA
......

Installation

No scripts need be executed to use XA. It is necessary, however, to run the xaview.sql script to run Release 7.3 applications with the Oracle8 Server. Grant the SELECT privilege on SYS.DBA_PENDING_TRANSACTIONS  to all users that connect to Oracle through the XA interface.

.......
No Installation Script Is Needed to Run XA

The SQL script XAVIEW.SQL is not needed to run XA applications in Oracle Version 8. It is, however,
Still necessary for Version 7.3 applications.
=============================================
For Oracle Server Release 7.3:

Make sure V$XATRANS$ exists.
This view should have been created during the XA library installation. You can manually create the view,  if needed, by running the SQL script XAVIEW.SQL. This SQL script should be executed as the Oracle user  SYS. Grant the SELECT privilege to the V$XATRANS$ view for all Oracle Server accounts which will be used by Oracle XA library applications.

See Also:
Your Oracle operating system-specific documentation contains the location of the XAVIEW.SQL script.  Install the resource manager, using the open string information, into the TPM configuration, following the TPM vendor instructions.

The DBA or system administrator should be aware that a TPM system starts the process that connects to an Oracle8 Server. See your TPM documentation to determine what environment exists for the process and what user ID it will have.  Be sure that correct values are set for ORACLE_HOME and ORACLE_SID.

================================================
ID: Note:1075577.6
Subject: DATABASE PERFORMANCE SLOWS DOWN FROM MICROSOFT TRANSACTION SERVER
Type: PROBLEM
Status: PUBLISHED
Creation Date: 18-AUG-1999
Last Revision Date:  05-JUN-2000
Copyright Oracle

Problem Description:
====================
 
You are using the Microsoft Transaction Server to execute a stored procedure from an 8.1.5 database and the performance from the transaction server is very  slow.  

This problem is very deceptive because stopping and re-starting the Microsoft Transaction Server does not correct the problem but stopping and re-starting the Oracle database does correct it.  

While the Microsoft Transaction Server is having slow performance you can also connect using SQL*Plus and run the same stored procedure and the performance from SQL*Plus is normal.

Problem Explanation:
====================
The Microsoft Transaction Server looks for at a special v$ view.  If this view does not exist, then  the transaction server begins to slow down.

Search Words:
=============
MTS
Microsoft Transaction Server
XA

Solution: Create the v$ view v$xatrans$ view

Solution Description:
=====================
 
To correct the performance problem you must create the v$ view v$xatrans$ view.
To do this run the following script:

xaview.sql

From the following directory:
c:\oracle\ora81\rdbms\admin         <========  Interesting!
 
Solution Explanation:
=====================
 
The Microsoft Transaction Server required the Oracle XA views to function  properly.
================================================================

IT IS KIND OF INTESTING THAT THE DOCUMENTATION FOR 8i SAYS THAT YOU DO NOT NEED IT, BUT THE METALINK DOC tells you that you do need it.

hope this helps,
dBalaski
Avatar of NBee

ASKER

My God!
I still get the same message. Help me please!
Do I have to config anything else??
Avatar of NBee

ASKER

There's one more thing. I don't know if it's the reason why I can't do it:
   I use the database that is created during the Oracle's Installation (maybe Typical).
Is it the reason? Do I have to reconfig the database? And how?
Do you have the latest patchsets?
The OraOLEDB provider, being a COM component, is NOT multiple Oracle Home compliant. Only one version of OraOLEDB can exist on a machine at a time.  So, if you have Oracle Provider for OLE DB installed on your machine, deinstall that provider before installing the new provider.
The recommended that Oracle Patchsets 8.1.6.1.1 and 8.1.6.1.2 are  installed from ftp://oracle-ftp.oracle.com/server/patchsets/wgt_tech/ server/windowsNT/81patchsets

Oracle recommends that for MTS and COM+ users, it is recommended that Oracle Services for  Microsoft Transaction Server 8.1.6.0.1 gets installed --  you can get the patches at  http://technet.oracle.com/tech/nt/ora_mts.

The Oracle 8.1.6.1 patchset fixes a  Bug #1378691Catastrophic failure related to OraOLEDB and OLEDB resource pooling.    It also fixes Bug #1328496,   where Catastrophic failures are encountered when SQL queries include a mix of   tables with aliases and some without.

Give these a try as well.

The database should be okay --  however, you realize that the xaviews  need to be run as the SYS user (ie: my previous message).        You should run the latest versions of the patches for the database as well,  found a couple of annoying bugs in the database that 8.1.6.2 correct.

Good luck,
let me know how it works out.

dBalaski

Avatar of NBee

ASKER

Where do I have to install these patches: on COM Server (with Oracle 8i Client 8.1.6) or On ORACLE Server (with Oracle 8i 8.1.6)???
Avatar of NBee

ASKER

Wow ! I found no sqllib18.dll and xa73.dll on both servers.
Must I have these on servers with ORacle 8i 8.1.5?
Well,  both the Database Server & COM Server require patches.

The database server should be brought up to at least Oracle 8.1.6.1 or higher
For a database on NT,  the highest database patchset is 8.1.6.1.4
available at:
ftp://oracle-ftp.oracle.com/server/patchsets/wgt_tech/server/windowsNT/81patchsets/
for unix -- the versions can go higher  (for instance Solaris  goes todatabase patchset  8.1.6.3.0)
go to:    ftp://oracle-ftp.oracle.com/server/patchsets/unix/


Okay,  for the Com server:   The highest patch is  8.1.6.0.1
available at:   http://technet.oracle.com/tech/nt/ora_mts/
Also look at http://technet.oracle.com/tech/nt/   for  OLEDB Provider components...
also try:  http://technet.oracle.com/software/index.htm

I would not utilize  8.1.5 -- very buggy.....     Sounds like you do not have a clean install  or some mismatches in software.    

Good luck,
dB
Avatar of NBee

ASKER

Do u have any guide document to setup MTS and Oracle to run together?
Avatar of NBee

ASKER

I have installed a new version of Oracle and try to use the transaction again and I got this messsage:
 "Cannot connect to the transaction manager or the transaction manager is unavailable"

What is it? How can I solve this problem?
(I have used the solutions u suggested)
ASKER CERTIFIED SOLUTION
Avatar of dbalaski
dbalaski

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Did you find the manuals I listed:
  "Using Microsoft Transaction Server with Oracle 8i" Release 8.1.5, dated February 25, 1999, (A68065-01)
     Note:  The manual listed above is in the on-line help documentation and can be found on the install
                CD in folder 'pdfdoc' as  'a68065.pdf'.

Where they helpful?

dB
NBee,

The following questions are open, and have been open for some time. Further, your profile indicates you logged in as recently as Jan 29, 2002. Please resolve them within the next seven (7) days; following that period of time, I or one of the other Moderators will take action to close this question. Additionally, this list has been forwarded to Administration; please expect an email from them regarding your account's status.

https://www.experts-exchange.com/jsp/qShow.jsp?qid=11516478
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11529339
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11585158
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11697479
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11744478
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11766758
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11818478
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11905339
https://www.experts-exchange.com/jsp/qShow.jsp?qid=11938458
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20003831
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20038010
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20127372
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20135883
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20136023
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20142078
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20142830
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20143154
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20144259
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20144370
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20148973
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20150132
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20153238
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20160626
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20162000
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20163157
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20163178
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20163242
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20163142
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20163674
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20165141
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20174284
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20184042
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20242564
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20242585
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20252565
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20259368

Further, you should note that the guidelines for questions suggest that an "Easy" question is worth 50 points.

Thank you for your attention to the above questions.

Netminder
Community Support Moderator
Experts Exchange
Admin notified of user neglect. Force/accepted by

Netminder
Community Support Moderator
Experts Exchange