OraFAQ Forum: Server Utilities » SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*LDR fails ORA-12154: TNS:could not resolve service name, but SQL*PLUS works fine [message #388315]

Tue, 24 February 2009 04:04

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

Report message to a moderator

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

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

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

sqlldr 'babu/****@abc'

Report message to a moderator

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

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?

Report message to a moderator

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

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

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

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

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

ollyb303
Messages: 3
Registered: January 2009

Junior Member

Problem is solved. Thankyou. The problem was with tnsnames at the server end.

Report message to a moderator

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

Mahesh Rajendran
Messages: 10708
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).

Report message to a moderator

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

joy_division
Messages: 4963
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?

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

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

Report message to a moderator

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

andrew again
Messages: 2577
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

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 20:12:32 CST 2024