sqlplus in the shell and crontab 1

[GuzaPasha]

Hi all,

I have a problem in running shell script, that uses sqlplus command, using the crontab.

Here is the script

#!/bin/ksh
export ORACLE_HOME
ORACLE_HOME=/oracle
export PATH
PATH=$PATH:$ORACLE_HOME:

izbor1=`$ORACLE_HOME/bin/sqlplus -s sti/sti <<end
set pagesize 0 feedback off ver off heading off echo off
select count (*) as &quot; no&quot; from Table where no = 1;
exit;
end`
..................

When I run the crontab at desired time the following output is shown

izbor1=ERROR: ORA-01034: ORACLE not available
ORA-07200: slsid: oracle_sid not set.

What's wrong with the script?
What else should I put?

Any suggestion would be helpfull.

GuzaPasha

 

[aigles]

Set the ORACLE_SID to the database name.

The database name can also be specified on the sqlplus command :

sqlplus -s user/password@databasename

Jean Pierre.

 

[GuzaPasha]

Hello Jean,

I am new to the ORACLE,
where to find database name.
How to obtain it?

Thank you in advance.
GuzaPasha

 

[alexhu]

when you log in to the database you have to specify

user
password
alias to database which is normally ORACLE_SID

or

have a look in tnsnames.ora and find the SID associated with the alias

or

when in sqlplus do

select * from v$database;

or

echo $ORACLE_SID in UNIX

or

search ther registry in Windows for ORACLE_SID

or

..........


Alex

 

[GuzaPasha]

Hi Alex,

I'got it.
ORACLE_SID=PROD

I've tried the following
sqlplus -s sti/sti@PROD

TNS couldn't resolve service name

How should tnsnames.ora looks like?
Cron job is running the shell cript that uses sqlplus command. cron,shell and database ae on the same machine.

Could you please provide me the look for the tnsnames.ora file for PROD, previously mentioned.

Thank you,
GuzaPasha

 

[aigles]

Don't specify the database name on the sqlplus command, define the ORACLE_SID variable instead; in that case you don't need to resolve service name defined by in the tnsnames.ora file

export ORACLE_SID=PROD
sqlplus -s sti/sti


Jean Pierre.

 

[alexhu]

tnsnames.ora should be in $ORACLE_HOME/network/admin

let us see what is in it

Alex

 

[GuzaPasha]

Hi Jean Pierre,

look at the script below
#!/bin./sh
ORABIN=/oracle/bin
ORACLE_HOME=/oracle
PATH=$PATH:$ORACLEHOME:$ORABIN:
export PATH
export ORACLE_SID=A

izbor1=`$ORABIN/sqlplus -s sti/sti <<end
set pagesize 0 feedback off ver off heading off echo off
select count (*) as &quot; izbor1&quot; from RADIOF where izbor = 1;
exit;
end`

Do you think it is fine?

When I use this in the crontab the response is the following
izbor1=Message file sp1<lang>.msb not found

Could please lead me to solve this to the end?

I think I am really close....

 

[GuzaPasha]

Here is the tnsnames.ora
ivrdata =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = ip_address)
(Port = x)
)
)
(CONNECT_DATA =
(SID = A)
)
)

Help?

 

[aigles]

Try this (not tested)[tt]
#!/bin/sh
export ORABIN=/oracle/bin
export ORACLE_HOME=/oracle
PATH=$PATH:$ORACLE_HOME:$ORABIN:
export PATH
export ORACLE_SID=A

izbor1=`$ORABIN/sqlplus -s sti/sti <<end
set pagesize 0 feedback off ver off heading off echo off
select count (*) from RADIOF where izbor = 1;
exit;
end`
[tt]
[tt]

Jean Pierre.

 

[GuzaPasha]

Thank you, that's it.

GuzaPasha