Re: Listener Woes

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Sun, 2 Jun 2002 18:30:57 +1000
Message-ID: <adcld2$vnj$1@lust.ihug.co.nz>

Firewalls are a bit of a no-no, I'm afraid. When you contact the Listener, it spawns a process which you then connect to via another port altogether -and that port number is pretty much randomly assigned. So short of opening all possible ports, you're going to be in trouble.

That said, you seem to have gotten end of the wrong end of the stick a bit. See if this helps.

On the Server, you create a listener, called by default 'LISTENER', listening out on a port (by default, 1521) and listening for calls on one or more protocols (TCP/IP is your best bet). Creating the Listener is most easily done using the Net8 Configuration Assistant. That single listener is now responsible for handling ALL requests, from any number of clients, to connect to an Instance. So, you only need one Listener per Server.

There is a lot of old nonsense going around about what should be in the Listener.ora file, largely arising from the fact that prior to version 8i, you had to specifically list the Instances on behalf of which the Listener was to listen. That's no longer true: in 8i and above, Instances register themselves with the Listener automatically (and PMON continually makes sure the registration is current). So, my own listener.ora looks like this:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
(ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521))
      )

    )
  )

I.E, there's practically nothing in it, just the protocol and port number. The Instances do their registration thing because this Listener is called 'Listener' (the default name) and is listening on 1521 (the default port). If either of those two things weren't true, I'd have to use the local_listener parameter in my init.ora to direct the Instance to register with a non-default listener or a non-default port.

The sqlnet.ora file on the server should also contain practically nothing. In fact, you don't need one at all for simple configurations. Mine reads:

NAMES.DIRECTORY_PATH= (TNSNAMES) I'm using it to specify what names resolution method should be used when, *on the server*, I connect using syntax like 'connect blah/blah_at_db9' -this tells Oracle that "db9" is a tnsnames alias, and to resolve it by referring to a copy of the tnsnames.ora stored on the server itself. You'd need something like this if you had more than one Instance running on the same server (because otherwise you'll be forever fiddling with ORACLE_SID to tell Oracle which Instance you want to connect to each time you try). You'd also need it to deal with a non-default listener name or port, because the local_listener parameter I mentioned earlier actually takes a tnsnames alias as its argument, and therefore that needs to be resolved if it's specified.

Again, if you're going to be doing 'connect x/x_at_something' on the server, or using a non-default listener requiring the use of the local_listener init.ora parameter, then you need a tnsnames.ora on the server to resolve the supplied aliases. If you've only got one Instance on the server, then connections to that Instance can be done *on the server* with a simple 'connect x/x' -no alias needed, therefore no resolution needed, therefore no server-side tnsnames needed. If you need a server-side tnsnames.ora, it needs to look like this:

DB9.ALDEBURGH.LOCAL =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mozart)(PORT = 1521))
    )
    (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB9.aldeburgh.local)
    )
  )

Here, the alias "db9.aldeburgh.local" tells Oracle to find a listener on a particular host and port, use a particular port, and request a connection to a particular "service" (ie, in this case, an Instance called "DB9.aldeburgh.local"). My alias happens to be the same as the actual instance name -but it needn't be. The alias could be 'FRED', and a connect blah/blah_at_FRED would then still get me connected to the db9 Instance.

Incidentally, the alias 'db9.aldeburgh.local' is a bit of a mouthful to have to type every time I want to connect. You can get Oracle to help you here: if your sqlnet.ora contains the line: NAMES.DEFAULT_DOMAIN = aldeburgh.local, then Oracle will append that to whatever alias you happen to type. So if I included that in my sqlnet.ora I could get away with 'connect blah/blah_at_db9' and it would still work, even though the tnsnames.ora is expecting the full-blown 'db9.aldeburgh.local' -because that's what it actually gets by virtue of sqlnet.ora doing its thing silently.

On the Client, you will always have to use a connection alias, and therefore that will always need to be resolved one way or another. Therefore, each and every client needs to have a sqlnet.ora to tell it which resolution method to use (it need only contain the one line I mentioned above). Each and every client will also need a tnsnames.ora. The contents of these files will be identical to what I mentioned for the server-side versions: the point is that if you supply a connection alias, it matters not whether you are doing so on the client or on the server: Oracle still has to resolve whatever you type down into something it can actually connect to, and tnsnames/sqlnet are the tools needed to do that job. And since the resolution issues are the same on both client and server, identical copies are required on both. (Remember, though, that if you never use aliases on the server, the files are optional on the server).

None of which helps you much at this stage: you need to get rid of that firewall to make life easier. When you've got it all working without the firewall, you can investigate things like Connection Manager (CMAN) which can be used to, effectively, tunnel through the firewall using predictable ports (so you only have to open one or two of them).

You'll also want to start investigating things like Names Server and/or Oracle Internet Directory, which take the place of tnsnames.ora, because making sure an identical copy of tnsnames is maintained stably on a thousand client machines is a royal pain in the butt: these technologies allow you to have a central store of alias resolution information, instead of each client having their own.

Hope that helps clarify things a bit, anyway.

Regards
HJR "Bob" <bobs_at_yahoo.com> wrote in message
news:fPjK8.82305$Ka.6280906_at_news2.calgary.shaw.ca...

> Sybrand,
>
> I have been sitting here trying to figure out what else I could try.

tnsping
> oratest returned error #12541 but then I hd the notion to ping oratest and > that was successful. It has suddenly occurred to me that I am going through

> a LinkSys Router/Firewall box. I don't know if this might be affecting my
> setup. Thanks.
>
> Bob :-)
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:2lcjfuckn11mm9n6mu1igspcmr5cgg2316_at_4ax.com...
> > On Sun, 02 Jun 2002 03:17:03 GMT, "Bob" <bobs_at_yahoo.com> wrote:
> >
> > >I'm brand new to Oracle Server and have a bit of a problem with
> listeners.
> > >
> > >I have set the Oracle 8i for NT server up on a Windows 2000 Server and
> then
> > >installed the Oracle Client over on a Windows 2000 workstation.
> > >The server appears to be working fine and I have connected with SQL

Plus

> on
> > >the server machine. However over on the Client side I have no listener.
> > >
> > >I initially had nothing showing in the services but have started a
> listener
> > >using lsnrct.start. Now I have a listener that starts automatically on
> > >startup. However if I check it with lsnrctl services I get the

following

> > >display:
> > >
> > >Conecting to <Description =<Address =<protocol = TCP><Host =
> BigDell><Port =
> > >1521>>>
> > >TNS -15241: TNS: No Listener
> > >TNS -12560: TNS: Protocol Adapter Error
> > >TNS 00511: No Listener
> > >32-Bit Windows Error: 61: Unknown Error
> > >
> > >Anyone have any ideas as to what my problem is. I am connected to the
> Server
> > >machine and can get files etc. just fine. Thanks.
> > >
> > >Bob :-)
> > >
> > >
> >
> > The listener needs to run on the server only.
> > On the *server* issue lsnrctl start, you need listener.ora, sqlnet.ora
> > and tnsnames.ora
> > On the client you need the latter 2 files only, and you should be able
> > to tnsping and connect.
> >
> > Hth
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
>

Received on Sun Jun 02 2002 - 03:30:57 CDT