Friday, March 1, 2019

The listener supports no services

When i am trying to connect newly created database I am getting below error
ERROR
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor

When I checked my listener status it shown below

$ /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

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.
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.240.230)(PORT=1530)))' sid='QA' scope=spfile;
- 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
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.

Above steps fixed my issue.
Check the listener status now
$/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 14:25:41
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 14:22:39
Uptime                    0 days 0 hr. 3 min. 2 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)))
Services Summary...
Service "QA.tiermeddc.inc" has 1 instance(s).
  Instance "QA", status READY, has 1 handler(s) for this service...
Service "QAXDB.tiermeddc.inc" has 1 instance(s).
  Instance "QA", status READY, has 1 handler(s) for this service...
The command completed successfully

My TNSNAMES.ora as below
QA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.240.230)(PORT = 1530))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = QA.domain.inc)
    )
  )  

Also add host name to /etc/hosts

No comments:

Post a Comment