5.28.2011

Terminating sessions on a specific RAC database instance

No matter how many years of experience or knowledge we possess, in the modern days, every passing day is a learning day, we surely come across something new every day and learn a new thing.

Perhaps people out there who already discovered and discussed about this feature earlier,  but, I have learned about this feature today and felt quite useful, at least for me, as we manage around 4 cluster environments with nearly 200 databases and 300 instance running across.

As a DBA, we typically use the 'ALTER SYSTEM KILL SESSION' statement to terminate (kill) any specific session on the database, and if the session is active and involved with any operations, we tend to get the 'ORA-00031: session marked for kill' and the session won't be terminated until the outstanding activity complete.

I then learned about the IMMEDIATE (ALTER SYSTEM KILL SESSION ...... IMMEDIATE') clause that most probably terminates the session without actually waiting for the outstanding activity to complete.  In addition, it also records the information (SID,PID) with the database alert.log file.
Today, I come across about terminating sessions on a specific RAC database instance in Oracle 11gR2 (I am not sure about Oracle 11gR1). This will give the flexibility to terminate a session on a specific RAC database instance across cluster environment. The following is the syntax and example of the enhancement:

Syntax 
KILL SESSION 'sid, serial#[, @integer3]'
 
Example
ALTER SYSTEM KILL SESSION '1287, 9823, @2' IMMEDIATE; 
 
Addition

My friend Aman Sharma just tweeted that the feature is available with 11gR1. Thank you buddy.



1 comment:

The Human Fly said...

True.. Its instance ID.