Creating and Managing Oracle Listeners
7. Accept the default settings and click File -> Save Network Configuration to create the listener
Note: You can determine the location of files by examining the title bar of the Oracle Net Manager screen.
Adding a Service Name to the Listener
A service is a program that a listener executes as a background process in the Windows environment.
You must add a service name to the listener if you want to call and execute the service. You can define the services to which a listener can connect by specifying the name, protocol, port, and host information for the service. This process is referred to as static service registration because you need to implicitly register a service with the listener. You can add multiple service names to a listener. To add a service name to a listener, complete the following steps:
1. Expand the Listener folder on the opening screen of Oracle Net Manager and select the name of the listener you want to configure.
2. Select the Database Services option from the Listening Locations drop-down list.
3. Click the Add Database button at the bottom of the Listening Locations screen. The Database Services screen is displayed.
Figure 6-3 shows the Database Services screen.
Figure 6-3: The Database Services Screen The Database Services screen contains the following text boxes:
Global Database Name – Contains the fully-qualified name of the Oracle database.
Oracle Home Directory – Contains the location of the directory where Oracle is installed.
SID – Contains a unique name of the database instance.
4. Accept the default settings and click File -> Save. This saves the service name information for the listener at the specified location.
Using Oracle Enterprise Manager to Manage Listeners
You can also use Oracle Enterprise Manager to manage Oracle Net Services.
To manage a listener using Oracle Enterprise Manager, complete the following steps:
1. Type the http://hostname:5500/em URL in a Web browser.
The Oracle Enterprise Manager page is displayed.
2. Type the User Name as sys, Password as oracle, and select the sysDBA option from the Connect As drop-down list to login as the SYS user. This enables you to perform administrative functions, such as creating a listener.
3. Click Login to Continue. The Database: oracle page of Oracle Enterprise Manager is displayed. Figure 6-4 shows the Database: oracle page.
Figure 6-4: The Database: oracle Page
4. Click the LISTENER_rd02 link in the General pane. The Serviced Databases page is displayed.
This page displays information, such as location and net address of the listener. It also displays various links present in the Related Links pane.
5. Click the Net Services Administration option in the Related Links pane. The Net Services Administration page is displayed. This page displays the information about configuration file location that can be used for network administration, such as the name of a net service.
6. Select the Listeners option from the Administer drop-down list and click Go. The Listeners page is displayed. This page displays information, such as protocol details, status, and Enterprise Manager target for existing listeners.
7. Click the Create button. The Create Listener page is displayed.
8. Accept the default name as LISTENER0 in the Listener Name text box and click Add to continue. The Add Address page is displayed. This page displays the default values as TCP/IP in the Protocol drop-down list and the name of the host computer in the Host text box. Type the port number as 1522 in the Port text box.
9. Click OK to continue. The Create Listener page is displayed again.
Figure 6-5 shows the Create Listener page.
Figure 6-5: The Create Listener Page
10. Click OK to save the information. The Creation Message page is displayed. This page displays the listener, LISTENER0. You can select the Start/Stop option from the Actions drop-down list in the Creation Message page. This action stops a running listener or starts one that is not running.
Modifying Existing Listeners Using Oracle Enterprise Manager
You can also modify existing listeners by using Oracle Enterprise Manager. Editing an existing listener modifies the parameters in the listener.ora file and saves the modified information in the same file.
To edit an existing listener, complete the following steps:
1. Open the Creation Message page of Oracle Enterprise Manager and click the Edit button. The Edit Listener page is displayed. This page displays the protocol used to create the listener and the listener's details, such as Host and Port, with corresponding values of name of the host computer and port number of the listener, respectively.
2. Click Edit to continue. The Edit Address: TCP/IP page is displayed. This page enables you to modify the Host and Port values associated with the listener.
3. Click OK to save the modified information.
Managing Listeners Using the LSNRCTL Utility
You can also manage listeners by using the LSNRCTL command-line tool. You must type the LSNRCTL command at the command prompt to invoke the command-line utility.
You can perform several tasks using the LSNRCTL utility. Some of the important tasks are:
• Starting a listener – Type start at the LSNRCTL prompt to start the default listener. You must type start followed by the listener name, if you want to start a different listener.
• Reloading a listener – Type reload followed by the listener name at the LSNRCTL prompt to update the listener information. If you do not type the listener name after typing reload, the default listener is reloaded. You need to reload a listener by using Oracle Net Manager or Oracle Enterprise Manager, if you have modified the parameters in the listener.ora file manually.
There is no need to stop and start the listener before executing the reload command. However, you can use the reload command as an alternative for stopping and starting the listener, to refresh the contents of the listener.
• Displaying the status of a listener – Type status followed by the listener name at the LSNRCTL prompt to display the status of a listener. If you do not type the listener name after typing status, the status of the default listener is displayed. The status command displays information, such as location of log files, services associated with the listener, and the time from which the listener has been executing.
• Displaying the services for a listener – Type services followed by the listener name at the LSNRCTL prompt to display the services associated with a listener. If you do not type the listener name after typing services, the services associated with the default listener are displayed. The information displayed includes the number of connections that are accepted or rejected per service, and whether the services have dispatched or dedicated processes associated with a listener.
• Stopping a listener – Type stop followed by the listener name at the LSNRCTL prompt to stop the executing listener. If you do not type the listener name after typing stop, the default listener stops executing.
• Changing the password – Type change_password followed by the listener name at the LSNRCTL prompt to change a password required for stopping the executing listener. If you do not type the listener name after typing change_password, the password for the default listener is changed.
Additional Configurations for Using Multiple Listeners
When multiple listeners service your database connection requests, you need to use additional
configuration features for using multiple listeners. The following are the additional configuration features:
• Connect-time failover
• Transparent application failover
• Connection load balancing Connect-Time Failover
A connect-time failure occurs when the initial connection to the first listener fails. The connect-time failover feature enables a client to connect to another listener if the initial connection fails. The
tnsnames.ora file specifies the location of multiple listeners. A connection request to the next listener in the list is attempted, if a connection attempt to the first listener fails. This feature enhances the availability of Oracle services. The following is the structure of the tnsnames.ora file when the connect-time failover feature is enabled:
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP) (HOST=DBPROD)(PORT=1521)
(ADDRESS=(PROTOCOL=TCP) (HOST=DBFAIL)(PORT=1521)
)
(CONNECT_DATA=(SERVICE_NAME=PROD)(SERVER=DEDICATED) )
)
The ADDRESS_LIST section specifies an additional listener. When a connection fails when attempting to connect to the DBPROD host on port 1521, a connection attempt is made to connect to the DBFAIL host on port 1521.
Transparent Application Failover
Transparent Application Failover (TAF) is a feature that ensures automatic re-establishment of connection between a client and Oracle service, if the current connection between them fails. This feature is used in an Oracle network where high availability of Oracle services is required. If a client connection to the database fails, TAF enables a client application to automatically reconnect to the database.
Connection Load Balancing
Oracle Net Services enables you to use client load balancing, which enables a client to randomly select a listener from the available list of listeners. It balances the load of connection requests among available listeners. Connection load balancing efficiently distributes connections among dispatchers in an Oracle Shared Server environment.
Troubleshooting Connection Problems at the Server-Side
You may need to troubleshoot the side connection problems, even if you have configured server-side components correctly. Some of the reasons for connection problems include:
• The Oracle server, middle-tier, or client is not configured correctly.
• The client is unable to resolve the name of the net service associated with a listener.
• The network protocol, such as TCP/IP, is not active on the server.
• The user types an invalid username, password, or net service name.
You can identify and resolve these errors by performing server-side computer and database checks.
Before executing these checks, you must ensure that the computer on which the Oracle server is configured is running properly and the listener is active. The various server-side computer and database checks include:
• Verifying the server computer: Ensure that the server computer is running properly and is available for accepting new connections. However, a few computers allow only administrators to access the computer. Therefore, you need to ensure that the computer is available to all users. In a TCP/IP network, you can use the ping utility on a client computer to verify connectivity to the server.
• Verifying the database is running: Connect to the Oracle server and log on to the database using SQL*Plus to verify whether or not the database is running. You can first attempt to create a local connection that does not use an Oracle listener. For this, you need to set the ORACLE_SID environment variable to the name of the Oracle instance, and then connect to SQL*Plus. You can type the sqlplus system/manager command at the command prompt to verify the database connectivity. When connecting to SQL*Plus if an ORA-01034 error occurs, it indicates that the Oracle instance is not running and that you need to start the Oracle instance. The ORA-27101 error indicates that no instance is presently available for connecting to the ORACLE_SID environment variable.
• Verifying the database is open to users: Ensure that the database is not opened in a restricted mode. When a database is opened in a restricted mode, unprivileged users will not be allowed to connect to the Oracle server. You can type the sqlplus scott/tiger@ORCL command at the command prompt to verify whether or not the database is open to users. The ORA-01035 error indicates that the Oracle server is only available to users with RESTRICTED SESSION privilege.
• Verifying the user privileges: Ensure that the user has been granted the CREATE SESSION privilege to establish the connection to the Oracle server.
After performing the server-side computer and database checks, you need to perform the server-side network checks. The various server-side network checks include:
• Verifying the listener: Ensure that the listener and its associated services are running on the Oracle server. You can type the lsnrctl status command to verify the status of the default listener, LISTENER, and the services associated with the listener.
• Verifying listener protocols: Ensure that the protocol used by the client is the same as the protocol used by the service requesting a connection. You can type the lsnrctl services command at the command prompt to accomplish this. The ORA-12541:“No Listener” error message indicates that the client is requesting to connect using a protocol for which a listener is not listening.
• Verifying server protocols: Ensure that the network protocol on the server is active. You can use the ping command at the command prompt to accomplish this.
• Verifying connection timeouts: Ensure that the client is not timing out before establishing a valid connection. The ORA-12535 or ORA-12547 error indicates a connection timeout, which can occur in the case of slow network connections. You can set a higher value for the
INBOUND_CONNECT_TIMEOUT parameter so that the listener will wait longer for a valid response from the client.