Home » Infrastructure » Unix » run sql commands in background (Sun OS, Oracle 10.2.0.3)
run sql commands in background [message #511318] Sat, 11 June 2011 12:08 Go to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I am trying to execute sql commands in a shell script and run in the background via crontab. I am providing the input files.

vi test.sql
-----------
spool test.log
select name from v$database;
select name from v$database;
select name from v$database;
spool off

vi script.sh
------------
#! /bin/ksh
sqlplus / as sysdba @test.sql
exit 0

crontab -l
----------
00 01 11 06 * /ora01/script.sh &

The job did not run at 1 pm as I expected. I want to know what's wrong with it.
Re: run sql commands in background [message #511319 is a reply to message #511318] Sat, 11 June 2011 12:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The job did not run at 1 pm as I expected.
I disagree.

add new line as line #2 as shown below to script.sh
touch /tmp/launched.txt

Does /tmp/launched.txt get created after cron launches script?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Sat, 11 June 2011 12:16]

Report message to a moderator

Re: run sql commands in background [message #511320 is a reply to message #511319] Sat, 11 June 2011 12:20 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I modified the script like this :

spool test.log
touch /tmp/launched.txt
select name from v$database;
select name from v$database;
select name from v$database;
spool off


I din't get any output.
Re: run sql commands in background [message #511321 is a reply to message #511320] Sat, 11 June 2011 12:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vi script.sh
------------
#! /bin/ksh
touch /tmp/launched.txt
sqlplus / as sysdba @test.sql
exit 0

TRY AGAIN!
Re: run sql commands in background [message #511322 is a reply to message #511321] Sat, 11 June 2011 12:29 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
still no output.
Re: run sql commands in background [message #511323 is a reply to message #511322] Sat, 11 June 2011 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/ => do #7 -> SHOW Us!
Re: run sql commands in background [message #511324 is a reply to message #511318] Sat, 11 June 2011 12:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
lg123 wrote on Sat, 11 June 2011 18:08

crontab -l
----------
00 01 11 06 * /ora01/script.sh &

The job did not run at 1 pm as I expected.

Since crontab uses 24hr notation that would be 1 am not 1 pm.
Re: run sql commands in background [message #511325 is a reply to message #511324] Sat, 11 June 2011 12:43 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
vi test.sql
-----------
spool test.log
select name from v$database;
select name from v$database;
select name from v$database;
spool off

vi script.sh
------------
#! /bin/ksh
touch /tmp/launched.txt
sqlplus / as sysdba @test.sql
exit 0

crontab -l
----------
40 13 11 06 * /ora01/script.sh &

The file launched.txt got created under /tmp. But I dont find the test.log file which needs to have the output of the sql statments executed.
Re: run sql commands in background [message #511326 is a reply to message #511325] Sat, 11 June 2011 12:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
vi script.sh
------------
#! /bin/ksh
env | sort -o /tmp/capture.env
sqlplus / as sysdba @test.sql
exit 0


after cron launches modified script above post content of /tmp/capture.env back here
Re: run sql commands in background [message #511327 is a reply to message #511326] Sat, 11 June 2011 12:57 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
capture.env
-----------
HOME=/ora01
LOGNAME=oracle
PATH=/usr/bin:
PWD=/ora01
SHELL=/usr/bin/sh
TZ=US/Eastern
_=/usr/bin/env
Re: run sql commands in background [message #511328 is a reply to message #511327] Sat, 11 June 2011 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Will a process with environment as shown above be able to successfully invoke sqlplus or even know where to find sqlplus?
Re: run sql commands in background [message #511329 is a reply to message #511318] Sat, 11 June 2011 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cron does not execute the .profile (or similar).
You have to explicitly set all Oracle variables environment including ORACLE_HOME, ORACLE_SID and PATH.
When something goes wrong cron send a mail to the owner (if mail is configure on your server, if not do it to send mail locally).

Regards
Michel
Re: run sql commands in background [message #511330 is a reply to message #511329] Sat, 11 June 2011 13:42 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
vi script.sh
------------
#! /bin/ksh
export ORACLE_SID=xxxxxxxx
export ORACLE_HOME=/ora01/app/oracle/product/10.2.0
export PATH=$HOME/bin:$PATH
sqlplus / as sysdba @test.sql
exit 0

crontab -l
----------
35 14 11 06 * /ora01/script.sh MAILTO=xxxxxx.xxxxxxxxxxxx@xxxxxxxxxx.com &

I am not getting any output. Even the email option doesn't seem to work.
Re: run sql commands in background [message #511331 is a reply to message #511330] Sat, 11 June 2011 13:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am not getting any output.
because process environment is not correct!

What harm would result is we knew actual ORACLE_SID?
Re: run sql commands in background [message #511332 is a reply to message #511331] Sat, 11 June 2011 13:57 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
what do you mean? As far as my knowledge is concerned, I have set the environment variables correctly. Let me know what I need to do to get the output?
Re: run sql commands in background [message #511333 is a reply to message #511332] Sat, 11 June 2011 14:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>As far as my knowledge is concerned,
If you really knew what needed to be done, you would not be here asking for assistance; now would you?

vi script.sh
------------
#! /bin/ksh
export ORACLE_SID=xxxxxxxx
export ORACLE_HOME=/ora01/app/oracle/product/10.2.0
export PATH=$HOME/bin:$PATH
env | sort -o /tmp/capture.env
sqlplus / as sysdba @test.sql
exit 0

post content of /tmp/capture.env after new/next cron invocation
Re: run sql commands in background [message #511334 is a reply to message #511333] Sat, 11 June 2011 14:11 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
capture.env
-----------
HOME=/ora01
LOGNAME=oracle
ORACLE_HOME=/ora01/app/oracle/product/10.2.0
ORACLE_SID=xxxxxxx
PATH=/ora01/bin:/usr/bin:
PWD=/ora01
SHELL=/usr/bin/sh
TZ=US/Eastern
_=/usr/bin/env
Re: run sql commands in background [message #511335 is a reply to message #511334] Sat, 11 June 2011 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PATH must contain $ORACLE_HOME/bin

Regards
Michel
Re: run sql commands in background [message #511336 is a reply to message #511335] Sat, 11 June 2011 14:39 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
It is working properly now. Thanks.
Re: run sql commands in background [message #511874 is a reply to message #511336] Wed, 15 June 2011 12:19 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
Is there any easy way for the crontab to send me email once the task is done?
Re: run sql commands in background [message #511876 is a reply to message #511874] Wed, 15 June 2011 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Include "sendmail" in your script.
For more just execute "man sendmail" or Google.

Regards
Michel
Re: run sql commands in background [message #511878 is a reply to message #511876] Wed, 15 June 2011 13:14 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I gave the crontab command like this :

04 14 15 06 * /ora01/script.sh | mail -s "task completed" xxxxx.xxxxxxxx@xxxxxxxxxxx.com &

I am receiving the output to my email id. But I am not able to view the subject in the email. Subject is blank.
Re: run sql commands in background [message #511879 is a reply to message #511878] Wed, 15 June 2011 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
FWIW - The ampersand on the end of the line is superfluous.
Re: run sql commands in background [message #511880 is a reply to message #511879] Wed, 15 June 2011 13:23 Go to previous messageGo to next message
lg123
Messages: 225
Registered: August 2008
Senior Member
I added ampersand at the end so that the job will run in the background.
Re: run sql commands in background [message #511882 is a reply to message #511880] Wed, 15 June 2011 13:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I added ampersand at the end so that the job will run in the background.
Since there is no "foreground" with regards to a cron job, running cron job in "background" adds nothing.
The behavior will be the same with or without the additional character; which is why I said SUPERFLUOUS!
Re: run sql commands in background [message #511884 is a reply to message #511878] Wed, 15 June 2011 13:42 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am receiving the output to my email id. But I am not able to view the subject in the email. Subject is blank.

So this has nothing to do with Oracle.
I advise you to put the "mail" command inside the script.
You should better ask this in a Unix forum instead of an Oracle one.

Regards
Michel
Previous Topic: KUP-04063 SQLNET + NFS
Next Topic: Redhat linux
Goto Forum:
  


Current Time: Thu Mar 28 06:31:44 CDT 2024