Oracle Server Process Management
Oracle Tips by Burleson Consulting

Process Management

 

There are times and scenarios where a user’s Oracle server process on the database server may require intervention by the DBA. For example, the session may be either hung or aggressively “spinning” and thus blocking out other transactions or consuming excessive resources. When situations like this occur, the DBA needs a way to terminate the offending Oracle server process to release the operating system and database resources  and thus, clear the log jam. Hopefully, this scenario should only occur occasionally.

 

In most cases, assuming the database itself is up and accepting new connections, the DBA can simply connect to the database, find the offending process by querying the data dictionary, and terminate it via an SQL command. The query to find the offending process and the SQL command to terminate it will look something like the following:

 

 

However, there are rare times when the database is either not up, not accepting any new connections, or the machine is just so slow that opening a new database connection is taking far too long. Likewise, this scenario should only occur very rarely  and possibly not at all in an ideal world. But when it does, the DBA needs a method to terminate the session and must rely upon operating system level commands. Nevertheless, far too often it is common practice to kill Oracle processes and so prevalent that even non-DBAs start to perform them on a regular basis. Make sure that the following facilities are only used under appropriate circumstance and with controlled supervision. Remember, KILL is a four-letter word.

 

kill 

On UNIX and Linux, the Oracle process architecture follows the standard UNIX paradigm, which is that every program execution forks or spawns a process to perform that contextual task. As such, the ps command, even on a small system, can display hundreds to even thousands of processes and that is why a pipe to grep is often paired with it, as shown here:

 

oracle    5400     1  0 10:55 ?        00:00:00 ora_pmon_ORLI10

oracle    5402     1  0 10:55 ?        00:00:00 ora_psp0_ORLI10

oracle    5404     1  0 10:55 ?        00:00:00 ora_mman_ORLI10

oracle    5406     1  0 10:55 ?        00:00:00 ora_dbw0_ORLI10

oracle    5408     1  0 10:55 ?        00:00:00 ora_lgwr_ORLI10

oracle    5410     1  0 10:55 ?        00:00:00 ora_ckpt_ORLI10

oracle    5412     1  0 10:55 ?        00:00:01 ora_smon_ORLI10

oracle    5414     1  0 10:55 ?        00:00:00 ora_reco_ORLI10

oracle    5416     1  0 10:55 ?        00:00:01 ora_cjq0_ORLI10

oracle    5418     1  0 10:55 ?        00:00:01 ora_mmon_ORLI10

oracle    5420     1  0 10:55 ?        00:00:00 ora_mmnl_ORLI10

oracle    5437     1  0 10:55 ?        00:00:00 ora_qmnc_ORLI10

oracle    5860     1  0 10:56 ?        00:00:00 ora_q000_ORLI10

oracle    6347     1  0 10:56 ?        00:00:00 ora_q001_ORLI10

oracle    6621  6552  0 10:58 pts/0    00:00:00 grep ora_

 

When there is a legitimate reason to terminate a user’s SQL and the associated dedicated database server process, then the following steps are required to effect that interruption.

• Identify the correct operating system process associated with the errant SQL

• Issue the UNIX kill command for that process with the terminate flag

• Verify that the operating system process indicated terminates as planned

Following is an example where there are two users with a problem. The first user running SQL*Plus on the database server placed an exclusive lock on all the rows in a table by forgetting to place a WHERE clause in their UPDATE statement. The second user running SQL*Plus from their Windows PC is thus blocked from updating the rows they need until the first user issues a commit. Further, assume that the database is not accepting any new connections, so the DBA must rely on an operating system command to kill the Oracle process. Doing a ps command shows both SQL*Plus sessions, where the first user’s process  is 6781.

 

 

So now, to kill that Oracle database dedicated server session, use the UNIX kill command incorporating either of the two following syntaxes:

 

 

To find the names of the valid termination signal levels, simply perform a kill –l as shown here to see what signal level values are permissible:

 

 1) SIGHUP       2) SIGINT       3) SIGQUIT      4) SIGILL

 5) SIGTRAP      6) SIGABRT      7) SIGBUS       8) SIGFPE

 9) SIGKILL     10) SIGUSR1     11) SIGSEGV     12) SIGUSR2

13) SIGPIPE     14) SIGALRM     15) SIGTERM     17) SIGCHLD

18) SIGCONT     19) SIGSTOP     20) SIGTSTP     21) SIGTTIN

22) SIGTTOU     23) SIGURG      24) SIGXCPU     25) SIGXFSZ

26) SIGVTALRM   27) SIGPROF     28) SIGWINCH    29) SIGIO

30) SIGPWR      31) SIGSYS      34) SIGRTMIN    35) SIGRTMIN+1

36) SIGRTMIN+2  37) SIGRTMIN+3  38) SIGRTMIN+4  39) SIGRTMIN+5

40) SIGRTMIN+6  41) SIGRTMIN+7  42) SIGRTMIN+8  43) SIGRTMIN+9

44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13

48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13

52) SIGRTMAX-12 53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9

56) SIGRTMAX-8  57) SIGRTMAX-7  58) SIGRTMAX-6  59) SIGRTMAX-5

60) SIGRTMAX-4  61) SIGRTMAX-3  62) SIGRTMAX-2  63) SIGRTMAX-1

64) SIGRTMAX