ORA-02045 error 2

[MARMAZ]

Hello all,

I am experiencing the following error while
performing a large multi-table update transaction
using VB6 SP3 with MTS against Oracle 8.1.6 database
and Oracle 7.3.4 client, on NT4:-

"Failure enlisting in Resource Manager (-2147168246)"

The XA trace gives the following error

ORA-02045: too many local sessions participating in global transaction

Has anyone out there experienced the same error or can anyone shed any light on this?

Thanks in advance

 

[karluk]

I found some documentation that suggests this error may be solved by setting an initialization parameter, _MAX_TRANSACTION_BRANCHES.

The default value of this parameter is 8, but it can be increased as high as 32.

Please be aware that if you implement this suggestion you do so at your own risk! Parameters that begin with "_" are unsupported by Oracle and not guaranteed to work in future releases.

 

[MARMAZ]

karluk

On receiving you posting we contacted Oracle and they told us to increase the following parameters:

_MAX_TRANSACTION_BRANCHES 32 (defaults to 8)
_DISTRIBUTED_LOCK_TIMEOUT 300 (defaults to 60)
LARGE_POOL_SIZE 20M (defaults to 0)
DML_LOCKS 1980 (500)

This has done the trick.

Thanks

mgmazz

 

[MARMAZ]

Hello All,

As a footnote to the above, Oracle have now told us that there is a bug in 8.1.6 that manifests itself as an ORA-60 error. This is what causes the

"Failure enlisting in Resource Manager (-2147168246)"

error. This error is not specific to using Microsoft Transaction Server but occurs when the database is under stress. Oracle advises you move to 8.1.7, which has fixed this bug.

If you are using Microsoft Transaction Server I would definitely set the following parameters in init.ora to at least the following levels:

_MAX_TRANSACTION_BRANCHES 32 _DISTRIBUTED_LOCK_TIMEOUT 300
LARGE_POOL_SIZE 20MB
shared_pool_size 40MB
sort_area_size 512K
PROCESSES 450
SESSIONS 450
TRANSACTIONS 450
DISTRIBUTED_TRANSACTIONS 450
DML_LOCKS 1980

 

[THoey]

Thanks for the info karluk and Marmaz. I am running 8.1.6 and don't know if we are able to jump to 8.1.7 at this time. I don't recognize MTS, but should I still jump these settings? Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?