Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-07-10
10
Medium Priority
?
5,408 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 14

Accepted Solution

by:
sathyagiri earned 1000 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

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.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 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