SPOOL verify_scheduler_changes.log
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "START_TIME"
FROM DUAL;
--
PROMPT '*****************************************************************';
--
DEFINE TEST='CHECK Scheduler Jobs Creation and various status';
--
PROMPT 'Verify: &TEST'
PROMPT 'User: &_USER'
PROMPT 'Database: &_CONNECT_IDENTIFIER';
PROMPT '**** CHECK Scheduler Jobs Creation and various status Report ****';
PROMPT '*****************************************************************';
--
SET linesize 157;
--
PROMPT ' '
PROMPT '**** Query Number 1 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) Whether the scheduler jobs have been created successfully.'
PROMPT ' b) All the jobs should be in enabled state i.e. ENABLED=TRUE. If it is not then the job would probably not run on the schedule date/time'
PROMPT ' c) Check the state column, it should be 'SCHEDULED''
PROMPT ' d) Check the last_start_date, the next_run_date to verify whether the job ran at the scheduled time.'
PROMPT ' e) Check the failure_count, ideally it should be zero. If not then use query # 2 or 3 to check why the job failed.'
PROMPT ' f) Check the run_count column to see how many times the job ran.'
--
SELECT job_name
,enabled
,state
,run_count
,failure_count
,rpad(last_start_date,39) AS "LAST_START_DATE"
,rpad(next_run_date,39) AS "NEXT_RUN_DATE"
FROM user_scheduler_jobs
ORDER BY job_name;
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 2 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) Whether each job ran at the requested scheduled time.'
PROMPT ' b) Check the actual start date.'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
--
SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
,job_name
,status
,to_char(req_start_date, 'DD-MON-YY HH24:MI:SS') AS "REQ_START_DATE"
,to_char(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS "ACTUAL_START_DATE"
,rpad(run_duration,25) AS "JOB_RUN_DURATION"
FROM user_scheduler_job_run_details
ORDER BY log_date DESC;
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 3 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) How many jobs are in failed or broken status.'
PROMPT ' b) Check the additional_info column to check why the job failed'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
COLUMN additional_info word_wrapped
--
SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
,job_name
,status
,additional_info
FROM user_scheduler_job_run_details
WHERE status != 'SUCCEEDED'
ORDER BY log_date DESC;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 4 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) Whether all the schedules have been created.'
PROMPT ' b) Start date of each schedule should match with the create scripts.'
PROMPT ' '
--
COLUMN schedule_name FORMAT A30
COLUMN repeat_interval FORMAT A50
COLUMN comments FORMAT A55 word_wrapped
SELECT schedule_name
,to_char(start_date, 'DD-MON-YY HH24:MI:SS') AS "START_DATE"
,repeat_interval
,comments
FROM user_scheduler_schedules
ORDER BY schedule_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 5 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) Whether all the programs have been created.'
PROMPT ' b) Program action for each program, this tells us what package/procedure is been referenced.'
PROMPT ' c) All the programs should be in Enables state'
PROMPT ' '
--
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A60 word_wrapped
COLUMN comments FORMAT A55 word_wrapped
--
SELECT program_name
,program_action
,enabled
,comments
FROM user_scheduler_programs
ORDER BY program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 6 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) This query will show all the programs which are not in enabled state. Ideally you should see no rows.'
PROMPT ' b) All the programs should be in Enables state'
PROMPT ' '
--
SELECT program_name
,program_action
,enabled
,comments
FROM user_scheduler_programs
WHERE enabled != 'TRUE'
ORDER BY program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 7 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) How many dbms_jobs are still running and their function as defined in the "what column"'
PROMPT ' b) Ideally there should not be any dbms_jobs in the critical DB and there should be one dbms_job on the log DB to delete the event log'
PROMPT ' tables. This would be soon removed once the partitioning changes are checked in.'
PROMPT ' c) If you find dbms_jobs in the critical then all of them should be removed by running remove_all_user_jobs.sql (checkd in and part of 9A kit)'
PROMPT ' This script is not part of the rebuild script as it had to be run only once.'
PROMPT ' d) Once the partition maintenance code is part of the kit, then the dbms_job on the log side has to be removed.'
PROMPT ' '
--
COLUMN job FORMAT 9999
COLUMN what word_wrapped
--
SELECT job
,what
FROM user_jobs;
--
PROMPT ' '
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "END_TIME"
FROM DUAL;
--
PROMPT '***** END Of Report *****'
--
SPOOL OFF;
QUIT
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "START_TIME"
FROM DUAL;
--
PROMPT '*****************************************************************';
--
DEFINE TEST='CHECK Scheduler Jobs Creation and various status';
--
PROMPT 'Verify: &TEST'
PROMPT 'User: &_USER'
PROMPT 'Database: &_CONNECT_IDENTIFIER';
PROMPT '**** CHECK Scheduler Jobs Creation and various status Report ****';
PROMPT '*****************************************************************';
--
SET linesize 157;
--
PROMPT ' '
PROMPT '**** Query Number 1 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) Whether the scheduler jobs have been created successfully.'
PROMPT ' b) All the jobs should be in enabled state i.e. ENABLED=TRUE. If it is not then the job would probably not run on the schedule date/time'
PROMPT ' c) Check the state column, it should be 'SCHEDULED''
PROMPT ' d) Check the last_start_date, the next_run_date to verify whether the job ran at the scheduled time.'
PROMPT ' e) Check the failure_count, ideally it should be zero. If not then use query # 2 or 3 to check why the job failed.'
PROMPT ' f) Check the run_count column to see how many times the job ran.'
--
SELECT job_name
,enabled
,state
,run_count
,failure_count
,rpad(last_start_date,39) AS "LAST_START_DATE"
,rpad(next_run_date,39) AS "NEXT_RUN_DATE"
FROM user_scheduler_jobs
ORDER BY job_name;
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 2 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) Whether each job ran at the requested scheduled time.'
PROMPT ' b) Check the actual start date.'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
--
SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
,job_name
,status
,to_char(req_start_date, 'DD-MON-YY HH24:MI:SS') AS "REQ_START_DATE"
,to_char(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS "ACTUAL_START_DATE"
,rpad(run_duration,25) AS "JOB_RUN_DURATION"
FROM user_scheduler_job_run_details
ORDER BY log_date DESC;
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 3 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) How many jobs are in failed or broken status.'
PROMPT ' b) Check the additional_info column to check why the job failed'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
COLUMN additional_info word_wrapped
--
SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
,job_name
,status
,additional_info
FROM user_scheduler_job_run_details
WHERE status != 'SUCCEEDED'
ORDER BY log_date DESC;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 4 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) Whether all the schedules have been created.'
PROMPT ' b) Start date of each schedule should match with the create scripts.'
PROMPT ' '
--
COLUMN schedule_name FORMAT A30
COLUMN repeat_interval FORMAT A50
COLUMN comments FORMAT A55 word_wrapped
SELECT schedule_name
,to_char(start_date, 'DD-MON-YY HH24:MI:SS') AS "START_DATE"
,repeat_interval
,comments
FROM user_scheduler_schedules
ORDER BY schedule_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 5 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) Whether all the programs have been created.'
PROMPT ' b) Program action for each program, this tells us what package/procedure is been referenced.'
PROMPT ' c) All the programs should be in Enables state'
PROMPT ' '
--
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A60 word_wrapped
COLUMN comments FORMAT A55 word_wrapped
--
SELECT program_name
,program_action
,enabled
,comments
FROM user_scheduler_programs
ORDER BY program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 6 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) This query will show all the programs which are not in enabled state. Ideally you should see no rows.'
PROMPT ' b) All the programs should be in Enables state'
PROMPT ' '
--
SELECT program_name
,program_action
,enabled
,comments
FROM user_scheduler_programs
WHERE enabled != 'TRUE'
ORDER BY program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT '**** Query Number 7 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT ' a) How many dbms_jobs are still running and their function as defined in the "what column"'
PROMPT ' b) Ideally there should not be any dbms_jobs in the critical DB and there should be one dbms_job on the log DB to delete the event log'
PROMPT ' tables. This would be soon removed once the partitioning changes are checked in.'
PROMPT ' c) If you find dbms_jobs in the critical then all of them should be removed by running remove_all_user_jobs.sql (checkd in and part of 9A kit)'
PROMPT ' This script is not part of the rebuild script as it had to be run only once.'
PROMPT ' d) Once the partition maintenance code is part of the kit, then the dbms_job on the log side has to be removed.'
PROMPT ' '
--
COLUMN job FORMAT 9999
COLUMN what word_wrapped
--
SELECT job
,what
FROM user_jobs;
--
PROMPT ' '
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "END_TIME"
FROM DUAL;
--
PROMPT '***** END Of Report *****'
--
SPOOL OFF;
QUIT
