Solved

ORA-12154: TNS:could not resolve.....with a twist

Posted on 2006-07-10
10
5,405 Views
Last Modified: 2013-12-11
I have 2 Oracle databases running locally on my Windows XP machine, one 9i and one 10g. I just loaded the 10g and I get the error 'ORA-12154: TNS:could not resolve the connect identifier specified' when I try to connect to the 10g database with TOAD or sqlldr (from command line), but I can login SQLPLUS with no problem. Here is the tnsnames.ora in D:\oracle10g\product\10.2.0\db_1\NETWORK\ADMIN:

# tnsnames.ora Network Configuration File: D:\oracle10g\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = DLV27-SCOTTSA1.northgrum.com)(PORT = 1522))

ORCL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DLV27-SCOTTSA1.northgrum.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Any idea why SQLPLUS works fine, but TOAD and sqlldr do not?

Thanks
0
Comment
Question by:wscott30
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17077693
Check what's your ORACLE_HOME set to in your registry.
Also check your env variable path to see if oracle9i\bin comes before oracle10g\bin
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17079347
This is common problem. You have to check two things:
1. Turn off the firewall on XP and antiviruses.

2. Try to use static registration of the 10g instance:

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

Thats all.

0
 

Author Comment

by:wscott30
ID: 17082074
schwertner:

1.  Not sure if firewall and antivirus is relavant. Everything is on my local machine.
2.  I tried changing and restarting the listener.ora as you said, but that didn't work. Here is how the listener.ora looked after the change:

# listener.ora Network Configuration File: D:\oracle10g\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle10g\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = D:\oracle10g\product\10.2.0\db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = DLV27-SCOTTSA1.northgrum.com)(PORT = 1522))
    )
  )


sathyagiri:

1.  ORACLE/ALL_HOMES /DEFAULT_HOME is OraHome92 (which is the oracle home for my 9i database)
ORACLE/ALL_HOMES /HOME_COUNTER is 1.
So maybe OraDb10g_home1 isn't being recognized....not really sure how it works.

2.  Here is the contents of the system path variable, broken out for readability...
D:\OWB_Home\bin;
D:\oracle10g\product\10.2.0\db_1\bin;
c:\oracle\ora92\bin;
C:\Program Files\Oracle\jre\1.3.1\bin;
C:\Program Files\Oracle\jre\1.1.8\bin;
%SystemRoot%\system32;
%SystemRoot%;
%SystemRoot%\System32\Wbem;
C:\Program Files\Common Files\Roxio Shared\DLLShared;
C:\Program Files\Microsoft SQL Server\80\Tools\BINN

Remember, the problem is I can login to SQLPLUS, but not TOAD and sqlldr.

Thanks guys
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Accepted Solution

by:
sathyagiri earned 250 total points
ID: 17086517
Ok can you try changing your default_home to 10G and then check if toad and sqlldr works?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17088279
Because of the firewall and antiviruses.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17088728
You use the 10g ORACLE_HOME to connect to 9i database also. Configure the tnsnames.ora and listener.ora in 10g.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 17089534
wscott30:

The firewall can stop transactions via ports.

Also try to make the DEFAULT_HOME the home where the tnsnames.ora you use reside.
0
 
LVL 1

Expert Comment

by:Tareq_Asebie
ID: 17130192
The Frist Thing make sure the name of each datebase
and chosee which database you want to connect like this :
1- The name of database9i is  TEST_DB1
2- The name of database9i is  TEST_DB2
3- COMPUTER NAME = DLV27-SCOTTSA1.northgrum.com  make sure
4- make sure the LISTENER  is started
in the each tnsnames.ora in your computer write this :

conn1=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DLV27-SCOTTSA1.northgrum.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB1)
    )
  )
********************************
importanat thing :

1- HOST =
2- SERVICE_NAME =  OR   SID =  
   which means the name of database which is created and you want to connect .
 














0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

688 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