Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Networking with Oracle’ Category

How to resolve TNS:Connection closed error?

Posted by Pavan DBA on April 25, 2012


Today I faced some issue regarding listener startup and want to share this info with you folks…

I got an email from users saying they are unable to connect to one of the production server. They are getting “NO LISTENER” message. So, its clear from this that listener could have been shutdown.

I logged in and checked the listener status using both “lsnrctl status” command and “ps -ef | grep tns” command. Both of the commands didn’t given any posivitive result.

So I started the listener with the below command and got error as this…

[oracle@dbserver1 admin]$ lsnrctl start LISTENER
 
 
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 25-APR-2012 18:16:24
 
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
 
Starting /u01/ora10g/bin/tnslsnr: please wait…
 
TNS-12537: TNS:connection closed
 TNS-12560: TNS:protocol adapter error
  TNS-00507: Connection closed
   Linux Error: 29: Illegal seek

The problem is identified as a line is missing in /etc/hosts file (mine is a Linux server). I added below line and problem solved


127.0.0.1         localhost.localdomain       localhost

So, whenever you hit with the above error (Illegal Seek), please check hosts file and add the above line which will solve the issue……

Posted in Networking with Oracle | Tagged: , , , , | 9 Comments »

Listener fails to start if listener log file is > 4GB

Posted by Pavan DBA on March 27, 2012


Let me share an experience with you people which I faced 2 weeks back…

I got an email from application team stating they are unable to connect to database and getting below error

TNS-12518: TNS: listener could not hand off client connection

I immediately checked the listener status on the database server (My database is of 11.2.0.1 version on Windows platform) and it is up and running fine.As per regular troubleshoot check, the solution would be to trace the connection

ORA-12518: TNS:listener could not hand off client connection
 
Cause: The process of handing off a client connection to another process failed.

Action: Turn on listener tracing and re-execute the operation. Verify that the listener and database instance are properly configured for direct handoff. If problem persists, call Oracle Support.
But, before tracing, I checked the listener log file and observed below message

TNS-12560: TNS: Protocol adapter error
TNS-00530: Protocol adapter error
64-bit Windows Error: 53: Unknown error
TNS-12518: TNS: listener could not hand off client connection
TNS-12571: TNS: packet writer failure
TNS-12560: TNS: Protocol adapter error
TNS-00530: Protocol adapter error
64-bit Windows Error: 54: Unknown error
I tried reloading the listener and after restarting, users were able to connect for sometime. But after few minutes again same issue(error) occurred.

Finally after searching few sites, I got to know that this would be because of large size of listener log file.

If the listener.log is greater than 4GB in size, the listener may fail to start on Windows platforms.

This is a bug (bug no 9497965) on windows and as a resolution, I moved the file to other location, so that Oracle will created a new log file. From that moment, there are no issues observed in the user connectivity.

So, next time if you face this issue, check your listener log file size.

Please refer to MOS DOC ID 9497965.8 for more details….

Note : As per the MOS doc, this bug will not reproduce in 11.1.0.7 patch 29 or higher, but I am still wondering because my version is 11.2.0.1. It might have not fixed now….

Posted in Networking with Oracle | Tagged: , , , , | Leave a Comment »

Resolving TNS – Message 3511 not found

Posted by Pavan DBA on September 14, 2010


Today i got the following error message when trying to do tnsping for a instance. This is the only database existing on this server.

blacksun037@root# tnsping ora11db

TNS Ping Utility for Solaris: Version 11.2.0.1.0 – Production on 14-SEP-2010 04:26:51

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

Message 3511 not found; No message file for product=network, facility=TNSTNS-03505: Message 3505 not found; No message file for product=network, facility=TNS
I wondered and started analyzing what happend exactly wrong….and finally found solution to be pretty simpler

we need to set home path and sid (which i had not done yet as its new database server)

blacksun037@root# export ORACLE_SID=ora11db
blacksun037@root# export ORACLE_HOME=/oracle/product/11.2.0/dbhome_1

After setting above variables, its working

blacksun037@root# tnsping ora11db

TNS Ping Utility for Solaris: Version 11.2.0.1.0 – Production on 14-SEP-2010 04:42:12

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

Used parameter files:
/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = blacksun037)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora11db)))
OK (0 msec)

Posted in Networking with Oracle | Tagged: , , , | 14 Comments »

how to handle inbound connection timed out (ORA-3136)?

Posted by Pavan DBA on June 16, 2010


Good day friends…

Today when going through regular activities like alert log checking, i found the following warning in one of database

Tue Jun 15 14:44:55 2010
WARNING: inbound connection timed out (ORA-3136)

Also, following entry was there in sqlnet.log file

Fatal NI connect error 12170.

  VERSION INFORMATION:
 TNS for 64-bit Windows: Version 10.2.0.2.0 – Production
 Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 10.2.0.2.0 – Production
 Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 10.2.0.2.0 – Production
  Time: 15-JUN-2010 12:58:20
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=20.60.103.141)(PORT=3741))

To my surprise, in next 2 min i got a ticket stating user is facing problem in connecting to database. After waiting for long time, user is getting error in connection establishment.

After analysis, i found following

user connectivity time bound is 60 sec from 10.2 which means oracle will try to establish connection for 60 seconds, once it crosses that limit, it will send error to user process.

This error you will not see in 10.1 version, because in that version time bound limit in infinite.

I did following which resolved my problem

1) Add following entires in listener.ora file on database server

INBOUND_CONNECT_TIMEOUT_<LISTENER_NAME>=0
DIRECT_HANDOFF_TTC_<LISTENER_NAME>=OFF

alternatively you can also do this

lsnrctl> set inbound_connect_timeout=0

2) add following entries in sqlnet.ora of database server

SQLNET.INBOUND_CONNECT_TIMEOUT=0

we are setting time bound limit to infinite using above parameters. you need to reload the listener after performing above changes using

$ lsnrctl reload

Note : Always take backup of listener.ora or sqlnet.ora files before modifying anything inside

More about this error, you can check in metalink docs 465043.1 and 345197.1

Sometimes, this problem will also occur if you have firewall restrictions. So please check from that end too.

Posted in Networking with Oracle | Tagged: , , | 4 Comments »

how to resolve ORA-12638: Credential retrieval failed

Posted by Pavan DBA on May 19, 2010


Hi Friends, today i faced very nasty situation in establishing network connection with Oracle database.

I created a user as per request from application team and i got response that they are not able to connect. when analyzing i found tnsping is not working.

I started looking at tnsnames.ora and compared that with what is there in server and its same. so as next step i looked at SQLNET.ORA file and added following line to make sure both the machines are in same domain

NAMES.DEFAULT_DOMAIN = klpcph.local

Still nothing worked. Then i tried telnet from client machine and realized that 1521 port is blocked at firewall level.

Once firewall team provided access, tnsping worked but it throwed ORA-12638 : credential retrieval failed error

I found following line in sqlnet.ora file

SQLNET.AUTHENTICATION_SERVICES= (NTS)

Then i modified the above line as below and it started working

SQLNET.AUTHENTICATION_SERVICES= (NONE)

Reason : 

Oracle client attempt to use your current Windows domain credentials to authenticate you with the Oracle server. This could fail for a couple of reasons:

– The Oracle server is not configured to support Windows authentication
– The credentials you use to login to your local machine are not sufficient to allow you to login to the server.

In my case, it was the later. This failed because I was logged on to my local machine using my normal domain credentials rather than my administrator account. 

Posted in Networking with Oracle | Tagged: , | 19 Comments »

 
%d bloggers like this: