Home » RDBMS Server » Networking and Gateways » Cannot connect to DB (Oracle Express 11g)
Cannot connect to DB [message #529724] Wed, 02 November 2011 12:36 Go to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
Hi everyone, yesterday I installed Oracle Express on my Windows 2003 Enterprise server. And right off the bat I cannot connect to it via command line. I have googled and read many sites, but am still getting the same error, no matter what I do. I am newbie again (was a DBA in 1990...when I used to have the same error and fix it in seconds...not sure why this problem still lingers in Oracle) so most likely I am overlooking something. Below are my tnsnames.ora and liserner.ora files. Can you please help me solve this? Thanks in advance!!

tnsnames.ora
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myservername.domainname.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 

ORCL.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myservername.mydomain.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL.WORLD)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myservername.mydomain.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )



listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = myservername.mydomain.com)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (ORCL)


sqlnet.ora
# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)
Re: Cannot connect to DB [message #529726 is a reply to message #529724] Wed, 02 November 2011 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>but am still getting the same error,
ERROR? What Error?

I don't see any error.

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.
Re: Cannot connect to DB [message #529731 is a reply to message #529726] Wed, 02 November 2011 13:06 Go to previous messageGo to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
DUH! Sorry about that. I'm trying to connect from the command line and this is what I get:

SQL> connect system/mypassword as sysdba
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Let me know what else I have left out...still waking up here. Smile
Re: Cannot connect to DB [message #529732 is a reply to message #529731] Wed, 02 November 2011 13:11 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>ORA-12154: TNS:could not resolve the connect identifier specified

should NOT be throw TNS when no "@REMOTE" exists.

this error frequently results on Windows when ORACLE_SID is not set or set to incorrect value
do as below from OS command line

SET ORACLE_SID=XE
sqlplus scott/tiger

COPY commands (above) & results then PASTE all back here
Re: Cannot connect to DB [message #529735 is a reply to message #529732] Wed, 02 November 2011 13:30 Go to previous messageGo to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
Thanks for your help.

Here's the output:

C:\Documents and Settings\skhorram>SET ORACLE_SID=XE

C:\Documents and Settings\skhorram>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 2 18:23:53 2011

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

ERROR:
ORA-01017: invalid username/password; logon denied


I tried the "connect system/password as sysdba" as well, and it still gives the same result.
Re: Cannot connect to DB [message #529736 is a reply to message #529735] Wed, 02 November 2011 13:35 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
there is difference between "SYS" and "SYSTEM".
They are different users/schemas

do as below & type on 2 separate lines exactly as below starting from open Command Window

sqlplus
/ as sysdba

COPY commands & results then PASTE all back here



Re: Cannot connect to DB [message #529737 is a reply to message #529736] Wed, 02 November 2011 13:38 Go to previous messageGo to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
I'm in! Thank you!!!

One quick question, what is the command line window that opens up when I go to Start > Programs > Oracle > Run SQL Command Line?
Re: Cannot connect to DB [message #529739 is a reply to message #529737] Wed, 02 November 2011 13:40 Go to previous messageGo to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
More qustions, what am I logged in as? I need to create a tablespace, so I suppose I need either SYS or SYSTEM permissions to do that? If I try to log in to sqlplus as SYS or SYSTEM, both attempts give me the ORA-12154 error again.
Re: Cannot connect to DB [message #529740 is a reply to message #529739] Wed, 02 November 2011 13:42 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>More qustions, what am I logged in as?

SQL> SHOW USER
Re: Cannot connect to DB [message #529741 is a reply to message #529740] Wed, 02 November 2011 13:48 Go to previous messageGo to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
USER is "SYS"

Smile))) Perfect! Thanks again!
Re: Cannot connect to DB [message #529742 is a reply to message #529739] Wed, 02 November 2011 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I need to create a tablespace, so I suppose I need either SYS or SYSTEM permissions to do that?


SYSTEM not SYS.
And with SYSTEM create immediately a new DBA account and no more use SYSTEM.

Quote:
both attempts give me the ORA-12154 error again


Post the result of SET at DOS prompt.

Regards
Michel
Re: Cannot connect to DB [message #529743 is a reply to message #529742] Wed, 02 November 2011 13:52 Go to previous messageGo to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
Oh, so I need SYSTEM? OK, here is what I have at the DOS prompt:

C:\Documents and Settings\skhorram>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 2 18:51:17 2011

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

Enter user-name: SYSTEM
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:
Re: Cannot connect to DB [message #529753 is a reply to message #529743] Wed, 02 November 2011 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Post the result of SET at DOS prompt.


What don't you understand in this sentence?

Regards
Michel
Re: Cannot connect to DB [message #529756 is a reply to message #529753] Wed, 02 November 2011 14:28 Go to previous messageGo to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
C:\Documents and Settings\skhorram>SET
ALLUSERSPROFILE=C:\Documents and Settings\All Users
APPDATA=C:\Documents and Settings\skhorram\Application Data
ClusterLog=C:\WINDOWS\Cluster\cluster.log
CommonProgramFiles=C:\Program Files\Common Files
COMPUTERNAME=DPSONYEMAIL
ComSpec=C:\WINDOWS\system32\cmd.exe
DEBIX_CONFIG=F:\sonyM3workspace\Debix\Program Files
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Documents and Settings\skhorram
JAVA_HOME=C:\Program Files\Java\jre6
lib=C:\Program Files\SQLXML 4.0\bin\
LOGONSERVER=\\ZEUS
NUMBER_OF_PROCESSORS=8
ORACLE_SID=XE
OS=Windows_NT
Path=C:\oraclexe\app\oracle\product\11.2.0\server\bin;;C:\Program Files\HP\NCU
:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Micros
t SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\
:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft
QL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual
tudio 8\Common7\IDE\PrivateAssemblies\;C:\WINDOWS\system32\WindowsPowerShell\v
0;C:\WINDOWS\system32\WindowsPowerShell\v1.0;C:\Program Files\HP\P4000\HP P400
CLI
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.PSC1;.PSC1
PEGASUS_HOME=C:\Program Files\HP\P4000\HP P4000 VSS Provider\CIM
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 6 Model 15 Stepping 11, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=0f0b
ProgramFiles=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\
SESSIONNAME=RDP-Tcp#6
SystemDrive=C:
SystemRoot=C:\WINDOWS
TEMP=C:\DOCUME~1\skhorram\LOCALS~1\Temp
TMP=C:\DOCUME~1\skhorram\LOCALS~1\Temp
USERDNSDOMAIN=DIRECTPARTNERS.COM
USERDOMAIN=DIRECTPARTNERS
USERNAME=skhorram
USERPROFILE=C:\Documents and Settings\skhorram
VBOX_INSTALL_PATH=C:\Program Files\Oracle\VirtualBox\
windir=C:\WINDOWS

C:\Documents and Settings\skhorram>



[EDITED by LF: fixed [code] tags]

[Updated on: Thu, 03 November 2011 02:19] by Moderator

Report message to a moderator

Re: Cannot connect to DB [message #529757 is a reply to message #529756] Wed, 02 November 2011 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>VBOX_INSTALL_PATH=C:\Program Files\Oracle\VirtualBox\

Is Oracle installed in/on a "virtual" system?
Re: Cannot connect to DB [message #529758 is a reply to message #529757] Wed, 02 November 2011 14:52 Go to previous messageGo to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
No, not at all.
Re: Cannot connect to DB [message #529760 is a reply to message #529758] Wed, 02 November 2011 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
open Command Window & issue following OS commands

lsnrctl status
lsnrctl service

COPY commands & results then PASTE all back here
Re: Cannot connect to DB [message #529761 is a reply to message #529758] Wed, 02 November 2011 14:55 Go to previous messageGo to next message
shahgols
Messages: 11
Registered: November 2011
Junior Member
We do have virtual box software installed on this server, it is not being used right now, and I am not sure to what capacity it was used for before.
Re: Cannot connect to DB [message #529762 is a reply to message #529756] Wed, 02 November 2011 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you have this message with these settings?
I don't think this is possible.

Post the content of HKLM\Software\Oracle registry key (Execute regedit, go to this key, right-click on it and choose export and post the result).

Regards
Michel

Re: Cannot connect to DB [message #529763 is a reply to message #529761] Wed, 02 November 2011 14:56 Go to previous message
shahgols
Messages: 11
Registered: November 2011
Junior Member
Sure, here it is:

C:\Documents and Settings\skhorram>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 02-NOV-2011 19:55
:37

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date                31-OCT-2011 23:48:09
Uptime                    1 days 20 hr. 7 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           ORCL
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\dpsonyemail\listen
er\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dpsonyemail.directpartners.com)(PORT
=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dpsonyemail.directpartners.com)(PORT
=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", 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

C:\Documents and Settings\skhorram>lsnrctl service

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 02-NOV-2011 19:55
:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
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 "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:6 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: DPSONYEMAIL, pid: 6916>
         (ADDRESS=(PROTOCOL=tcp)(HOST=dpsonyemail.directpartners.com)(PORT=3144)
)
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:4 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

C:\Documents and Settings\skhorram>
Previous Topic: listener.ora is not found on oracle on windows
Next Topic: ORA-01031: insufficient privileges
Goto Forum:
  


Current Time: Fri Aug 29 17:17:15 CDT 2014

Total time taken to generate the page: 0.13793 seconds