ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
TNS-12541: TNS:no listener
TNS-00511: No listener
ERROR:
ORA-12560: TNS:protocol adapter error

So you tried connecting to an Oracle Database and got the dreadful error. TNS listener not available, this is one of the most common errors connecting to an Oracle database and one which could have a wide variety of reasons. Though it would not be possible to touch the length and breadth of why this error cancome. I will elaborate using a small test scenario.

The first thing you would like to look at when you get this error is if you have defined the SERVICE_NAME correctly in the tnsnames.ora file

After which login to the Oracle DB Server and checl with “lsnrctl status”
$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 27-OCT-2014 07:35:22

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 26-OCT-2014 20:51:02
Uptime 0 days 10 hr. 44 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/testdb01/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Services Summary…
If you do no see the service name defined in your TNSNAMES.ora file in the listener services, then there is a good possibility that the PMON process (the process responsible for registering the instance with listener) has not done its job. There are 2 days to register the instance with the listener : Dynamic Registration and Static Registration.

Static Registration : It is the process by which an explicit name is defined in the listener.ora file to register the instance with the listener.

Below is an example of a listener.ora file where instances are statically registered.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test01)
(ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = test01)
)
(SID_DESC =
(GLOBAL_DBNAME = test02)
(ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = test02)
)
(SID_DESC =
(GLOBAL_DBNAME = test03)
(ORACLE_HOME = /u02/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = test03)
)
)
This will reguster the test01, test02, test03 services with the listener. Please note that this does not guarantee a connection, since the appropriate instance should be running in the background.
Dynamic Registration: PMON is the process associated with registering the instance with the listener. Generally there are few parameters associated with dynamic registration viz. LOCAL_LISTENER, DB_NAME,DB_DOMAIN, SERVICE_NAMES etc.

To add address of a local listener and register it, below are the steps you have to do

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))’ scope=both;

System altered.

SQL> alter system register;

System altered.
SQL> show parameter local

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=1
27.0.0.1)(PORT=1521))
This will register the service on listener as DB_NAME (if DB_DOMAIN is defined DB_NAME.DB_DOMAIN is the format used)

SQL> show parameter db_domain

NAME TYPE VALUE
———————————— ———– ——————————
db_domain string easyoradba.com
SQL> show parameter db_name

NAME TYPE VALUE
———————————— ———– ——————————
db_name string test
SQL> show parameter service_name

NAME TYPE VALUE
———————————— ———– ——————————
service_names string test.easyoradba.com
So when you check the listener you will see a service called ‘test.easyoradba.com’ is registerd with instanced called ‘test’

The tnsnames.ora for this setup will ideally look like

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test.easyoradba.com)
)
)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s