Configuring the "listener.ora file" and adding an Oracle Database

Oracle Net configuration information is stored in a special text file named listener.ora^[1]. The normal location for this file is in $ORACLE_HOME/network/admin. Under Windows, if you have done a default install of Oracle, you should find listener.ora in the

 c:\oracle\ora11g\network\admin

directory. Under Unix, you should look first in $ORACLE_HOME/network/admin. If you do not find it there, then try looking in the /var/opt/oracle directory. The important part of the listener.ora file, at least for our purposes, is the part under the heading SID_LIST_LISTENER. This is important to us, because that is the section containing the list of databases served by the listener. The heading could be at any point in the file, because Oracle just looks for it by name. It will not take you too long to find it, though, because listener.ora files are not usually very big. The following Slide Show shows what this looks like, and describes the purpose of each of the elements that fall under that heading:

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sales.us.example.com)
(ORACLE_HOME=/oracle11g)
(SID_NAME=sales))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/oracle11g)
(PROGRAM=extproc)))

The SID_LIST_LISTENER heading in the Oracle Network Services configuration plays a pivotal role in the establishment and management of connections between the Oracle database and its users. The SID_LIST_LISTENER section is part of the listener.ora configuration file, which is the configuration file for the Oracle Net Listener. This section contains entries that map Oracle system identifiers (SIDs) to the Oracle instances they represent. Each SID_LIST_LISTENER entry contains one or more SID_DESC entries that specify:

1. The global database name (GLOBAL_DBNAME), which is usually the same as the SID.
2. The Oracle home directory (ORACLE_HOME) for the database.
3. The program used for dedicated connections (PROGRAM), typically the Oracle executable (oracle).

When a client sends a connection request to the listener, the client provides the SID for the desired database. The listener then uses the SID_LIST_LISTENER entries in its configuration file to determine how to route the connection request. For example, for a client requesting a connection to a database with the SID "ORCL", the listener would look up "ORCL" in the SID_LIST_LISTENER section of its configuration file and use the corresponding ORACLE_HOME and PROGRAM settings to establish a dedicated server process for the connection. In Oracle 11g and later, dynamic service registration eliminates the need for static SID_LIST_LISTENER entries for instances running on the same machine as the listener. When an instance starts, it automatically registers itself and its services with the listener, supplying all of the information that the listener needs to manage connections to that instance. However, SID_LIST_LISTENER entries may still be needed for some configurations, such as those using Oracle's external procedures, Oracle Connection Manager, or when instances are running on a different machine from the listener. The appropriate configuration under these circumstances ensures successful connectivity and efficient performance of the Oracle network services.

Once you start working with Oracle Net configuration files, it will not be long before the parentheses start to drive you crazy. All the information is contained in lists that are enclosed in parentheses, and these lists can sometimes be nested several levels deep. When editing these files be very careful not to alter the nesting of the parentheses.

When you need to add an entry for a new database to a listener.ora file, you should usually follow these steps:

1. Open listener.ora using a text editor such as vi or Notepad.
2. Find the section in the file that starts with "SID_LIST_LISTENER = ".
3. Duplicate the entry for one SID using cut and paste.
4. Edit the new entry so that it reflects the new database.
5. Save the file.

Step 3, where you duplicate the entry for a SID, is where you are most likely to make a mistake. That's because of all the parentheses. It's easy to miss one when copying and pasting. The entry for one SID looks like this:

(SID_DESC =
(GLOBAL_DBNAME = jonathan.course.dispersednet.com)
(ORACLE_HOME = C:\Oracle\Ora81)
(SID_NAME = JONATHAN)
)

After carefully determining where the entry starts and ends, you should copy the entry and paste a duplicate of it right below. From then on, it is a simple matter to edit the SID_NAME and the GLOBAL_DBNAME values. The SID_NAME value can be anything you want, but it's customary to use the database name for this SID name. The database name can be determined by looking at the db_name parameter in your database parameter file. The GLOBAL_DBNAME parameter should consist of your database name and domain separated by a period. You can find your domain by looking at your database's db_domain initialization parameter.

Click the exercise link below to add an entry for the COIN database to your listener.ora file.
Configuring Listener - Exercise

[1]listener.ora: The listener.ora file is a configuration file that defines the properties of an Oracle Net Listener. It determines the protocol addresses the listener will accept connections on, the database services it will register with, and various control parameters for the listener's behavior.