SQLoader & SQLplus error ?

Hi experts,

I have got the following errors when running my sqloader script :

Can anyone give me a hand ?

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Nov 22 15:24:18 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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

You didn't show the command you are running, so it's difficult to say why, exactly, you are getting this error!

In AIX server's command prompt for example,
just run the script file as below:

CommandPrompt>./xxxxxxxxx.sh

---------- Post updated at 03:52 PM ---------- Previous update was at 03:50 PM ----------

Another question:

Can SQLPLUS and SQLDR run in the same script for 2 different purposes ??

Showing nothing but a script name (be it generic or specific) doesn't really help when asking for advice with specific database errors. My oracle is somewhat rusty, but above error messages seem to indicate that you supplied an invalid DB identifier for both SQLPlus and SQLLoader. Make sure the DB instance and/or host do (still) exist.

Hi,

How about the error saying this :

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

What can I do ?

See my last remark in post#4. Test the connection / authentication with a small script or command.

As far as i remember my Oracle it has its own "name resolution", which is somewhat similar (but not the same as) host names. The file necessary for it to work is called tnsnames.ora and is somewhere in the instance directory. Use find to locate it.

You can test the connection with Oracles tnsping command once you corrected the tnsnames.ora file instead of running your complete script.

And at last: putting "ORA-12154 oracle error" as search words into my search machine the first link was:
Oracle Developer Community

and the second was:
Oracle Documentation, Errors ORA-12150 - ORA-12236

both describing in painstaking detail where the error comes from and how it is debugged, traced and corrected. As a responsible DBA or SysAdmin you should try and find such easily researched solutions yourself instead of asking others to do your homework.

I hope this helps.

bakunin

I am neither a DBA or sys admin...... :rolleyes:

With it being TNS, can I presume you are trying to connect from your desktop to a central server that runs the databases? If so, your desktop needs to know where the target is. I assume that you are doing something like sqlplus username/password@database and your desktop needs to interpret the database part and know where to send the IP connection.

This reads a file that should have been installed or created on your desktop (or network drive) to point you to a host (and various other things) that it will then use to look up the IP address and try to open the connection to that on the port given. On the server, there needs to be a TNS listener on the port that responds to your desktop and connects you to the correct database instance. All this happens before the database tries to authenticate your credentials.

Can you show us (in CODE tags) the output from a tnsping database putting in the name you are trying to connect to.

Can you tell us what Operating System you are trying to connect from and if you are using GUI tools or the command line.

Thanks, in advance,
Robin

I tried to tnsping the database and it return the following :

ksh: tnsping:  not found.

However, if I ping the database and it returns the following :

ping DATABASENAME
PING DATABASENAME: (10.xxx.xx.xxx): 56 data bytes
64 bytes from 10.xxx.xx.xxx: icmp_seq=0 ttl=254 time=0 ms
64 bytes from 10.xxx.xx.xxx: icmp_seq=1 ttl=254 time=0 ms
64 bytes from 10.xxx.xx.xxx: icmp_seq=2 ttl=254 time=0 ms
64 bytes from 10.xxx.xx.xxx: icmp_seq=3 ttl=254 time=0 ms

---------- Post updated at 10:27 AM ---------- Previous update was at 10:20 AM ----------

Anyone knows if running sqlplus or sqlldr inside a ksh script,
should I enter the full path of where sqlplus and sqlldr are located like the below inside the script :

/u01/app/oracle/product/11.2.0/client_1/bin/sqlplus

/u01/app/oracle/product/11.2.0/client_1/bin/sqlldr

Why i am asking this because I got the following errors if I just
put in

sqlplus username/password@database

OR

sqlldr username/password@database

./targetTodosomething1.sh[41]: sqlplus:  not found.
./targetTodosomething1.sh[49]: sqlldr:  not found.

---------- Post updated at 06:06 PM ---------- Previous update was at 10:27 AM ----------

Can you give me any comment on the following ?

"/u01/app/oracle/product/11.2.0/client_1/bin/"

is where sqlldr located in.

/u01/app/oracle/product/11.2.0/client_1/bin/sqlldr loginName/password@jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = XYZ)(PORT =15203)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = DBNAME))) log=$JOBLOG control=$SQLLDR_CTL data=$INFILE errors=10000 direct=true rows=10000

---------- Post updated at 06:14 PM ---------- Previous update was at 06:06 PM ----------

Or should I use this command ?

/u01/app/oracle/product/11.2.0/client_1/bin/sqlldr loginName/password@//10.X.X.X:1521/ABC log=$JOBLOG control=$SQLLDR_CTL data=$INFILE errors=10000 direct=true rows=10000

The commands ping and tnsping do different tasks. The ping will target an IP address, be that a server, desktop, switch, security camera, fridge or whatever networked device. tnsping on the other hand will take the name and try to make a connection to the running Oracle SID. It will first have to determine which host the database is on and get the IP address, so in at least needs to be able to ping the server. It will then use the name you give it and try to open a conversation with the listener (typically on port 1521 or the server) and check if the SID is valid.

The contents of tnsnames.ora provides the translation between the name you use and the server & name that the listener will respond to. The name you use could be changed to another by tnsnames.ora and/or another by the listener on the target server to get to the actual Oracle instance. If you are unable to validate the connection with tnsping, you will not be able to connect, however in your case you can't even get tnsping to start.

These errors suggest that you have not set up the environment correctly on your desktop or client, and that would explain why it cannot connect too, because it will not know where to find the tnsnames.ora file.

You should (at the least) have entries in your $PATH that included where executables are. Do you have a colleague that this works for that we can compare?

It would be good to see the output for both you & your colleague for:-

uname -a
ps
echo $PATH
echo $ORACLE_HOME
echo $ORACLE_SID
find / -name tnsnames.ora -o -name oraenv 2>/dev/null

You might also try

/u01/app/oracle/product/11.2.0/client_1/bin/tnsping database-name

to see the tool start, but I would not expect it to find tnsnames.ora so it will probably not connect. You might like to post the output in the thread too.

Please post all output/errors in CODE tags to make it easier to read and to preserve the spacing/indenting.

Thanks, in advance,
Robin

Now I changed the code to the following :

sqlldr loginName/password@//10.x.x.x:1521/ABC log=$JOBLOG control=$SQLLDR_CTL data=$INFILE errors=10000 direct=true rows=10000

It returns the following error :
Anyone has solution ???

ERROR:
ORA-12541: TNS:no listener


SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

SQL*Loader: Release 11.2.0.4.0 - Production on Fri Nov 24 18:12:19 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12541: TNS:no listener

There is usually information on the Oracle error with the oerr command. Try oerr ORA-12541 Failing that, have a look here

Basically the server is not listening for you or there is a network issue preventing you connecting, e.g. firewall, routing, IP tables (local firewall on the server itself) Firstly, check you have the right server. You would be far better using a tnsnames.ora file rather than coding IP addresses in your connection string.

Your updates are quite a leap. Do you have any output from the requests above? That way we will know where we're starting from and be better placed to help.

Robin

Finally, I got the problem solved. Thanks to all kindly suggestions and advice.
Cheers.

Hi Jaewong,
Can you tell us what you finally did to solve your problem. We'd like for other people reading this thread in the future to be able to learn from your experience.

Cheers,
Don