Home » RDBMS Server » Server Utilities » SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine (10.2g, SQL*Loader: Release 9.0.1.4.0, WinXPpro)
SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #388315] Tue, 24 February 2009 04:04 Go to next message
ollyb303
Messages: 3
Registered: January 2009
Junior Member
Hi,

I have a problem using SQL*LDR to load data into a 10.2g db. The log file reads:

SQL*Loader: Release 9.0.1.4.0 - Production on Tue Feb 24 09:44:32 2009

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve service name


Bizarrely, this was working just fine until this weekend at which point it stopped working.

I can connect to the db using SQL*PLUS and SQL developer, and also using a front end MS Access application - all of these use the same connection properties.

I have checked my tnsnames.ora file and the one on the server and they are fine, as is listener.ora and the listener is working. The listener log shows the connections from the access application, sql*plus etc, but there is no sign of any log entries for the attempts I have made using SQL*LDR.

Can anyone shed any light on this at all?

Many thanks,

Olly
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #388316 is a reply to message #388315] Tue, 24 February 2009 04:10 Go to previous messageGo to next message
babuknb
Messages: 1703
Registered: December 2005
Location: NJ
Senior Member

check two_task parmeter on the sever what it has been set to, otherwise use connect string for sql loader.

sqlldr 'babu/****@abc'
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #388319 is a reply to message #388316] Tue, 24 February 2009 04:22 Go to previous messageGo to next message
ollyb303
Messages: 3
Registered: January 2009
Junior Member
gentlebabu wrote on Tue, 24 February 2009 10:10
use connect string for sql loader.

sqlldr 'babu/****@abc'


That is what I'm doing! Same as I have for the last 3 months with the same settings!

Where do I find the two_task parameter please?


Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #388322 is a reply to message #388319] Tue, 24 February 2009 05:00 Go to previous messageGo to next message
babuknb
Messages: 1703
Registered: December 2005
Location: NJ
Senior Member

1/ Post your SQLLDR cmd..

2/ I think your reading wrong files; Please post your controlfile.

3/ Post your logfiles

Babu

[Updated on: Tue, 24 February 2009 05:12]

Report message to a moderator

Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #388341 is a reply to message #388315] Tue, 24 February 2009 06:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Bizarrely, this was working just fine until this weekend at which point it stopped working.
Well, something changed.
Post how you are invoking sqlldr. Most probably, your tns entries are wrong or you must be using a password with special character like @.
look into sqlnet.ora for. default_domain.
Compare that with tnsnames.ora. Make sure you are using the right entry.

Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #388362 is a reply to message #388315] Tue, 24 February 2009 08:22 Go to previous messageGo to next message
ollyb303
Messages: 3
Registered: January 2009
Junior Member
Problem is solved. Thankyou. The problem was with tnsnames at the server end.
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #388379 is a reply to message #388362] Tue, 24 February 2009 08:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10613
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>The problem was with tnsnames at the server end
Makes no sense to me.
TNSNAMES.ORA is all about client.
Oracle / sqlldr does not care what is in tnsnames.ora in serverside.
The sqlldr will use the tnsnames.ora in your client (unless you are executing this directly in the server).
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #388425 is a reply to message #388315] Tue, 24 February 2009 12:34 Go to previous messageGo to next message
joy_division
Messages: 4453
Registered: February 2005
Location: East Coast USA
Senior Member
ollyb303 wrote on Tue, 24 February 2009 05:04
Hi,

I have a problem using SQL*LDR to load data into a 10.2g db. The log file reads:

SQL*Loader: Release 9.0.1.4.0 - Production on Tue Feb 24 09:44:32 2009




On a side observation, was your intention to use the 9i sqlldr for the 10g database?
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #398499 is a reply to message #388315] Thu, 16 April 2009 08:27 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Hi All,

i am facing similar problem

Problem:

SQL*Loader: Release 9.2.0.5.0 - Production on Thu Apr 16 06:50:12 2009


SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve service name
i All,

i am facing similar problem

Problem:

SQL*Loader: Release 9.2.0.5.0 - Production on Thu Apr 16 06:50:12 2009


SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve service name
recently i have installed 10g client to my system. and i can able to connect to the data base very much..

Database version which i am trying to upload is :9.2.0.5.0

Please give your suggestions..
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #398510 is a reply to message #398499] Thu, 16 April 2009 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 57602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12154: TNS:could not resolve the connect identifier specified
 *Cause:  A connection to a database or other service was requested using
 a connect identifier, and the connect identifier specified could not
 be resolved into a connect descriptor using one of the naming methods
 configured. For example, if the type of connect identifier used was a
 net service name then the net service name could not be found in a
 naming method repository, or the repository could not be
 located or reached.
 *Action:
   - If you are using local naming (TNSNAMES.ORA file):
      - Make sure that "TNSNAMES" is listed as one of the values of the
        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA)
      - Verify that a TNSNAMES.ORA file exists and is in the proper
        directory and is accessible.
      - Check that the net service name used as the connect identifier
        exists in the TNSNAMES.ORA file.
      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
        file.  Look for unmatched parentheses or stray characters. Errors
        in a TNSNAMES.ORA file may make it unusable.
   - If you are using directory naming:
      - Verify that "LDAP" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Verify that the LDAP directory server is up and that it is
        accessible.
      - Verify that the net service name or database name used as the
        connect identifier is configured in the directory.
      - Verify that the default context being used is correct by
        specifying a fully qualified net service name or a full LDAP DN
        as the connect identifier
   - If you are using easy connect naming:
      - Verify that "EZCONNECT" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Make sure the host, port and service name specified
        are correct.
      - Try enclosing the connect identifier in quote marks.

   See the Oracle Net Services Administrators Guide or the Oracle
   operating system specific guide for more information on naming.

Regards
Michel
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #398653 is a reply to message #388315] Fri, 17 April 2009 01:37 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Thanks for your quick reply Michel,

i have verified all possible methods for TNSNAMES.ORA file and still am getting the same error..

Now my machine having both 9.1.2 and 10.2.0.1 client versions of Oracle.

yesterday when i run my SQLLDR which was pointing to version 9, now i renamed version 9 and now its pointing to version 10.

Error:
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Apr 17 11:58:22 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified 


This problem existing since i loaded client version 10.2.0.1..

please advice me how i can get back to load data using Sql Loader.

Thanks in advance.
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #398657 is a reply to message #398653] Fri, 17 April 2009 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 57602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you copy tnsnames.ora to 10g home?
Is 9i or 10g your default home?
Is TNS_ADMIN variable set?
...

Regards
Michel
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #398670 is a reply to message #388315] Fri, 17 April 2009 01:59 Go to previous messageGo to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Hi Michel,

Following are the details

Did you copy tnsnames.ora to 10g home?

yes , i copied tnsnames.ora & SQLnet.ora files to 10g home

Is 9i or 10g your default home?

Now its pointing to 10g home

Is TNS_ADMIN variable set?

Am not sure how to set this variable value, i have placed all files at default location. i can able to connect my SQLPLUS window and using TOAD from my machine..

This problem appearing one when i rung SQLLDR control file.

Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #398674 is a reply to message #398670] Fri, 17 April 2009 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post evidence of all what you said, including the connection strings (without password) for SQL*Plus and SQL*Loader and tnsping, from the same (DOS) window.

Regards
Michel
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #398751 is a reply to message #398674] Fri, 17 April 2009 07:37 Go to previous messageGo to next message
babuknb
Messages: 1703
Registered: December 2005
Location: NJ
Senior Member

>>Am not sure how to set this variable value, i have placed all files at default location. i can able to connect my SQLPLUS window and using TOAD from my machine..

Ask your DBA. Or

SET ORACLE_SID=ABC

echo %ORACLE_SID%

SET ORACLE_HOME=.....

echo %ORACLE_HOME%

SET TNS_ADMIN=...

echo %TNS_ADMIN%




Try and post.

Babu
Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #399135 is a reply to message #398751] Mon, 20 April 2009 19:21 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
you shouldn't be renaming sqlldr 9 out the way to make it pick up sqlldr 10. Your path & library paths should be adjusted to point to the verion you want.

1) examine your environment:
env | grep ora | sort

2) consider setting TNS_ADMIN to point to a single common tnsnames.ora location

Re: SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #399136 is a reply to message #388315] Mon, 20 April 2009 19:30 Go to previous message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
etl_prasad,

Please clarify
Operating System (OS) name & version of DB server system.
Operating System (OS) name & version of client system.
Oracle version to 4 decimal places of DB server s/w.
Oracle version to 4 decimal places of client s/w.

Post complete list of environmental variables for client session.

Post the complete command which is generating the error(s).

[Updated on: Mon, 20 April 2009 19:47]

Report message to a moderator

Previous Topic: IMP only filtered data
Next Topic: SQLLoader(DIRECT=FLASE, PARALLEL=TRUE)
Goto Forum:
  


Current Time: Wed Apr 16 13:10:40 CDT 2014

Total time taken to generate the page: 0.06382 seconds