Terminating Oracle Database processes on Linux/Solaris server

Problem

You’re running a Oracle database backup job, and you think the process is hung. You want to kill the process.

Solution

The OS PID can be used to terminate a process with the kill utility. In this example, ps is used to show the PID of an RMAN backup job that seems to be hung and needs to be terminated:

 

$ ps -ef | egrep ’rman|UID’ | egrep -v egrep

Here is some sample output:

UID        PID  PPID  C STIME TTY          TIME CMD

oracle    6822  6234  0 11:40 pts/0    00:00:00 rman target /

The PID is 6822 in this example. To terminate that process, issue a kill command, as shown here:

$ kill -9 6822

The -9 option sends a kill signal to the process, which causes it to terminate. You don’t see any output from the kill command because it unceremoniously removes the specified process. Ensure that you don’t kill the wrong Oracle process. If you accidentally kill a required Oracle background process, your instance will abort.


 Note  To see a list of all available types of kill signals, use the kill -l command.


 

How It Works

To run the kill command, you either have to own the process or have root privileges. Sometimes it is necessary to use the kill command to terminate unresponsive Oracle database processes. For example, you might sometimes need to kill a long-running or hung Oracle process (e.g., RMAN, SQL*Plus, and so on). If you know the process name, you can use the ps command to identify the PID (as shown in the solution section of this recipe).

In some situations, you might not know the OS process name. In these scenarios, you can identify the PID by querying data dictionary views, as shown here:

ACCEPT active DEFAULT ’y’ PROMPT ’Active only processes y/n? [y is default]: ’

SET LINES 200 PAGES 0 HEAD OFF LONG 100000

COL dummy_value NOPRINT


--


SELECT ’dummy_value’ dummy_value,

  ’USERNAME    : ’ || s.username     || CHR(10) ||

  ’SCHEMA      : ’ || s.schemaname   || CHR(10) ||

  ’OSUSER      : ’ || s.osuser       || CHR(10) ||

  ’MODULE      : ’ || s.program      || CHR(10) ||

  ’ACTION      : ’ || s.schemaname   || CHR(10) ||

  ’CLIENT INFO : ’ || s.osuser       || CHR(10) ||

  ’PROGRAM     : ’ || s.program      || CHR(10) ||

  ’SPID        : ’ || p.spid         || CHR(10) ||

  ’SID         : ’ || s.sid          || CHR(10) ||

  ’SERIAL#     : ’ || s.serial#      || CHR(10) ||

  ’KILL STRING : ’ || ’’’’ || s.sid || ’,’ || s.serial# || ’’’’  || CHR(10) ||

  ’MACHINE     : ’ || s.machine      || CHR(10) ||

  ’TYPE        : ’ || s.type         || CHR(10) ||

  ’TERMINAL    : ’ || s.terminal     || CHR(10) ||

  ’CPU         : ’ || q.cpu_time/1000000     || CHR(10) ||

  ’ELAPSED_TIME: ’ || q.elapsed_time/1000000 || CHR(10) ||

  ’BUFFER_GETS : ’ || q.buffer_gets  || CHR(10) ||

  ’SQL_ID      : ’ || q.sql_id       || CHR(10) ||

  ’CHILD_NUM   : ’ || q.child_number || CHR(10) ||

  ’START_TIME  : ’ || TO_CHAR(s.sql_exec_start,’dd-mon-yy hh24:mi’) || CHR(10) ||

  ’STATUS      : ’ || s.status       || CHR(10) ||

  ’SQL_TEXT    : ’ || q.sql_fulltext

FROM            v$session s

JOIN            v$process p ON (s.paddr  = p.addr)

LEFT OUTER JOIN v$sql     q ON (s.sql_id = q.sql_id)

WHERE s.username IS NOT NULL -- eliminates background procs

AND NVL(q.sql_text,’x’) NOT LIKE ’%dummy_value%’ -- eliminates this query from output

AND s.status != DECODE(’&&active’,’n’,’xyz’,’N’,’xyz’,’INACTIVE’)

ORDER BY q.cpu_time;

You’ll be prompted about whether you want to see only active sessions displayed in the output:

Active only processes y/n? [y is default]:

Press Enter; here’s some sample output from the previous query:

USERNAME    : SYS

SCHEMA      : SYS

OSUSER      : oracle

MODULE      : rman@cs-xvm (TNS V1-V3)

ACTION      : SYS

CLIENT INFO : oracle

PROGRAM     : rman@cs-xvm (TNS V1-V3)

SPID        : 9458

SID         : 102

SERIAL#     : 49521

KILL STRING : ’102,49521’

MACHINE     : cs-xvm

TYPE        : USER

TERMINAL    : pts/0

CPU          :

ELAPSED_TIME :

BUFFER_GETS  :

SQL_ID       :

CHILD_NUM    :

START_TIME   :

STATUS       : ACTIVE

SQL_TEXT     :

From the name of the program in the prior output, you can tell this is an RMAN process executing. The SPID column in the output is the OS PID. Once the SPID is identified, the kill command can be used to terminate the process:

$ kill -9 9458

Caution  In some rare situations, killing an Oracle process associated with a SQL transaction can have an adverse impact on the stability of the instance. For example, killing a process participating in a distributed transaction might cause the instance to crash. To determine whether this is an issue for the version of the database you’re using, see MOS bug IDs 8686128 and 12961905. In older versions of Oracle, various other bugs associated with killing a process have been identified and fixed and are documented in MOS bug IDs 5929055 and 6865378.


You can also kill a database connection with the SQL*Plus alter system kill session command by using the session ID (SID) and serial number. Here is the general syntax:

alter system kill session ’integer1, integer2 [,integer3]’ [immediate];

In this syntax statement, integer1 is the value of the SID column, and integer2 is the value from the SERIAL# column (of V$SESSION). In an RAC environment, you can optionally specify the value of the instance ID for integer3. The instance ID can be retrieved from the GV$SESSION view.

As a DBA privileged user, the following command kills the database connection that has an SID of 102 and a serial number of 49521:

SQL> alter system kill session ’102,49521’

If successful, you should see this output:

System altered.

When you kill a session, the session is marked as terminated, active transactions (within the session) are rolled back, and any locks (held by the session) are released. The session will stay in a terminated state until any dependent transactions are rolled back. If it takes a minute or more to roll back the transaction, Oracle reports the session as “marked to be terminated” and returns control to the SQL prompt. Suppose that you specify IMMEDIATE:

SQL> alter system kill session ’102,49521’ immediate;

Oracle will roll back any active transactions and immediately return control back to you.


Caution  Killing a session that is executing a select statement is fairly harmless. However, if you terminate a session that is performing a large insert/update/delete, you might see a great deal of database activity (including I/O in the online redo logs) associated with Oracle rolling back the terminated transaction.


 

Вас заинтересует / Intresting for you:

How to ensure the Oracle datab...
How to ensure the Oracle datab... 905 views Aaz24 Mon, 26 Nov 2018, 12:20:58
Creating Oracle 12c Tablespace...
Creating Oracle 12c Tablespace... 725 views Ded Sat, 18 Aug 2018, 16:42:57
What Does Java Mean to an Orac...
What Does Java Mean to an Orac... 1099 views Александров Попков Tue, 27 Feb 2018, 18:52:19
Troubleshooting and Tuning LOB...
Troubleshooting and Tuning LOB... 5118 views Aaz24 Wed, 18 Sep 2019, 11:09:34

Comments on Terminating Oracle Database processes on Linux/Solaris server

Be the first to comment
Please login to comment