Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Creating Multiple Listeners Tips

Oracle Database Tips by Donald BurlesonSeptember 3, 2015

Question:  I need to understand how to configure multiple listeners, and learn the difference between having a global and a local multiple listeners.

Are there guidelines on when to create multiple listeners?  What is the procedure for creating multiple listeners?

Answer:  In the following example, we configure multiple listeners.

Since they should be listening on separate ports, we will configure a listener that allows unrestricted access as listener1, listening on port 1521. The other listener will be named listener2 and will listen on port 1526.

On the first Oracle Home, under the directory for TNS_ADMIN, typically /u01/oracle/product/9.2/network/admin, modify the file listener.ora to look like the following:

 LISTENER1 =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS =
           (PROTOCOL = TCP)
           (HOST = claimdbsvr1)
           (PORT = 1521)
         )
       )
     )
   )

SID_LIST_LISTENER1 =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = CLAIMDB1)
       (ORACLE_HOME = /u01/oracle/product/9.2)
       (SID_NAME = CLAIMDB1)
     )
   )

Configure the second listener by modifying the file listener.ora under the second Oracle Home, /u02/oracle/product/9.2/network/admin to look like the following:

LISTENER2 =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS =
           (PROTOCOL = TCP)
           (HOST = claimdbsvr1)
           (PORT = 1526)
         )
       )
     )
   )

SID_LIST_LISTENER2 =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = CLAIMDB1)
       (ORACLE_HOME = /u01/oracle/product/9.2)
       (SID_NAME = CLAIMDB1)
     )
   )

Notice, the port is 1526 and the Oracle Home is still the same, pointing to the original one under /u01.

The listener name has been changed to listener2 from listener1, and the rest of the file is pretty much the same.

Once configured, start the listeners from the command line as:

Lsncrctl start listener1
Lsncrctl start listener2

This will start both listeners.

On the client side, configure the service name entries in the tnsnames.ora file under the network/admin directory under the Oracle Home.

Any client who will have unrestricted access to the server without any validation check should have an entry in the file as follows:

CLAIMDB1 =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS =
         (PROTOCOL = TCP)
         (HOST = claimdbsvr1)
         (PORT = 1521)
       )
     )
     (CONNECT_DATA =
       (SID = CLAIMDB1)
     )
   )

 This will let the users connect to the listener listening on port 1521, meaning they will bypass the node validation checking.

The clients for whom access should be restricted should have an entry in the tnsnames.ora file similar to the following:

 CLAIMDB1 =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS =
         (PROTOCOL = TCP)
         (HOST = claimdbsvr1)
         (PORT = 1526)
       )
     )
     (CONNECT_DATA =
       (SID = CLAIMDB1)
     )
   )

 The entry is identical to the one above, except the port, which is 1526. The listener listening on that port does the node checking, and thus allows or rejects connections based on the clients from which they originate.

This setup can be an immense help in cases where the sub-networks are established to prevent connections from outside, yet allow free access from the inside. Instead of creating a multitude of sub-networks, the node checking can be turned on to dynamically allow or reject connections from client machines.

Multiple listeners - Global vs Local

Also note that you can create a global and a local listener.

A local listener is specified in your pfile/spfile startup deck when you database an instance with multiple listeners.

The local_listener init.ora parameter specifies a network name that resolves to an address or address list of Oracle Net local listeners and it is used ONLY when you have multiple listeners that are running on the same machine as this instance.

The local_listener service name, address or address list is must be specified in the tnsnames.ora file or you will get an ORA-00119 error.

Security and multiple listeners

It is possible to easily set up a rudimentary firewall-like system to accept connections from a set of trusted clients and servers and reject connections from known potentially unsafe machines. OracleNet is used for Oracle versions 9i and higher.  Prior to that, Net8 was used.  The following example refers to a set of client application servers and a database server provide the functionality for the users.

In this network, the application servers CLAIMS1 and CLAIMS2 access the database on server CLAIMDBSVR. Since we know that no client machines other than the ones shown here are going to access the server database, the OracleNet or Net8 listener on the database server can be configured to accept connections only from these machines.

The network also has some other servers, such as the application servers ENROLLMENT1 and ENROLLMENT2 accessing the database on server ENROLLDBSVR. The listener on CLAIMDBSVR can be configured so that the connection requests from ENROLLMENT1 and ENROLLMENT2 are rejected and CLAIMS1 and CLAIMS2 are accepted.

This can be done using a facility called Valid Node Checking. A set of parameters can be placed in some parameter files on the server with the following lines:

 tcp.validnode_checking = yes
tcp.excluded_nodes = (enrollment1, enrollment2)
tcp.invited_nodes = (claims1, claims2)

 The location of these parameters varies from version to version.

In Oracle 8i, these are set in a file named protocol.ora.  In Oracle 9i, these are set in sqlnet.ora.

Both of these files are located in the directory specified by the environmental variable tns_admin, which defaults to $ORACLE_HOME/network/admin in UNIX or %ORACLE_HOME%\network\admin in Windows.

These parameters are intuitively self-explanatory.

tcp.validnode_checking = yes - The first line specifies that the nodes are to be validated before accepting the connection.

tcp.excluded_nodes = (enrollment1, enrollment2) - The second line specifies that the nodes enrollment1 and enrollment2 are not allowed to connect to the listener. The nodes are indicated either by IP Address (e.g. 192.168.1.100), or the node names as shown above. A single line that is separated by commas specifies the list of node names. The single line is important - you can't break the line.

tcp.invited_nodes = (claims1, claims2) - The third line states that the nodes claims1 and claims2 are allowed. This is important if you specify some node named in the excluded list, but then specify that node as allowed in the invited list. If this list is specified, then it takes precedence over the excluded list.

After making the change in protocol.ora or sqlnet.ora, stop and restart the listener. The values take effect only during the startup of the listener. After this change, if a user, regardless of the authentication in the database or authority level, connects from the node enrollment1, he or she receives the ORA-12537 error:

ERROR: ORA-12537: TNS:connection closed
Enter user-name:

The error message is not very intuitive.

 It does not explicitly state the nature of the error. The same user connecting from node CLAIMS1 will succeed.

In this case, the listener simply rejects the connection originating from the node ENROLLMENT1, regardless of the user. This principle can be extended to cover a variety of friend or foe servers and clients. However, there is no way to specify a wild card character in the node list. For this reason, this method is not very suitable for excluding a list of servers from a network or sub-network. This can be used where the list of machines accessing the network is relatively small and the machines are in a sub-network, behind a firewall. Since the names of the machines are known, the list of allowed and excluded nodes can be easily set up, and this approach can be used.

Multiple Listener Load Balancing

One good way to increase SQL*Net performance is multiple listener load balancing. This has major impact on connection time for heavily loaded systems. With several listeners configured for a single database or several database instances and load balancing, there are more chances to bypass the bottlenecks when establishing a connection. This works for both dedicated servers and multi-threaded servers. One can configure multiple listeners configuring init.ora and listener.ora files on the server side, and tnsnames.ora on the client side. To enable load balancing between multiple listeners with multi- threaded server, one should assign TRUE to the MTS_MULTIPLE_LISTENERS parameter of the init.ora file. If we need to run several listeners for single multi-threaded server, we should add several addresses to init.ora file like shown below:

MTS_LISTENER_ADDRESS=
       (ADDRESS=(PROTOCOL=tcp)(HOST=myhost)
          (PORT=1521))
MTS_LISTENER_ADDRESS=
       (ADDRESS=(PROTOCOL=tcp)(HOST=myhost)
          (PORT=1522))

 If each listener is to serve several ports, one should include the ADDRESS_LIST keyword. Note that, for dedicated servers, one does not need to change init.ora parameters at all. Now go to the client side and see how to configure the tnsnames.ora file. To gain the increased performance from load balancing, one should enable the random connection between listeners. This can be done by providing each listener coordinates with a separate DESCRIPTION keyword.

oracle.world=
    (DESCRIPTION_LIST=
       (DESCRIPTION=(ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=tcp)(HOST=myhost) (PORT=1521))))
       (DESCRIPTION=(ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=tcp)(HOST=myhost) (PORT=1522))))
    (CONNECT_DATA=(SID=mysid)))

 For many listeners to communicate with many database instances, one should indicate the CONNECT_DATA keyword for each description and skip the final CONNECT_DATA for all listeners. If there are equal replicated databases, one can create a service name that maps to several database instances with different global names.

To make sure that load balancing works, you can run the Listener Control utility (lsnrctl) and issue the SERVICES command.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster