Archive for Uncategorized

Killing the Oracle DBMS_JOB

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.

Leave a Comment

How to make task monitor work

This is indeed somewhat tricky to find it out… I have understood at 
least some basics, and will try to explain it.

These animations are controlled by a background task. Background tasks 
should be used when time consuming tasks are made, which should not 
“freeze” the apllication. So a background task runs in the background, 
and is firing messages every now and then, while the main programming 
is still running and reacting. You can observe the task messages via a 
taskmonitor and than e.g. make changes to GUI components, like in this 
case the animated label (circling icon).

First of all, here is how a task is started. One way to do this is via 
actions (right click on a component and choose “set action”). Then you 
could start a task like this:

@Action
public Task myTask() {
return new 
ThisIsMyTask 
(org 
.jdesktop.application.Application.getInstance(yourappsnamehere.class));
}

The above sample shows how to start a task via an action, e.g. 
pressing a button. But you can also start a task manually, out of your 
application, without user interaction. I recently asked this question 
in this list and got following perfectly working answer:

Task mT = myTask();
ApplicationContext appC = Application.getInstance().getContext();
TaskMonitor tM = appC.getTaskMonitor();
TaskService tS = appC.getTaskService();
tS.execute(mT);
tM.setForegroundTask(mT);

This starts the task manually, by referring to the method shown in the 
first sample above. So, you have to have this method anyway, as far as 
I understood, but not necessarily declared as “action”.

Now, the Task class itself looks like the following:

private class ThisIsMyTask extends 
org.jdesktop.application.Task<Object, Void> {
ThisIsMyTask(org.jdesktop.application.Application app) {
super(app);

}

Furthermore, this class has some overidden methods. The most important 
one ist “doInBackground()”. This method contains the time consuming 
source code, which runs in the background, while the “main 
application” can still do other things (even waiting, e.g.).

@Override protected Object doInBackground() throws 
IOException {
// your heavy code here
return null;
}

Two more useful methods are “succeeded” and “finished”. succeeded is 
called, when the task has been successfully completed. Finished is 
called afterwards – but it is also called, when the task is “finished” 
by a cancel action for instance.

@Override protected void succeeded(Object result) {
// what should happen if task was successfully completed?
// insert here
}

@Override
protected void finished()
{
// when the task is finished, enter your code here. this method is 
even called, when
// a task was cancelled – if I understood right
super.finished();
}

Now, these are the tasks basics. Now to the animated cycling icon.

First of all, you have to store the single icons from the animations 
in an array. this is what you find in the samples from NetBeans:

// initiate animated busy-icons, which are animated when the 
thread is running
int busyAnimationRate = 
resourceMap.getInteger(“StatusBar.busyAnimationRate”);
for (int i = 0; i < busyIcons.length; i++) {
busyIcons[i] = resourceMap.getIcon(“StatusBar.busyIcons[" 
+ i + "]“);
}

Than you have to create a timer, which will start the animation once 
this timer is started. By creating this timer, it is not automatically 
running, just initiated…

// and create a busy-icon-timer
busyIconTimer = new Timer(busyAnimationRate, new 
ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
busyIconIndex = (busyIconIndex + 1) % busyIcons.length;
statusAnimationLabel.setIcon(busyIcons[busyIconIndex]);
}
});

The following lines just set the icon to the statusAnimationLabel (the 
one which show the cycling icon respectively a light grey one if 
nothing happens)

// initiate the idle icon and make it visible
idleIcon = resourceMap.getIcon(“StatusBar.idleIcon”);
statusAnimationLabel.setIcon(idleIcon);
progressBar.setVisible(false);

Now you have to create a task monitor, which observes the task. If I 
understood correctly, it is observing the current foreground task, so 
it might get in conflict with several background tasks at the same 
time – BUT I’M NOT SURE WITH THIS!

// connecting action tasks to status bar via TaskMonitor
TaskMonitor taskMonitor = new 
TaskMonitor 
(org 
.jdesktop 
.application.Application.getInstance(yourapp.class).getContext());

taskMonitor.addPropertyChangeListener(new 
java.beans.PropertyChangeListener() {
@Override
public void propertyChange(java.beans.PropertyChangeEvent 
evt) {
String propertyName = evt.getPropertyName();

And now come the events or messages which are fired by the background 
tasks. Here you decide, *what* should be done in such a case.

If I understood correctly, the “start” message is automatically fired 
by the task. So everything here will be done automatically when the 
background thread starts. In this case, the timer of the cycling icon 
is started, i.e. the icon is animated, and the progressbar is made 
visible (but not animated! there is no timer for the progressbar, it 
is just made visible; we come back to this later)

// when a background thread starts, start the busy 
icon animation
if (“started”.equals(propertyName)) {
if (!busyIconTimer.isRunning()) {
statusAnimationLabel.setIcon(busyIcons[0]);
busyIconIndex = 0;
busyIconTimer.start();
}
// and make the progressbar visible
progressBar.setVisible(true);
progressBar.setIndeterminate(true);

On the opposite, when the task is done, the “done” message is 
automatically fired. Accordingly, here the cycling icon timer is 
stopped and the progressbar is hidden

// when the thread finished working, stop animation, 
set idle icon
// and hide the progress bar
} else if (“done”.equals(propertyName)) {
busyIconTimer.stop();
statusAnimationLabel.setIcon(idleIcon);
progressBar.setVisible(false);
progressBar.setValue(0);

Now here comes a message which can be fired by your application, that 
means you can let this event happen. for instance, you can tell the 
task monitor out of your background task(!) that it is proceeding, 
i.e. firing a “progress” message. when the task monitor gets this 
message from your background task, you can insert your code here what 
should be done. typically, the progressbar is animated.

// if a progress was indicated during the thread 
using the
// “setProgress(value,min,max)” method, we can change 
the state
// of the progress bar here.
} else if (“progress”.equals(propertyName)) {
int value = (Integer)(evt.getNewValue());
progressBar.setVisible(true);
progressBar.setIndeterminate(false);
progressBar.setValue(value);
}
}
});
}
}

As you see, we have to important components here: statusAnimationLabel 
and progressBar. I don’t use the statusMessageLabel, so it’s missing 
here.

Now, once the background task is started (see very above), the icon is 
automatically animated, because we have started a timer which is 
responsible for the animation. but: the progressbar is only “animated” 
when we fire a progress event, which does not occur automatically - 
unlike “start” and “done” events/messages.

The “progress” event/message for the taskmonitor is simply fired when 
invoking the “setProgress” method!

This is what you could put in your doInBackground-Method in your task. 
Here’s an example for loading a file and animating the progressbar 
which show the process of the amount of loaded bytes:

// length of file
final long l = filepath.length();
// length of file in kilobytes
final long kbl = l / 1024;
// counter for progbar
long counter = 0;

fr = new FileReader(filepath);
buffer = new StringBuffer(“”);

for (int c; (c=fr.read()) != -1;) {
// append the bytes to the buffer
buffer.append((char)c);
// increase the counter for the progress 
bar
counter++;
// this method fires the “progress” event/message for the task 
monitor
setProgress(counter/1024,0,kbl);

that’s it. all your time consuming stuff should be put into the 
doInBackground method. If you like, you can animate a progress bar, if 
you don’t do this, you just have the cycling icon.

Now, since the initialization of the animated icons and progressbar 
needs known GUI components, usually all the init stuff is included in 
that class that contains also the components (statusMessageLabel, 
progressBar). But since I’m writing a desktop application which uses 
several forms with background tasks, I simply created an “task monitor 
initialisation class”. Whenever I have a form which needs background 
tasks, I simply pass the JLabel (statusAnimationLabel) and 
JProgressBar (progressbar) as parameters to the constructor:

CInitStatusBar isb = new 
CInitStatusBar( statusAnimationLabel, progressBar );

Now I only need to write my Task Class and start the task via an 
action or manually. But I don’t need to have the init stuff also 
included.

Here’s the complete class with the “externalised” taskmonitor-init:

public class CInitStatusBar {

private final Timer busyIconTimer;
private final Icon idleIcon;
private final Icon[] busyIcons = new Icon[15];
private int busyIconIndex = 0;

org.jdesktop.application.ResourceMap resourceMap = 
org 
.jdesktop 
.application 
.Application 
.getInstance 
(zettelkasten 
.ZettelkastenApp 
.class).getContext().getResourceMap(ZettelkastenView.class);

/**
* Initiates the status bar for background tasks.
* Catches messages from the doInBackground task
* and changes the progressbar state, the busy icon animation
* and – if necessary – the status message.
*/
CInitStatusBar( final javax.swing.JLabel statusAnimationLabel,
final javax.swing.JProgressBar progressBar ) {
/**
* This is pre-defined code taken from the NetBeans IDE
* Initiates some basic things for background tasks, like
* associating a statusbar and busy-icon to a background thread
*/

// initiate animated busy-icons, which are animated when the 
thread is running
int busyAnimationRate = 
resourceMap.getInteger(“StatusBar.busyAnimationRate”);
for (int i = 0; i < busyIcons.length; i++) {
busyIcons[i] = resourceMap.getIcon(“StatusBar.busyIcons[" 
+ i + "]“);
}
// and create a busy-icon-timer
busyIconTimer = new Timer(busyAnimationRate, new 
ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
busyIconIndex = (busyIconIndex + 1) % busyIcons.length;
statusAnimationLabel.setIcon(busyIcons[busyIconIndex]);
}
});

// initiate the idle icon and make it visible
idleIcon = resourceMap.getIcon(“StatusBar.idleIcon”);
statusAnimationLabel.setIcon(idleIcon);
progressBar.setVisible(false);

// connecting action tasks to status bar via TaskMonitor
TaskMonitor taskMonitor = new 
TaskMonitor 
(org 
.jdesktop 
.application 
.Application 
.getInstance(zettelkasten.ZettelkastenApp.class).getContext());

taskMonitor.addPropertyChangeListener(new 
java.beans.PropertyChangeListener() {
@Override
public void propertyChange(java.beans.PropertyChangeEvent 
evt) {
String propertyName = evt.getPropertyName();
// when a background thread starts, start the busy 
icon animation
if (“started”.equals(propertyName)) {
if (!busyIconTimer.isRunning()) {
statusAnimationLabel.setIcon(busyIcons[0]);
busyIconIndex = 0;
busyIconTimer.start();
}
// and make the progressbar visible
progressBar.setVisible(true);
progressBar.setIndeterminate(true);
// when the thread finished working, stop animation, 
set idle icon
// and hide the progress bar
} else if (“done”.equals(propertyName)) {
busyIconTimer.stop();
statusAnimationLabel.setIcon(idleIcon);
progressBar.setVisible(false);
progressBar.setValue(0);
// if a progress was indicated during the thread 
using the
// “setProgress(value,min,max)” method, we can change 
the state
// of the progress bar here.
} else if (“progress”.equals(propertyName)) {
int value = (Integer)(evt.getNewValue());
progressBar.setVisible(true);
progressBar.setIndeterminate(false);
progressBar.setValue(value);
}
}
});
}
}

Comments (3)

Toys

we all love toys when we are young, well guess what we still love toys when we grow up, its just that the “form” of toys changes like FaceBook, twitter, wordpress or wiki or digg or you name it. But as far as I remember there was one problem with toys and that’s called “Getting Bored”. sure all these sites and technologies are great and they make us connected to our friends and family, but what I think is that we will get bored by them sooner or later. That’s where innovation comes into play, keep giving the customer what they want, whenever they want and where ever they want it :) .

Leave a Comment