Skip to Main Content
  • Questions
  • Not able to run DBMS_SCHEDULER jobs on Shell Scripts from any user other than SYS

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, FEMY.

Asked: October 28, 2020 - 9:16 pm UTC

Answered by: Connor McDonald - Last updated: November 05, 2020 - 12:49 am UTC

Category: PL/SQL - Version: 18c

Viewed 100+ times

You Asked

Hello Team,
Greetings!

I tried to run dbms_scheduler jobs (executable) from schema other than SYS and it is failing and not able to see the error for the failure. But when I run the same job from SYS user it is succeeding. Please help me to resolve this issue.

Thanks
Femy Mellwin

and we said...

Without much detail, its hard to say, but at a guess

1) you might be missing privileges

grant create job, create external job to YOUR_USER;


2) you might be missing a credential

exec dbms_scheduler.create_credential('YOUR_USER.LOCAL_ORACLE','oracle','*****');


3) making sure you pass the credential

  dbms_scheduler.create_job(
     job_name=>'YOUR_USER.MY_SHELL_SCRIPT',
     job_type=>'EXECUTABLE',
     job_action=>'/home/oracle/my_script.sh',
     enabled=>false
     );

  DBMS_SCHEDULER.set_attribute('YOUR_USER.MY_SHELL_SCRIPT', 'credential_name', 'LOCAL_ORACLE');
  DBMS_SCHEDULER.enable('YOUR_USER.MY_SHELL_SCRIPT');



and you rated our response

  (9 ratings)

Reviews

Review on ask tom's reply

November 02, 2020 - 6:43 am UTC

Reviewer: FEMY MELLWIN from India

Hello Team,

I followed as per mentioned in the reply still my issue is not resolved.

Regards
Femy Mellwin

Still my job is failing

November 02, 2020 - 7:04 am UTC

Reviewer: FEMY MELLWIN from India

Hello Connor,
I followed the steps you have mentioned and still the job is failing.

Codes I followed mentioned below

exec dbms_scheduler.create_credential('TESTUSER.LOCAL_ORACLE','oracle','Password');

begin
  dbms_scheduler.create_job(
     job_name=>'TESTUSER.TEST_SHELL_SCRIPT_002',
     job_type=>'EXECUTABLE',
     job_action=>'/home/oracle/upload_files/test.sh',
     enabled=>false
     );
end; 

exec DBMS_SCHEDULER.set_attribute('TESTUSER.TEST_SHELL_SCRIPT_002', 'credential_name', 'TESTUSER.LOCAL_ORACLE'); 
 

exec DBMS_SCHEDULER.enable('TESTUSER.TEST_SHELL_SCRIPT_002');  



SQL> select status from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='TEST_SHELL_SCRIPT_002';

STATUS
------------------------------
FAILED

$ cat test.sh

#!/bin/bash
echo 11111 > /home/oracle/upload_files/test1.log

Please let me know if anything I should check..

Regards
Femy Mellwin
Connor McDonald

Followup  

November 03, 2020 - 1:59 am UTC

show us

ls -l /home/oracle/upload_files/test.sh
ls -ld /home/oracle/upload_files

and fully qualify your echo as well (it could be a path issue), ie

/bin/echo 11111 > /home/oracle/upload_files/test1.log

You shud provide all the details

November 02, 2020 - 7:31 am UTC

Reviewer: A reader


How do you expect someone (anyone) to help when you have not provided any detail?

What are you running? What is the error message, etc.?

Shell script not getting executed using dbms_scheduler.create_job for users other than SYS

November 02, 2020 - 9:37 am UTC

Reviewer: FEMY MELLWIN from India

Hi Reader,

I have given all details:

I can explain the issue once again.

I have a shell script (test.sh)

I tried running it using the below code from SYS user and it ran

begin
  dbms_scheduler.create_job(
     job_name=>'TEST_SHELL_SCRIPT_002',
     job_type=>'EXECUTABLE',
     job_action=>'/home/oracle/upload_files/test.sh',
     enabled=>true
     );
end;


But when I tried from any other schema it is not running and I can see the status as Failed. I am not able to get the real error. If you are aware where to check the error, please given me.

As per Connor's reply, I created credential and tried too. Still the job is failing.

I am a new user to this forum. May be I am not good to post the detail clearly.

Regards
Femy Mellwin
Connor McDonald

Followup  

November 03, 2020 - 2:00 am UTC

and did you do all the grants ?

Requested information given below:

November 03, 2020 - 5:15 am UTC

Reviewer: FEMY MELLWIN from India

Hi Connor & Team,

ls -ld upload_files
drwxrwxrwx. 2 oracle dba 100 Nov  2 07:03 upload_files

 ls -l test.sh
-rwxrwxrwx. 1 oracle dba 68 Nov  2 05:42 test.sh



I even modified the code as given below

#!/bin/bash
/bin/echo 11111 > /home/oracle/upload_files/test1.log

SQL> select status from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='TEST_SHELL                                                                             _SCRIPT_003';

STATUS
------------------------------
FAILED



Thanks Connor!

Regards
Femy Mellwin

Regarding grants:

November 03, 2020 - 5:34 am UTC

Reviewer: FEMY MELLWIN from India

Hi Connor,

I have given the below grants

GRANT CREATE JOB, CREATE EXTERNAL JOB TO TESTUSER;


I have a question:

Is it mandatory that this path should be a database directory?

If yes, I have even tried that using the below code:

From SYS user:
grant create directory to TESTUSER;
GRANT CREATE JOB, CREATE EXTERNAL JOB TO TESTUSER;
GRANT SCHEDULER_ADMIN TO TESTUSER;
create directory UPLOAD_FILES  as '/home/oracle/upload_files';
GRANT READ, WRITE ON DIRECTORY UPLOAD_FILES TO TESTUSER;


Thanks Connor!

Regards
Femy Mellwin

Got ORA error by logging:

November 03, 2020 - 5:50 am UTC

Reviewer: FEMY MELLWIN from India

Hi Connor,

Somehow I could get the error by enabling logging:

EXTERNAL_LOG_ID="job_85932_101060",
ORA-27369: job of type EXECUTABLE failed with exit code: 5 Login executable not setuid-root
STANDARD_ERROR="Launching external job failed: Login executable not setuid-root"


Regards
Femy Mellwin
Connor McDonald

Followup  

November 04, 2020 - 12:18 am UTC

Ah....your Oracle software is not installed correctly. On my database:

[oracle@db20 bin]$ cd $ORACLE_HOME/bin
[oracle@db20 bin]$ ls -l | grep root
-rwxr-xr-x. 1 oracle oinstall       945 Jun 20  2016 acfsroot
-rwxr-xr-x. 1 oracle oinstall      1000 Jun 20  2016 afdroot
-rwsr-x---. 1 root   oinstall   3080744 Mar 30  2020 extjob
-rwsr-x---. 1 root   oinstall   2334824 Mar 30  2020 jssu
-rwxr-xr-x. 1 oracle oinstall       940 Jun 20  2016 okaroot
-rwxr-xr-x. 1 oracle oinstall      1007 Jun 20  2016 olfsroot
-rwxr-xr-x. 1 oracle oinstall      1616 Mar  4  2020 rootPreRequired.sh


Notice that some binaries are owned by root.

In your installation, I would suspect that someone did not run the "root.sh" script that is run after the software installation.

root.sh was not run

November 04, 2020 - 3:08 am UTC

Reviewer: A reader

Hi Connor,
Thanks!

As you mentioned root.sh was not ran and it was missed out. Now after changing the permission to root I am able to run the shell script, but there is an issue

for every command I am using, I am forced to give the bin path

e.g
/bin/echo
..../bin/sqlplus
etc.

and few commands like
cd
not working even when I gave as /bin/cd

how can I resolve this Connor? can I do something inside the script, so that every binary is picked without mentioning the full binary path? Please advise and guide me

Thanks again!

Regards
Femy Mellwin
Connor McDonald

Followup  

November 04, 2020 - 3:27 am UTC

The path is left unset for security reasons (to avoid anyone injecting or taking advantage of their own path binaries).

So just proceed as per normal shell scripting. At the start of your script:

PATH=/bin:/usr/bin: ........
export PATH

Script is working!

November 04, 2020 - 4:17 am UTC

Reviewer: FEMY MELLWIN from India

Hi Connor,
Thanks a lot for your patience and your promptness in solving my issue.

I have set the path and the script is working now without providing the full path of the binary.

@Hi all!

Thanks for your support and we can close this issue as it is resolved.

Issue Diagnosed:
Few binaries ownership was oracle and not root
Steps I followed to resolve:
Executed root.sh

Regards
Femy Mellwin
Connor McDonald

Followup  

November 05, 2020 - 12:49 am UTC

Glad we could help

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database