ORA-12520: TNS:listener could not find available handler for requested type of server

One afternoon in the office, user complained unable to connect to the database.
Database is newly setup by build team.

ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server


so immediately perform a tnsping from my local machine and its working.

PS C:\Users\> tnsping MXXXNU5

TNS Ping Utility for 32-bit Windows: Version 11.2.0.4.0 - Production on 02-AUG-2016 13:12:09

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\Oracle\product\11.2.0.4_32\client\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lnxxdsgtu5-scan.vai.com)(PORT = 15211))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = MXXXNU5)))
OK (900 msec)
PS C:\Users\> 

But when i try to connect using sqlplus

PS C:\Users\> sqlplus system/system@MXXXNU5

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 2 14:01:00 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


PS C:\Users\> 

so i decided to login to server and check the scan listener

$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-AUG-2016 01:51:32

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                27-JUL-2016 21:26:00
Uptime                    5 days 4 hr. 25 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /optware/grid/11.2.0.4/network/admin/listener.ora
Listener Log File         /optware/grid/11.2.0.4/log/diag/tnslsnr/lnxxdsgtu51/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.158.113)(PORT=15211)))
Services Summary...
Service "MXXXNU5" has 2 instance(s).
  Instance "MXXXNU51", status READY, has 1 handler(s) for this service...
  Instance "MXXXNU52", status READY, has 1 handler(s) for this service...
The command completed successfully
$

but for local listner for both nodes it shows no services

bash-4.1$ lsnrctl status LISTENER_MXXXNU5

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-AUG-2016 02:06:27

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_MXXXNU5)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_MXXXNU5
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                02-AUG-2016 01:11:01
Uptime                    0 days 0 hr. 55 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /optware/oracle/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /optware/oracle/11.2.0.4/db_1/log/diag/tnslsnr/lnxxdsgtu51/listener_metanu5/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_MXXXNU5)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.158.91)(PORT=15220)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.158.95)(PORT=15220)))
The listener supports no services
The command completed successfully
bash-4.1$ 

perform alter system register same issue.
after few minutes of checking found the issue.
-node1 db, local listener parameter is pointing to node 2

SQL> show parameter local_listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.158.92)(PORT=15220)) -- should connect to 192.168.158.95 (node1 vip)

SQL> !host 192.168.158.95
95.158.72.168.in-addr.arpa domain name pointer lnxxdsgtu51-vip.vai.com.

SQL> !host 192.168.158.92
92.158.72.168.in-addr.arpa domain name pointer lnxxdsgtu52.vai.com.

SQL> !hostname
lnxxdsgtu51

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.158.95)(PORT=15220)))' sid='MXXXNU51' scope=spfile;

System altered.

SQL>

-node2 db, local_listener parameter is pointing to vip of node1

SQL> show parameter local_listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.158.95)(PORT=15210)) -- should connect to 192.168.158.96 (node2 vip) 

SQL> !host 192.168.158.95
95.158.72.168.in-addr.arpa domain name pointer lnxxdsgtu51-vip.vai.com.

SQL> !hostname
lnxxdsgtu52

SQL> !host 192.168.158.96
96.158.72.168.in-addr.arpa domain name pointer lnxxdsgtu52-vip.vai.com.

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.158.96)(PORT=15220)))' sid='MXXXNU52' scope=spfile;

System altered.

SQL>

-restarted both instance

bash-4.1$ srvctl stop instance -d MXXXNU5 -i MXXXNU51
bash-4.1$ srvctl start instance -d MXXXNU5 -i MXXXNU51

bash-4.1$ srvctl stop instance -d MXXXNU5 -i MXXXNU52
bash-4.1$ srvctl start instance -d MXXXNU5 -i MXXXNU52

now lets check both listener

-node 1
    $lsnrctl status LISTENER_MXXXNU5

    LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-AUG-2016 02:47:23

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_MXXXNU5)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER_MXXXNU5
    Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    Start Date                02-AUG-2016 02:23:10
    Uptime                    0 days 0 hr. 24 min. 13 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /optware/oracle/11.2.0.4/db_1/network/admin/listener.ora
    Listener Log File         /optware/oracle/11.2.0.4/db_1/log/diag/tnslsnr/lnxxdsgtu51/listener_metanu5/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_MXXXNU5)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.158.91)(PORT=15220)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.158.95)(PORT=15220)))
    Services Summary...
    Service "MXXXNU5" has 1 instance(s).
      Instance "MXXXNU51", status READY, has 1 handler(s) for this service...
    The command completed successfully

    SQL>

-node2
    $ lsnrctl status LISTENER_MXXXNU5

    LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-AUG-2016 02:48:51

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_MXXXNU5)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER_MXXXNU5
    Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    Start Date                02-AUG-2016 02:23:10
    Uptime                    0 days 0 hr. 25 min. 41 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /optware/oracle/11.2.0.4/db_1/network/admin/listener.ora
    Listener Log File         /optware/oracle/11.2.0.4/db_1/log/diag/tnslsnr/lnxxdsgtu52/listener_metanu5/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_MXXXNU5)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.158.92)(PORT=15220)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.158.96)(PORT=15220)))
    Services Summary...
    Service "MXXXNU5" has 1 instance(s).
      Instance "MXXXNU52", status READY, has 1 handler(s) for this service...
    The command completed successfully
    $

finally user can connect now.

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