Solved

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

Posted on 2014-11-13
8
546 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
8 Comments
 
LVL 76

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 76

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now