| Unable to Connect to Local oracle DB (2 Merged) [message #566858] |
Thu, 20 September 2012 06:50  |
 |
jb12
Messages: 17
Registered: September 2012
|
Junior Member |
|
|
Hello,
Facing issue in connecting to local oracle db.
Able to logon via cmd prompt "sqlplus owner/owner" but not using "sqlplus owner/owner@orcl".
It goes in hang state.Tried through SQL Developer,same issue..Testing Connection keeps on moving...
Request if anyone can suggest way forward
Configuration details below:
Tnsnames.ora
========
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.corp.comp.com)
)
)
Listener.ora
============
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = F:\app\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:F:\app\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = F:\app\oracle
sqlnet.ora
==========
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tnsping orcl
============
Used parameter files:
F:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.corp.
comp.com)))
OK (0 msec)
lsnrctl status
==============
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 20-SEP-2012 19:41
:17
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 20-SEP-2012 19:40:12
Uptime 0 days 0 hr. 1 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\oracle\product\11.2.0\dbhome_1\network\admin\li
stener.ora
Listener Log File f:\app\oracle\diag\tnslsnr\LT-1\listener\alert\log
.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl.corp.comp.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.corp.comp.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
lsnrctl services
================
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 20-SEP-2012 19:42
:01
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl.corp.comp.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:11 refused:0 state:ready
LOCAL SERVER
Service "orclXDB.corp.comp.com" 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: LT-1, pid: 844>
(ADDRESS=(PROTOCOL=tcp)(HOST=LT-1.corp.comp.com)(PORT=56323))
The command completed successfully
pfile:
=======
*.db_domain='corp.comp.com'
*.db_name='orcl'
Computer Name : LT-1
Full computer name : LT-1.corp.comp.com
Domain : corp.comp.com
Thanks in advance.
[Updated on: Thu, 20 September 2012 06:54] Report message to a moderator |
|
|
|
| Unable to Connect to Local oracle DB [message #566859 is a reply to message #566858] |
Thu, 20 September 2012 06:52  |
|
jb12
Messages: 17
Registered: September 2012
|
Junior Member |
|
|
Hello,
Facing issue in connecting to local oracle db.
Able to logon via cmd prompt "sqlplus owner/owner" but not using "sqlplus owner/owner@orcl".
It goes in hang state.Tried through SQL Developer,same issue..Testing Connection keeps on moving...
Request if anyone can suggest way forward
Configuration details below:
Tnsnames.ora
========
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.corp.comp.com)
)
)
Listener.ora
============
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = F:\app\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:F:\app\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
ADR_BASE_LISTENER = F:\app\oracle
sqlnet.ora
==========
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tnsping orcl
============
Used parameter files:
F:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.corp.
comp.com)))
OK (0 msec)
lsnrctl status
==============
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 20-SEP-2012 19:41
:17
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 20-SEP-2012 19:40:12
Uptime 0 days 0 hr. 1 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\oracle\product\11.2.0\dbhome_1\network\admin\li
stener.ora
Listener Log File f:\app\oracle\diag\tnslsnr\LT-1\listener\alert\log
.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl.corp.comp.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.corp.comp.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
lsnrctl services
================
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 20-SEP-2012 19:42
:01
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl.corp.comp.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:11 refused:0 state:ready
LOCAL SERVER
Service "orclXDB.corp.comp.com" 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: LT-1, pid: 844>
(ADDRESS=(PROTOCOL=tcp)(HOST=LT-1.corp.comp.com)(PORT=56323))
The command completed successfully
pfile:
=======
*.db_domain='corp.comp.com'
*.db_name='orcl'
Computer Name : LT-1
Full computer name : LT-1.corp.comp.com
Domain : corp.comp.com
Thanks in advance.
|
|
|
|
|
|
| Re: Unable to Connect to Local oracle DB [message #566865 is a reply to message #566864] |
Thu, 20 September 2012 09:03 |
|
jb12
Messages: 17
Registered: September 2012
|
Junior Member |
|
|
Hosts
=====
# Copyright (c) 1993-2009 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
# 102.54.94.97 rhino.acme.com # source server
# 38.25.63.10 x.acme.com # x client host
# localhost name resolution is handled within DNS itself.
# 127.0.0.1 localhost
# ::1 localhost
C:\Users\Administrator>ping LT-1
Pinging LT-1.corp.comp.com [fe80::e4b4:6ee4:9f0e:b142%13] with 32 bytes
of data:
Reply from fe80::e4b4:6ee4:9f0e:b142%13: time<1ms
Reply from fe80::e4b4:6ee4:9f0e:b142%13: time<1ms
Reply from fe80::e4b4:6ee4:9f0e:b142%13: time<1ms
Reply from fe80::e4b4:6ee4:9f0e:b142%13: time<1ms
Ping statistics for fe80::e4b4:6ee4:9f0e:b142%13:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
C:\Users\Administrator>ping LT-1.corp.comp.com
Pinging LT-1.corp.comp.com [fe80::e4b4:6ee4:9f0e:b142%13] with 32 bytes
of data:
Reply from fe80::e4b4:6ee4:9f0e:b142%13: time<1ms
Reply from fe80::e4b4:6ee4:9f0e:b142%13: time<1ms
Reply from fe80::e4b4:6ee4:9f0e:b142%13: time<1ms
Reply from fe80::e4b4:6ee4:9f0e:b142%13: time<1ms
Ping statistics for fe80::e4b4:6ee4:9f0e:b142%13:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
C:\Users\Administrator>ping 192.168.0.58
Pinging 192.168.0.58 with 32 bytes of data:
Reply from 192.168.0.58: bytes=32 time<1ms TTL=128
Reply from 192.168.0.58: bytes=32 time<1ms TTL=128
Reply from 192.168.0.58: bytes=32 time<1ms TTL=128
Reply from 192.168.0.58: bytes=32 time<1ms TTL=128
Ping statistics for 192.168.0.58:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
Thanks for the prompt reply.Would like to add,the db was working properly 2 days back
until i restarted the system.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Unable to Connect to Local oracle DB [message #566914 is a reply to message #566913] |
Thu, 20 September 2012 20:58 |
|
jb12
Messages: 17
Registered: September 2012
|
Junior Member |
|
|
C:\Users\Administrator>lsnrctl stop
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 21-SEP-2012 09:4
:53
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
C:\Users\Administrator>lsnrctl start
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 21-SEP-2012 09:5
:00
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Log messages written to f:\app\oracle\diag\tnslsnr\LT-1\listener\alert\log.
ml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT-1.corp.comp.
om)(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.1.0 - Prod
ction
Start Date 21-SEP-2012 09:50:04
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File f:\app\oracle\diag\tnslsnr\LT-1\listener\alert\lo
.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT-1.corp.comp.com)(PORT=15
1)))
The listener supports no services
The command completed successfully
C:\Users\Administrator>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 21-SEP-2012 09:5
:32
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.1.0 - Prod
ction
Start Date 21-SEP-2012 09:50:04
Uptime 0 days 0 hr. 3 min. 30 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File f:\app\oracle\diag\tnslsnr\LT-1\listener\alert\lo
.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LT-1.corp.comp.com)(PORT=15
1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "orcl.corp.comp.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.corp.comp.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
C:\Users\Administrator>lsnrctl services
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 21-SEP-2012 09:5
:37
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "orcl.corp.comp.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:31 refused:0 state:ready
LOCAL SERVER
Service "orclXDB.corp.comp.com" 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: LT-1, pid: 3256>
(ADDRESS=(PROTOCOL=tcp)(HOST=LT-1.corp.comp.com)(PORT=53219))
The command completed successfully
C:\Users\Administrator>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 21 09:54:01 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
No response after this...
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Unable to Connect to Local oracle DB [message #566972 is a reply to message #566967] |
Fri, 21 September 2012 13:09 |
|
jb12
Messages: 17
Registered: September 2012
|
Junior Member |
|
|
BlackSwan,
Thanks a ton,It's working now.
After going through the link, realised that I had done a cleanup of the system using some software.
Would have caused some problem and when I restarted the system ,if took effect.
I did a system restore and now able to connect to db using sql developer.
Once again ,thanks for your time and expertise.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
