Ora-00031 session marked for kill

Today we face a problem on our production database.

problem is :- One of user's sesssion is killed by "alter system kill session" but that session was being

a huge DML operation.

after this command that session marked for kill .

by this time, we fine that this session is blocker of approximate 1200 session.

and system is about hang.

In that situation we killed that session from OS. and it solve our problem.

Ora-00031 session marked for kill

ORA-00031:	session marked for kill
Cause:	The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done.
Action:	No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.

1. To find out the session what need to killed

SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program

FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND'

And s.sid=4314 -- ( select blocking_session from v$active_session_history )

2. For killing the session normaly

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

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

3. if it is DML transaction session then it need to be rollback

what is so time consuming .

(To see the kill session (rollback status )

SQL> select used_ublk from v$transaction;

If USED_UBLK > 0, the session is in the process of being rolled back.

If USED_UBLK is 0, the session is effectively KILLED, resources have been released, we are just

waiting for their client to send a request so we can tell them "you have been killed". Locks are

gone at this point, its rolled back.

If you see used_ublk > 0 (say 1000) and 15 seconds later its at 800, you can guess that it will

take about another minute (15 seconds/ 200 blocks, 800 more to go) to finish rolling back.

But you need kill the process immediately

You need to kill the session’s process from OS level

------For seeing the spid (process id) to kill the process in OS level

select spid

from v$process a , v$session b

where a.addr=b.paddr

and b.sid=4314

------OS kill statemente

=========================

on windows

---------

C:> orakill ORACLE_SID spid

On Unix

---------

$ kill spid

$ kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.

$ ps -ef | grep ora

Index need to be check when migration the database server

We migrate our Oracle Database one server to another one.
we cross checked the two databases . all were OK.
even if parameters, memory size, objects , network all were ok

but our new database was performing very slow for some application reports,
programs...
we can't able to find. whats the cause??

after three days, we find that two big index were not import to new server.
after creating these. we solve our problem.

Network Cable unplug of the server

I want to share A Practical experience of mine.
yesterday, we install (patch of sun solaries spark) OS patch to our production
Database.

It takes at least four hours, after this we shutdown our oracle Database server.
at first all were right but after 10 minutes we were not able to connect to server
without console.

We checked all possible option but failed.
suddenly, one of our system admin unpluged the network cable then plug.

after this all were 0k. we don't whats the problem? may be port hang.. or may be something else ....