scheduler


http://facedba.blogspot.com/2014/05/dbmsscheduler-jobs-are-not-running-case.html

On Oracle Database, How DBAs can check broken job for Oracle Job ?



 DBAs can check on *_SCHEDULER_JOBS.STATE column.

select job_name, state,enabled, failure_count, max_failures from user_scheduler_jobs;


When does Oracle Scheduler change STATE to be BROKEN? Then, DBAs should know some columns as well.?

FAILURE_COUNT NUMBER Number of times the job has failed to run
MAX_FAILURES NUMBER Number of times the job will be allowed to fail before being marked broken



*_SCHEDULER_JOBS.STATE column will change to "BROKEN", when *_SCHEDULER_JOBS.FAILURE_COUNT value = _SCHEDULER_JOBS.MAX_FAILURES value.

Now!  *_SCHEDULER_JOBS.STATE = "BROKEN". How to fix "BROKEN" state? - Just enable Job.
SQL> EXEC DBMS_SCHEDULER.enable(name=> 'test_my_job');



How to unset MAX_FAILURES value?

exec DBMS_SCHEDULER.set_attribute (name => 'test_my_job', attribute=> 'max_failures',value => '');

Manually calling the job

 exec DBMS_SCHEDULER.run_job('owner.jobname');



SQL> select value from v$parameter where name='job_queue_processes';
Then check the number of running jobs
SQL> select count(*) from dba_scheduler_running_jobs;
SQL> select count(*) from dba_jobs_running;


--scheduled job run details--------------------
SQL> set linesize 800
SQL> select LOG_ID,LOG_DATE,OWNER,JOB_NAME,STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,RUN_DURATION,SESSION_ID,CPU_USED,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS;
--------------------------------------------------

--scheduled job logs------

SQL> select LOG_ID,LOG_DATE,OWNER, JOB_NAME,STATUS,USER_NAME,CLIENT_ID,GLOBAL_UID,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_LOG;

-------------------------DBA JOBS---------------


SQL> SELECT LOG_USER,SCHEMA_USER,LAST_DATE, LAST_SEC,THIS_DATE,THIS_SEC, NEXT_DATE, NEXT_SEC,TOTAL_TIME,BROKEN,INTERVAL,FAILURES,WHAT,INSTANCE FROM D
BA_JOBS;

----------------------------------------------------SCHEDULED JOBS--------------


SQL> SELECT OWNER,CLIENT_ID, PROGRAM_OWNER, PROGRAM_NAME,SCHEDULE_OWNER,START_DATE END_DATE,ENABLED,STATE,RUN_COUNT,LAST_START_DATE,LAST_RUN_DURATION,
NEXT_RUN_DATE, MAX_RUN_DURATION,COMMENTS FROM DBA_SCHEDULER_JOBS;

To create a job, program and chains we need to grant our user some privileges.


2
3
4
5
6
7
8
9
10
11
12
BEGIN
GRANT CREATE ANY JOB TO <USER NAME>;
GRANT EXECUTE ON DBMS_SCHEDULER TO <USER NAME>;
GRANT MANAGE SCHEDULER TO <USER NAME>;
END;








- Display the schedule details.
SELECT owner, schedule_name FROM dba_scheduler_schedules;
Schedules optionally define the start time, end time and interval related to a job. Schedules are created using the CREATE_SCHEDULE procedure.

 Schedules don't have to be created as separate objects. They can be defined using the REPEAT_INTERVAL parameter of the CREATE_JOB procedure.

 

-- Display the program details.
SELECT owner, program_name, enabled FROM dba_scheduler_programs;
The scheduler allows you to optionally create programs which hold metadata about a task, but no schedule information. A program may related to a PL/SQL block, a stored procedure or an OS executable file. Programs are created using the CREATE_PROGRAM procedure. 

Jobs



Jobs are what the scheduler is all about. They can either be made up of predefined parts (programs and schedules) or completely self contained depending on which overload of the CREATE_JOB procedure is used to create them.







Example ,






-- Create jobs.
BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');

  -- Job defined by an existing program and schedule.
  DBMS_SCHEDULER.create_job (
    job_name      => 'test_prog_sched_job_definition',
    program_name  => 'test_plsql_block_prog',
    schedule_name => 'test_hourly_schedule',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule.');

Comments

Post a Comment

Popular posts from this blog

more about scheduler window

Installed by default, the database comes with three preconfigured Automatic Maintenance Tasks, called clients. First, the collection of optimizer statistics (Optimizer Statistics Collection), the Segment Advisor (Space Advisor) and the automatic SQL tuning (SQL Tuning Advisor). a. Status First, the query for the current state of the database in DBA_AUTOTASK_CLIENTS. Only in Enterprise Edition is the SQL Tuning Advisor ENABLED. SQL> select client_name, status, attributes from dba_autotask_client; CLIENT_NAME                        STATUS   ATTRIBUTES ---------------------------------  -------  ---------------------------------------- auto optimizer stats collection    ENABLED  ON BY DEFAULT , VOLATILE, SAFE TO KILL auto space advisor               ...
Read more

query

############################################################        DB QUERIES          ####################################################################### =================QUERY FOR TOTAL SIZE OF ORACLE DATABASE: SELECT ( SELECT SUM(BYTES)/1024/1024/1024 DATA_SIZE FROM DBA_DATA_FILES ) + ( SELECT NVL(SUM(BYTES),0)/1024/1024/1024 TEMP_SIZE FROM DBA_TEMP_FILES ) +( SELECT SUM(BYTES)/1024/1024/1024 REDO_SIZE FROM SYS.V_$LOG ) +( SELECT SUM(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CONTROLFILE_SIZE FROM V$CONTROLFILE) "SIZE IN GB" FROM DUAL; =================INDIVIDUALLY CHECKING ALL THE DATABASE FILES AS WELL AS NON-DATABASE  FILES: SET LINES 100 PAGES 999 COL NAME FORMAT A50 SELECT NAME, BYTES FROM (SELECT NAME, BYTES FROM V$DATAFILE UNION ALL SELECT NAME, BYTES FROM V$TEMPFILE UNION ALL SELECT LF.MEMBER "NAME", L.BYTES FROM V$LOGFILE LF , V$LOG L WHERE LF.GROUP# = L.GROUP# UNION ALL SELECT NAME, 0 FROM V$CONTROLFIL...
Read more

pdb queries

set lines 333 col tablespace_name for a30 col contents for a25 col status for a15 col con_name for a20 select c.con_id, c.name  con_name, t.tablespace_name, t.contents, t.status from v$containers c, cdb_tablespaces t where c.con_id=t.con_id order by 1,2; ---------------------------------------------------------------------------------------------------------- set lines 400 col con_name for a20 col username for a25 col account_status for a28 -- col LOCK_DATE -- col EXPIRY_DATE -- col LAST_LOGIN col DEFAULT_TABLESPACE for a25 col TEMPORARY_TABLESPACE for a15 --col LOCAL_TEMP_TABLESPACE for a15 col PROFILE for a25 select c.con_id, c.name  con_name, u.username,u.account_status,u.common,u.default_tablespace,u.temporary_tablespace,u.profile from v$containers c, cdb_users u where c.con_id=u.con_id order by 1,2; ---------------------------------------------------------------------------------------------------------- set lines 400 col file_name for a66 col...
Read more