?
Solved

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

Posted on 2007-10-01
9
Medium Priority
?
612 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:rostara
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 19990887
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
 
LVL 48

Expert Comment

by:schwertner
ID: 19990898
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19990907
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:rostara
ID: 19991034
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
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 19996486
Are you trying to connect as SYS or a non-sysdba user?
0
 

Author Comment

by:rostara
ID: 21120517
It occurs when I try to connect as either type of user.
0
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 21121376
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
 
LVL 48

Accepted Solution

by:
schwertner earned 375 total points
ID: 21123858
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
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 21123949
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

864 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