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)
)
)

Category: Uncategorized

Tags:

Leave a Reply

Article by: Shadab Mohammad