TNS Timeout Error when connecting to SQLPLUS through scripts only

aashish.sharma8 · May 19, 2012, 10:55am

Hi,

I am facing a strange issue when connecting to SQLPLUS via a shell scripts.

I am using Linux 2.6.18-274.18.1 and gbash shell.

When I connect to SQLPLUS through scripts then it throws TNS Time Out error ""sometimes"" and connects successfully other times.This is only happening when sqlplus is called within scripts and working fine when called directly from terminal.

Database is on differnent machine and TNS and network connections are perfectly set on both machines.Ping stats are also perfect

for demonstration i have created a test script:

#!/bin/bash
i=0
ID=userid/password@sid
while [ $i -le 3 ]
do
  echo $i
  sqlplus -s ${ID}<<EOF
  select sysdate from dual;
  exit;
EOF
  i=`expr $i + 1`
done

Output:

 
0
SYSDATE
---------
19-MAY-12
1
SYSDATE
---------
19-MAY-12
2
ERROR:
ORA-12170: TNS:Connect timeout occurred
 
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_identifier>] | /
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
 
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
3
SYSDATE
---------
19-MAY-12

Entries of sqlnet.log :

***********************************************************************

Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 10.2.0.3.0 
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.3.0
Time: 18-MAY-2012 18:26:25
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 110
nt OS err code: 0
Client address: <unknown>

Strange thing is that it connects sometimes and fails other times.
Please help

monalsinha · May 19, 2012, 11:37am

ID="userid/password@sid"

or

usr=userid
pswd=password
tns=sid

sqlplus -s ${usr}/${pswd}@${tns}

# or

export ID=userid/password@sid

Scott · May 19, 2012, 11:46am

Could you explain how you think this would address the issue? I don't see how it could.

monalsinha · May 19, 2012, 12:40pm

ERROR: ORA-12170: TNS:Connect timeout occurred

SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] where <logon> ::= <username>[/<password>][@<connect_identifier>] | / ERROR: ORA-12162: TNS:net service name is incorrectly specified

my answer is based on the fact that somehow connection parameter gets changed
in the 3 attempts and as it is in loop, so i thought there might be some special character.
I understand there is no explanation to this... but i faced this problem sometime back
and solved by putting double quotes.

aashish.sharma8 · May 19, 2012, 2:14pm

Password doesn't contains any special character, though I have tried enclosing userid,password and sid in double quotes but no success.

it is not necessary the code will struck at third iteration only.it gets struck at any point.

Another example :

0
ERROR:
ORA-12170: TNS:Connect timeout occurred

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
1
SYSDATE
---------
19-MAY-12
2
ERROR:
ORA-12170: TNS:Connect timeout occurred

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon>  ::= <username>[/<password>][@<connect_identifier>] | /
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
3
SYSDATE
---------
19-MAY-12

monalsinha · May 19, 2012, 2:48pm

it may sound weird but can u try this

sqlplus -s "${ID}" <<EOF
 select sysdate from dual;
 exit;
EOF

aashish.sharma8 · May 20, 2012, 12:55pm

HI Monalsinha, i have tried all the possible combinations but no success.
I think this is not due to any issue in script as the same is happening with other scripts toooo.
Can this be result of any security or network settings of shell?

selinux is disabled on both machines.

binlib · May 20, 2012, 8:08pm

Maybe you are creating connections too fast. Try to put a sleep after each invocation of sqlplus.

jim_mcnamara · May 20, 2012, 8:34pm

We have has varying causes of the problem which is a listener problem in disguise.
Here are some things we have had to fix:

use IP ADDRESSES for your oracle instances in the tnsnames.ora file
check: ORACLE_HOME is correct in the script file. Put:

echo "this is oracle_home: $ORACLE_HOME"

in your scripts. The environment the scripts run in has to match the "manual one".
Do not dismiss this as "not possible" until you have verified ORACLE_HOME for each problem. This messed up a lot of things for developers.

This bit us once:

[ -r   $ORACLE_HOME/network/admin/tnsnames.ora ]  || echo 'bad tnsnames permissions')

Duplicate listeners for one instance.

All of these caused TNS-12560 as a root cause for timed out errors. Since there are so many possible causes that I have seen, and there are others, if this does not work, open a support ticket with oracle. I assumed Linux on the "user side" and the "oracle server side"

aashish.sharma8 · June 22, 2012, 8:32am

The issue was resolved by changing the database SID, the route that packets were taking was over trafficked as told by a DBA.
All well now... !!