Run shell script via dbms_scheduler

Run Shell script via Oracle Job



SQL> begin
dbms_scheduler.create_job
(
job_name => 'EXP_DATAPUMP_FULL_BACKUP',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/ksh',
start_date =>sysdate+ 1/288 ,
number_of_arguments => 1,
repeat_interval => 'freq=daily; byhour=16; byminute=0; bysecond=0',
enabled => false,
comments => 'Oracle Export Data Pump Backup' );
end;
/

PL/SQL procedure successfully completed.


SQL> exec dbms_scheduler.set_job_argument_value(job_name=>'EXP_DATAPUMP_FULL_BACKUP',argument_position=>1,argument_value=>'/home/oracle/Oracle_Datapump/exp_pump.sh') ;

PL/SQL procedure successfully completed.


run this job manually

BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'EXP_DATAPUMP_FULL_BACKUP');
END;



SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
BSLN_MAINTAIN_STATS_JOB TRUE 48
DRA_REEVALUATE_OPEN_FAILURES TRUE 281
EXP_DATAPUMP_FULL_BACKUP FALSE 0 <<<<<<<--------
FGR$AUTOPURGE_JOB FALSE 0
FILE_WATCHER FALSE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 296
PURGE_LOG TRUE 296
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 294
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 294
XMLDB_NFS_CLEANUP_JOB FALSE 0



BEGIN
DBMS_SCHEDULER.enable (NAME => 'EXP_DATAPUMP_FULL_BACKUP');
END;
/



to disable

BEGIN
DBMS_SCHEDULER.disable (NAME => 'EXP_DATAPUMP_FULL_BACKUP', FORCE=> TRUE);
END;
/



SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
BSLN_MAINTAIN_STATS_JOB TRUE 48
DRA_REEVALUATE_OPEN_FAILURES TRUE 281
EXP_DATAPUMP_FULL_BACKUP TRUE 0 <<<<<<<<<<<<<-------------
FGR$AUTOPURGE_JOB FALSE 0
FILE_WATCHER FALSE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 296
PURGE_LOG TRUE 296
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 294
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 294
XMLDB_NFS_CLEANUP_JOB FALSE 0

11 rows selected.


TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_LOG)'
COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL operation FORMAT A12 HEADING 'Operation'
COL status FORMAT A12 HEADING 'Step|Status'
COL condition FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,operation
,status
,TO_CHAR(additional_info) condition
FROM dba_scheduler_job_log
WHERE 1=1
-- and owner = 'ANUJ'
AND log_date > (SYSDATE - 1/12)
ORDER BY log_id DESC;


COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;




COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;

===========================




cat /aptus/oracle/admin/Scripts/Exp_Datapump1.sh

# !/bin/bash
TODAY=`date`
ORACLE_SID=aptdb ; export ORACLE_SID
ORACLE_BASE=/aptus/oracle ; export ORACLE_BASE
ORACLE_HOME=/aptus/oracle/product/11.2.0/db_1 ; export ORACLE_HOME
PATH=${ORACLE_HOME}/bin:$PATH ; export PATH
EXPFILE=$ORACLE_SID.datapump.`date +%d%m%Y`.dmp; export EXPFILE
ERRFILE=$ORACLE_SID.datapump.`date +%d%m%Y`.err; export ERRFILE
LOGFILE=$ORACLE_SID.datapump.`date +%d%m%Y`.log ; export LOGFILE
SUCC=/aptus/db-backup/OracleExpBackup/SUCC.txt ; export SUCC

# echo $EXPFILE

find /aptus/db-backup/OracleExpBackup -name "*.err" -ctime +1 -exec rm {} \;
find /aptus/db-backup/OracleExpBackup -name "*.dmp*" -ctime +1 -exec rm {} \;
find /aptus/db-backup/OracleExpBackup -name "*.log*" -ctime +1 -exec rm {} \;
find /aptus/db-backup/OracleExpBackup -name "*.dmp*" -ctime +1 -exec rm {} \;
expdp "'/ as sysdba'" directory=aptusbackup DUMPFILE=$EXPFILE full=y logfile=$LOGFILE EXCLUDE=STATISTICS parallel=4
exit ;
==========




metalink ref


Window issues:
Note 742683.1 Scheduled Job Works Does Not Start In Window.

Purging Diagnostics
Note 443364.1 How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG
Note 749440.1 Dbms_scheduler.Purge Not Removing Entries from dba_scheduler_job_run_details

Schedule issues:
Note 312547.1 Advanced use of DBMS_JOB.SUBMIT �interval� Parameter
Note 428872.1 Scheduled Jobs Do Not Run After A Re-Start With Repeat_interval => Null.

Issues with Statistic Jobs:
Note 430636.1 GATHER_STATS_JOB Is Not Running
Note 377143.1 How to check what automatic statistics collection is scheduled on 10g
Note 803191.1 Checklist To Diagnose Issues Related To Automatic Run Of
Job GATHER_STATS_JOB

Issues with external Jobs:

http://forums.oracle.com/forums/thread.jspa?threadID=555102

NOTE:389685.1 Items to Check when Problems Running Executables through the Scheduler
NOTE:976049.1 DBMS_SCHEDULER fails with ORA-27369 and
STANDARD_ERROR= Launching external job failed: Invalid username or password
NOTE:1300215.1 ORA-27369: job of type EXECUTABLE failed with exit code: 274667

Please note that this article will guide through the correct analysis for Scheduler/Job Problems.

It is also practicable to go through a list of known issues / bugs which will be listed in
NOTE:1311355.1 Known Issues And Bugs With Using the Scheduler
This article is currently under edit and will be made publish in the near future


these file should be there



oracle@apt-amd-02:~/Oracle_Datapump> ls -ltr $ORACLE_HOME/rdbms/admin/externaljob.ora
-rw-r----- 1 root oinstall 1534 2005-12-21 12:30 /opt/app/oracle/product/11.2/rdbms/admin/externaljob.ora



SQL> !ls -ltr $ORACLE_HOME/bin/extjob
-rwsr-x--- 1 root oinstall 1232563 2011-01-10 15:16 /opt/app/oracle/product/11.2/bin/extjob