| Oracle connection problem [message #558548] |
Sat, 23 June 2012 13:36  |
 |
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  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
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 #558603 is a reply to message #558601] |
Sun, 24 June 2012 16:31 |
|
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 #558617 is a reply to message #558604] |
Mon, 25 June 2012 02:48 |
John Watson
Messages: 8965
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 #558626 is a reply to message #558625] |
Mon, 25 June 2012 04:34 |
John Watson
Messages: 8965
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 |
|
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 |
John Watson
Messages: 8965
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 |
|
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 |
John Watson
Messages: 8965
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 |
|
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...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
