Re: cron, Oracle, and sqlplus

Re: cron, Oracle, and sqlplus

[Igor Pechtchanski]

On Thu, 9 Jan 2003, James Schnedar wrote:

> I am experiencing a problem with running Oracle sqlplus within a shell
> script using cron.  The shell script runs correctly from the command
> line, but once I add it to cron, the shell script will execute up until
> the sqlplus command.  Therefore, cron is working correctly as well, but
> the addition of the sqlplus command causes it to hang.  The sqlplus
> session has started, but appears hung.
>
> ps -ef yields:
>
>      UID     PID    PPID TTY     STIME COMMAND
>   SYSTEM    3296     552   ?  14:53:00 /usr/sbin/cron
> Administ    3312    3296   ?  14:53:00 /usr/bin/sh
> Administ    3388    3312   ?  14:53:00 /usr/bin/sh
> Administ    3472    3296   ?  14:53:01 /usr/sbin/ssmtp
> Administ    3508    3388   ?    Dec 31 /cygdrive/d/ORACLE/806/bin/sqlplus
>
> Any suggestions?
> Thanks,

James,

Is sqlplus trying to open a window or access a network share?  Try
checking the "Allow service to interact with the desktop" box for the cron
service,and see if it helps (having restarted cron, of course).
	Igor
-- 
				http://cs.nyu.edu/~pechtcha/
      |\      _,,,---,,_		pechtcha@cs.nyu.edu
ZZZzz /,`.-'`'    -.  ;-;;,_		igor@watson.ibm.com
     |,4-  ) )-,_. ,\ (  `'-'		Igor Pechtchanski
    '---''(_/--'  `-'\_) fL	a.k.a JaguaR-R-R-r-r-r-.-.-.  Meow!

Oh, boy, virtual memory! Now I'm gonna make myself a really *big* RAMdisk!
  -- /usr/games/fortune

cron, Oracle, and sqlplus

[James Schnedar]

I am experiencing a problem with running Oracle sqlplus within a shell
script using cron.  The shell script runs correctly from the command
line, but once I add it to cron, the shell script will execute up until
the sqlplus command.  Therefore, cron is working correctly as well, but
the addition of the sqlplus command causes it to hang.  The sqlplus
session has started, but appears hung.

ps -ef yields:

     UID     PID    PPID TTY     STIME COMMAND
  SYSTEM    3296     552   ?  14:53:00 /usr/sbin/cron
Administ    3312    3296   ?  14:53:00 /usr/bin/sh
Administ    3388    3312   ?  14:53:00 /usr/bin/sh
Administ    3472    3296   ?  14:53:01 /usr/sbin/ssmtp
Administ    3508    3388   ?    Dec 31
/cygdrive/d/ORACLE/806/bin/sqlplus


Any suggestions?

Thanks,

-- 
-Jim Schnedar
New Mexico Mutual Casualty Company
jims@nmmcc.com

Re: cron, Oracle, and sqlplus

[Peter A. Castro]

On Thu, 9 Jan 2003, James Schnedar wrote:

> I am experiencing a problem with running Oracle sqlplus within a shell
> script using cron.  The shell script runs correctly from the command
> line, but once I add it to cron, the shell script will execute up until
> the sqlplus command.  Therefore, cron is working correctly as well, but
> the addition of the sqlplus command causes it to hang.  The sqlplus
> session has started, but appears hung.
> 
> ps -ef yields:
> 
>      UID     PID    PPID TTY     STIME COMMAND
>   SYSTEM    3296     552   ?  14:53:00 /usr/sbin/cron
> Administ    3312    3296   ?  14:53:00 /usr/bin/sh
> Administ    3388    3312   ?  14:53:00 /usr/bin/sh
> Administ    3472    3296   ?  14:53:01 /usr/sbin/ssmtp
> Administ    3508    3388   ?    Dec 31
> /cygdrive/d/ORACLE/806/bin/sqlplus

sqlplus is a native windows app, so perhaps you are trying to invoke it
with a cygwin-ized path to a command file?  If you can send us a section
of your shell script, where it's invoking sqlplus, perhaps we can figure
out what's going on.  Also, there are some environmental variables that
can effect sqlplus execution, and cron doesn't invoke things in a full
environment.

> Any suggestions?
> Thanks,

-- 
Peter A. Castro <doctor@fruitbat.org> or <Peter.Castro@oracle.com>
	"Cats are just autistic Dogs" -- Dr. Tony Attwood


--
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple
Bug reporting:         http://cygwin.com/bugs.html
Documentation:         http://cygwin.com/docs.html
FAQ:                   http://cygwin.com/faq/

Re: cron, Oracle, and sqlplus

[Chiranth]

On Jan 9, James Schnedar wrote....

>I am experiencing a problem with running Oracle sqlplus within a shell
>script using cron.  The shell script runs correctly from the command
>line, but once I add it to cron, the shell script will execute up until
>the sqlplus command.  Therefore, cron is working correctly as well, but
>the addition of the sqlplus command causes it to hang.  The sqlplus
>session has started, but appears hung.
>

sqlplus needs some environment variables to be set, like ORACLE_HOME ( I
am not sure what others).  Andd cron does not set these variables while
starting your process.  Hence sqlplus hangs.

Solution: Use the line #!/bin/bash  as the first line of your shell script
and define the required environment variables in your .bashrc file.  Ig
you are using the Korn shell, ensure that these variables are set in
your .profile and use #!/bin/ksh as the first line of your shell script.
It will ensure that the correct shell interpreter is invoked and the
environment variables read appropriately.

-- 
Chiranth M.C.
Netkraft Pvt Ltd


--
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple
Bug reporting:         http://cygwin.com/bugs.html
Documentation:         http://cygwin.com/docs.html
FAQ:                   http://cygwin.com/faq/

Re: cron, Oracle, and sqlplus

[James Schnedar]

> > I am experiencing a problem with running Oracle sqlplus within a shell
> > script using cron.  The shell script runs correctly from the command
> > line, but once I add it to cron, the shell script will execute up until
> > the sqlplus command.  Therefore, cron is working correctly as well, but
> > the addition of the sqlplus command causes it to hang.  The sqlplus
> > session has started, but appears hung.
> >
> > ps -ef yields:
> >
> >      UID     PID    PPID TTY     STIME COMMAND
> >   SYSTEM    3296     552   ?  14:53:00 /usr/sbin/cron
> > Administ    3312    3296   ?  14:53:00 /usr/bin/sh
> > Administ    3388    3312   ?  14:53:00 /usr/bin/sh
> > Administ    3472    3296   ?  14:53:01 /usr/sbin/ssmtp
> > Administ    3508    3388   ?    Dec 31 /cygdrive/d/ORACLE/806/bin/sqlplus
> >
> > Any suggestions?
> > Thanks,
> > James Schnedar

Reply #1:
Igor Pechtchanski wrote:
> 
> James,
> 
> Is sqlplus trying to open a window or access a network share?  Try
> checking the "Allow service to interact with the desktop" box for the cron
> service,and see if it helps (having restarted cron, of course).
>         Igor

Reply #2:
"Peter A. Castro" wrote:
> 
> sqlplus is a native windows app, so perhaps you are trying to invoke it
> with a cygwin-ized path to a command file?  If you can send us a section
> of your shell script, where it's invoking sqlplus, perhaps we can figure
> out what's going on.  Also, there are some environmental variables that
> can effect sqlplus execution, and cron doesn't invoke things in a full
> environment.
> 
> 
> --
> Peter A. Castro <doctor@fruitbat.org> or <Peter.Castro@oracle.com>
>         "Cats are just autistic Dogs" -- Dr. Tony Attwood

Reply #3:
Chiranth wrote:
> 
> sqlplus needs some environment variables to be set, like ORACLE_HOME ( I
> am not sure what others).  Andd cron does not set these variables while
> starting your process.  Hence sqlplus hangs.
> 
> Solution: Use the line #!/bin/bash  as the first line of your shell script
> and define the required environment variables in your .bashrc file.  Ig
> you are using the Korn shell, ensure that these variables are set in
> your .profile and use #!/bin/ksh as the first line of your shell script.
> It will ensure that the correct shell interpreter is invoked and the
> environment variables read appropriately.
> 
> --
> Chiranth M.C.
> Netkraft Pvt Ltd


All,
	Thank you for the replies.  Through researching these replies, I
discovered:

1) sqlplus does indeed open a window.  Therefore, the "Allow service to
interact with the desktop" box needs to be clicked.  Since I am coming
from a UNIX environment and am fairly ignorant of Windows, here's how to
get to it (for Windows 2000) (this portion primarily for archiving on
Cygwin mailing list):

a) right click My Computer
b) select Manage.  This will open Computer Management window.
c) select/double click Servies and Applications on right side
d) select/double click Services on the right side
e) Assuming cron has been set up correctly, you should see it as a
service.  Right click on cron.
f) select Properties
g) select Log On tab
h) click "Allow service to interact with desktop" box.  Done.

It also took me awhile to start cron, so in case someone comes across
this post to starting cron:

cygrunsrv -I cron -p /usr/sbin/cron -a -D
cygrunsrv -S cron
to stop cron:
cygrunsrv -E cron

Also, if you are not logging in as Adminstrator, then you will be
required to have an entry in /etc/passwd for cron to run your scripts. 
Administrator already exists, so copy that line and change Administrator
to your user name and give yourself a new user ID.

These actions allowed the cron service to run my shell script.  But, see
below for further sqlplus information.

2) ORACLE_HOME is inherited from the Window's repository.  I did not
(and should not) reset it in the shell script.  I also should not set
LD_LIBRARY_PATH.

3) Evidentally, sqlplus in windows environment behaves differnently than
a unix environment.  Primarily, it opens a new window, end of story.  In
UNIX, the -s option is silent mode and not interactive.  For windows,
the command line sqlplus command is "plusxx" where xx is the version you
are on.  This allows for the -s option and works with directing files in
(< in_file) and redirecting output out (> out_file).

4) While working with sql scripts and running Oracle Reports from Cygwin
shell scripts, once control is passed to the Oracle environments
(spool'ing in sqlplus, Oracle Reports, etc.), it requires window's path
names (i.e. d:\cygwin\tmp) as opposed to relying on Cygwin to interpret
the path name.

Background information:  We are in the midst of moving from a Unix
backend to a Linux backend.  Oracle 9i is certified on Red Hat Advance
Server 2.1.  Oracle ReportWriter is not certified as stand alone on
Linux.  You have to buy Oracle 9i App Server (that's how they get you). 
Oracle Report Writer 6i is certified as stand alone on windows. 
Therefore, overnight batch process that is done on our current Unix
backend needs to run on windows with ReportWriter 6i connecting to 9i
database on linux.  Cygwin is proving very beneficial in this move
because we are doing very minimal rewrites to our overnight shell
scripts.  I may post this to Oracle, but they haven't been very helpful.

Thanks for your help, it has been very benficial.


-- 
-Jim Schnedar
New Mexico Mutual Casualty Company
jims@nmmcc.com

--
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple
Bug reporting:         http://cygwin.com/bugs.html
Documentation:         http://cygwin.com/docs.html
FAQ:                   http://cygwin.com/faq/