ERROR
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor
When I checked my listener status it shown below
[code] $ /mnt/ora01/app/oracle/product/11.2.0/db_1/bin/lsnrctl status LISTENER_QA LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-JAN-2016 13:47:33 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.240.230)(PORT=1530))) STATUS of the LISTENER ------------------------ Alias LISTENER_QA Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 13-JAN-2016 13:44:43 Uptime 0 days 0 hr. 2 min. 50 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /mnt/ora01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /mnt/ora01/app/oracle/diag/tnslsnr//listener_qa/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530))) The listener supports no services The command completed successfully [/code]
I tried multiple option but no luck.
Reason behind the issue:
================
- Listener is a process that runs on database server and is responsible for receiving client connection and passing connection to database.
- If you are using default port (1521) for listener, then database will automatically register the instance with listener.
- But what if you are not using default listener port (1521). Instead, you are using some other port – Say 1530. Oracle doesnt do dyamic registration on non-default listener port automatically.
- If you are using non default port (other than 1521). In local_listener parameter we can specify non-default ports on which listener is running and Oracle will register services on those ports. [code] SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))' sid='QA' scope=spfile; [/code]
- As soon as I set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.
As sys/system user
[code] SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))' sid='QA' scope=spfile; System altered. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 4.1823E+10 bytes Fixed Size 2237488 bytes Variable Size 4429188048 bytes Database Buffers 3.7313E+10 bytes Redo Buffers 78639104 bytes Database mounted. sDatabase opened. SQL>show parameter local NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO L=tcp)(HOST=172.16.240.230)(PO RT=1530))) log_archive_local_first boolean TRUE parallel_force_local boolean FALSE SQL> alter system register; System altered. [/code]
Above steps fixed my issue.
Check the listener status now
My TNSNAMES.ora as below
[code] QA = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.240.230)(PORT = 1530)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = QA.domain.inc) ) ) [/code]
Also add host name to /etc/hosts
