`
wuhuizhong
  • 浏览: 684572 次
  • 性别: Icon_minigender_1
  • 来自: 中山
社区版块
存档分类
最新评论

Killing the Oracle DBMS_JOB

 
阅读更多

Take control of Oracle's queue with a step by step approach to getting rid of those pesky DBMS_JOBs.

Let's face it, Oracle's job scheduling facility is a wonderful tool for scheduling Oracle related jobs without having to maintain a cron job on Unix or an AT job in windows. It is also very robust and reliable. It is that very reliability and robustness that gives many of us our problems.

If you have any form of jobs running on your system, you will at one time or another come across the issue of a run-away job that just doesn't seem to want to end. Or maybe you will try and shutdown the database only to find out that it is waiting to complete a job. I would like to offer some help in the management of those job queues when they just don't seem to want to end or go away.

A while back I needed to find information on how to clear the job queue for jobs running with no apparent end in sight. Some had hung, while others just were taking a bad access path to data. I needed to bring down these jobs, do a bit of tuning and then restart the jobs. Well, to my amazement, there wasn't very much information out on the web that gave good insight into this process. Basically the method suggested was to first break the job and then issue an ALTER SYTEM KILL SESSION command. This method does not always work and unfortunately--never on my system, for the jobs I had. I then called Oracle support and basically got the same answer as I found out on the web. They did give me one added piece of information. They said, if the ALTER SYSTEM KILL SESSION didn't work, I was supposed to bounce my database in order to bring down the job queue processes. First of all, this wasn't an option and when I did get the opportunity to bounce the database box, many of the jobs seemed to come right back as strong as ever.

Before writing this article I did another quick search on the topic of killing dbms_jobs and to my amazement there still wasn't much good information out there. This is why I want to share my method, so that you won't be stuck up against the wall with this problem and nowhere to turn, as I was.

Lets first go through a few different methods of viewing the information about job queues.
Viewing scheduled dbms_jobs

When looking at what jobs have been scheduled, there is really only one view that you need to go to. The dba_jobs view contains all of the information you need, to see what has been scheduled, when they were last run, and if they are currently running. Use the following simple script to take a look. Bear with me on the sub-select, I will build on this query as we go on in the presentation.

scheduled_dbms_jobs.sql

set linesize 250
col log_user       for a10
col job            for 9999999  head 'Job'
col broken         for a1       head 'B'
col failures       for 99       head "fail"
col last_date      for a18      head 'Last|Date'
col this_date      for a18      head 'This|Date'
col next_date      for a18      head 'Next|Date'
col interval       for 9999.000 head 'Run|Interval'
col what           for a60

select j.log_user,
     j.job,
     j.broken,
     j.failures,
     j.last_date||':'||j.last_sec last_date,
     j.this_date||':'||j.this_sec this_date,
     j.next_date||':'||j.next_sec next_date,
     j.next_date - j.last_date interval,
     j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
             dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
             dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
        from dba_jobs dj) j;
 




What Jobs are Actually Running

A simple join to the dba_jobs_running view will give us a good handle on the scheduled jobs that are actually running at this time. This is done by a simple join through the job number. The new column of interest returned here is the sid which is the identifier of the process that is currently executing the job.

running_jobs.sql

set linesize 250
col sid            for 9999     head 'Session|ID'
col log_user       for a10
col job            for 9999999  head 'Job'
col broken         for a1       head 'B'
col failures       for 99       head "fail"
col last_date      for a18      head 'Last|Date'
col this_date      for a18      head 'This|Date'
col next_date      for a18      head 'Next|Date'
col interval       for 9999.000 head 'Run|Interval'
col what           for a60
select j.sid,
       j.log_user,
       j.job,
       j.broken,
       j.failures,
       j.last_date||':'||j.last_sec last_date,
       j.this_date||':'||j.this_sec this_date,
       j.next_date||':'||j.next_sec next_date,
       j.next_date - j.last_date interval,
       j.what
from (select djr.SID,
             dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
             dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
             dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
        from dba_jobs dj, dba_jobs_running djr
       where dj.job = djr.job ) j;
 



What Sessions are Running the Jobs

Now that we have determined which jobs are currently running, we need to find which Oracle session and operating system process is accessing them. This is done through first joining v$process to v$session by way of paddr and addr which is the address of the processs that owns the sessions, and then joining the results back to the jobs running through the sid value. The new columns returned in our query are spid which is the operating system process identifier and serial# which is the session serial number.

session_jobs.sql

set linesize 250
col sid            for 9999     head 'Session|ID'
col spid                        head 'O/S|Process|ID'
col serial#        for 9999999  head 'Session|Serial#'
col log_user       for a10
col job            for 9999999  head 'Job'
col broken         for a1       head 'B'
col failures       for 99       head "fail"
col last_date      for a18      head 'Last|Date'
col this_date      for a18      head 'This|Date'
col next_date      for a18      head 'Next|Date'
col interval       for 9999.000 head 'Run|Interval'
col what           for a60
select j.sid,
s.spid,
s.serial#,
       j.log_user,
       j.job,
       j.broken,
       j.failures,
       j.last_date||':'||j.last_sec last_date,
       j.this_date||':'||j.this_sec this_date,
       j.next_date||':'||j.next_sec next_date,
       j.next_date - j.last_date interval,
       j.what
from (select djr.SID,
             dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
             dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
             dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
        from dba_jobs dj, dba_jobs_running djr
       where dj.job = djr.job ) j,
     (select p.spid, s.sid, s.serial#
          from v$process p, v$session s
         where p.addr  = s.paddr ) s
 where j.sid = s.sid;
 



Now that we have a good handle on how we can look at the jobs and the key columns involved, let's go through the steps needed to bring down a job. The following is a 5 to 11 step process that should solve all of your problems.

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down
In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.

2. Mark the DBMS_JOB as Broken
Use the following command for the job that you have to deal with.

SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.

As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.
3. Kill the Oracle Session

Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.

ALTER SYSTEM KILL SESSION 'sid,serial#';

4. Kill the O/S Process

More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid

The orakill is an Oracle command, while kill is a Unix command.
5. Check if the Job is Still Running

Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

This will bring down the entire job queue processes.

8. Validate that No Processes are Using the Job Queue
Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.

9. Mark the DBMS_JOB as Not Broken
You can now reset the broken job to not broken so they can run again. Just issue the command.

SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):

10. Alter the Job Queue to Original Value
Set the job queue to its' original value so that the jobs can run again.

ALTER SYSTEM SET job_queue_processes = original_value;

11. Validate that DBMS_JOB Is Running
To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.

Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.

 

 

分享到:
评论

相关推荐

    2019高考英语新课件二轮练习精品测试卷2unit4.doc

    3. 名词辨析 - 野生动植物保护:"Laws have been made to prevent people from killing _____animals and birds ."法律旨在保护野生动植物,所以用wild animals。 4. 名词辨析 - 损失:"suffered a heavy _______...

    fend_killing_johanna_parcel

    用法 要使用此示例,您必须安装Node.js并附带一个程序包管理器。 我建议使用yarn,但是npm对于像这样的简单示例同样适用。 安装软件包并运行启动脚本: npm install npm start 要么 yarn yarn start ...

    源码 source for killing all the semaphore

    This is a program is writen when i study program on GUN, it could delete all the exiting semaphore, if you just start to study c programing on linux ,maybe it will be one good example to help you ...

    2D_killing_time

    在提供的"2D_killing_time-main"文件中,很可能包含了项目的源代码、资源文件和配置信息。分析这些文件可以帮助我们更深入地理解这个项目的实现细节。例如,源代码文件可能包含C#脚本,资源文件可能有Sprite图像、...

    2018专业四级部分题目.doc

    16. Indeed, it is arguable that body shattering is the very point of football, as killing and maiming ______________. 题目要求填入谓语动词。选项D "are of war" 表明“杀伤和致残”是战争的本质,与前面的...

    高二英语下学期期末补考试题.doc

    8. **动名词作主语** - 句子"_________ from the job for a long time makes my father have a sense of loss."中,动名词"Retiring"作主语,表示退休这个行为,因此选项B "Retiring"正确。 9. **宾语从句** - 句子...

    2014届高考英语一轮复习 课时作业(十六) 模块6 Unit 1 Laughter is good for you 译林牛津版

    5. "It suddenly ________ to me that we could use a computer to do the job."这道题考察固定句型"It occurred to sb. that…"表示“某人突然想到…”,因此正确答案是D. occurred。 【知识点二:动词短语辨析】 ...

    初中词汇考纲基本词性变换列表.docx

    The report about that famous singer killing people will ____________(disappear) in tomorrow’s newspapers. - 正确答案:disappear - 解析:此处需要填入动词形式,表示“消失”,因此使用disappear。 17....

    非谓语动词填空练习高二英语.doc

    "killing" 是现在分词,表示自然而然的结果。 13. "There are lots of places of interest _____(need; repair) in our city." "needing repair" 是现在分词短语作定语,城市中有许多需要修复的名胜古迹。 14. ...

    具有单个Killing矢量场的黑洞:黑色谐振器

    我们在四个维度上数值构造渐近的反德西特(AdS)黑洞,这些黑洞仅包含一个Killing向量场。 通过将超辐射不稳定性的发生点连接到称为geons的光滑无水平几何体,将这些解决方案(我们称之为黑色谐振器)将Kerr-AdS的超...

    2014高考英语 单项选择 2013暑假自测题(20)

    在句子"A all planes crashed into a hillside five miles east of the city, _______ all forty people on board."中,现在分词"killing"用作结果状语,表示自然而然的结果,表明飞机坠毁导致了所有人的死亡。...

    江苏省南京市溧水区孔镇中学八年级英语上册Unit5WildanimalsPeriod10Checkout练习1新版牛津版

    5. "______(hunt) like ______(kill) wild animals" 主语是猎人,因此填入"hunters"和"killing"。6. "He was even ______(ill) this morning." 更病态用"more ill"。7. "The dishes in the restaurant are very ____...

    UiPath高级开发者认证-操作题

    6. Populate KillAllProcesses.xaml from the Framework folder with killing the process used. 7. Populate the Process.xaml file with the following actions: Web scraping, Filtering and Appending to Excel....

    Killing向量场形式主义的新应用:修正的周期性电势和两性分布的轴离子暗物质分布

    我们使用具有扩展周期电势的伪标量场模型,该模型取决于与Killing向量的模量成比例的其他参数。 在我们的方法中,它们扮演模型指导功能的角色。 具有修改后的潜力的轴力场的协变模型配备了Killing向量场的扩展形式...

    killing-the-monster

    在IT行业中,"killing-the-monster"这个标题可能是在指代解决一个具有挑战性的问题或者优化一个复杂的技术难题。在这种上下文中,我们将其与JavaScript关联起来,这意味着我们可能正在探讨如何利用JavaScript来处理...

    scrcpy-win64.rar

    scrcpy是Android投屏工具,此项目为开源项目在Github上,为了方便大家下载使用,本人把该资源放在这里了。scrcpy就是通过adb调试的方式来将手机屏幕投到电脑上,并可以通过电脑控制您的Android设备。...

    扭曲的AdS背景的所有Killing超级代数

    我们介绍了所有扭曲的AdS k×w M d − k,k> 2的所有对称超代数g $$ \ mathfrak {g} $$,在d = 10、11维上的通量背景保留了任何数量的超对称性。 首先,我们给出g分解为AdS k的等距代数与内部空间M d-k的直接和的...

    刷机包 adb killing解决方案

    当我们在使用安卓设备进行刷机操作时,尤其是尝试刷入自定义的recovery镜像时,可能会遇到一个错误提示:“adb killing”,这个错误提示会导致刷机过程无法正常进行。"adb killing"错误通常与Android Debug Bridge ...

    超对称背景,Killing超代数和广义特殊完整

    我们证明,对于M理论或类型II,在维D≥4中保留N $$ \ mathcal {N} $$超对称的通用Minkowski通量背景正好对应于可积广义GN $$ {G} _ {\ mathcal {N} } $$结构,其中GN $$ {G} _ {\ mathcal {N}} $$是Killing旋转子...

    英语六级学习100条阅读难句

    - _He fired, killing one of the passers-by._(这里,“killing”引导了一个现在分词结构,作为结果状语。) - _Being tired with the work, he sat down to rest._(在这个例子中,“Being tired with the work...

Global site tag (gtag.js) - Google Analytics