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

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
wscott30Asked:
Who is Participating?
 
sathyagiriConnect With a Mentor Commented:
Ok can you try changing your default_home to 10G and then check if toad and sqlldr works?
0
 
sathyagiriCommented:
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
 
schwertnerCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
wscott30Author Commented:
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
 
schwertnerCommented:
Because of the firewall and antiviruses.
0
 
MohanKNairCommented:
You use the 10g ORACLE_HOME to connect to 9i database also. Configure the tnsnames.ora and listener.ora in 10g.
0
 
schwertnerCommented:
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
 
Tareq_AsebieCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.