Home » SQL & PL/SQL » SQL & PL/SQL » Calling shell scripts from dbms_scheduler (Oracle 12.1.0.2.0, Red Hat 7.6)
Calling shell scripts from dbms_scheduler [message #686460] Wed, 21 September 2022 07:48 Go to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
Hi,

long time no see

I've got an issue trying to use dbms_scheduler to run liunx shell scripts on the OS

Here's some setup for a script in the DB:
CREATE table bob (a varchar2(100));
Here's a script test_script.sh
#!/bin/bash
echo "TEST"
sqlplus -s <user>/<pass> <<EOF

begin
  
  insert into bob (a) values ('TESTED');

  COMMIT;

end;
/

exit;

EOF

echo "************************************"
replace <user> and <pass> as appropriate

I created a directory called training_scripts in the oracle user home directory and stuck file in there.

I then tried running the following to run it:
begin
    dbms_scheduler.create_job(
        job_name => 'my_external_job',
        job_type => 'EXECUTABLE',
        job_action => '/home/oracle/training_scripts/test_script.sh',
        auto_drop => false,
        enabled => true);
end;
/

begin
    dbms_scheduler.run_job( 'my_external_job');
end;
/
The run job call errors with:
ORA-27369: job of type EXECUTABLE failed with exit code: 274666 Oracle Scheduler error: Cannot access or open configuration file.?6
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
ORA-06512: at line 2
So I poked around and found oracle support note Doc ID 2763299.1 - Now that note is for oracle clincial but the solution (in an attached pdf) seems to just be general oracle DB set up, and when I tried it on a oracle 19.3.0.0.0 instance it worked.
Unfortunately I've got some customers on oracle 12 - 12.1.0.2.0 (yes, I know, I would love to upgrade them, at some point it'll happen, but not soon enough for my issue).
Everything I've found implies those instructions should work on 12 as well.

Short hand version of what I did from the note instructions:
cd $ORACLE_HOME/rdbms/admin
sudo vi externaljob.ora
change run_user to oracle, run_group to dba
save file

cd $ORACLE_HOME/bin
sudo chown root extjob
sudo chmod 4750 extjob
then restart DB (not sure that's necessary but doesn't hurt.

OS version for oracle 12 is Linux Red Hat 7.6 (19 is Linux Red Hat 8.4)

Any idea what I'm missing?

Re: Calling shell scripts from dbms_scheduler [message #686461 is a reply to message #686460] Wed, 21 September 2022 08:18 Go to previous messageGo to next message
John Watson
Messages: 8766
Registered: January 2010
Location: Global Village
Senior Member
Hi, man - I've had that sort of thing before, usually because the relink after patching has messed up the ownerships and permissions. Running root.sh may sort it out. Another file you have to look for is bin/jssu, mine looks like this,

-rwsr-x---. 1 root oinstall 2333832 Jan 4 2021 jssu


Re: Calling shell scripts from dbms_scheduler [message #686462 is a reply to message #686461] Wed, 21 September 2022 08:28 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
So same ownership and permissions as extjob for jssu by the looks of it?
Re: Calling shell scripts from dbms_scheduler [message #686463 is a reply to message #686462] Wed, 21 September 2022 08:32 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
my 12 has jssu the same as yours.
It's different on my 19 (go figure)
-rwxr-xr-x. 1 oracle oinstall 2335704 Sep 12 08:51 jssu

For root.sh:
I assume you mean the one in $ORACLE_HOME - do I need to be root to run it?
Re: Calling shell scripts from dbms_scheduler [message #686464 is a reply to message #686463] Wed, 21 September 2022 08:34 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
and should I shut the DB down first?
Re: Calling shell scripts from dbms_scheduler [message #686465 is a reply to message #686464] Wed, 21 September 2022 08:40 Go to previous messageGo to next message
John Watson
Messages: 8766
Registered: January 2010
Location: Global Village
Senior Member
Definitely root to run it. I don't know about bouncing the instance. Wouldn't hurt, of course. I guess there might be a copy of the programmes in memory, or something, so the change might not be picked up otherwise.
Re: Calling shell scripts from dbms_scheduler [message #686466 is a reply to message #686465] Wed, 21 September 2022 08:47 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've run root.sh - no difference
Re: Calling shell scripts from dbms_scheduler [message #686467 is a reply to message #686466] Wed, 21 September 2022 09:03 Go to previous messageGo to next message
John Watson
Messages: 8766
Registered: January 2010
Location: Global Village
Senior Member
root.sh calls some scripts in here,
/u01/app/oracle/product/19.0.0/dbhome_prod/network/install

Looking at them I see this:

oracle@DB-19C-OL8:[/u01/app/oracle/product/19.0.0/dbhome_prod/rdbms/install]$ grep ext *
config_filemap.sbs:# Note that the ordering of the libraries in this file is extremely           #
config_filemap.sbs:## The next row needs to be commented out only for Solaris on EMC storage
config_filemap.sbs:## The next row can be comented out on ALL UNIX PLATFORMS if no other
filemap.ora:# Note that the ordering of the libraries in this file is extremely           #
filemap.ora:## The next row needs to be commented out only for Solaris on EMC storage
filemap.ora:## The next row can be comented out on ALL UNIX PLATFORMS if no other
install.excl:bin/extjob
grep: instantclient: Is a directory
grep: rdbms: Is a directory
rootadd_rdbms.sh:# copy extjobo to extjob if it doesn't exist
rootadd_rdbms.sh:if [ ! -f $ORACLE_HOME/bin/extjob -a -f $ORACLE_HOME/bin/extjobo ]; then
rootadd_rdbms.sh:       $CP -p $ORACLE_HOME/bin/extjobo $ORACLE_HOME/bin/extjob
rootadd_rdbms.sh:if [ -f $ORACLE_HOME/bin/extjob ]; then
rootadd_rdbms.sh:       $CHOWN root $ORACLE_HOME/bin/extjob
rootadd_rdbms.sh:       $CHMOD 4750 $ORACLE_HOME/bin/extjob
rootadd_rdbms.sh:if [ -f $ORACLE_HOME/rdbms/admin/externaljob.ora ]; then
rootadd_rdbms.sh:       $CHOWN root $ORACLE_HOME/rdbms/admin/externaljob.ora
rootadd_rdbms.sh:       $CHMOD 640 $ORACLE_HOME/rdbms/admin/externaljob.ora
rootadd_rdbms.sh:# Bug#25068882: properly set up extproc executable
rootadd_rdbms.sh:if [ -f $ORACLE_HOME/bin/extproc ]; then
rootadd_rdbms.sh:        $CHMOD g+s $ORACLE_HOME/bin/extproc
rootadd_rdbms.sh:if [ -f $ORACLE_HOME/rdbms/admin/externaljob.ora.orig ]; then
rootadd_rdbms.sh:       $RM -f $ORACLE_HOME/rdbms/admin/externaljob.ora.orig
grep: sbs: Is a directory
setup_processor_group.sh:  echo "Next step: create a new proc group using option -create"
oracle@DB-19C-OL8:[/u01/app/oracle/product/19.0.0/dbhome_prod/rdbms/install]$
oracle@DB-19C-OL8:[/u01/app/oracle/product/19.0.0/dbhome_prod/rdbms/install]$ grep jssu *
grep: instantclient: Is a directory
grep: rdbms: Is a directory
rootadd_rdbms.sh:if [ -f $ORACLE_HOME/bin/jssu ]; then
rootadd_rdbms.sh:        $CHOWN root $ORACLE_HOME/bin/jssu
rootadd_rdbms.sh:        $CHMOD 4750 $ORACLE_HOME/bin/jssu
grep: sbs: Is a directory
oracle@DB-19C-OL8:[/u01/app/oracle/product/19.0.0/dbhome_prod/rdbms/install]$
So the scripts are correctly setting file owner and the permissions, but they are NOT setting the file group. It does need to be oinstall (or dba, I suppose), or the database won't be able to read or execute them.
Any good?

Re: Calling shell scripts from dbms_scheduler [message #686468 is a reply to message #686467] Wed, 21 September 2022 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
those 3 files on my working 19
-rwsr-x---. 1 root oinstall 3023080 Sep 12 08:51 extjob
-rwxr-xr-x. 1 oracle oinstall 2335704 Sep 12 08:51 jssu
-rw-r-----. 1 root oinstall 1531 Sep 20 15:41 externaljob.ora
and the not working 12
-rwsr-x---. 1 root oinstall 1630688 Sep 20 13:17 extjob
-rwsr-x---. 1 root oinstall 47064 Sep 20 13:17 jssu
-rw-r-----. 1 root oinstall 1536 Sep 21 10:18 externaljob.ora
jssu is different, but the one that looks wrong is the 19, which works
Re: Calling shell scripts from dbms_scheduler [message #686469 is a reply to message #686468] Wed, 21 September 2022 09:57 Go to previous messageGo to next message
John Watson
Messages: 8766
Registered: January 2010
Location: Global Village
Senior Member
Well, it looks fine, doesn't it. I have only two more suggestions, neither of which strikes me as being relevant.

First, I used to create external jobs by specifying the shell as the executable and the script as an argument. The idea is that a script isn't actuallly on executable file. So like this:
begin
   dbms_scheduler.create_job (job_name    => 'myjob',
                              job_type    => 'executable',
                              job_action  => '/bin/sh',
                              number_of_arguments => 1,
                              auto_drop   => true);
   dbms_scheduler.set_job_argument_value ('myjob', 1, '/home/oracle/script.sh');
   dbms_scheduler.run_job ('myjob');
end;
/ 
Second, how about creating a credential with the oracle u/p and passing it to the job, rather than relying on externaljob.ora?



Re: Calling shell scripts from dbms_scheduler [message #686470 is a reply to message #686469] Wed, 21 September 2022 10:56 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
first - makes no difference

second - don't actually know the oracle password (use sudo for everything) so I created a credential for my application user using:
BEGIN
dbms_scheduler.create_credential(
  credential_name => 'MY_OS_CREDS',
  username        => '<username>',
  password        => '<password>');
END;
/
then copied the file to home/<app user>/training_scripts, made sure it was owned by said user and executable and then ran:

begin
    dbms_scheduler.create_job(
        job_name => 'my_external_job',
        job_type => 'EXECUTABLE',
        job_action => '/home/<app user>/training_scripts/test_script.sh',
        auto_drop => false,
        credential_name => 'MY_OS_CREDS',
        enabled => true);
end;
/

begin
    dbms_scheduler.run_job( 'my_external_job');
end;
/
And got from run_job:
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing login executable failed with status: 13
ORA-27301: OS failure message: Permission denied
ORA-27302: failure occurred at: sjseccel 1
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
ORA-06512: at line 2
Re: Calling shell scripts from dbms_scheduler [message #686471 is a reply to message #686470] Wed, 21 September 2022 11:39 Go to previous messageGo to next message
John Watson
Messages: 8766
Registered: January 2010
Location: Global Village
Senior Member
Right, that is familiar and I think gives me the solution. You need to run the job as the Oracle owner, and I am 100% sure that the oracle Linux password has expired. If you reset it (to anything!) your job might run OK. I've been there before - I'm sure it is something like this.
Re: Calling shell scripts from dbms_scheduler [message #686475 is a reply to message #686471] Thu, 22 September 2022 04:21 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
So the oracle password wasn't even set. I set it and tried running it the way I did in the original post - same error
Re: Calling shell scripts from dbms_scheduler [message #686477 is a reply to message #686475] Thu, 22 September 2022 09:30 Go to previous messageGo to next message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
So I've got it working on a 12.2.0.1.0 with the steps from the original post.
Only difference I can see between the 2 is extjob:
12.1.0.2.0

-rwsr-x---. 1 root oinstall 1630688 Sep 20 13:17 extjob

12.2.0.1.0

-rwsr-x---+ 1 root oinstall 2241816 Jan 22  2018 extjob

bigger file and has that + on the end of the permissions, which I discovered indicates alternate access methods

12.1.0.2.0

getfacl extjob
# file: extjob
# owner: root
# group: oinstall
# flags: s--
user::rwx
group::r-x
other::---


12.2.0.1.0

getfacl extjob
# file: extjob
# owner: root
# group: oinstall
# flags: s--
user::rwx
group::r-x
group:dba:rwx                   #effective:r-x
mask::r-x
other::---
So whether that additional access on the 12.2 is what makes it work I don't know (getfacl output on 19 looks the same as 12.1)
Re: Calling shell scripts from dbms_scheduler [message #686478 is a reply to message #686477] Thu, 22 September 2022 09:37 Go to previous messageGo to next message
John Watson
Messages: 8766
Registered: January 2010
Location: Global Village
Senior Member
Beats me, man. Thanks for posting (what appears to be...) the answer. I'm sure I'm going to hit the problem myself again at some point, and will need it. I have usually hit this issue with external jobs after the SAs have done things like applying the CIS security benchmarks. You have been warned.....
Re: Calling shell scripts from dbms_scheduler [message #686479 is a reply to message #686477] Thu, 22 September 2022 10:20 Go to previous message
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
Senior Member
Right, in the meantime I've come up with an alternative approach - used java inside the DB to run my scripts, and that works on all oracle versions I've tried.

So cheers for the help but I'm good. That said, if anyone does know the solution I'm still curious (though my money says it's a bug and I need a patch).

EDITED TO ADD:

so for clarity the dbms_scheduler approach still isn't working in 12.1 and I still need solution for that version <sighs> but my java stored procedure does the job

[Updated on: Thu, 22 September 2022 10:22]

Report message to a moderator

Previous Topic: Min and Max values of contiguous rows (3 merged)
Next Topic: INDEX hint
Goto Forum:
  


Current Time: Fri Oct 07 05:40:17 CDT 2022