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/[email protected]
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.
