Home » Infrastructure » Unix » Having a problem with tcpip connection (Oracle 10g on windows)
Having a problem with tcpip connection [message #566193] Tue, 11 September 2012 13:21 Go to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
The server has two oracle homes on it a 10.2.0.3 and a 11.2.0.1. I'm using the listener on the 11g side and the database is 10.2.0.3, I'm using port 1528 just so we can differentiate between old and new dbs. I've done the same thing on other servers and never had a problem until now.

I can connect to the database if I set the oracle_sid, no problem. But when I try to use the connect string (i.e. sqlplus scott/tiger@test), the connection just hangs for as long as I can wait until I do a control-c.

I've tried deleting and recreating the listener several times without any change using the network configuration assistant. I can do a tnsping with no problem, comes right back.

when I look in the listener.log I see this kind of entry:

11-SEP-2012 14:12:07 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=imcsa_oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
Tue Sep 11 14:12:26 2012
11-SEP-2012 14:12:21 * (CONNECT_DATA=(SERVICE_NAME=ORCQA04)(CID=(PROGRAM=d:\oracle\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=IMCNWORCQA21)(USER=imcsa_or acle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=40.151.4.122)(PORT=2638)) * establish * ORCQA04 * 0

The only thing I don't understand from the output is the port number, it says port=2638, I don't know where that came from.

Any idea?

TIA

Dave


Re: Having a problem with tcpip connection [message #566195 is a reply to message #566193] Tue, 11 September 2012 13:40 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
You really should be using only a single listener; since having 2 complicates reality.
I suspect a problem with tnsnames.ora file; but again complicated by having 2 ORACLE_HOME trees.
I suspect that the connection request never gets to any listener.
Consider packet sniffing to gain visibility as to what really occurs on the network.
Re: Having a problem with tcpip connection [message #566197 is a reply to message #566195] Tue, 11 September 2012 13:50 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
I'm not using two listeners, only one from the 11g home. As far as packet sniffing goes, I have no idea how to do that.
Re: Having a problem with tcpip connection [message #566198 is a reply to message #566197] Tue, 11 September 2012 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
WIRESHARK or ETHEREAL can be used to packet sniff.


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

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


post results from OS command below

tnsping test
Re: Having a problem with tcpip connection [message #566211 is a reply to message #566198] Tue, 11 September 2012 17:37 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
First of all I want to thank the people who've replied to this post. I needed to walk away from this for a little while. I CAN do a ping of the server from home (logged into work), I CAN do a tnsping from my pc, if I try to connect to the database from my pc, I see an entry in the logfile of the listener, this tells me that the connection is getting to the server but not to the database. I've corrected all the full names, short names, that didn't do anything. I tried "sqlplus system@orcqa04" from home and I got this message on the listener.log, Tue Sep 11 18:31:15 2012
11-SEP-2012 18:31:10 * (CONNECT_DATA=(SERVICE_NAME=orcqa04)(CID=(PROGRAM=c:\oracle\product\11.2.0\client_1\bin\sqlplus.exe)(HOST=4606LT)(USER=xxxxxx))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.4.200.22)(PORT=49886)) * establish * orcqa04 * 0 I xx'd out my name, but 4606LT is my laptop's name.
I still don't know what it is but maybe this helps?
Re: Having a problem with tcpip connection [message #566212 is a reply to message #566211] Tue, 11 September 2012 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
listener log file entry shows that the request got to the listener & the listener reports it was successful at handing it off to the DB.
Next the DB would attempt to establish packet exchange with the client on (PORT=49886).
Do the Firewall rules allows this to succeed?
Re: Having a problem with tcpip connection [message #566213 is a reply to message #566212] Tue, 11 September 2012 17:54 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
Well this happens even if I'm trying to connect on the server. So that's a connection that isn't going through the firewall, isn't it? This server was set up with the same image as other servers that do not have this issue.

In this case, the listener is an 11g listener and the database is 10g. I have other servers set up like this and there's no problem. In fact during the day I tried to create a 10g listener and got the same issue. In the 10g home, I have an ifile in the tnsnames.ora pointing back to the 11g home.
Re: Having a problem with tcpip connection [message #566214 is a reply to message #566213] Tue, 11 September 2012 17:56 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
one other thing, I put a wrong pw in just to see what would happen, and it still hung. To me this means that it's not getting to the database? Am I correct in this?
Re: Having a problem with tcpip connection [message #566215 is a reply to message #566212] Tue, 11 September 2012 18:00 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
This is going to turn out to be something stupid, I know it. I've been a dba for 20 years.
Re: Having a problem with tcpip connection [message #566216 is a reply to message #566215] Tue, 11 September 2012 18:03 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
post results from following SQL below

SELECT * FROM V$VERSION;

why is this UNIX subforum when "(Oracle 10g on windows)" is in Title?
Re: Having a problem with tcpip connection [message #566219 is a reply to message #566216] Tue, 11 September 2012 18:09 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
Here you go:

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\davidmarcus>set oracle_sid=orcqa04

C:\Documents and Settings\davidmarcus>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 11 19:09:02 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL>
Re: Having a problem with tcpip connection [message #566220 is a reply to message #566216] Tue, 11 September 2012 18:14 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
as far as what forum it's in. I put it in the networking forum! I just noticed its in the unix forum, don't know how that happened. Is there anyway to move it?
Re: Having a problem with tcpip connection [message #566221 is a reply to message #566219] Tue, 11 September 2012 18:16 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
FWIW - There was a bug in *NIX versions in V10.2.0.1 (& I recall it was fixed in V10.2.0.4)
that exhibited this very behavior & was related to system uptime.
If/when system was up more than 200 days or so, SQL*Net could no longer be used.
The work around as just an OS reboot.
I don't know if it occurred on Windows.
One way to get visibility on *NIX for this type of issue would be like below
# strace sqlplus scott/tiger@test
Re: Having a problem with tcpip connection [message #566222 is a reply to message #566221] Tue, 11 September 2012 18:18 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
No that's not it. This is a new server it's only been around a month or so, and I rebooted it today.
Re: Having a problem with tcpip connection [message #566224 is a reply to message #566222] Tue, 11 September 2012 18:43 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
I have one suggestion that is equivalent of giving Chicken Soup to a dead man.
It can't hurt & just might help.
$ORACLE_HOME\bin\relink sqlplus
Re: Having a problem with tcpip connection [message #566226 is a reply to message #566224] Tue, 11 September 2012 18:48 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
at this point I'll try anything, I'll let you know.
Re: Having a problem with tcpip connection [message #566227 is a reply to message #566226] Tue, 11 September 2012 18:51 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
That's a unix option. No relink in windows or at least I can't find it.
Re: Having a problem with tcpip connection [message #566228 is a reply to message #566227] Tue, 11 September 2012 18:54 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
did a google on it, unix only.
Re: Having a problem with tcpip connection [message #566229 is a reply to message #566228] Tue, 11 September 2012 19:06 Go to previous messageGo to next message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
Found this trace file, does it mean anything to anyone?

Dump file d:\oracle\diag\rdbms\orcqa04\orcqa04\udump\orcqa04_ora_3444.trc
Tue Sep 11 19:58:02 2012
ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V5.2 Service Pack 2
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:3227M/4095M, Ph+PgF:6310M/8021M, VA:662M/2047M
Instance name: orcqa04

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 3444, image: ORACLE.EXE (SHAD)


opiino: Attach failed! error=-1 ifvp=00000000
Re: Having a problem with tcpip connection [message #566230 is a reply to message #566229] Tue, 11 September 2012 19:14 Go to previous message
dmarcus48
Messages: 44
Registered: May 2008
Location: Central NJ
Member
ok I got it, the windows service was run as a system account. I changed it to a local account and it worked. Thanks for your help!!!
Previous Topic: Help!! Where I can download Oracle 10.2.0.4 for HP-UX
Next Topic: How to make mailx command work
Goto Forum:
  


Current Time: Wed Oct 22 04:10:02 CDT 2014

Total time taken to generate the page: 0.09197 seconds