?
Solved

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

Posted on 2014-11-13
8
Medium Priority
?
891 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 2000 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

777 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