ORA-12505: TNS:LISTENER DOES NOT CURRENTLY KNOW OF SID

[Matthias Danzer]

Hi folks,
we are running several oracle databases (9i/10g) on two node rp7410 HP-UX 11.11 MC/SG Cluster v. 11.16.

We have 2 databases running on one node called SPCP and TDSP. There is a listener named LSNRSPCP for the SPCP database listening on ip 1xx.xx.xx.11 and another listener named LSNRTDSP for the TDSP database
listening on ip 1xx.xx.xx.17.

From time to time we get the error that the SPCP database would not connect to TDSP database using the right connect string of the tnsnames. The SPCP tries to connect to a third database on the same machine. But the connect string is right.
So the source database gets the following error:

ORA-12505: TNS:LISTENER DOES NOT CURRENTLY KNOW OF SID GIVEN IN CONN

Which is right because the conncetion is made to the wrong database. This although the connection data of the tnsnames is correct. The problems couldn't be reproduced. Sometimes it works, sometimes not...


A of the listener logs was send to oracle. They found the following hp-ux error:

HP-UX error 232 ECONNRESET Connection reset by peer

...but it is not useful.

The DNS resolution works fine, tnsnames is right....

What is wrong?
How could I setup a useful trace?

Regards
Matthias

[Volker Borowski]

Hello,

what about attaching/posting

listener.ora
tnsnames.ora
sqlnet.ora

of both listeners

/* use fake IPs, if you do not like to publish them */

this would take away a lot of guessing.

Volker

[TwoProc]

There's not enough here to know for sure. But, where is the error appearing? In the database alert log of SPCP? TDSP? Or, is this in the listener log file for LSNRSPCP or LSNRTDSP?

Or, is this error in the environment trying to make the connection?

Reason: if the error is coming from the host database (and not the one trying to make the connection), then how do you know if the tnsnames.ora file is right? You'd have one of these at every computer trying to make the connection to the host database. And, if on one of those systems, someone copied the entry for LSNRTDSP or LSNRSPCP as a prototype, and renamed the Listener name, but didn't fix the SID name, then I'd expect the error that you've gotten.

In fact, I can't remember the error, but I've made this mistake myself. Rather than getting a new tnsnames.ora file, I just copy an entry from somewhere else in the file, and make a new one. And, at times, I've forgotten to change something, like the machine name, SID name, etc).

Anyway, check the tnsnames.ora file of your client machines...

Oh, one more to check. The error may not be occurring from a tnsnames.ora file, it could be from a jdbc connection. In that case, the tnsnames.ora file isn't used, but rather the whole connection string is used directly by java itself, and it doesn't read the tnsnames.ora file for the listener details. So, you might also have some misconfigured java program out there.

Just some thoughts to look into, hope that helps a bit and doesn't confuse the issue more if I'm on the wrong track here.