?
Solved

SQLplus connection check

Posted on 2007-10-11
15
Medium Priority
?
5,270 Views
Last Modified: 2013-12-19
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
Comment
Question by:dkim18
  • 6
  • 5
11 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1200 total points
ID: 20061000
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
 

Author Comment

by:dkim18
ID: 20061167
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 20061388
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:dkim18
ID: 20061592
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 20061710
do you have access to the listener.ora on the remote system?
0
 

Author Comment

by:dkim18
ID: 20061746
*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
 

Author Comment

by:dkim18
ID: 20061758
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 20061760
Is that your listener.ora for your XE or the remote listener.ora?
0
 

Author Comment

by:dkim18
ID: 20061814
the above listener.ora  is for remote listener.ora
0
 

Author Comment

by:dkim18
ID: 20061840
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 21584359
do you need further assistance?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question