Oracle RAC 10G -- Learning to know

Nov 18 – 2006 : The day I came to know that I am going to involve in a new project which is going to on Real Application Clusters, with a project of Asia-Pacific regions to be live on this and targeting a data of 4TB and more. As I am new to this and very enthusiastic to learn more. To start with, I browsed Oracle Documents for Oracle RAC 10g R1 online documentation and few others to learn more. In fact I got a corporate training which helped me a lot to go with.

I found few of the books which helped me a lot,
1. Oracle Online Documenation
2. Oracle 10g Grid & Real Application Clusters

Killing a User’s Session

SQL> SELECT sid, serial# FROM v$session
2* WHERE username='user_name';
SID SERIAL#
----------------
10 32

SQL> ALTER SYSTEM KILL SESSION '10, 32’;
System altered.

If the session you want to kill is involved in a long operation, such as a lengthy rollback, Oracle will inform you that the session is marked for kill and it will be killed after the operation is completed.

When Oracle kills a session, it rolls back all ongoing transactions and releases all session locks.

If the UNIX process of the user is killed, the Oracle session will most likely be killed also, but that isn’t the most graceful way to end a session.

OS Level - If you want to End the section - USE KILL

If you think you must kill a user’s UNIX session, and the Oracle KILL SESSION command isn’t working, or it’s taking a long time, you can terminate the session rather abruptly by using the UNIX kill command as follows.

$ kill -9 345678

Use the SQL Syntax - to find the - USER

SQL> SELECT process,sid,serial# FROM v$session
WHERE username='&user';

Enter value for user: SOMEUSER
old 2: username='&user'
new 2: username='SOMEUSER'
PROCESS SID SERIAL#
-----------------------------------
2920:2836 10 34

How about working with WINDOWS?

ORAKILL - Utility

Oracle user’s session on Windows, can use the ORAKILL utility, which will kill a specific thread under the Oracle .exe process.

How do you find out what that user’s thread is?

SQL> SELECT sid, spid as thread, osuser, s.program
2 FROM v$process p, v$session s
3* WHERE p.addr = s.paddr;

SID THREAD OSUSER PROGRAM
-------------------------------------------------------------
1 1192 SYSTEM ORACLE.EXE
2 1420 SYSTEM ORACLE.EXE
3 1524 SYSTEM ORACLE.EXE
4 1552 SYSTEM ORACLE.EXE
5 1528 SYSTEM ORACLE.EXE
6 1540 SYSTEM ORACLE.EXE
7 1580 SYSTEM ORACLE.EXE
8 1680 SYSTEM ORACLE.EXE
9 2948 NETBSA\user sqlplusw.exe
10 4072 NETBSA\user sqlplusw.exe
10 rows selected.

Find the thread number where you want to kill...

C:> orakill 2948

Unix Commands that always usefull

netstat -na grep 1522
ps -ef grep lsnr
glance -j 1
du -k sort -n
du -sk * sort -nrhead
df -k grep /bkdump
ifconfig -a
du -sh *
ipcs -m grep ora

What is blocking at the DB Level

On web i found this script which helped me a lot :-)
***************************************************
SQL> select s1.username '@' s1.machine
2 ' ( SID=' s1.sid ' ) is blocking '
3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------------------
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=32 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=36 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=45 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=49 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=72 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=86 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=90 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=99 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=123 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=141 )

10 rows selected.

SQL> alter system kill session '88,24356';
System altered.
SQL> select * from v$lock where block <> 0 or request <> 0;
no rows selected
************************************
Please let me know your experience in handling this issue.