Home » RDBMS Server » Networking and Gateways » Erratic TNS behavior-errors (Red hat - 11gR2)
Erratic TNS behavior-errors [message #589048] Mon, 01 July 2013 12:30 Go to next message
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.

    ORA-12547 TNS lost contact
    ORA-12571 TNS packet writer failure
    ORA-12541 TNS no listener


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 Go to previous messageGo to next message
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.

Re: Erratic TNS behavior-errors [message #589050 is a reply to message #589049] Mon, 01 July 2013 12:52 Go to previous messageGo to next message
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.

Re: Erratic TNS behavior-errors [message #589051 is a reply to message #589050] Mon, 01 July 2013 12:56 Go to previous messageGo to next message
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.
Re: Erratic TNS behavior-errors [message #589087 is a reply to message #589048] Tue, 02 July 2013 03:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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!
Re: Erratic TNS behavior-errors [message #589128 is a reply to message #589087] Tue, 02 July 2013 09:20 Go to previous messageGo to next message
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>
Re: Erratic TNS behavior-errors [message #589131 is a reply to message #589128] Tue, 02 July 2013 09:31 Go to previous messageGo to next message
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.
Re: Erratic TNS behavior-errors [message #589155 is a reply to message #589131] Tue, 02 July 2013 17:28 Go to previous messageGo to next message
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
Re: Erratic TNS behavior-errors [message #589156 is a reply to message #589155] Tue, 02 July 2013 17:58 Go to previous message
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.
Previous Topic: TNS Could not Found
Next Topic: Connection between oracle and mssql
Goto Forum:
  


Current Time: Fri Mar 29 05:47:19 CDT 2024