This discussion is archived
1 2 3 5 Previous Next 70 Replies Latest reply: May 28, 2013 3:48 AM by VinodrajJayaraman RSS

Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch files

RnR Pro
Currently Being Moderated
GUIDE TO RUNNING EXTERNAL JOBS ON 10g WITH DBMS_SCHEDULER

NOTE: Users using 11g should use the new method of specifying a credential which eliminates many of the issues mentioned in this note.

This guide covers several common questions and problems encountered when using
dbms_scheduler to run external jobs, either on Windows or on UNIX.

What operating system (OS) user does the job run as ?

External jobs which have a credential (available in 11g) run as the user
specified in the credential. But for jobs without credentials including
all jobs in 10gR1 and 10gR2 there are several cases.

- On UNIX systems, in releases including and after 10.2.0.2 there is a file $ORACLE_HOME/rdbms/admin/externaljob.ora . All external jobs not in the SYS schema and with no credential run as the user and group specified in this file. This should be nobody:nobody by default.

- On UNIX systems, in releases prior to 10.2.0.2 there was no "externaljob.ora" file. In this case all external jobs not in the SYS schema and with no credential run as the owner and group of the $ORACLE_HOME/bin/extjob file which should be setuid and setgid. By default extjob is owned by nobody:nobody except for oracle-xe where it is owned by oracle:oraclegroup and is not setuid/setgid.

- On Windows, external jobs not in the SYS schema and with no credential run as the user that the OracleJobScheduler Windows service runs as. This service must be started before these jobs can run.

- In all releases on both Windows and UNIX systems, external jobs in the SYS schema without a credential run as the oracle user.

What errors are reported in SCHEDULERJOB_RUN_DETAILS views ?

If a job fails, the first place to look for diagnostic information is the SCHEDULERJOB_RUN_DETAILS set of views. In 10gR2 and up the first 200 characters of the standard error stream is included in the additional_info column.

In all releases, the error number returned by the job is converted into a
system error message (e.g. errno.h on UNIX or net helpmsg on Windows) and that
system error message is recorded in the additional info column. If there is no
corresponding message the number is displayed.

In 11g and up the error number returned by the job is additionally recorded in
the error# column. In earlier releases 27369 would always be recorded in the
error# column.

Generic Issues Applicable to UNIX and Windows

- The job action (script or executable) must return 0 or the job run will be marked as failed.

- Always use the full pathname to executables and scripts.

- Do not count on environment variables being set in your job. Make sure that the script or executable that your jobs runs sets all required environment variables including ORACLE_HOME, ORACLE_SID, PATH etc.

- It is not recommended to pass in a complete command line including arguments as the action. Instead it is recommended to pass in only the path to and name of the executable and to pass in arguments as job argument values.

- Scripts with special characters in the execution path or script name may give problems.

- Ensure that the OS user your job runs as has the required privileges/permissions to run your job. See above for how to tell who the job runs as.

- External job actions cannot contain redirection operators e.g. > < >> | && ||

- In general try getting a simple external job working first e.g. /bin/echo or ipconfig.exe on Windows. Also try running the job action directly from the commandline as the OS user that the job will run as.

Windows-specific Issues

- The OracleJobScheduler Windows service must be started before external jobs will run (except for jobs in the SYS schema and jobs with credentials).

- The user that the OracleJobScheduler Windows service runs as must have the "Log on as batch job" Windows privilege.

- A batch file (ending in .bat) cannot be called directly by the Scheduler. Instead cmd.exe must be used and the name of the batch file passed in as an argument. For example
begin
dbms_scheduler.create_job('myjob',
   job_action=>'C:\WINDOWS\SYSTEM32\CMD.EXE',
   number_of_arguments=>3,
   job_type=>'executable', enabled=>false);
dbms_scheduler.set_job_argument_value('myjob',1,'/q');
dbms_scheduler.set_job_argument_value('myjob',2,'/c');
dbms_scheduler.set_job_argument_value('myjob',3,'c:\temp\test.bat');
dbms_scheduler.enable('myjob');
end;
/
- In 10gR1 external jobs that wrote to standard output or standard error streams would sometimes return errors. Redirect to files or suppress all output and error messages when using 10gR1 to run external jobs.

UNIX-specific Issues

- When running scripts, make sure that the executable bit is set.

- When running scripts directly, make sure that the first line of the script in a valid shebang line - starting with "#!" and containing the interpreter for the script.

- In release 10.2.0.1, jobs creating a large amount of standard error text may hang when running (this was fixed in the first 10.2.0.2 patchset). If you are seeing this issue, redirect standard error to a file in your job. This issue has been seen when running the expdp utility which may produce large amounts of standard error text.

- the user that the job runs as (see above section) must have execute access on $ORACLE_HOME/bin and all parent directories. If this is not the case the job may be reported as failed or hang in a running state. For example if your $ORACLE_HOME is /opt/oracle/db then you would have to make sure that

chmod a+rx /opt
chmod a+rx /opt/oracle
chmod a+rx /opt/oracle/db
chmod a+rx /opt/oracle/db/bin

- On oracle-xe, the primary group of your oracle user (if it exists) must be dba before you install oracle-xe for external jobs to work. If you have an oracle user from a regular Oracle installation it may have the primary group set to oinstall.

- On oracle-xe, the extjobo executable is missing so external jobs in the SYS schema will not work properly. This can be fixed by copying the extjob executable to extjobo in the same directory ($ORACLE_HOME/bin).

- Check that correct permissions are set for external job files - extjob and externaljob.ora (see below)

Correct permissions for extjob and externaljob.ora on UNIX

There is some confusion as to what correct permissions are for external job related files.

In 10gR1 and 10.2.0.1 :

- rdbms/admin/externaljob.ora should not exist

- bin/extjob should be setuid and setgid 6550 (r-sr-s---). It should be owned by the user that jobs should run as and by the group that jobs should run as.

- bin/extjobo should have normal 755 (rwxr-xr-x) permissions and be owned by oracle:oraclegroup

In 10.2.0.2 and higher

- rdbms/admin/externaljob.ora file must must be owned by root:oraclegroup and be writable only by the owner i.e. 644 (rw-r--r--) It must contain at least two lines: one specifying the run-user and one specifying the run-group.

- bin/extjob file must be also owned by root:oraclegroup but must be setuid i.e. 4750 (-rwsr-x---)

- bin/extjobo should have normal 755 (rwxr-xr-x) permissions and be owned by oracle:oraclegroup

In 11g and higher

Same as 10.2.0.2 but additionally bin/jssu should exist with root setuid
permissions i.e. owned by root:oraclegroup with 4750 (-rwsr-x---)

Internal Error numbers for UNIX on 10.2.0.2 or 10.1.0.6 or higher

If you are not using a credential and are using version 10.2.0.2 or higher or 10.1.0.6 or higher you may come across an internal error number. Here are the meanings for the internal error numbers.

274661 - can't get owner of or permissions of externaljob.ora file
274662 - not running as root or externaljob.ora file is writable by group or other or externaljob.ora file not owned by root (can't switch user)
274663 - setting the group or effective group failed
274664 - setting the user or effective user failed
274665 - a user or group id was not changed successfully
274666 - cannot access or open externaljob.ora file
274667 - invalid run_user specified in externaljob.ora file
274668 - invalid run_group specified in externaljob.ora file
274669 - error parsing externaljob.ora file
274670 - extjobo is running as root user or group
  • 1. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch files
    547817 Newbie
    Currently Being Moderated
    Thanks Ravi. this is helpful.
    i got a question and a remark.

    is it possible to fetch the standard output from the program (not error message, just standard output) ?

    if i recall correctly calling a batch file from an external job does not require using the full path to CMD.exe, executing standard cmd commands such as COPY and DEL does require the full path.
  • 2. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    RnR Pro
    Currently Being Moderated
    Hi,

    In 11g, using the new method of executing an external job (using a credential) it is possible to retrieve the job output using the dbms_scheduler.get_file command. In 10g or using the old method (without a credential) it is not possible to retrieve the job output and you would need to redirect the output to a file to see it.

    I know that lots of people have seen errors when running batch scripts directly (which went away when using cmd.exe), but I guess it is possible that some Windows configurations allow it.

    Thanks,
    Ravi.
  • 3. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    571582 Newbie
    Currently Being Moderated
    Hi Ravi,

    Can you help me...

    Hi All,

    I planned to create a job to do rman backup daily at 04:00 AM.
    1. I created a program as follows
    BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name => 'rman_backup_prg',
    program_action => '/u02/rmanback/rman.sh',
    program_type => 'EXECUTABLE',
    comments => 'RMAN BACKUP');
    END;
    /

    my rman script is

    #!/usr/bin/ksh
    export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
    export PATH=$PATH:/u01/app/oracle/product/10.2.0/db_1/bin
    /u01/app/oracle/product/10.2.0/db_1/bin/exp rman/cat@catdb file=/u02/rmanback/rm
    an_220108.dmp log=/u02/rmanback/rman_220108.log owner=rman statistics=none comp
    ress=n buffer=400000
    compress *.dmp
    exit

    2. I created a schedule as follows

    BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'rman_backup_schedule',
    start_date => SYSTIMESTAMP,
    end_date => '31-DEC-16 05.00.00 AM',
    repeat_interval => 'FREQ=DAILY; BYHOUR=4',
    comments => 'Every day at 4 am');
    END;
    /

    3. I created ajob as follows.

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'rman_backup_job',
    program_name => 'rman_backup_prg',
    schedule_name => 'rman_backup_schedule',
    enabled=> true,
    auto_drop=> false
    );
    END;
    /

    While I am running the job I am getting the following error anybody help me.

    ORA-27369: job of type EXECUTABLE failed with exit code: Not owner
    ORA-06512: at "SYS.DBMS_ISCHED", line 150
    ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
    ORA-06512: at line 2

    If I removed "compress *.dmp" line in rman script it is working fine.

    /* additional Info from dba_scheduler_job_run_details as follows */
    ORA-27369: job of type EXECUTABLE failed with exit code: Not owner
    STANDARD_ERROR="
    Export: Release 10.2.0.3.0 - Production on Tue Jan 22 14:30:08 2008

    Copyright (c) 1982, 2005, Oracle. All rights reserved.


    Connected to: Oracle Database 10g Release 10.2.0.3.0 - Production
    Export"

    Regards,
    Kiran
  • 4. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    RnR Pro
    Currently Being Moderated
    Hi Kiran,

    Try specifying the complete path to compress e.g. /usr/bin/compress
    Also try cd'ing to the correct directory first
    Also try redirecting all output to a file and then taking a look at the file afterwards to see any errors.

    e.g.

    cd /u02/rmanback
    /usr/bin/compress *.dmp >> /tmp/backup.log

    Hope this helps,
    Ravi.
  • 5. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    571582 Newbie
    Currently Being Moderated
    Hi Ravi,

    Thank you very much.

    Its worked.

    Kiran
  • 6. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    571582 Newbie
    Currently Being Moderated
    Hi Ravi,

    For oracle 10.2.0.1, Is it supports for scheduler jobs with action type 'EXECUTABLE'

    regards
    Kiran
  • 7. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    RnR Pro
    Currently Being Moderated
    Hi,

    Yes external jobs are supported on 10.2.0.1 on all platforms except AIX.

    -Ravi
  • 8. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    445103 Newbie
    Currently Being Moderated
    Hi,

    While working with scheduler and external jobs on 11g I have encoutered error:
    SQL> exec DBMS_SCHEDULER.run_job('FTJOB');
    BEGIN DBMS_SCHEDULER.run_job('FTJOB'); END;

    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel
    Process ID: 31854
    Session ID: 295 Serial number: 20

    - from trace fie:

    Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC8] [PC:0x1218199, Exception [type: SIGSEGV, Address not
    mapped to object] [ADDR:0xC8] [PC:0x1218199, jslve_execute0()+3927]
    Incident 24154 created, dump file: /opt/oracle/diag/rdbms/stat/stat/incident/incdir_24154/stat_ora_31652_i24154.trc
    ORA-07445: exception encountered: core dump [jslve_execute0()+3927] [SIGSEGV] [ADDR:0xC8] [PC:0x1218199] [Address not mapped to object] []

    ssexhd: crashing the process...
    Shadow_Core_Dump = partial

    It sounds like a bug?
    Did anyone else have encoutered similar error?

    Thanks
    goran
  • 9. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    RnR Pro
    Currently Being Moderated
    Hi Goran,

    Yes an Oracle crash is almost always a bug and this does look like a bug. Can you post a test case here and/or file it through your support rep as a bug ?

    Thanks,
    Ravi.
  • 10. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    631010 Newbie
    Currently Being Moderated
    Hi Ravi,

    could you post the correct configuration for 10.0.2.0.3 too?

    I got a little desperate trying to get a simple shell to run. More complex stuff (actually calling sqlldr with a bunch of parameters) will come later on.

    Here's what I try to do:

    begin
    dbms_scheduler.create_job(
    job_name=>'TEST1',
    job_type=>'executable',
    job_action=>'/home/lmpadmin/test.sh',
    enabled=>TRUE
    );
    end;

    test.sh is
    #!/bin/sh
    /bin/echo `/bin/date` >> mist

    Checking status:
    select error#,status,additional_info from DBA_SCHEDULER_JOB_RUN_DETAILS
    where job_name='TEST1';

    Initially all results were:
    27370 FAILED ORA-27370: Job-Unterprozess konnte einen Job vom Typ EXECUTABLE nicht starten
    ORA-27300: BS-abhängiger Vorgang accessing execution agent mit Status: 2 nicht erfolgreich
    ORA-27301: BS-Fehlermeldung: No such file or directory
    ORA-27302: Fehler aufgetreten bei: sjsec 3

    Everything seemed right, the shell exists, permissions are set, no paths used in the shell, etc. After a long search through the web and reading this thread I discovered that bin/extjob was missing. I made the admins copy bin/extjob.nobody and set the permissions you recommended (is that documented anywhere?). Now at least the error message is different, so I suspect the oracle configuration might be misconfigured:

    27369 FAILED ORA-27369: Job vom Typ EXECUTABLE nicht erfolgreich mit Exit-Code: 274668
    STANDARD_ERROR="Oracle Scheduler error: Invalid or missing run_group in configuration file."

    What's the next step to fix the problem? My guess is to mess with the rdbms/admin/externaljob.ora file - but what is expected? I didn't find anything useful on tahiti.

    Thanks,
    Stephan
  • 11. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    631010 Newbie
    Currently Being Moderated
    I found a first solution.

    * In rdbms/admin/externaljob.ora set oracle:dba instead of nobody:nobody.
    * set the s bit in the permissions to this file.

    Now I can execute shells from Oracle. They know nothing, need to set every environment variable by themselves.

    Important question: Did I open a security hole? If yes, how to close it again without losing the functionality?

    Stephan
  • 12. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    RnR Pro
    Currently Being Moderated
    Hi,

    "invalid or missing run-group" means that your externaljob.ora file is missing the line to specify what group to run jobs as. The line should look like

    run_group = nobody

    The s bit does not have to be set on externaljob.ora but it must be owned by root and have permissions set to 640 (rw-r----) .

    Yes you do have to set all environment variables since they are cleared out.

    By setting run-user and run-group to oracle:dba you are opening up a slight hole. However, if you make sure that only privileged users are granted the "CREATE EXTERNAL JOB" system privilege then you are safe. What you have done here basically allows anyone with CREATE EXTERNAL JOB system privilege to have SYSDBA access. This may be permissible in your case or not.

    Hope this helps,
    Ravi.
  • 13. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    413063 Newbie
    Currently Being Moderated
    I am confused about the correct method when I want to call a batch program from a PROGRAM rather than from a job.

    For example, I can use:

    DBMS_SCHEDULER.CREATE_PROGRAM
    (
    'ArchLogRMANBackup',
    'EXECUTABLE',
    'c:\Windows\System32\CMD.EXE' ,
    3,
    TRUE,
    'Runs a compressed RMAN backup of all the archive logs generated by this process'
    );

    However, Then there is no way for me to the equivalent of:

    dbms_scheduler.set_job_argument_value('ArchLogRMANBackup',1,'/q');
    dbms_scheduler.set_job_argument_value('ArchLogRMANBackup',2,'/c');
    dbms_scheduler.set_job_argument_value('ArchLogRMANBackup',3,'D:\Oracle\Admin\DTOD\Work\RMAN_ARCH_OPEN
    DOR.bat');

    at the program level. This interface only exists at the job level. In other words, there is no set_program_argument_value interface. How do I do this?

    I need for this to run as part of a chain and so, it needs to be a program and not a job.

    Thanks,

    -Joe
  • 14. Re: Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch fi
    RnR Pro
    Currently Being Moderated
    Hi,

    The way to do this is to have a program with 3 arguments and define each of the arguments with a default value. After you have defined the arguments you can enable the program e.g.
    begin
    dbms_scheduler.define_program_argument('ArchLogRMANBackup',1,null,'varchar2','/q');
    dbms_scheduler.define_program_argument('ArchLogRMANBackup',2,null,'varchar2','/c');
    dbms_scheduler.define_program_argument('ArchLogRMANBackup',3,null,'varchar2',
            'D:\Oracle\Admin\DTOD\Work\RMAN_ARCH_OPENDOR.bat');
    dbms_scheduler.enable('ArchLogRMANBackup');
    end;
    /
    You can then use the program (which must have default values for all aarguments) in chain steps.

    Hope this helps,
    Ravi.
1 2 3 5 Previous Next