Oracle job failure solution example

Oracle job failure solves a problem where the JOB in the latest business database is always unable to be executed at regular intervals without any errors. Today I took the time to read it. It should be that the JOB can be executed manually if it fails, dbms_job.run (: job) can also be executed normally. Metalink looked at [ID 313102.1] and posted the specific content below. In fact, there are also checking the most common reasons why jobs don't execute automatically and as scheduled: 1) on the Internet) 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; ^ -- Checked! 2) JOB_QUEUE_PROCESSES = 0 Make sure that job_queue_processes is> 0 show parameter job_queue_processes ^ -- Checked! 3) _ SYSTEM_TRIG_ENABLED = FALSECheck if _ system_enabled_trigger = falsecol parameter format a25col value format a15select. ksppinm parameter, B. ksppstvl value from x $ ksppi a, x $ ksppcv bwhere. indx = B. indx and ksppinm = '_ system_trig_enabled'; If _ system_trig_enabled = false, thenalter system set "_ system_trig_enabled" = TRUE scope = both; ^ -- Checked! 4) Is the job BROKEN? Select job, broken from dba_jobs where job = <job_number>; If broken, then check the alert log and trace files to diagnose the issue. ^ -- Checked! The job is not broken. 5) Is the job COMMITted? Make sure a commit is issued after submitting the job: declare x number; BEGINSYS. DBMS_JOB.SUBMIT (job => X, what => 'dbms _ utility. analyze_schema (''scott '', ''compute'', NULL); ', next_date => to_date ('2014/1/2 09: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 (<job_no>);), then likely a commitis missing. ^ -- Checked! The job is committed after submission. 6) UPTIME> 497 daysCheck 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 unpublished bug 3427424 (Jobs may stop running after 497 days uptime) which is fixed in 9206 and A102 ^ -- Checked! The server in this case has been up 126 days only 7) DBA_JOBS_RUNNINGCheck dba_jobs_running to see if the job is still running: select * from dba_jobs_running; ^ -- Checked! The job is not running. 8) LAST_DATE and NEXT_DATECheck if the last_date and next_date for the job are proper: select Job, Next_date, Last_date from dba_jobs where job = <job_number>; ^ -- NEXT_DATE is proper, however LAST_DATE is null since the job never executes automatically.9) NEXT_DATE and INTERVALCheck if the Next_date is changing properly as per the interval set in dba_jobs: select Job, Interval, Nex T_date, Last_date from dba_jobs where job = <job_number>; ^ -- This is not possible since the job never gets executed automatically. 10) Toggle value for JOB_QUEUE_PROCESSESStop and restart CJQ process (es) alter system set job_queue_processes = 0; -- <Wait for some time to ensure CJQ process stopped> alter system set job_queue_processes = 4; ref: Bug 2649244 (fixed by: 9015,920 3, 10201) ^ -- Done but did not h Elp11) DBMS_IJOB (Non-writable ented): Either restart the database or try the following: exec dbms_ijob.set_enabled (true); Ref: Bug 3505718 (Closed, Not a Bug) ^ -- Done but did not help12) Check view records 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 -- Certificate ---------------------------- CURRENT_OPEN_WINDOW WEEKNIGHT_WINDOW SQL> exec values ('weeknight _ window'); ^ -- Done but did not help These are the most common causes for this behavior. solutionThe solution ended up to be the server (machine) uptime. even though it was up for only 126 days, after the server was rebooted all jobs were able to execute Utomatically. to implement the solution, please execute the following steps: 1. shutdown all applications, including databases.2. Shutdown the server (machine) 3. restart all applications, including databases.4. Check that jobs are executing automatically .. I have used the preceding methods, but they still do not work. I finally solved the problem through [ID 309945.1]: 1) Login as SYS2) Execute the following commandSQL> exec dbms_ijob.set_enabled (true ); 3) Verify that kkjsre Is set to 1SQL> oradebug setmypidStatement processed. SQL> oradebug dumpvar sga kkjsreword kkjsre _ [20B7480, 20B7484) = 000000014) Verify that jobs are now starting automatically. if not, restart the database and recheck kkjsre. it shoshould still be equal to 1 and jobs shoshould now execute normally. the steps are a few steps, which is very simple. However, if you need to restart the database, we recommend that you perform this operation when the business is low.