As you may recall from Chapter 1, the Net8 listener is the software that runs on the server and that monitors the network for incoming database connection requests. Properly configuring the listener—or listeners if you are running more than one—is your key task when installing Net8 on a server.
Net8 listeners are configured by editing a text file named
listener.ora
.
This is a file that contains a set of parameters for each listener
that control that listener’s operation. You place the
parameters in the file, and they are read and take effect when the
listener is started. The listener.ora parameters
define the following:
The listener’s name
The protocols supported by the listener
The addresses, often TCP/IP port numbers, that the listener monitors for incoming connection requests
Service names and SID names of the databases and instances serviced by the listener
Other configuration details related to dead connection detection, prespawned dedicated server processes, and other advanced Net8 features
Since listener.ora is a text file, you can
modify it using a text editor such as vi or Notepad. You can also use
Oracle’s Net8 Assistant, which provides you with a GUI
interface for editing listener.ora parameters.
Many DBAs prefer to edit listener.ora by hand,
and it’s not that difficult to do. However, if you’re at
all uncomfortable with the syntax, using the Net8 Assistant gives you
one less thing to worry about.
While you can have as many listeners as you like, there is usually no need to have more than one or two. You do not need to have a listener for each database. It’s perfectly okay to have multiple databases served by one listener. Strictly from the standpoint of the number of connections that one listener can handle, it’s rarely necessary to have more than one.
Even though one listener may be all you need, there are some management advantages to using more than one: redundancy and separation of environments.
Two listeners can be used to provide redundancy in case one fails. In Chapter 8, you’ll learn how to configure clients so that if a connection attempt using one listener fails, an attempt will automatically be made using a backup listener. Such a configuration not only protects you from the failure of one listener, it also enables you to more easily make changes that require a listener to be restarted. While you’re restarting the primary listener, clients can connect using the backup. After the primary listener has been restarted, you can make the identical changes to your backup listener.
Separation of environments is another reason to use multiple listeners. If you have two databases running on a server, you may not want any part of those two database environments to be intertwined. That way, if you need to restart the listener for one database, you don’t affect users of the other. A good example of this is a configuration where you have both a test database and a production database on the same server. If you’re experimenting with listener settings on the test database, you don’t want to affect users of the production database.
On Unix systems, listener.ora is found in the
following directory:
$ORACLE_HOME/network/admin
The concept is the same on Windows NT systems, but the exact
pathname varies somewhat between versions. Assuming that you
installed to the C drive, and that you took the default path for your
Oracle home directory, listener.ora for Windows
NT can be found in one of the following directories:
C:\ORANT\NETWORK\ADMIN (Oracle7)
C:\ORANT\NET80\ADMIN (Oracle8)
C:\ORACLE\ORA81\NETWORK\ADMIN (Oracle8i)The TNS_ADMIN environment variable can be
used to override the default location for Net8 configuration files.
If TNS_ADMIN is defined, then Oracle looks for
listener.ora in the directory to which TNS_ADMIN
points. On Windows NT systems, TNS_ADMIN may be defined as an
environment variable or as a registry entry. See Appendix E, for more information.
The listener.ora
file
contains the following three types of parameters:
Each listener gets its own set of these parameters. Each listener
also has a name, and that name is embedded in the parameter names in
order to keep the parameters for one listener separate from the
others. This can be confusing at first, but you do get used to it.
Figure 4.2 shows how this works using some typical
listener.ora parameters as an example.
The listener address entry is mandatory—you must have one for each listener that you define. The SID_LIST entry is optional but if you have it, you can only have one per listener. Control parameters are entirely optional and have default values that will apply if you don’t include them.
The listener address and SID_LIST entries can be quite long and can involve a lot of nested parentheses. Control parameters, on the other hand, consist of relatively simple name/value pairs. The next three sections describe each type of parameter in more detail.
The listener address entry defines the listener name, the network protocols that it supports, and the network addresses that it monitors. The general format for the entry looks like this:
listener_name= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS =address_data) (PROTOCOL_STACK =protocol_data) (DESCRIPTION = ... )
Each listener has one DESCRIPTION_LIST parameter. Within that you can place one or more DESCRIPTION parameters. Each description supports one specific address and protocol stack combination, so you’ll need multiple descriptions in order to listen on multiple ports, or to support more than one protocol stack.
If you have only one description, the description list parameter is optional. For example:
listener_name= (DESCRIPTION = (ADDRESS =address_data) (PROTOCOL_STACK =protocol_data) )
ADDRESS parameters are used
within the listener address entry to specify the network addresses
that the listener monitors for incoming connection requests. The
format of the address data is exactly the same as that used in the
tnsnames.ora file, and you must have an address
match between the client and the server in order for a connection to
be made. Refer to Chapter 3 and to Appendix B, for details on how to specify ADDRESS
parameters.
The PROTOCOL_STACK parameter is a new feature of Oracle8i and was added to support the use of Java in the database. It specifies which presentation and session layers to support on the associated address. The default protocol stack consists of the Two-Task Common (TTC) presentation layer together with the Transparent Network Substrate (TNS) Network Session (NS) layer. These support traditional client connections to an Oracle instance and are specified using the following PROTOCOL_STACK entry:
(PROTOCOL_STACK = (PRESENTATION = TTC) (SESSION = NS))
When Java was added to the database, Oracle also added support for the Common Object Request Broker Architecture (CORBA). CORBA clients can connect to the Java option using the General Inter-Orb Protocol (GIOP), which is a presentation layer. Java clients do not use a session layer. The PROTOCOL_STACK entry to support GIOP looks like this:
(PROTOCOL_STACK = (PRESENTATION = GIOP) (SESSION = RAW))
The following listener address entry defines a listener named PRODUCTION_LISTENER that will support standard Oracle client connections, as well as Java connections, using the TCP/IP protocol:
PRODUCTION_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jonathan.gennick.org)(PORT = 1521))
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jonathan.gennick.org)(PORT = 2481))
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
)
)If you are using the default protocol stack and you don’t include the PROTOCOL_STACK parameter, the DESCRIPTION parameter is optional. This ensures compatibility with older versions of Net8 and SQL*Net.
The following example shows a complete listener address entry for a listener named PRODUCTION_LISTENER:
PRODUCTION_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jonathan.gennick.org)(PORT = 1521))
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jonathan.gennick.org)(PORT = 2481))
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = HERMAN))
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)
)The listener in this example listens for incoming database connections on three addresses using two protocols. It listens on TCP/IP port 1521 for standard database connections and on TCP/IP port 2481 for CORBA connections using the GIOP protocol. Finally, it accepts connections made using Inter-Process Communications (IPC), where a key of HERMAN is used to identify the listener.
It’s important to remember that in order for a client to
connect to a server, there must be a match between an
ADDRESS parameter in the server’s
listener.ora file and an ADDRESS parameter in
the client’s tnsnames.ora file. For
example, the following two net service names can be used to connect
through the PRODUCTION_LISTENER using TCP/IP and IPC, respectively:
HERMAN.GENNICK.ORG =
(DESCRIPTION =
(SOURCE_ROUTE = OFF)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = jonathan.gennick.org)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = herman.gennick.org)
)
)
HERMAN-IPC.GENNICK.ORG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = HERMAN))
)
(CONNECT_DATA =
(SERVICE_NAME = herman.gennick.org)
)
)Notice how the ADDRESS parameter for each of these net service names matches one of the listener addresses. This is a basic requirement in order for a client to connect to a database instance on a server.
Tip
In addition to being defined in tnsnames.ora, net service names may also be defined in Oracle Names, or they may be resolved externally. Whatever the case, the address used by the client must still match an address monitored by the listener.
Once you’ve written the listener address entry, you can go ahead and start the listener using the Listener Control utility. Before doing that, however, you may want to write the SID_LIST entry, and you may want to set some control parameters.
If you have multiple network interface cards (NICs) installed on your system, each of those cards will have its own IP address. It’s possible to have the Net8 listener monitor each of those IP addresses for inbound connections. To do that, you must use an ADDRESS_LIST to specify a list of protocol addresses for the listener. For example:
PRODUCTION_LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.12.73)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.12.74)(PORT = 1521))
)
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)
)Notice that in this example, IP numbers have been used in place of hostnames. Two different hostnames could be used, assuming they were properly defined in your Domain Name Service (DNS). You can’t, however, use the same hostname in both addresses, nor can you define a hostname in DNS with two IP addresses. When a hostname is used, the listener queries DNS for the IP address and binds only to the first IP address that is returned. If you’re using multiple NICs, you’re probably better off specifying their exact IP addresses.
After
you’ve created a listener address
entry to define a listener, the protocols it will support, and the
addresses that it will monitor, your next task is to give some
thought to the services that the listener will handle.
Oracle8i database instances register themselves
with the listeners running on the server, but it’s also
possible to statically define services. For Oracle7 and Oracle8
databases, it is necessary to do so. The SID_LIST parameter is used
to specify database instances and services for which a listener will
handle incoming connection requests, and it is sometimes referred to
as the static service section of the
listener.ora file.
To associate a SID_LIST parameter with a specific listener, the listener name is appended to the parameter name. The basic format for the SID_LIST parameter looks like this:
SID_LIST_listener_name= (SID_LIST = (SID_DESC = (GLOBAL_DBNAME =db_service_name) (ORACLE_HOME =oracle_home_directory) (SID_NAME =name) ) (SID_DESC = ... )
The SID_LIST parameter encloses a list of SID_DESC parameters for a listener. Each SID_DESC parameter identifies a specific database instance service for which the listener will accept connections. The following three parameters are used to identify each database instance or service:
- GLOBAL_DBNAME
Identifies a database by its global name. This is a combination of the DB_NAME and DB_DOMAIN parameters in the instance initialization parameter file (INIT.ORA). The global name must also be listed as one of the names in the SERVICE_NAMES parameter, which is also found in the instance parameter file.
- ORACLE_HOME
Identifies the Oracle Home directory for the database service or instance in question. This allows Net8 to distinguish among multiple releases of the Oracle database software running on the same node.
- SID_NAME
Identifies a database instance by name. The name must match the value of the INSTANCE_NAME parameter in the instance parameter file.
In Oracle7, the SID_NAME parameter was the only means for identifying the database instances serviced by a listener. The GLOBAL_DBNAME parameter was introduced with the release of Oracle8. When Oracle8i was released, the need to have a SID_DESC entry for a database was eliminated. Instances now register themselves with the listener. However, for purposes of backward compatibility, Net8 still supports both SID_NAME and GLOBAL_DBNAME. In addition, these parameters are sometimes used to identify non-database services.
When a client connects to a server, the connect data information
passed by the client contains either a SID name or a global service
name. This information comes from the CONNECT_DATA parameter in the
client’s
tnsnames.ora
file. The information in the
client’s CONNECT_DATA parameter must match the information in
the server’s SID_DESC parameter in order for a connection to be
made.
Database services are defined in terms of a global database name, an Oracle Home directory, and a SID name, as shown in this example:
SID_LIST_PRODUCTION_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = herman.gennick.org)
(ORACLE_HOME = E:\Oracle\Ora81)
(SID_NAME = HERMAN)
)
)You can have as many SID_DESC entries as you need. In addition to the three parameters that you see here, a SID_DESC entry may also contain parameters controlling dead connection detection and prespawned dedicated server processes. Parameters controlling these functions are described later in this chapter.
Oracle8i databases automatically register themselves with the Net8 listener (or listeners). There’s no need to create SID_DESC entries for any Oracle8i databases that you are running, but there’s no harm in doing so either. If you are only running Oracle8i databases on your server, and you are using Oracle8i ’s Net8 listener, you can omit the SID_LIST entry entirely.
Recall that in a dedicated server environment, each incoming client connection gets connected to a corresponding server process. The server process then performs database operations on behalf of the client. This is illustrated in Figure 4.3.
The time it takes to start up a
dedicated
server process on behalf of a new client connection adversely impacts
the time needed for the connection to be made. For this reason,
Oracle provides a way for you to
precreate—prespawn is Oracle’s
term—a number of dedicated server processes. Because the
processes don’t need to be created at the time a new connection
is made, the time needed to open a new connection is reduced.
Prespawned dedicated server processes are created on a per-instance
and a per-protocol basis. They are configured in the SID_LIST entry
of your
listener.ora
file. For each instance in your SID_LIST,
you can add a PRESPAWN_LIST parameter that defines
the prespawned dedicated server processes that you want to create for
that instance. Within a PRESPAWN_LIST, you can have
multiple PRESPAWN_DESC parameters; each
PRESPAWN_DESC deals with one specific networking protocol. For each
PRESPAWN_DESC parameter, you need to specify the following:
The protocol to use
The number of prespawned dedicated servers to create for that protocol
A timeout value, which is expressed in minutes
The PROTOCOL, POOL_SIZE, and TIMEOUT parameters are used to specify these three items. For example, the following SID_LIST describes one instance. That instance has been configured for 10 prespawned dedicated server processes using the TCP/IP protocol:
SID_LIST_PRODUCTION_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=donna.gennick.org)
(SID_NAME=donna)
(ORACLE_HOME=/s01/app/oracle/product/8.1.5)
(PRESPAWN_MAX=13)
(PRESPAWN_LIST=
(PRESPAWN_DESC=
(PROTOCOL=TCP)
(POOL_SIZE=10)
(TIMEOUT=1)
)
)
)
)The POOL_SIZE parameter in this example specifies the number of dedicated server processes that the listener maintains in the pool. These processes are created when you first start the listener. The PROTOCOL parameter specifies that these processes are used for TCP/IP connections. The TIMEOUT parameter specifies a timeout of 1 minute.
You can configure prespawned processes for more than one protocol by placing multiple PRESPAWN_DESC parameters within the PRESPAWN_LIST. The following example calls for 10 TCP/IP and 10 SPX processes:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = donna.gennick.org)
(ORACLE_HOME = /s01/app/oracle/product/8.1.5)
(SID_NAME = donna)
(PRESPAWN_MAX = 33)
(PRESPAWN_LIST =
(PRESPAWN_DESC =
(PROTOCOL = TCP)
(POOL_SIZE = 10)
(TIMEOUT = 1)
)
(PRESPAWN_DESC =
(PROTOCOL = SPX)
(POOL_SIZE = 10)
(TIMEOUT = 1)
)
)
)
)The listener reads the prespawn parameters and creates the required
processes at listener startup time. Whenever you make a change to the
prespawn-related parameters in listener.ora, you
need to stop and restart your listener in order for those changes to
take effect.
When you use prespawned dedicated server processes, they sit idle until a client attempts a connection. At that point, the listener hands off the client to one of the precreated processes, and the client can begin to work. The time required to hand off a client to an existing process is much less than that required to create a new process, so connections are made much faster than they would be otherwise. Once the handoff has been made, the listener will spawn a new process to replace the one just used. Figure 4.4 illustrates this process.
When you configure a listener to use this feature, you specify the
number of prespawned dedicated server processes that you want to
maintain for each database instance in the listener’s
SID_LIST. This value is referred to as the
pool size
. As users connect, and as
processes are taken out of the pool and used, the listener will
create new processes to maintain the pool at the specified size.
There is a limit to this, however, which you can specify using the
PRESPAWN_MAX parameter. Once the total
number of prespawned processes, both those in the pool and those
being used, equals the PRESPAWN_MAX value, the listener will stop
replenishing the pool.
As users disconnect, their prespawned dedicated server processes are returned to the pool. At this point, the TIMEOUT parameter takes effect. A used server process will remain in the pool only for the amount of time specified by the TIMEOUT parameter, and then it will be deleted. The exception is that Net8 attempts to maintain the pool size, so it won’t delete so many processes that the number in the pool drops below the number you specified with the POOL_SIZE parameter.
When using prespawned dedicated server processes, you need to specify a limit on the number of such processes that can be created. You do this using the PRESPAWN_MAX parameter within the SID_DESC parameter that describes an instance. The two examples in the previous section used maximums of 13 and 33 respectively.
PRESPAWN_MAX applies at the instance level. All of the prespawned processes for an instance, regardless of the protocol used, must be taken into account. Your value for PRESPAWN_MAX must be at least equal to the sum of all the POOL_SIZE parameters for the instance. For example, if you have the following two PRESPAWN_DESC parameters, your PRESPAWN_MAX value must be at least 20:
(PRESPAWN_DESC = (PROTOCOL = TCP) (POOL_SIZE = 10) (TIMEOUT = 1) ) (PRESPAWN_DESC = (PROTOCOL = SPX) (POOL_SIZE = 10) (TIMEOUT = 1) )
PRESPAWN_MAX is not an optional parameter. If you are using prespawned dedicated server processes for an instance, then you must specify an upper limit on the number of such processes that can be created.
There are a couple of approaches that you can take to setting the POOL_SIZE and PRESPAWN_MAX parameters. One approach to is to set POOL_SIZE to match the number of users that you typically expect to be connected at any one time. That way, all the necessary processes get created once, and more won’t be needed except when an unusually high number of users connect. Your PRESPAWN_MAX value in such a case would be somewhat higher than your POOL_SIZE.
Another approach is to set your POOL_SIZE high enough so that enough processes are maintained in the pool to handle the maximum number of concurrent connection attempts that you expect to occur at any one time. This way users connect quickly. Remember that as users connect, the pool of server processes is replenished. You’ll still get the overhead of process creation as a result of this replenishment, but that won’t have a direct impact on the user that is connecting. In this scenario, your PRESPAWN_MAX value should then be high enough to accommodate the total number of users that you expect to be connected at any given time.
The PRESPAWN_MAX value does not represent a limit on the total number of database connections. If more dedicated server processes are required, they are created as new users connect. Users connecting after the PRESPAWN_MAX value has been reached experience slower connect times as they have to wait while their dedicated server process is created.
Oracle8 and Oracle8i both allow you to make external procedure calls from PL/SQL code. When an external procedure is invoked, Net8 plays a part in connecting the database session invoking the procedure with the external shared library that implements the procedure. Figure 4.5 illustrates Net8’s role in this process.
In order for the process shown in Figure 4.5 to
occur, you need to have specific entries in your
listener.ora and
tnsnames.ora files.
In your
listener.ora
file you need to define two
things: you need an address through which the listener can be reached
when an external procedure call is made, and you need a
SID_LIST entry
pointing to the
extproc
executable in the
$ORACLE_HOME/bin directory. The address is
typically defined using a DESCRIPTION parameter that resembles the
following:
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)If you’re running an 8.0.x release of Oracle, the DESCRIPTION keyword isn’t used, and the PROTOCOL_STACK parameter isn’t present. Instead, you’ll just have the ADDRESS parameter in your listener address list. See Other Listener Address Entry Formats earlier in this chapter.
To go along with the address, you need a SID_DESC entry that looks like this:
(SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = E:\Oracle\Ora81) (PROGRAM = extproc) )
The program name in this SID_DESC entry must be
extproc, which corresponds to the name of a
specific executable in your $ORACLE_HOME/bin
directory. The key value EXTPROC0 in the address
entry and the SID name PLSExtProc do not need to
be the values shown above, but they must match the values specified
for the extproc_connection_data net service name
in your tnsnames.ora file.
When an external procedure is invoked from
PL/SQL, the Oracle software looks up the definition for the net
service name extproc_connection_data. This is a
specific name that is hardcoded into the Oracle server software. The
extproc_connection_data
service name must resolve to the address
and SID that you specified in your listener.ora
file. For example:
EXTPROC_CONNECTION_DATA.GENNICK.ORG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)If you’re using a naming method other than local naming, it
isn’t absolutely necessary for
extproc_connection_data to be defined in
tnsnames.ora. If you’re using Oracle
Names, for example, you can define
extproc_connection_data in your Names
server’s database.
When you
invoke an external procedure from PL/SQL, the listener spawns a
process to run the
extproc
executable. This spawned process then
inherits the listener’s operating system privileges. This
presents a certain security risk. To guard against any security
problems, you can create a separate listener just for use with
external procedure calls. For example:
EXTERNAL_PROCEDURE_LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)
SID_LIST_EXTERNAL_PROCEDURE_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = e:\oracle\ora81)
)
)You would then start this listener while logged in as some user other than the Oracle software owner. You should ensure that the user starting the external procedure listener does not have read or write permission on any database files, and that it does not have any unneeded operating system privileges.
Control parameters are used in the
listener.ora
file to control and specify
various aspects of a listener’s behavior. Among other things,
these allow you to specify listener trace settings, log file
locations, and listener timeouts. Control parameters are all simple
name/value pairs that take the following form:
parameter_name_listener_name=value
The listener name is always appended to the end of the parameter name. This allows Net8 to distinguish the parameters controlling one listener from those controlling another. Here are four examples, the first two for a listener named PROD_LISTENER, and the second two for a listener named DEV_LISTENER:
CONNECT_TIMEOUT_PROD_LISTENER = 20 TRACE_LEVEL_PROD_LISTENER = OFF CONNECT_TIMEOUT_DEV_LISTENER = 60 TRACE_LEVEL_DEV_LISTENER = SUPPORT
Control parameters may appear anywhere in the
listener.ora file, and in any order. However,
it’s certainly easier to keep up with what’s going on if
you group all the parameters for a listener together.
Get Oracle Net8 Configuration and Troubleshooting now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
