Not able to connect to Oracle server from shell script

Shell Programming and Scripting
1

Hi,

I am facing an issue in connecting to database from Linux shell script. Here is my code

export ORACLE_BASE=xxx
export ORACLE_SID=xxx
export ORACLE_HOME=xxxx

DB_PATH=xxx/'yyy@yy'


UserId=`sqlplus -s $DB_PATH  <<EOF
set head off feedback off
select max(USER_ID)+1 from USERS;
exit;
EOF`
echo "User ID is - $UserId"

When I run the script, I am getting the below error.

User ID is - ERROR:
ORA-12545: Connect failed because target host or object does not exist

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

I tried with ping and telnet to the DB Server from Linux server then connection was success.
I verified tnsnames.ora and tried by giving IP address of host etc but did not work. Can anyone please let me know any solution for this?

2

Have you tried: tnsping ${ORACLE_SID} ?
Also:

UserId=$(sqlplus -s $DB_PATH  <<EOF 
set head off feedback off 
select max(USER_ID)+1 from USERS;
exit; 
EOF
)
3

How can you connect to a database without a userid and password for the database?

Your script does not seem to perform the basic connection call with the right parameters; and that is also what the error message indicates.

4

Thanks for your suggestions.
@vgersh99
Hi, I have tried with the code provided by you but same error is coming. I also tried to tnsping but getting the below error.
-bash: tnsping: command not found.
Please suggest.
@Neo
Hi, I have given userid and password in the variable DB_PATH and used the same. Since there are special characters in the password I enclosed in parenthesis.

5

tnsping lives in the same path as sqlplus

6

I tried from oracle home directory and getting the below error.

TNS Ping Utility for Linux: Version 11.2.0.3.0 

Used parameter files:
/opt/app/oracle/product/11.2.0/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
7

there you have it - your sqlnet.ora is not configured (correctly?) for the SID you're trying to tnsping .