Managing Users  «Prev  Next»

Lesson 5Configuring the listener.ora file
Objective Add a database to your listener.ora file.

Add Oracle Database

Configuring listener.ora file

Oracle Net configuration information is stored in a special text file named listener.ora. 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:

The SID_LIST_LISTENER heading

The SID_LIST_LISTENER entry marks the list of services that the listener is handling.
1) 
SID_LIST_LISTENER=
 (SID_LIST=
  (SID_DESC=
   (SID_NAME=PLSExtProc)
   (ORACLE_HOME = C:\Oracle\Ora81)
   (PROGRAM =extproc)
  )
  (SID_DESC=
   (GLOBAL_DBNAME=jonathan)
   (ORACLE_HOME = C:\Oracle\Ora81)
   (SID_NAME=JONATHAN)
  )
)

1) The SID_LIST_LISTENER entry marks the list of services that the listener is handling. Notice that the entire list is enclosed within parentheses.

2) Oracle allows you to have multiple listeners running at once.
2) Oracle allows you to have multiple listeners running at once. The word LISTENER, shown here in bold, represents the name of the listener to which this entry applies. The default name is 'LISTENER' , which is what we are using now.

3) Our listener has a SID list or list of services.
3) Our listener has a SID list or list of services that it is handling. This list is highlighted above, and consists of two entries, each of which starts with SID_DESC.

4) The first item that we are listening for is named PLSExtProc.
4) The first item that we are listening for is named PLSExtProc. This represents an executable file that is used to interface Oracle to external DLL libraries. You will only see extproc on Windows.

5) The second item does refer to a database. You can tell because it refers to a SID name rather than a program.
5) The second item does refer to a database. You can tell because it refers to a SID name rather than a program. JONATHAN happens to be the database that I created when I first installed the Oracle software.

6) The GLOBAL_DBNAME entry allows clients to reference the database by the name and domain specified in the database initialization file
6) The GLOBAL_DBNAME entry allows clients to reference the database by the name and domain specified in the database initialization file.

7) The SID_NAME entry allows clients to reference the database by the name of the instance.
7) The SID_NAME entry allows clients to reference the database by the name of the instance.

8)The ORACLE_HOME entry tells the listener the location of the Oracle home directory for this database.
8) The ORACLE_HOME entry tells the listener the location of the Oracle home directory for this database.

  1. The SID_LIST_LISTENER entry marks the list of services that the listener is handling.
  2. Oracle allows you to have multiple listeners running at once.
  3. Our listener has a SID list or list of services that it is handling.
  4. The first item that we are listening for is named PLSExtProc. This represents an executable file that is used to interface Oracle to external DLL libraries.
  5. The second item does refer to a database. You can tell because it refers to a SID name rather than a program.
  6. The GLOBAL_DBNAME entry allows clients to reference the database by the name and domain specified in the database initialization file
  7. The SID_NAME entry allows clients to reference the database by the name of the instance.
  8. The ORACLE_HOME entry tells the listener the location of the Oracle home directory for this database.

Sid List Listener
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, very careful not to mess up on the parentheses.

Adding an entry for a new Database

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.

Configuring Listener - Exercise

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