Solved

In what situation to have listener entry in the tnsnames.ora

Posted on 2014-11-13
8
784 Views
Last Modified: 2015-01-06
I found that all of my databases at the new shop has the listener entry (LISTENER_DATABASE_NAME) in the tnsnames.ora.
What may be the requirement of this setting? I am curious to know why it is required or is this at all required. How the connections will be affected using this entry?
example:---
vi tnsnames.ora

ADPPRD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = adprdb01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ADPPRD)
    )
  )
LISTENER_ADPPRD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = adprdb01)(PORT = 1521))
0
Comment
Question by:Oranew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40439884
listener is for database servers.  It allows for network connections to the database.

tnsnames.ora is a client config file that tells the client where the listener is.

That said:Oracle is client server out of the box so the database server also has a tnsnames.ora file in most cases.
0
 

Author Comment

by:Oranew
ID: 40439959
Hi Slightwv,
May be I did not ask in a right way. Let me explain it again
The server has both the listener.ora and tnsnames. ora files.
The tnsnames should be  sufficient as below:-
ADPPRD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = adprdb01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ADPPRD)
    )
  )

But I have the extra entry also (like this below which starts from LISTENER_) in the tnsnames.ora file. What is the need for this extra lines from (LISTENER_ADPPRD)?

ADPPRD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = adprdb01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ADPPRD)
    )
  )
LISTENER_ADPPRD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = adprdb01)(PORT = 1521))


---------------------------------
What is the need of this extra entry in the tnsnames.ora file??
****
LISTENER_ADPPRD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = adprdb01)(PORT = 1521))
****
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40440049
I cannot find the exact description in the online docs (I'm still learning how to get around in the new documentation layout).

It is a server side entry in the tnsnames.ora file that helps with non-standard listeners when the LOCAL_LISTENER isn't set in the database instance itself and seems to also aid in RAC implementations.

From what I can find, it is added by dbca (database configuration assistant).

Best I can find in the docs was this:
https://docs.oracle.com/html/B14406_01/undrstnd.htm

Nondefault listeners

Where listener_sid is resolved to a listener address through a naming method such as a tnsnames.ora file.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Oranew
ID: 40440053
Update:-- the above file is just the tnsnames.ora . Do you need the listener.ora also in order to answer this question?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40440061
>>Do you need the listener.ora also in order to answer this question?

No.
0
 
LVL 1

Accepted Solution

by:
ora-600 earned 500 total points
ID: 40440107
Hello Oranew,

let me first explain the database initialization parameter "local_listener":
If you leave this parameter empty the database (starting with Oracle 10g) automatically registers itself at the listener which is running on local port 1521. If you have multiple listeners on your database server you can configure "local_listener" to a different listener. When setting local_listener = 'LISTENER_ADPPRD' the database will try to register itself at the listener which is defined in the tnsnames.ora entry.

In other words: You need the LISTENER_<ORACLE_SID> entry if you want a database to connect to a different listener than the default listener. In you case LISTENER_ADPPRD points to the default listener.

If you want to remove the entry then please check the database parameter "local_listener" (Same applies to "remote_listener").

Best Regards!
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40440923
Our tnsnames.ora files on our Oracle11g database servers also have a line like that (LISTENER_DATABASE_NAME), so that appears to be the default in Oracle11.  But, I don't know if this entry is required or how it may be used.   I also don't know what happens if you change your listener name for some reason without changing this value.

Maybe this entry helps me understand a problem we had recently (and a different question here also referred to recntly).  That is: lots of errors reported in the listener.log file from a jdbc connection attempt from the local OEM db console on the server that fail with TNS-12514 errors.
0
 

Author Closing Comment

by:Oranew
ID: 40533591
Thanks everyone who participated on this question. Thanks for nice explanation
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question