Solved

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

Posted on 2006-07-10
10
5,399 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 47

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
 
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
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 47

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 47

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

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.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now