Thursday 17 November 2011

Networking with Oracle Category


Archive for the ‘Networking with Oracle’ Category
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)
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.
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. 

No comments:

Post a Comment