Re: problem with TNSNames.ora connections

Matthew Cromer wrote:
> We periodically have problems connecting with clients who use
> SERVICE_NAME to connect. SID always seems to work.
>
> Any idea what might be going on on the server to cause this problem?
> We get an error "ORA-12500: TNS:listener failed to start a dedicated
> server process" on the client when we try to connect. This only
> happens when we use the SERVICE_NAME on the client side, not when we
> use SID. And it only happens some of the time, with some databases.

Well, the 12500 error message says a bit more than you're letting on here! In full, it reads

"The process of starting up a dedicated server process failed. The executable could not be found or the environment may be set up incorrectly."

And the last bit is your hint. The Oracle docs go on to suggest that you should "Turn on tracing at the ADMIN level and re-execute the operation. Verify that the Oracle Server executable is present and has execute permissions enabled. Ensure that the Oracle environment is specified correctly in LISTENER.ORA. The Oracle Protocol Adapter that is being called may not be installed on the local hard drive. Check that the correct Protocol Adapter are successfully linked."

That's Listener tracing they're talking about turning on, incidentally.

The other thing that's different between SERVICE_NAME and SID is that SID is dumb whilst SERVICE_NAME isn't. Meaning that if your tnsnames refers to SID, the Listener works out what to connect to by reading the hard-coded SID_LIST section of the Listener.ora. But with SERVICE_NAME, the *instance* has to make its existence known to the Listener, at startup, by what's known as 'dynamic instance registration'.

Basically, PMON hares off to the Listener to announce the fact that a new instance has been born (and does so periodically thereafter, incidentally).

You want to make sure that dynamic instance registration is working reliably (though if it isn't, you wouldn't normally expect to get a 12500, it's true).

Bear in mind that instances register themselves automatically only to a Listener called "listener" running on port 1521, and using TCP. If any of those things are not true for you (a Listener called "FRED", for example; or one listening on another port or protocol), then the instance won't be able to find the Listener -unless you tell it.

You do that by setting the LOCAL_LISTENER init.ora parameter. It's set to a tnsnames alias (meaning that there needs to be a tnsnames.ora on the *server*, and not just the clients), not the actual name of the listener. So if your listener was running on port 9876, for example, you'd have a tnsnames.ora on the server that said:

MARY =
    (DESCRIPTION =

       (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL=TCP)(HOST=173.382.28.926)(PORT=9876)

etc etc etc

Instances normally register themselves with whatever name they acquired by virtue of the ORACLE_SID environment variable that was current when the instance was created. But it doesn't have to be that way: the INSTANCE_NAME init.ora parameter allows the instance to register under a completely different name. If your client tnsnames is seeking to connect to an instance called SALES, but your INSTANCE_NAME is set to "SALE", expect problems.

Obviously, Oracle's own doco. is suggesting that environment variables (ORACLE_SID, ORACLE_HOME, PATH etc) might be being set incorrectly, or being re-set somehow. You need to check your server environment is as it should be at the moment someone fails to connect.

The SID_LIST being a static, hard-coded bit of the listener.ora, it is not a surprise that it always works. SERVICE_NAME relying as it does on instance registration is inherently vulnerable to anything that disrupts   the initial and subsequent registrations by PMON.

Not much help, I suppose.

Regards
HJR Received on Fri Jan 24 2003 - 10:24:01 CST