Friday 6 January 2017

Oracle Database Connectivity Issue | ORA-17002 IO Error : Socket read timed out

While connecting oracle database with SQL Developer got below error.

ORA-17002 IO Error: Socket read timed out


After analysis found that listener was down. Here are steps to analyze and fix this.

1. Go to ORACLE_HOME/bin and check listener status using lsnrctl status command. It will show below message.
=======================================================================
[root@dev bin]# ./lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-JAN-2017 18:42:11

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused


=======================================================================

2. Now start the listener using lsnrctl start command.

=======================================================================
 [oracle@dev bin]$ ./lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-JAN-2017 18:55:16

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

Starting /app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /app/oracle/diag/tnslsnr/dev/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                 TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date             06-JAN-2017 18:55:18
Uptime                  0 days 0 hr. 0 min. 0 sec
Trace Level           off
Security                ON: Local OS Authentication
SNMP                   OFF
Listener Parameter File   /app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/dev/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


=======================================================================

3. Now you can confirm listener status again using lsnrctl status command.

=======================================================================
 [oracle@dev bin]$ ./lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-JAN-2017 18:55:42

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                 TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date             06-JAN-2017 18:55:18
Uptime                  0 days 0 hr. 0 min. 24 sec
Trace Level          off
Security               ON: Local OS Authentication
SNMP                  OFF
Listener Parameter File   /app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/dev/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))(Security=(my_wallet_directory=/app/oracle/product/12.1.0/dbhome_1/admin/dev/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "localhost" has 1 instance(s).
  Instance "dev", status READY, has 1 handler(s) for this service...
Service "devXDB.dev.local" has 1 instance(s).
  Instance "dev", status READY, has 1 handler(s) for this service...
The command completed successfully


=======================================================================

Note : To perform this activity you should login as non root user.

No comments:

Post a Comment