Re: Connection problems with TNS Listener

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 10 Sep 2003 13:59:19 +1000
Message-ID: <3f5ea20a$0$14561$afc38c87@news.optusnet.com.au>

"dbuckingham" <member8912_at_dbforums.com> wrote in message news:3350777.1063157762_at_dbforums.com...
>

Let's cut to the chase:

[snip]
> LISTENER =
>
> (DESCRIPTION_LIST =
>
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(PORT = 1521))
> )
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
> )
> )
> )
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = /sda1/oracle/product/8.1.7)
> (PROGRAM = extproc)
> )
> )

Your listener.ora is missing a reference to a host (you have the protocol and port, but that's not enough). And, as you go on to say, you are missing a static declaration of your various instances in the SID_LIST_LISTENER section (although in 8i, instances should be able to register themselves with the listener... so static declaration is unnecesary (unless you intend to use Enterprise Manager)).

Here's mine (first bit of it, anyway):

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =

      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )

    )
  )

Spot the host.

Sort that out, and make sure you see services with lsnrctl services. (You'll have to wait for about 5 minutes after re-starting the listener until PMON re-registers the instance... you can always 'startup force' the listeners to cut the waiting time down.

Next:
>
> TEMPUS2 =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = tempus)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = TEMPUS2.WORLD)
> )
> )
>
> TEMPUS1 =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = tempus)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = TEMPUS1.WORLD)
> )
> )

> The error that I am getting when I try and connect externally is
> as follows:

> tnsping tempus1
> tns-03505: failed to resolve name

[snip]
>
> Connecting...ORA-12514: TNS:listener could not resolve SERVICE_NAME
> given in connect descriptor
>
> The test did not succeed.

You need to sort out what's going on here. In your sqlnet.ora, do you have a line that reads

NAMES.DEFAULT_DOMAIN = xxxxxxxx

If so, then whatever you've got instead of the 'xxxxx' there will be silently appended to your connection string. So a connection to 'tempus1' becomes, without your knowing it, a connection to 'tempus1.xxxxx'. And since your tnsnames.ora only has an entry for 'tempus1', with no domain specified, then you are going to get a failure to resolve the name.

If that's the case, either remove the above line from your sqlnet.ora, or add the domain description to the labels in the tnsnames.ora.

The failure of the listener to resolve a service name is simply a result of the earlier problem: you already know that the listener has no services listed for it in 'lsnrctl services', so it's no surprise when a connection request finally comes through to 'tempus1.blah' that it has no idea what you're talking about. Sort out the listener, and that problem will go away, too.

> However, if you select finish so the entry is saved to the
> tnsnames.ora file, then do a tnsping, you can ping the instance

No, actually. Tnsping tests the address of the *listener* in your tnsnames.ora. It tells you absolutely nothing about the status of the instance. So you tnsping listeners, not instances.

Get the listener right first, make sure your connection string+sqlnet.ora-provided-default-domain = the labels in your tnsnames.ora, and you should be set to go.

Regards
HJR Received on Tue Sep 09 2003 - 22:59:19 CDT