OraFAQ Forum: Networking and Gateways » Erratic TNS behavior-errors

Home » RDBMS Server » Networking and Gateways » Erratic TNS behavior-errors (Red hat - 11gR2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Erratic TNS behavior-errors [message #589048]

Mon, 01 July 2013 12:30

GBla201
Messages: 5
Registered: July 2013
Location: Europe

Junior Member

Hi experts,

I'm having trouble connecting my Oracle Database server.
It's a local linux Red hat with Oracle 11gR2. I can ping with hostname, hostname.server and IP.

I've installed on my clients ( Windows 2K8 R2 64 ) both Oracle 32 & 64 bits clients ( because of 3rd party softwares).
Now when i try to connect to my Oracle server via 3rd party or SQL navigator i'm facing ORA-XXXX errors.
I face those errors with tnsping too.

What is strange is thant when i try to connect to the database server is that i can manage to connect, sometimes after 2 or 3 failure and then work on the database but after a few seconds ( 30 s usually, somtimes more ) i get disconnected .

I can't work with the server because of the disconnections, but i still can manage to connect, create tables and be disconnected again ...
The server runs with TCP & IPC, port 1521.

Any hints ?
I've tested successfully the connections via ODBC drivers.
I have only 2 oracle entries on my environment variables in PATH, should i had some more ? ORACLE_HOST , ORACLE_HOME, ORACLE_SID, others ?
No oracle services running on the client.

Any help would be much apreciated

[Updated on: Mon, 01 July 2013 12:35]

Report message to a moderator

Re: Erratic TNS behavior-errors [message #589049 is a reply to message #589048]

Mon, 01 July 2013 12:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

We need to verify what exists on DB Server before dealing with any connection from remote client
while logged onto the DB Server itself issue the following OS commands

lsnrctl status
lsnrctl service

COPY the results from above then PASTE all back here.

Report message to a moderator

Re: Erratic TNS behavior-errors [message #589050 is a reply to message #589049]

Mon, 01 July 2013 12:52

GBla201
Messages: 5
Registered: July 2013
Location: Europe

Junior Member

Hi,

Thanks for your answer.
I've not access to the server, only the client.
I've asked the dba to run those commands, but he's not working till tomorow.
I've already asked earlier the DBA to stop & start the listener with no effect.

Report message to a moderator

Re: Erratic TNS behavior-errors [message #589051 is a reply to message #589050]

Mon, 01 July 2013 12:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

the easiest way to connect from client to DB Server is via EZCONNECT; which does not use any tnsnames.ora content
but doing so assumes that the listener is up & running and that no FireWall exists on or between client & DB Server

http://www.orafaq.com/wiki/EZCONNECT

It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

Report message to a moderator

Re: Erratic TNS behavior-errors [message #589087 is a reply to message #589048]

Tue, 02 July 2013 03:05

GBla201
Messages: 5
Registered: July 2013
Location: Europe

Junior Member

Hi.

Here is the output from lsnrctl status / service :

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-JUL-2013 09:01:17 

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serv.dix.com)(PORT=1521))) 
STATUS of the LISTENER 
------------------------ 
Alias                     LISTENER 
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production 
Start Date                01-JUL-2013 13:18:28 
Uptime                    0 days 19 hr. 42 min. 48 sec 
Trace Level               off 
Security                  ON: Local OS Authentication 
SNMP                      OFF 
Listener Parameter File   /app/11g/product/11.2.0/dbhome_1/network/admin/listener.ora 
Listener Log File         /app/11g/diag/tnslsnr/bool/listener/alert/log.xml 
Listening Endpoints Summary... 
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serv.dix.com)(PORT=1521))) 
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) 
Services Summary... 
Service "PROD" has 1 instance(s). 
  Instance "PROD", status READY, has 1 handler(s) for this service... 
Service "REPO" has 1 instance(s). 
  Instance "REPO", status READY, has 1 handler(s) for this service... 
Service "TEST" has 1 instance(s). 
  Instance "TEST", status READY, has 1 handler(s) for this service... 
The command completed successfully

lsnrctl service

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-JUL-2013 09:03:35 

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serv.dix.com)(PORT=1521))) 
Services Summary... 
Service "PROD" has 1 instance(s). 
  Instance "PROD", status READY, has 1 handler(s) for this service... 
    Handler(s): 
      "DEDICATED" established:3 refused:0 state:ready 
         LOCAL SERVER 
Service "REPO" has 1 instance(s). 
  Instance "REPO", status READY, has 1 handler(s) for this service... 
    Handler(s): 
      "DEDICATED" established:84 refused:0 state:ready 
         LOCAL SERVER 
Service "TEST" has 1 instance(s). 
  Instance "TEST", status READY, has 1 handler(s) for this service... 
    Handler(s): 
      "DEDICATED" established:7 refused:0 state:ready 
         LOCAL SERVER 
The command completed successfully

I've contacted the network team and there is not firewall nor antivirus.

Here is 2 outputs from tnsping

1st try : tnsping serv.dix.com

TNS Ping Utility for Linux: Version 11.2.0.1.2 on 02-JULY-2013 09:39:52

Copyright (c) 1997, 2009 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\Oracle_64\product\11.2.0\client_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PRO
TOCOL=TCP)(HOST=122.34.7.149)(PORT=1521)))
TNS-12571: TNS :  packet writer failure

2nd attempt a few seconds later :

TNS Ping Utility for Linux: Version 11.2.0.1.2 on 02-JULY-2013 09:39:52

Copyright (c) 1997, 2009 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\Oracle_64\product\11.2.0\client_1\network\admin\sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PRO
TOCOL=TCP)(HOST=122.34.7.149)(PORT=1521)))
OK (20 msec)

My client tnsnames.ora :

REPO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = serv.dix.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = REPO)
    )
  )

TNSPING REPO 1st try :

TNS Ping Utility for Linux: Version 11.2.0.1.2 on 02-JULY-2013 09:39:52

Copyright (c) 1997, 2009 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\Oracle_64\product\11.2.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

2nd attempt same command : TNSPING REPO

TNS Ping Utility for Linux: Version 11.2.0.1.2 on 02-JULY-2013 09:39:52

Copyright (c) 1997, 2009 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\Oracle_64\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T
P)(HOST = serv.dix.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = REPO
)))
TNS-12541: TNS : No listener

3rd attempt same command : TNSPING REPO

TNS Ping Utility for Linux: Version 11.2.0.1.2 on 02-JULY-2013 09:39:52

Copyright (c) 1997, 2009 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\Oracle_64\product\11.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T
P)(HOST = serv.dix.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = REPO
)))
OK (10 msec)

The error on the 3rd party software is the following : ERROR : OCI call <OCIServerAttach> for connexion <REPO> failed : <ORA-12547: TNS : contact lost>

Thanks for your help

[Updated on: Tue, 02 July 2013 08:18]

Report message to a moderator

Re: Erratic TNS behavior-errors [message #589126 is a reply to message #589087]

Tue, 02 July 2013 09:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I am at a loss to speculate what is the the problem.
A couple of the failures indicate that the connection request never leaves the client, which might indicate OS issue local to the client.

Good Luck!

Report message to a moderator

Re: Erratic TNS behavior-errors [message #589128 is a reply to message #589087]

Tue, 02 July 2013 09:20

GBla201
Messages: 5
Registered: July 2013
Location: Europe

Junior Member

I have get the listener log ( log.xml)

Here's an extract :

<msg time='2013-07-02T11:47:10.065+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='serv.dix.com'
 host_addr='127.64.17.88'>
 <txt>02-JUL-2013 11:47:10 * service_update * REPO * 0
 </txt>
</msg>
<msg time='2013-07-02T11:49:41.447+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='serv.dix.com'
 host_addr='127.64.17.88'>
 <txt>02-JUL-2013 11:49:41 * (CONNECT_DATA=(SERVICE_NAME=REPO)(CID=(PROGRAM=C:\Data?Services\bin\repoman.exe)(HOST=SERV02)(USER=admin43))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.42.63.64)(PORT=51274)) * establish * REPO * 0
 </txt>
</msg>
<msg time='2013-07-02T11:49:43.196+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='serv.dix.com'
 host_addr='127.64.17.88'>
 <txt>02-JUL-2013 11:49:43 * service_update * REPO * 0
 </txt>
</msg>
<msg time='2013-07-02T11:49:49.199+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='serv.dix.com'
 host_addr='127.64.17.88'>
 <txt>02-JUL-2013 11:49:49 * service_update * REPO * 0
 </txt>
</msg>
<msg time='2013-07-02T11:50:10.207+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='serv.dix.com'
 host_addr='127.64.17.88'>
 <txt>02-JUL-2013 11:50:10 * service_update * REPO * 0
 </txt>
</msg>
<msg time='2013-07-02T11:50:13.541+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='serv.dix.com'
 host_addr='127.64.17.88'>
 <txt>02-JUL-2013 11:50:13 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=SERV02$))(SID=REPO)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.42.63.64)(PORT=52649)) * establish * REPO * 0
 </txt>
</msg>
<msg time='2013-07-02T11:50:16.209+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='serv.dix.com'
 host_addr='127.64.17.88'>
 <txt>02-JUL-2013 11:50:16 * service_update * REPO * 0
 </txt>
</msg>
<msg time='2013-07-02T12:01:13.494+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='serv.dix.com'
 host_addr='127.64.17.88'>
 <txt>02-JUL-2013 12:01:13 * service_update * REPO * 0
 </txt>
</msg>
<msg time='2013-07-02T12:01:16.222+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='serv.dix.com'
 host_addr='127.64.17.88'>
 <txt>02-JUL-2013 12:01:16 * (CONNECT_DATA=(SERVICE_NAME=BOPROD)(CID=(PROGRAM=C:\Quest?Software\SQL?Navigator?for?Oracle\sqlnavigator.exe)(HOST=PC8155713)(USER=admin43))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.42.63.64)(PORT=64308)) * establish * REPO* 0
 </txt>
</msg>

Report message to a moderator

Re: Erratic TNS behavior-errors [message #589131 is a reply to message #589128]

Tue, 02 July 2013 09:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

listener logs every request that it receives.
When client initiates a connection request but fails while still on the client, nothing gets logged by the listener.

BTW, a status code of "0" in listener log file indicates a successful connection request & any non-zero value is the error code.

Report message to a moderator

Re: Erratic TNS behavior-errors [message #589155 is a reply to message #589131]

Tue, 02 July 2013 17:28

GBla201
Messages: 5
Registered: July 2013
Location: Europe

Junior Member

Ok any other way to investigate ?

Now i've found a way to maintain the connection : if i launch the following commande : ping -t serv.dix.com i can maintain the connexion to the database server.
I can't understand this behavior. I have faced 0 error since.

Is there any way this could be a Oracle server configuration issue ? or a network issue ?

Thanks

Report message to a moderator

Re: Erratic TNS behavior-errors [message #589156 is a reply to message #589155]

Tue, 02 July 2013 17:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there any way this could be a Oracle server configuration issue ?
I doubt that the root cause resides on the Oracle Server.
You can packet sniff to obtain visibility about the packet exchange between client & DB Server.
When the error indicates a failure BEFORE getting out onto the network, there will be nothing to packet sniff.

Report message to a moderator

Previous Topic:	TNS Could not Found
Next Topic:	Connection between oracle and mssql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 00:01:46 CST 2024