Hdr: 18394488 11.2.0.4 RDBMS 11.2.0.4 PRG INTERFACE PRODID-5 PORTID-212 18759589

Abstract: TOO MANY CONNECTIONS OVER SHARED DBLINK AFTER UPGRADE TO 11.2.0.4

*** 03/13/14 12:14 am ***

PROBLEM:

--------

The production database system upgraded from 11.2.0.1 to 11.2.0.4 in the

previous weekend.

There are several shared db links on this database to other production

database that its version is 11.2.0.3 .

When trying to connect sessions on 11.2.0.4 to 11.2.0.3 via shared db links

it opens too many connection on connected database.

We tested this situation between 11.2.0.3 to 11.2.0.3 databases via shared db

link , opened session count is two (one of them is PSEUDO other is DEDICATED

)

but between 11.2.0.4 to 11.2.04 opened session count is at least 10. When

1000 session trying to connect 11.2.0.4 to 11.2.0.3 via shared db links about

10000 connection opening.

This system working correctly before database upgrade.

We didn't change any parameter, listener configuration or unix parameter.

DIAGNOSTIC ANALYSIS:

--------------------

Checked with the customer on:

1. Why are you using shared DBLINKS and SHARED servers ?

Because main application works with shared dblinks. (from many years ) ... We

didn't use shared servers , after upgrade too many connection is opening and

we have to use it because of handle too many connection with less resource .

2. Can you use standard DBLINKS and not use shared servers ?

We cannot use standard db links because of application design... If Oracle

solve this problem we can use dedicated. (before upgrade we had used

dedicated )

When checking with application development team on why are they using shared

db links ;

they said that main banking application works in webspehere connection pool

using XA transaction and it must use shared db link to handle connections !

It can't use standart db links . If oracle created shared db link feature why

are you offer to use standard db link ? Before upgrade everything works fine.

I am also repeating my words , we had used dedicated server , we have to use

shared server because of performance issue for now . If Oracle solve opening

many connection issue we will use dedicated server again . But shared db link

feautere will remain because of websphere XA transaction handling.

3. How many sessions to server B does each of your application connections to

Server A open ?

Its count didn't change , anyway after upgrade it is same that the previous

value before upgrade.

ct found metalink document why they use shared db link.

ORA-24777 reported when using a database link from within an XA coordinated

transaction (Doc ID 1506756.1)

Solution from document;

Two options

1. Configure the database to allow the use of shared servers and then let the

application use these as per the following documentation

http://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc003.htm#ADMIN0050

2

or establish whether the Java application needs to start an XA transaction.

OR

2. Define the database link being utilised as a shared database link, i.e,

CREATE SHARED DATABASE LINK ..

They got this error many years ago ;

ORA-24777 reported by a JDBC/XA application when utilising a database link

between two Oracle databases. And db links was configured to shared db links.

I explained before why we are using shared server ( performance )

A complete test case epxlaining how a connection from 11.2.0.4. to 11.2.0.3

opens alot of connections: db_link.pdf

WORKAROUND:

-----------

none

RELATED BUGS:

-------------

Bug 18157790 - ORA-22 AFTER UPGRADE FROM 11.2.0.3 TO 11.2.0.4

Bug 1559258  - PSEUDO SESSION INCREASE WITH XA + SHARED DBLINK 

REPRODUCIBILITY:

----------------

issue is reproducible on ct's env.

TEST CASE:

----------

provided

STACK TRACE:

------------

SUPPORTING INFORMATION:

-----------------------

24 HOUR CONTACT INFORMATION FOR P1 BUGS:

----------------------------------------

DIAL-IN INFORMATION:

--------------------

IMPACT DATE:

------------