Note: this Demonstration using when you have not executes scripts via the OS cron Job then using Oracle Scheduler One Advantage of Oracle Scheduler Getting a Log & Status of Schedules.
1. METHOD With Create Job
execute shell script USING DBMS_SCHEDULER
--- create shell scrip as below
[oracle@server1 ~]$ cat vmstat.sh
#!/bin/bash
/usr/bin/vmstat 2 10 >> /home/oracle/x.txt
--------create job of VMSTAT, set arguments and enable
begin
dbms_scheduler.create_job
(
job_name => 'My_job',
job_type => 'EXECUTABLE',
job_action => '/bin/bash',
start_date =>sysdate ,
number_of_arguments => 1,
repeat_interval => 'FREQ=secondly; INTERVAL=5',
enabled => false,
comments => 'shell script test'
);
dbms_scheduler.set_job_argument_value(job_name=>'My_job',
argument_position=>1, argument_value=>'/home/oracle/vmstat.sh');
dbms_scheduler.enable(name=>'My_job');
end;
/
--------Schedule DD VIEW
select * from dba_scheduler_job_run_details x where
x.job_name='MY_JOB';
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;
select LOG_ID,LOG_DATE,OWNER,STATUS from
DBA_SCHEDULER_JOB_LOG where job_name='MY_JOB' order by 2;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
My Small Simple Demonstrations
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[oracle@server1 ~]$ cat script.sh
#!/bin/bash
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export
ORACLE_HOME
ORACLE_SID=sarathi; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export
LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect sys/jpdldimts53 as sysdba
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/analyzetable.sql
exit;
EOF
----------SQL SCRIPT analyzetable.sql
spool /home/oracle/tanalyze.out
Analyze table HR.ABBREV Validate Structure cascade;
Analyze table TEST.ACCOUNT Validate Structure cascade;
Analyze table TEST.ACCOUNT_CANCEL Validate Structure
cascade;
Analyze table TEST.ACCOUNT_FEE_PERIOD Validate Structure
cascade;
Analyze table HR.ACTION Validate Structure cascade;
Analyze table TEST.ACTION Validate Structure cascade;
Analyze table TEST.ACTION_HEAD Validate Structure cascade;
Analyze table TEST.ACTION_HEAD_DESC Validate Structure
cascade;
Analyze table TEST.ACTION_VCH_CATG Validate Structure
cascade;
Analyze table TEST.ADD_HIST Validate Structure cascade;
Analyze table HR.ADMINMAST Validate Structure cascade;
Analyze table HR.ADMINTEMP Validate Structure cascade;
Analyze table HR.ADMINTLOG Validate Structure cascade;
Analyze table TEST.ALLTABS Validate Structure cascade;
Analyze table HR.ANSWERBANK Validate Structure cascade;
Analyze table HR.APPLICANT Validate Structure cascade;
spool off;
-------Create Jobs------
begin
dbms_scheduler.create_job
(
job_name => 'My_job',
job_type => 'EXECUTABLE',
job_action => '/bin/bash',
start_date =>sysdate ,
number_of_arguments => 1,
repeat_interval=>'FREQ=DAILY;BYHOUR=11;BYMINUTE=35',
enabled => false,
comments => 'shell script test'
);
dbms_scheduler.set_job_argument_value(job_name=>'My_job',
argument_position=>1, argument_value=>'/home/oracle/script.sh');
dbms_scheduler.enable(name=>'My_job');
end;
/
--------Schedule DD VIEW-----------
select * from dba_scheduler_job_run_details x where
x.job_name='MY_JOB';
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;
select LOG_ID,LOG_DATE,OWNER,STATUS from
DBA_SCHEDULER_JOB_LOG where job_name='MY_JOB' order by 2;
2. METHOD With Program
----- DB Verify Script
Script Name : dbv_on_all_files.sh
#!/bin/bash
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export
ORACLE_HOME
ORACLE_SID=sarathi; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export
LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH
dbv file=/u01/app/oracle/oradata/system01.dbf
logfile=/u01/file1.log blocksize=8192
dbv file=/u01/app/oracle/oradata/undotbs01.dbf
logfile=/u01/file2.log blocksize=8192
dbv file=/u01/app/oracle/oradata/sysaux01.dbf
logfile=/u01/file3.log blocksize=8192
dbv file=/u01/app/oracle/oradata/users01.dbf
logfile=/u01/file4.log blocksize=8192
dbv file=/u03/datafs/bio01.dbf logfile=/u01/file5.log
blocksize=8192
dbv file=/u02/datafs/imagestsp01.dbf logfile=/u01/file6.log
blocksize=8192
dbv file=/u03/datafs/SARA.DBF logfile=/u01/file7.log
blocksize=8192
dbv file=/u01/app/oracle/oradata/sarathi_sc
logfile=/u01/file8.log blocksize=8192
dbv file=/u02/datafs/sara01 logfile=/u01/file9.log
blocksize=8192
dbv file=/u03/datafs/sara02.dbf logfile=/u01/file10.log
blocksize=8192
dbv file=/u01/app/oracle/oradata/sara03.dbf
logfile=/u01/file11.log blocksize=8192
dbv file=/u04/datafs/SARI.DBF logfile=/u01/file12.log
blocksize=8192
dbv file=/u04/datafs/SARIN.DBF logfile=/u01/file13.log
blocksize=8192
dbv file=/u02/datafs/STAL.DBF logfile=/u01/file14.log
blocksize=8192
dbv file=/u03/datafs/users02.dbf logfile=/u01/file15.log
blocksize=8192
dbv file=/u04/rcdata/vah.dbf logfile=/u01/file16.log
blocksize=8192
----- CREATE A PROGRAM
begin
dbms_scheduler.create_program
(
program_name => 'db_verify',
program_type => 'EXECUTABLE',
program_action =>
'/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/dbv_on_all_files.sh',
number_of_arguments => 0,
enabled => TRUE,
comments => 'Check the Time'
);
end;
/
------ CREATE A SCHEDULE
begin
dbms_scheduler.create_schedule
(
schedule_name => 'EVERY_12_HOURS',
start_date=> SYSDATE,
repeat_interval=>'FREQ=DAILY;BYHOUR=12;BYMINUTE=45',
/*repeat_interval => 'FREQ=HOURLY; INTERVAL=12?,*/
comments => 'Every 12 Hours'
);
end;
/
-- Display the schedule details.
SELECT owner, schedule_name FROM dba_scheduler_schedules;
OWNER
SCHEDULE_NAME
------------------------------
------------------------------
SYS
DAILY_PURGE_SCHEDULE
SYS
TEST_HOURLY_SCHEDULE
-- DROP_SCHEDULE
BEGIN
DBMS_SCHEDULER.drop_schedule (schedule_name =>
'TEST_HOURLY_SCHEDULE');
END;
/
----- CREATE A JOB
begin
dbms_scheduler.create_job
(
job_name => 'My_job',
schedule_name => 'EVERY_12_HOURS',
program_name => 'db_verify',
enabled => TRUE,
comments => 'shell script test'
);
end;
/
col PROGRAM_ACTION for a80
select OWNER,PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION,ENABLED
from DBA_SCHEDULER_PROGRAMS;
select LOG_ID,LOG_DATE,OWNER,STATUS from
DBA_SCHEDULER_JOB_LOG where job_name='MY_JOB' order by 2;
select OWNER,JOB_NAME,JOB_TYPE,STATE from
dba_scheduler_jobs where job_name='MY_JOB';
col log_date for a20
col job_name for a20
col additional_info for a50
select log_id, log_date, job_name, status, error#,
additional_info
from dba_scheduler_job_run_details
where job_name='MY_JOB' order by log_date;
----- RUN THE JOB MANUALLY
1. exec dbms_scheduler.run_job('RUN_UPDATE_DB_VERIFY');
2. BEGIN -- Run job synchronously.
DBMS_SCHEDULER.run_job (job_name =>
'test_full_job_definition', use_current_session => TRUE);
-- STOP JOBS
DBMS_SCHEDULER.stop_job (job_name =>
'test_full_job_definition, test_prog_sched_job_definition');
END;
/
-----Check the job run status and other details
select log_id, log_date, job_name, status, error#,
additional_info
from dba_scheduler_job_run_details
where job_name like 'RUN_UPDATE_DB_VERIFY';
Enjoy ........... :-)
RELATED TOPIC:
http://oracleeducom.blogspot.in/2014/07/enable-disable-scheduled-job.html
No comments:
Post a Comment