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.



3 comments:

Yasir said...

WHats is @2 in the command.
Is it node 2?

The Human Fly said...

True.. Its instance ID.

Yasir said...

With the amount of new features Oracle keeps on adding with every release,No matter how much experience we have, its always challenging to get familier with all of them.
Thanks to issues that arises and we search
for solutions and we see there is a new way to do the same thing and we use it;)