multiple connection problems with DBI DBD::Oracle

From: John Cotter <jdcotter_at_execpc.com>
Date: 1999/02/26
Message-ID: <7b791r$3is$1_at_nnrp1.dejanews.com>#1/1

I have a peculiar problem when connecting to multiple databases from a single perl script using DBI and DBD::Oracle. It seems that after certain connections complete the next connection attempt fails with a:

ORA-12505: TNS:listener could not resolve SID given in connect descriptor (DBD: login failed)

What's peculiar is that the order in which I'm connecting determines which connection fails with that message. So for example, if I'm connecting to two different databases, the first connection will succeed and the second connection will fail. Switch the order and I get the same results.

What I'm able to discern so far is that when I connect to a production database whatever database I connect to next will fail with the ORA-12505 message above. I can take out the connection to the production database and the connection that previously failed will work fine. So I know it's not really a TNS names problem or some other sort of syntax error.

I can connect to multiple test databases on different machines. Moreover, I can make multiple connections to different schemas within the same production database.

What's really, really strange is that if I connect to same production database *twice* in a row, then whatever connection attempt follows does NOT fail!

any ideas on what could be causing this?

• John

p.s. I'm running:
perl 5.00404
DBI version is 1.00 (statically linked in) DBD::Oracle version is 0.54 (statically linked in) HP-UX 10.20
Oracle 7.3.2.2

Below is some output to give you an idea of what I'm talking about. The 't0xx' databases are test and the 'p0xx' databases are production. The production databases are orders of magnitude bigger than the test databases.

Connect to p013 fails following a connect to p010

================================================= Connecting to <t012> via

connect2db. Connecting to <p010> via connect2db. Connecting to <p013> via connect2db. ORA-12505: TNS:listener could not resolve SID given in connect descriptor (DBD: login failed) at /opt/css/common/bin/CSS/CSSUTILS.pm line 79

Swith them around and now p010 fails following a connect to p013

================================================= Connecting to <t011> via

connect2db. Connecting to <p013> via connect2db. Connecting to <p010> via connect2db. ORA-12505: TNS:listener could not resolve SID given in connect descriptor (DBD: login failed) at /opt/css/common/bin/CSS/CSSUTILS.pm line 79

Connect to t012 fails following a connect to p010 (prev. example it worked

fine) ================================================= Connecting to <t011>

via connect2db. Connecting to <p010> via connect2db. Connecting to <t012> via connect2db. ORA-12505: TNS:listener could not resolve SID given in connect descriptor (DBD: login failed) at /opt/css/common/bin/CSS/CSSUTILS.pm line 79

An example where everything works

---

Connecting to <t011> via connect2db.
Connecting to <t012> via connect2db.
Connecting to <t011> via connect2db.
Connecting to <p010> via connect2db.

DBI version is 1.00
DBD::Oracle version is 0.54
db handle 1

---

Date is 02/26/1999 16:38:27
db handle 2

---

Date is 02/26/1999 17:38:28
db handle 3

---

Date is 02/26/1999 16:38:29
db handle 4

---

Date is 02/26/1999 17:38:30

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Feb 26 1999 - 00:00:00 CET