ANBOB™

How to create a dbms_job?( 10g and later version   had a new job dbms_scheduler_job.)

SQL> variable job1 number;
SQL> begin
dbms_job.submit(:job1,'p_test;',sysdate,'sysdate+1/24/60');
end;

Now the other possible causes of the collection to share , as follows:

1) Instance in RESTRICTED SESSIONS mode?
Check if the instance is in restricted sessions mode:

select instance_name,logins from v$instance;

If logins=RESTRICTED, then:

alter system disable restricted session;

2) JOB_QUEUE_PROCESSES=0
Make sure that job_queue_processes is > 0

Starting with Oracle Database release 11.2.0.2, setting JOB_QUEUE_PROCESSES to ‘0’(zero) causes both DBMS_SCHEDULER and DBMS_JOB jobs to not run. https://www.anbob.com/archives/2332.html

Sometimes even if this value is greater than 0, but because the value is not large enough, there will be the phenomenon that the job cannot be executed. You can try to increase the value to 1000 (12c default 4000)

show parameter 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;

3) _SYSTEM_TRIG_ENABLED=FALSE
Check if _system_enabled_trigger=false

col parameter format a25
col value format a15
select a.ksppinm parameter,b.ksppstvl value from x$ksppi a,x$ksppcv b
Where a.indx=b.indx and ksppinm=’_system_trig_enabled’;

4) Is the job BROKEN?

select job,broken from dba_jobs where job= xxx;

If broken, then check the alert log and trace files to diagnose the issue.

5) Is the job COMMITted?
Make sure a commit is issued after submitting the job:

DECLARE X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => 'dbms_utility.analyze_schema
(''SCOTT'',''COMPUTE'',NULL,NULL,NULL);'
,next_date => to_date('08/06/200509:35:00','dd/mm/yyyy hh24:mi:ss')
,no_parse => FALSE
);
COMMIT;
END;
/

If the job executes fine if forced (i.e., exec dbms_jobs.run();), then likely a commit
is missing.

6) UPTIME > 497 days
Check if the server (machine) has been up for more than 497 days:
For SUN , use ‘uptime’ OS command.
If uptime>497 and the jobs do not execute automatically, then you are hitting bug 3427424
(Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102

7) DBA_JOBS_RUNNING
Check dba_jobs_running to see if the job is still running:

select * from dba_jobs_running;

8) LAST_DATE and NEXT_DATE
Check if the last_date and next_date for the job are proper:

select Job,Next_date,Last_date from dba_jobs where job=;

^– NEXT_DATE is porper, however LAST_DATE is null since the job never executes automatically.

9) NEXT_DATE and INTERVAL
Check if the Next_date is changing properly as per the interval set in dba_jobs:

select Job,Interval,Next_date,Last_date from dba_jobs where job=;

^– This is not possible since the job never gets executed automatically.

10)  oracle background process ora_j000_<sid> not   exists.  e.g. ps -ef|grep ora_j000;

11) if  ora_j000 not exists, mack sure the aq_tm_processes parameter value is >0;

The CJQ0 process dynamically spawns job queue slave processes (J000…J999) to run the jobs.

12) max_job_slave_process  parameter (if the version have) is not enough;

13) processes or sessions parameter is not enough;

14) timezone
CDB difference to PDB;
Schedure timezone Wrong with DB timezone.

set serveroutput on
DECLARE
PROCEDURE display(p_param IN VARCHAR2) AS
l_result VARCHAR2(50);
BEGIN
DBMS_SCHEDULER.get_scheduler_attribute(
attribute => p_param,
value => l_result);
DBMS_OUTPUT.put_line(''||RPAD(p_param, 30, ' ') || ' : ' || l_result);
END;
BEGIN
display('current_open_window');   --(read-only)
display('default_timezone');
END;
/

default_timezone
we can simply change this default with a single call to DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE.
As the name implies, this attribute sets the default time zone for the scheduler. When a job is scheduled using the calendar syntax to define a repeat interval, the scheduler needs to know which time zone to apply when calculating the next run date. Since a time zone cannot be specified explicitly by the calendar syntax, it must be derived from the following sources, in the order noted below:

* The time zone of the job?s start_date attribute.
* The current session?s time zone.
* The scheduler?s default_timezone attribute.
* The time zone returned by the systimestamp function.

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(‘default_timezone’, ‘Pacific/Auckland’);

On 12C, 18c and 19c, using DBCA General mode create DB, the default timezone of scheduler of PDB is different with CDB$ROOT.
The default timezone of the scheduler of PDB is PST8PDT on 19c and Etc/UTC on 12C & 18c no matter what the timezone of the scheduler is in CDB$ROOT.
But using create database command or using DBCA customize mode, the default timezone of the scheduler of PDB is same with CDB$ROOT.
It is expected behavior.

15) Check view DBA_SCHEDULER_GLOBAL_ATTRIBUTE for CURRENT_OPEN_WINDOW:
SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name=’CURRENT_OPEN_WINDOW’;

If a window is open close it (e.g.):

ATTRIBUTE_NAME VALUE
——————————— —————————-
CURRENT_OPEN_WINDOW WEEKEND_WINDOW — it will be null

SQL> select WINDOW_NAME,NEXT_START_DATE,LAST_START_DATE from dba_scheduler_windows;

# fix
SQL> exec dbms_scheduler.disable(‘WEEKEND_WINDOW’);
SQL> exec dbms_scheduler.disable(‘WEEKNIGHT_WINDOW’);

SQL> exec DBMS_SCHEDULER.close_window (‘WEEKEND_WINDOW’);

Execute after 30 mts

SQL> exec dbms_scheduler.enable(‘WEEKEND_WINDOW’);
SQL> exec dbms_scheduler.enable(‘WEEKNIGHT_WINDOW’);

16), SCHEDULER_DISABLED –undocument

When the scheduler came into existence in Oracle 10g, there was a cool API call that could be used to temporarily turn the entire scheduler off. That command was:

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')
PL/SQL procedure successfully completed.

19c test

SQL> variable x varchar2(10)
SQL> exec dbms_scheduler.get_scheduler_attribute('SCHEDULER_DISABLED',:x)
PL/SQL procedure successfully completed.

SQL> print x

X
--------------------------------

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')
BEGIN dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE'); END;

*
ERROR at line 1:
ORA-27497: operation is not permitted inside a pluggable database
ORA-06512: at "SYS.DBMS_ISCHED", line 3438
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3511
ORA-06512: at line 1

SQL> conn / as sysdba
Connected.

USERNAME INST_NAME HOST_NAME I# SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS CDB$ROOT-anbob19c oel7db1 1 1 36023 19.0.0.0.0 20230718 7600 33 5682 0000000078081028 0000000078CF7F48

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');
PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.get_scheduler_attribute('SCHEDULER_DISABLED',:x)
PL/SQL procedure successfully completed.

SQL> print x
X
--------------------------------
TRUE

SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

ATTRIBUTE_NAME                            VALUE
----------------------------------       ------- 
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY                               30
DEFAULT_TIMEZONE                          EST5EDT
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION                   NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT                        0
CURRENT_OPEN_WINDOW
SCHEDULER_DISABLED                        TRUE

12 rows selected.

Note:
Since 12c, the supported way to turn off the scheduler is now to set job_queue_processes to zero. You should not use the SCHEDULER_DISABLED attribute.

18) MAX_JOB_SLAVE_PROCESSES may be too low.

SQL> select value from dba_scheduler_global_attribute
where attribute_name='MAX_JOB_SLAVE_PROCESSES';
-- Then check the number of running jobs
SQL> select count(*) from dba_scheduler_running_jobs;
-- If this is the problem you can increase the number or just NULL it out using
SQL> exec dbms_scheduler.set_scheduler_attribute('max_job_secondary_processes',null);

17) Check for unsuccessful shutdowns:
A shutdown immediate may get canceled because active sessions prevent the database close operation.
After issuing a ‘shutdown immediate’ command, if the command does not execute after an hour, the Oracle Server automatically cancels the operation. A message will appear in the alert log as follows,Please review the alert log for the last two shutdown / startups and the messages:

SHUTDOWN: Active sessions prevent database close operation
Please refer to
Note 434690.1 – Database Jobs Do Not Run After a Failed ‘Shutdown Immediate’

18)  Maintenance windows Limit

To get a list of maintenance window to use

SQL> select * from dba_scheduler_wingroup_members;

To see when the windows run use

SQL> select * from dba_scheduler_windows

Enable scheduler trace

Try to get scheduler trace by setting event 27402.

alter system set events '27402 trace name context forever, level 255';
-- or 
alter system set events '27402 trace name context forever, level 65355';

then check cjq trace file

SQL> select pname,tracefile from v$process where pname ='CJQ0';

disable 2402 event

alter system set events '27402 trace name context off';

check trace file grab abnormal status (status <> 0)

$ tail -f xxx_cjq0_381904.trc|grep status|grep -v "status 0"

MORE:
Troubleshooting these are too many scheduler jobs(owner sys) name like KWQICPOSTMSGDEL_nn in DB 11g

Known Issues And Bugs Using the Scheduler (Doc ID 1311355.1)

reference
Default Scheduler Timezone Value In PDB$SEED Different Than CDB (Doc ID 2702230.1)

打赏

微信扫一扫，打赏作者吧～