Unable to connect to Oracle 10 database via SQL*Plus Worksheet

I'm unable to connect to Oracle 10g database on a remote UNIX server via SQL*Plus Worksheet. I receive "ORA-12154 TNS:could not resolve connect descriptor specified." I can connect via OEM (9i), SQL*Plus, and Toad.
rostaraAsked:
Who is Participating?
 
schwertnerCommented:
There are complains that 10.1.0.x Oracle software works bad.
If this is the case try to use the latest version of 10g: 10.2.0.3.
0
 
schwertnerCommented:
For STATIC registration of the Oracle service to the Listener:

Go to the remote 10g installation.
In ...\network\admin open listener.ora

see

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Ora10g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
  )

Add the entry
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora10g)
    )
with regard to the particular Oracle Home and SID Name.

Restart the listener:
c:>lsnrctl stop
c:>lsnctl start
0
 
schwertnerCommented:
But if the command

% lsnrctl services

shows that the service is registered by the Listener,
that check what TNSNAMES.ORA (possibly there are more then one on the client machine)
the SQL*Plus uses and investigate that there is an entry with correct parameters.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark GeerlingsDatabase AdministratorCommented:
If you had Oracle9 client software installed (and working) but have recently installed Oracle10 client sofware, you need to configure SQL*Net in the Oracle10 Oracle_home for your database before you can use it.  Either use the GUI wizard, or save a copy of the two files: sqlnet.ora and tnsnames.ora from your Oracle10\Network\Admin directory (just in case this next step doesn't fix the problem) then copy the two files with this name from your Oracle9\Network\Admin directory to the Oracle10\Network\Admin directory.  That should get your Oracle10 Oracle_home working just your Oracle9 Oracle_home.
0
 
rostaraAuthor Commented:
tnsnames.ora file and listener.ora file are configured as suggested, but the problem still exists. This tnsnames.ora file is successfully used for several other remote databases. I have seen this problem with SQL*Plus Worksheet before, but don't remember the "fix".
0
 
Richard OlutolaConsultantCommented:
Are you trying to connect as SYS or a non-sysdba user?
0
 
rostaraAuthor Commented:
It occurs when I try to connect as either type of user.
0
 
Richard OlutolaConsultantCommented:
If you have multiple homes, make sure the entries in all tnsnames are the same. Perhaps you may do a search for tnsnames.ora to identify all copies on your machine.

0
 
Richard OlutolaConsultantCommented:
You could also try creating a TNS_ADMIN property in your registry if none exists. Or ensure it is correct if one already exists.
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.

All Courses

From novice to tech pro — start learning today.