Solved

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

Posted on 2006-07-10
10
5,403 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

809 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