Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

Oracle Server Process Management
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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:

 

SQL> select s.username, s.osuser, s.sid, s.serial#, p.spid

  2    from v$session s,v$process p

  3   where s.paddr = p.addr

  4     and s.username is not null;

 

USERNAME     OSUSER              SID    SERIAL# SPID

------------ ------------ ---------- ---------- ------------

BERT         oracle              532          9 6781

BERT         BSCALZO             526         31 6889

SYSTEM       BSCALZO             535         29 7066

 

SQL> -- ALTER SYSTEM KILL SESSION 'sid,serial#';

SQL> alter system kill session '526,31';

 

System altered.

 

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@LINUX_10G ~]$ ps -ef | grep ora_

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.

 

[oracle@LINUX_10G ~]$ ps -ef | grep sqlplus

oracle    6766  6735  0 11:26 pts/1    00:00:00 sqlplus

oracle   10942  6552  0 13:52 pts/0    00:00:00 grep sqlplus

[oracle@LINUX_10G ~]$

[oracle@LINUX_10G ~]$ ps -ef | grep oracleORLI10

oracle    6781  6766  0 11:26 ?        00:00:00 oracleORLI10 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle    7461     1  0 11:50 ?        00:00:00 oracleORLI10 (LOCAL=NO)

oracle   10957  6552  0 13:52 pts/0    00:00:00 grep oracleORLI10

 

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

 

kill –s SIGKILL process_id

 

kill -9 process_id

 

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:

 

[oracle@LINUX_10G ~]$ kill -l

 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


 


For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.