How to Implement server-level KILLORACLE in WINODS and LINUX

 

Kill Oracle processes with locks

 

There is often a problem: in ORACLE, a process that has been set to "killed" locks system resources for a long time and does not release them. There is a better way than restarting the database, the process is forcibly killed at the operating system level.

1. The following statement is used to query which objects are locked:

Col object_name for a30

Col machine for a20

Select object_name, machine, s. sid, s. serial #

From v $ locked_object l, dba_objects o, v $ session s

Where l. object_id = o. object_id and l. session_id = s. sid;

2. The following statement is used to kill a process:

Alter system kill session '20180101'; (42,219 of them are the sid found above, serial #)

You can use the following query to obtain statements similar to the preceding one in batches:

Select 'alter system kill session ''' | s. sid | ',' | s. serial # | ''';'

From v $ locked_object l, dba_objects o, v $ session s

Where l. object_id = o. object_id and l. session_id = s. sid;

3. if the process state is set to "killed" after a process is killed by using the preceding command, but the locked resources are not released for a long time, you can then kill the corresponding process (thread) at the OS level and obtain the process (thread) number first:

Select spid, osuser, s. program

From v $ session s, v $ process p

Where s. paddr = p. addr and s. sid = # sid; (# sid is the sid above)

4. Kill the corresponding process (thread) in the operating system ):

1) in Linux, run the command as root:

Kill-9 12345 (12345 is the spid obtained in step 1)

2) use orakill to kill a thread in windows. orakill is an executable Command provided by oracle. The syntax is orakill sid thread.

Where:

Sid: indicates the Instance name of the process to be killed.

Thread: the ID of the thread to be killed, that is, the spid obtained in step 1.

Example: c:> orakill orcl 12345

You can write a stored procedure for Combined Query to automatically perform the preceding four steps to conveniently kill all processes that do not automatically release resources. However, this is generally not recommended, after all, using the root user to kill the process in the system is inherently risky!

 

 

In Windows, there is only one process, ORACLE. EXE, and the user connection is processed in the Windows Thread, not in the process. If you use Windows Task Manager to end Oracle threads, it may affect all users and background threads and cause database crash.

 

For these reasons, Oracle provides an orakill.exe command in the Oracle Home/bin directory. The parameter of this command is the same as that of alter system kill session, but database connection is not required. To locate a specific thread, you need to find a program that can display all the threads of a process. Windows Task Manager can only display the number of threads and processes. You need to find a tool for Windows 2000 and NT from the Microsoft resource toolkit. For example, for free quickslice.exe, qslice.exe (this tool is based on Windows, and cmdpstat(pstat.exe is a command line tool ). Simply enter the thread ID (in decimal format) and SID after the orakill command:

 

Orakill <sid> <spid>
Orakill ORCL 2760

 

"Kill of thread id 2760 in instance ORCL successfully signalled [sic]."

 

Orakill should be used only when the database cannot be accessed to execute the alter system kill session. If a necessary background process, such as PMON, is accidentally terminated, the database may crash. Never do this for new users.