• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5283
  • Last Modified:

SQLplus connection check

Hi,

I am trying to check oracle connection through sqlplus and I used user name and pass word that I was given when I connect thru client program to remote oracle. However, when I perform the following command:

connect username/password@sid

I got this error

ORA-12154: TNS:could not resolve the connect identifier specified


I tried other database and I got this error:
ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Do I need other privileges and password?

thx
0
dkim18
Asked:
dkim18
  • 6
  • 5
1 Solution
 
sdstuberCommented:
It's not a username/password or privilege problem.

Those errors are telling you that you're not reaching the database at all.

Talk to your dba about your listener setup and make sure you have your oracle client setup properly with ldap or tnsnames or (onames for your 9i db)
0
 
dkim18Author Commented:
thanks for your comment. I have downloaded oracle instant client, but don't see tnsnames.ora file. Do I need a full oracle client?
0
 
sdstuberCommented:
no, go to your oracle home (where ever that is)  and then go to /network/admin subdirectory
you should have sqlnet.ora and depending on what's in there, a tnsnames.ora or ldap.ora, maybe both


0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
dkim18Author Commented:
I have installed oracle express 10g in my local mahcine and tnsnames.ora looks like this:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PRC-2-PROG2.kki.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

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

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
++++++++++++++++++++++++++++
I am trying to access remote oracle and I added this(*s_name -> sid, host_name -> actual working host name)

s_name =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = s_name)
   )
 )

but I got this error.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

the host name is the oracle server(listener.ora) is the same as "s_name" from the above.

the oracle server is up and it is also using default port 1521

0
 
sdstuberCommented:
do you have access to the listener.ora on the remote system?
0
 
dkim18Author Commented:
*host_name -> need to be substituted for real name

# LISTENER.ORA Network Configuration File: C:\OraHome1\network\admin\listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))
      )
    )
  )
...
...
0
 
dkim18Author Commented:
continued...
*s_name -> is not actual sid name
++++++++++++

SID_LIST_LISTENER =
  (SID_LIST =
    ...
...
    (SID_DESC =
      (GLOBAL_DBNAME = xxx.xxx.org)
      (ORACLE_HOME = C:\OraHome1)
      (SID_NAME = s_name)
    )
   ...
...
  )
 
 
0
 
sdstuberCommented:
Is that your listener.ora for your XE or the remote listener.ora?
0
 
dkim18Author Commented:
the above listener.ora  is for remote listener.ora
0
 
dkim18Author Commented:
BTW, I noticed that SID_NAME is as same as GLOBAL_DBNAME's first part of name(before fist dot - abc) For my guess, it might be host_name from the above.
++++++++++++++++

 (SID_DESC =
      (GLOBAL_DBNAME = abc.xxx.org)
      (ORACLE_HOME = C:\OraHome1)
      (SID_NAME = abc)
    )
0
 
sdstuberCommented:
do you need further assistance?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now