Home » Infrastructure » Windows » Oracle connection problem (Oracle RDBMS, 11g, Windows Vista )
Oracle connection problem [message #558548] Sat, 23 June 2012 13:36 Go to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
I'm having great difficulty connecting to two separate databases on my PC. Here are the details.
I'd be grateful if anyone can figure out what is wrong.

c:\>echo %TNS_ADMIN%
C:\TNS_ADMIN

#C:\TNS_ADMIN\tnsnames.ora
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Tams-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

tams=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Tams-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

#C:\TNS_ADMIN\sqlnet.ora
NAMES.DIRECTORY_PATH=(TNSNAMES)
SQLNET.AUTHENTICATION_SERVICES=(NTS)

c:\>tnsping xe

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-JUN-2
012 18:27:25

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

Used parameter files:
C:\TNS_ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Tams-PC
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)

c:\>tnsping tams

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-JUN-2
012 18:27:28

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

Used parameter files:
C:\TNS_ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Tams-PC
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)

c:\>ping Tams-PC

Pinging Tams-PC [10.10.10.10] with 32 bytes of data:
Reply from 10.10.10.10: bytes=32 time<1ms TTL=128
Reply from 10.10.10.10: bytes=32 time<1ms TTL=128
Reply from 10.10.10.10: bytes=32 time<1ms TTL=128
Reply from 10.10.10.10: bytes=32 time<1ms TTL=128

Ping statistics for 10.10.10.10:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

c:\>sqlplus user/pass

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 23 19:16:14 2012

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

c:\>sqlplus user/pass@tams - HANGS
c:\>sqlplus user/pass@xe   - HANGS

c:\>lsnrctl stat

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-JUN-2012 19:20
:30

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date                22-JUN-2012 15:54:49
Uptime                    1 days 3 hr. 25 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\Tams-PC\listener\
alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Tams-PC)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Tams-PC)(PORT=8080))(Presentation=H
TTP)(Session=RAW))
Services Summary...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

[Updated on: Sat, 23 June 2012 13:40]

Report message to a moderator

Re: Oracle connection problem [message #558549 is a reply to message #558548] Sat, 23 June 2012 14:00 Go to previous messageGo to next message
BlackSwan
Messages: 22517
Registered: January 2009
Senior Member
Interesting observation is that this listener was started without using any listener.ora.

after trying & failing once again to connect via @tams & @xe
post tail end (last 50 - 100) lines from C:\oraclexe\app\oracle\diag\tnslsnr\Tams-PC\listener\alert\log.xml

open Command Window & issue follow OS commands
set
echo %ORACLE_HOME%

COPY commands & results then PASTE all back here

[Updated on: Sat, 23 June 2012 14:07]

Report message to a moderator

Re: Oracle connection problem [message #558552 is a reply to message #558549] Sat, 23 June 2012 14:16 Go to previous messageGo to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
#C:\oraclexe\app\oracle\diag\tnslsnr\Tams-PC\listener\alert

<msg time='2012-06-23T19:50:31.676+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
 host_addr='10.10.10.10'>
 <txt>23-JUN-2012 19:50:31 * service_update * xe * 0
 </txt>
</msg>
<msg time='2012-06-23T19:51:43.677+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
 host_addr='10.10.10.10'>
 <txt>23-JUN-2012 19:51:43 * service_update * xe * 0
 </txt>
</msg>
<msg time='2012-06-23T19:55:15.365+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
 host_addr='10.10.10.10'>
 <txt>23-JUN-2012 19:55:15 * service_update * orcl * 0
 </txt>
</msg>
<msg time='2012-06-23T19:57:03.365+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
 host_addr='10.10.10.10'>
 <txt>23-JUN-2012 19:57:03 * service_update * orcl * 0
 </txt>
</msg>
<msg time='2012-06-23T19:57:15.365+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
 host_addr='10.10.10.10'>
 <txt>23-JUN-2012 19:57:15 * service_update * orcl * 0
 </txt>
</msg>
<msg time='2012-06-23T20:00:43.677+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
 host_addr='10.10.10.10'>
 <txt>23-JUN-2012 20:00:43 * service_update * xe * 0
 </txt>
</msg>
<msg time='2012-06-23T20:01:43.677+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
 host_addr='10.10.10.10'>
 <txt>23-JUN-2012 20:01:43 * service_update * xe * 0
 </txt>
</msg>
<msg time='2012-06-23T20:04:36.365+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
 host_addr='10.10.10.10'>
 <txt>23-JUN-2012 20:04:36 * service_update * orcl * 0
 </txt>
</msg>


c:\>set
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\A4\AppData\Roaming
asl.log=Destination=file
CLASSPATH=.;C:\Program Files\Java\jre6\lib\ext\QTJava.zip
CommonProgramFiles=C:\Program Files\Common Files
COMPUTERNAME=VP001E8C2AF418
ComSpec=C:\Windows\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\A4
LD_LIBRARY_PATH=C:\bimwh\Oracle_FRHome1\lib
LOCALAPPDATA=C:\Users\A4\AppData\Local
LOGONSERVER=\\VP001E8C2AF418
NLS_LANG=ENGLISH_UNITED KINGDOM.WE8MSWIN1252
NUMBER_OF_PROCESSORS=4
OnlineServices=Online Services
OS=Windows_NT
Path=C:\oraclexe\app\oracle\product\11.2.0\server\bin;;C:\bimwh\Oracle_FRHome1\b
in;C:\Program Files\PHP\;E:\app\Tams\product\11.1.0\db_1\bin;C:\Windows\system3
2;C:\Windows;C:\Windows\System32\Wbem;C:\hp\bin\Python;c:\Program Files\Common F
iles\Roxio Shared\DLLShared\;c:\Program Files\Common Files\Roxio Shared\9.0\DLLS
hared\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Mi
crosoft SQL Server\100\DTS\Binn\;C:\Program Files\MySQL\MySQL Server 5.1\bin;C:\
Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Common Files\GTK\2.0\b
in;C:\Program Files\QuickTime\QTSystem\;C:\bimwh\Oracle_BI1\products\Essbase\Ess
baseServer\bin;C:\bimwh\Oracle_BI1\bin;C:\bimwh\Oracle_BI1\opmn\bin;C:\bimwh\Ora
cle_BI1\opmn\lib;C:\bimwh\Oracle_BI1\perl\bin;C:\bimwh\Oracle_FRHome1\opmn\bin;C
:\bimwh\Oracle_FRHome1\opmn\lib;C:\bimwh\Oracle_FRHome1\perl\bin
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PCBRAND=Pavilion
PHPRC=C:\Program Files\PHP\
PLATFORM=HPD
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 6 Model 15 Stepping 11, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=0f0b
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
QTJAVA=C:\Program Files\Java\jre6\lib\ext\QTJava.zip
RoxioCentral=c:\Program Files\Common Files\Roxio Shared\9.0\Roxio Central33\
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\A4\AppData\Local\Temp
TMP=C:\Users\A4\AppData\Local\Temp
TNS_ADMIN=C:\TNS_ADMIN
USERDOMAIN=VP001E8C2AF418
USERNAME=A4
USERPROFILE=C:\Users\A4
VS100COMNTOOLS=C:\Program Files\Microsoft Visual Studio 10.0\Common7\Tools\
windir=C:\Windows

c:\>echo %ORACLE_HOME%
%ORACLE_HOME%


Cheers
Tams
Re: Oracle connection problem [message #558553 is a reply to message #558552] Sat, 23 June 2012 14:31 Go to previous messageGo to next message
BlackSwan
Messages: 22517
Registered: January 2009
Senior Member
First & foremost I want to commend you on your 1st post which was well formatted & very thorough.
I also what to thank you for actually following my directions (many don't).

ORACLE_HOME should be contain a value, but yours seems to be missing.
when was last time SQL*Net worked with out error (or hang)?
what changed since then.

>COMPUTERNAME=VP001E8C2AF418
what is explanation for difference between above COMPUTERNAME & (HOST=Tams-PC)?

explain/describe any & all OS virtualization involving your environment.

BTW - the lack of log records indicates that connection request did not get to the listener.
You may need to revert to packet sniffing using Ethereal, WireShark or similar utility.
much seems to be OK, but obviously something prevents SQL*Net from working.
Re: Oracle connection problem [message #558554 is a reply to message #558553] Sat, 23 June 2012 14:54 Go to previous messageGo to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
NetBIOS computer name = VP001E8C2AF418
Control Panel > System
Computer name = Tams-PC
Full computer name = Tams-PC

c:\>echo %ORACLE_HOME%
C:\oraclexe\app\oracle\product\11.2.0\server

I think sql*net may have stopped working a couple of weeks ago after I attempted to install Business Intelligence which required Repository Creation Utility (RCU).
I had to carry out a procedure that I believe was to install loopback adapter. That's why I have set the IP address to 10.10.10.10

To my knowledge I have no OS virtualisation.

Tams
Re: Oracle connection problem [message #558555 is a reply to message #558554] Sat, 23 June 2012 15:05 Go to previous messageGo to next message
BlackSwan
Messages: 22517
Registered: January 2009
Senior Member
Oracle XE does not come with dbca & expects to be the only Oracle DB on the system, AFAIK.
I would not be surprised to find that two (or more) different "ORACLE_HOME" directory trees exist on this system
how did it come to pass that both XE & orcl reside on this system?

>c:\>echo %ORACLE_HOME%
>%ORACLE_HOME%
above before, below after?
>c:\>echo %ORACLE_HOME%
>C:\oraclexe\app\oracle\product\11.2.0\server
WHAT Changed between when the two ECHO commands were issued?

I am at a loss to be able to solve your mystery.
Re: Oracle connection problem [message #558556 is a reply to message #558555] Sat, 23 June 2012 15:10 Go to previous messageGo to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
I installed XE yesterday to see if it might solve the problem.
I set ORACLE_HOME because I thought you meant for me to do that.
It's certainly a mystery!
Re: Oracle connection problem [message #558557 is a reply to message #558556] Sat, 23 June 2012 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 22517
Registered: January 2009
Senior Member
if you run OS File Find looking for SQLPLUS.EXE what does it report?
Re: Oracle connection problem [message #558558 is a reply to message #558557] Sat, 23 June 2012 15:32 Go to previous messageGo to next message
BlackSwan
Messages: 22517
Registered: January 2009
Senior Member
>#C:\TNS_ADMIN\sqlnet.ora
>NAMES.DIRECTORY_PATH=(TNSNAMES)
change sqlnet.ora to contain like below
NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)

then connect like below (using valid username/password)
sqlplus scott/tiger@'TAM-PC:1521/orcl'
Re: Oracle connection problem [message #558559 is a reply to message #558558] Sat, 23 June 2012 15:38 Go to previous messageGo to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
Is this what you meant?

c:\>where sqlplus.exe
C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe
C:\bimwh\Oracle_FRHome1\BIN\sqlplus.exe
E:\app\Tams\product\11.1.0\db_1\BIN\sqlplus.exe

Tried your latest suggestion and still hangs.
Re: Oracle connection problem [message #558600 is a reply to message #558559] Sun, 24 June 2012 15:40 Go to previous messageGo to next message
BlackSwan
Messages: 22517
Registered: January 2009
Senior Member
what happens when you do as below but use your own PC's name

bcm@bcm-laptop:~$ telnet localhost 1521
Trying ::1...
Connected to localhost.
Escape character is '^]'.


Re: Oracle connection problem [message #558601 is a reply to message #558600] Sun, 24 June 2012 16:12 Go to previous messageGo to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
C:\>telnet Tams-PC 1521

if that is the equivalent to your command, then it resulted in a black command window with the cursor flashing in the top left hand corner.

The same happened with
C:\>telnet localhost 1521

[Updated on: Sun, 24 June 2012 16:13]

Report message to a moderator

Re: Oracle connection problem [message #558602 is a reply to message #558601] Sun, 24 June 2012 16:30 Go to previous messageGo to next message
BlackSwan
Messages: 22517
Registered: January 2009
Senior Member
telnet for connected to listener & then timed out
<msg time='2012-06-24T13:57:54.881-07:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='bcm-laptop'
 host_addr='127.0.0.1'>
 <txt>24-JUN-2012 13:57:54 * &lt;unknown connect data&gt; * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=46733)) * establish * &lt;unknown sid&gt; * 12525
 </txt>
</msg>
<msg time='2012-06-24T13:57:54.881-07:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='bcm-laptop'
 host_addr='127.0.0.1'>
 <txt>TNS-12525: TNS:listener has not received client&apos;s request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred
 </txt>
</msg>


since you don't see anything similar I concluded your system suffers from OS/Networking mis-configuration
Do you have any Firewall running?
Re: Oracle connection problem [message #558603 is a reply to message #558601] Sun, 24 June 2012 16:31 Go to previous messageGo to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
C:\Users\A4>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 24-JUN-2012 22:28
:08

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "XEXDB" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: VP001E8C2AF418, pid: 7208>
(ADDRESS=(PROTOCOL=tcp)(HOST=Tams-PC)(PORT=50710))
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: VP001E8C2AF418, pid: 4724>
(ADDRESS=(PROTOCOL=tcp)(HOST=Tams-PC)(PORT=49265))
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Re: Oracle connection problem [message #558604 is a reply to message #558603] Sun, 24 June 2012 16:51 Go to previous messageGo to next message
BlackSwan
Messages: 22517
Registered: January 2009
Senior Member
yes, listener appears to have normal status.

But if no client can actually connect to the listener, it is rather meaningless.
Again, telnet is OS utility which failed to connect to the listener.
This leads me to conclude the problem is at OS level.
Re: Oracle connection problem [message #558617 is a reply to message #558604] Mon, 25 June 2012 02:48 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
OK, I'll have a go.
I think the problem may not be OS level, but to do with having multiple Oracle Homes but no listener.ora file. Your telnet test and the tnsping tests showed that you can contact the listener on port 1521, and the listener log shows that the services are registering. But if you are starting the listener with no listener.ora file, the listener will not know the location of the server process executable to launch for any particular instance: it will attempt to launch it from its own home, which will be the wrong one for at least one of the databases.

Determine which Oracle Home the listener is running from, like this:
c:\users\john\home>lsnrctl show oracle_home

LSNRCTL for 32-bit Windows: Version 11.2.0.3.0 - Production on 25-JUN-2012 08:36:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
ORACLE_HOME="C:\app\john\product\11.2.0\dbhome_1"
The command completed successfully

c:\users\john\home>

then go to that directory\network\admin and create a listener.ora file, with these entries:

SID_LIST_listener=
(SID_LIST=
 (SID_DESC=
  (SID_NAME=xe)
  (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server)
 )
 (SID_DESC=
  (SID_NAME=orcl)
  (ORACLE_HOME=E:\app\Tams\product\11.1.0\db_1)
 )
)


Then make sure you are connected to the Oracle Home from which the database listener is running, and restart:
lsnrctl stop
lsnrctl start

wait a couiple of minutes for the databzse intances to register with the listener, and try again.




Re: Oracle connection problem [message #558625 is a reply to message #558617] Mon, 25 June 2012 04:28 Go to previous messageGo to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
Thanks John. I've carried out your instructions, but sqlplus still hangs.
Re: Oracle connection problem [message #558626 is a reply to message #558625] Mon, 25 June 2012 04:34 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
In that case, you probably edited the wrong listener.ora file or stopped/started the wrong listener. How are you setting your Oracle Home and PATH variables? What is the contents of the appropriate listener.ora file? Does tnsping still work? What is in the listener log?
Re: Oracle connection problem [message #558638 is a reply to message #558626] Mon, 25 June 2012 05:16 Go to previous messageGo to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
I'm sure it's the correct listener.ora, John. But to make 200% certain, I copied it to the orcl oracle_home\network\admin too.

C:\>echo %ORACLE_HOME%
C:\oraclexe\app\oracle\product\11.2.0\server

I set ORACLE_HOME with the SET command.

The XE database also has this ORACLE_HOME set in the registry.

PATH is set in the environment variables.
C:\>echo %PATH%

C:\oraclexe\app\oracle\product\11.2.0\server\bin;;C:\bimwh\Oracle_FRHome1\bin;C:\Program Files\PHP\;E:\app\Tams\product\11.1.0\db_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\hp\bin\Python;c:\Prog ram Files\Common Files\Roxio Shared\DLLShared\;c:\Program Files\Common Files\Roxio Shared\9.0\DLLShared\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\MySQL\MySQL Server 5.1\bin;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Common Files\GTK\2.0\bin;C:\Program Files\QuickTime\QTSystem\;C:\bimwh\Oracle_BI1\products\Essbase\EssbaseServer\bin;C:\bimwh\Oracle_BI1\bin;C:\bimwh\Oracle_BI1\opmn\bin ;C:\bimwh\Oracle_BI1\opmn\lib;C:\bimwh\Oracle_BI1\perl\bin;C:\bimwh\Oracle_FRHome1\opmn\bin;C:\bimwh\Oracle_FRHome1\opmn\lib;C:\bimwh \Oracle_FRHome1\perl\bin

C:\Windows\system32>tnsping tams

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 25-JUN-2
012 10:54:48

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

Used parameter files:
C:\TNS_ADMIN\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Tams-PC
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)

I'm using the listener.ora that you gave me.

#C:\oraclexe\app\oracle\diag\tnslsnr\Tams-PC\listener\alert\log.xml
<msg time='2012-06-25T11:01:51.785+01:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
host_addr='192.168.100.100'>
<txt>25-JUN-2012 11:01:51 * service_register * xe * 0
</txt>
</msg>
<msg time='2012-06-25T11:01:52.144+01:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
host_addr='192.168.100.100'>
<txt>25-JUN-2012 11:01:52 * service_register * orcl * 0
</txt>
</msg>
<msg time='2012-06-25T11:02:22.252+01:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
host_addr='192.168.100.100'>
<txt>25-JUN-2012 11:02:22 * service_update * orcl * 0
</txt>
</msg>
<msg time='2012-06-25T11:03:20.288+01:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
host_addr='192.168.100.100'>
<txt>25-JUN-2012 11:03:20 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=A4))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0
</txt>
</msg>
<msg time='2012-06-25T11:03:22.097+01:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='VP001E8C2AF418'
host_addr='192.168.100.100'>
<txt>25-JUN-2012 11:03:22 * service_update * xe * 0
</txt>
</msg>

[Updated on: Mon, 25 June 2012 05:20]

Report message to a moderator

Re: Oracle connection problem [message #558641 is a reply to message #558638] Mon, 25 June 2012 05:25 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
You have three Oracle Homes, not two. You seem to be ignoring this one,
C:\bimwh\Oracle_FRHome1
Do you know from which home the listener is actually running? How are you starting and stopping it?
Re: Oracle connection problem [message #558643 is a reply to message #558641] Mon, 25 June 2012 05:39 Go to previous messageGo to next message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
I have put your listener.ora into C:\bimwh\Oracle_FRHome1\network\admin as well.

It has to be the XE home that the listener is running from, hasn't it? How could I show you evidence of that? It's definitely the XE listener that's running in services.
I'm stopping and starting the listener by the method you described earlier.

C:\>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 25-JUN-2012 11:28
:59

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

C:\>lsnrctl start

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 25-JUN-2012 11:29
:14

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

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
System parameter file is C:\oraclexe\app\oracle\product\11.2.0\server\network\ad
min\listener.ora
Log messages written to C:\oraclexe\app\oracle\diag\tnslsnr\Tams-PC\listener\al
ert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Tams-PC)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date 25-JUN-2012 11:29:18
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\a
dmin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\Tams-PC\listener\
alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Tams-PC)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
Instance "xe", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Re: Oracle connection problem [message #558645 is a reply to message #558643] Mon, 25 June 2012 05:48 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
You do appear to be using a listener running off the XE home: it is using a listener.ora file in that home, as is shown by the status, and you do have both database instances statically registered. You say that listener.ora does include a hardcoded path to the correct Oracle Home for each instance. Your TNS name resolution does go to that listener, and is requests a registered service.
Well, that is as far as I can go. Perhaps someone else has an idea.
Re: Oracle connection problem [message #558646 is a reply to message #558645] Mon, 25 June 2012 05:53 Go to previous message
Tams
Messages: 11
Registered: June 2012
Location: Scotland
Junior Member
Thanks John and and Blackswan for your help. If anything comes to you in a flash later on, then please let me know. I'm not quite at the point of throwing my machine out the window, but I'm getting closer...
Previous Topic: Oracle 10g database creation problem
Next Topic: Mismatched Oracle drivers - need upgrade (10.2.0.1 to 10.2.0.5)
Goto Forum:
  


Current Time: Mon Jul 28 20:32:32 CDT 2014

Total time taken to generate the page: 0.05812 seconds