Tag: kill

Database, Errors and Bugs

Automatic Killing Inactive Sessions with Resource Manager

 ,

Hello All!

So, your are manually (or via script) killing idle sessions on your database?

As consequence, your users are getting error “ORA-00028: your session has been killed.” and getting angry on you?

What about doing it automatically in a much more graceful way and be seen as a nicer DBA? You can do it using Resource Manager.

Are you already using Resource Manager on your database? Yes – Great!

No – Shame on you. Read this and put RM in place ASAP. This is a great tool for the database to manage the database resources, plus no additional licensing is needed. So go for it!

Ok, but what is the trick?
It’s the limit max_idle_time. You can use it either on existing groups or subgroups of your plan (or subplan) or switch to “KILL” groups with this you can even use the same criteria you’d use for any script to perform this action. For this you might user the parameter new_switch_group and have a created a different group only for those kills.

Have this option it’s nicer, if you want to avoid killing sessions on database, by the way, as you can always switch a session for the killing group manually, not demanding it to fill the requirements to automatic switching.

How? Quick example:

Creating new Plan Groups with MAX_IDLE_TIME:

begin
dbms_resource_manager.create_plan_directive( plan => '&RM_PLAN', group_or_subplan =>
'LONG_RUN', comment => 'Limit idle time to 5 minutes', max_idle_time => 300);
dbms_resource_manager.create_plan_directive( plan => '&RM_PLAN', group_or_subplan =>
'SHORT_RUN', comment => 'Limit idle time to 1 minute', max_idle_time => 60);
end;
/

Cool!

And what would be the error for the user that get’s the session killed?

ORA-3113 End of file on communication channel

Much nicer: Now you are a nice DBA and don’t kill sessions anymore.
You automated it!

Some additional recommendations:

  • Use this solution for Databases above 11.2.0.4 or 12.1.0.2, due some known bugs:
    • Bug 9523768 – IDLE SESSIONS AREN’T ACTUALLY KILLED IMMEDIATELY (affecting 11.2.0.1 to 11.2.0.3)
    • Bug 13837378 – ALTER SYSTEM KILL SESSION IMMEDIATE DOES NOT KILL QC SESSIONS (11.2.0.1 to 12.1.0.1)
    • Bug 8891495 – NON-IDLE SESSIONS ARE KILLED (11.2.0.1 only)
  • Some MOS references on it:
    • How To Automatic Kill Inactive Sessions using Resource Manager (Doc ID 1935739.1)
    • Using Resource Manager to Detect and Kill Idle Sessions (Doc ID 1557657.1)

Hope it helps!

Cloud Services, Cloud Services | Exadata | Linux, Database, Security, SQL | PLSQL

AWS: ALTER SYSTEM and Managing SYS Objects in RDS

 , , ,

I’m very often managing services over EC2 and there are a few actions clients are often getting some issues to perform in RDS. So I decided to list here 5 of them:

Kill sessions:

begin
rdsadmin.rdsadmin_util.kill(
sid => &sid,
serial => &serial,
method => 'IMMEDIATE');
end;
/

Flush shared_pool or buffer_cache:

exec rdsadmin.rdsadmin_util.flush_shared_pool;
exec rdsadmin.rdsadmin_util.flush_buffer_cache;

Grant Privileges to SYS Objects

# Grant

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_grantee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

# Grant with Grant Option

begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'V_$SESSION',
        p_grantee      => 'GREPORA',
        p_privilege    => 'SELECT',
        p_grant_option => true);
end;
/

# Revoke

begin
    rdsadmin.rdsadmin_util.revoke_sys_object(
        p_obj_name  => 'V_$SESSION',
        p_revokee   => 'GREPORA',
        p_privilege => 'SELECT');
end;
/

 

Hope it Helps!

Cloud Services, Cloud Services | Exadata | Linux, Database, SQL | PLSQL

AWS – Kill Sessions on RDS Oracle Database

 , ,

Hi all!
Today is a quick tip. Actually, just because more than once I was about to kill a session in a RDS session and it was simply not possible with my usual ALTER SYSTEM KILL SESSION scripts… 🙂

Know how to do it? Here it goes:

begin
rdsadmin.rdsadmin_util.kill(
sid => &sid,
serial => &serial);
end;
/

Putted it in a script: @killrds.sql

Cheers!

Database, Performance, SQL | PLSQL

Solving Simple Locks Through @lock2s and @killlocker

 , ,

Hi guys!
This post is to show the most simple and most common kind of locks for objects and the simpliest way to solve it (killing the locker). 🙂
It’s so common that I scripted it. Take a look:

greporadb> @lock2s
 Inst        SID SERIAL# UserName  STATUS   LOGON_TIME          LMODE  REQUEST LC_ET TY       ID1        ID2       CTIME LOCKWAIT         EVENT                                                                                           
----- ---------- ------- --------- -------- ------------------- ------ ------- ----- -- ---------- ---------- ---------- ---------------- -----------------------------------                                                             
    1        354   18145 MATHEUS   ACTIVE   17/06/2016 14:25:19 X      NONE    4032  TX     393238     424490        715 00000000DB0DF900 enq: TX - row lock contention                                                                   
    1        169   25571 GREPORA   ACTIVE   17/06/2016 14:22:48 NONE   X        714  TX     393238     424490        714 00000000DB0D5ED8 enq: TX - row lock contention                                                                   
    1        252   63517 MATHEUS   INACTIVE 17/06/2016 14:17:49 X      NONE     714  TX     655363    1550347       4195                  SQL*Net message from client                                                                     
    1        846   65011 GREPORA   ACTIVE   17/06/2016 14:20:18 NONE   X       4075  TX     655363    1550347        715 00000000DB0ECB88 enq: TX - row lock contention                                                                   
    1        354   18145 GREPORA   ACTIVE   17/06/2016 14:25:19 NONE   S       4032  TX     655363    1550347        715 00000000DB0DF900 enq: TX - row lock contention                                                                   
5 rows selected.

You can identify the Locker by LMODE column. And all his Waiters by REQUEST column marked by not ‘NONE’, below each Locker…

So, let’s kill the lockers:

greporadb> @killlocker
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'                                                                                                                                                                                                                                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                       
alter system kill session '252,63517' immediate;                                                                                                                                                                                                                                                                                                                                                       
alter system kill session '354,18145' immediate;                                                                                                                                                                                                                                                                                                                                                       
2 rows selected.
greporadb> alter system kill session '252,63517' immediate;      
System altered.
greporadb> alter system kill session '354,18145' immediate;      
System altered.
greporadb> @lock2s
no rows selected

Solved!
My magic scripts? Here it goes:

get lock2s.sql:

set lines 10000
set trimspool on
col serial# for 999999
col lc_et for 999999
col l1name for a50
col lmode for a6
col username for a25
select /*+ rule */
distinct b.inst_id,a.sid,b.serial#,b.username,b.status,
--b.audsid,
--b.module,
--b.machine,b.osuser,
b.logon_time,
decode(lmode,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',lmode) lmode,
decode(request,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',request) request,
b.last_call_et LC_ET,a.type TY,a.id1,a.id2,
d.name||'.'||c.name l1name,a.ctime,b.lockwait,b.event
--distinct b.inst_id,a.sid,b.username,a.type,d.name||'.'||c.name l1name,a.id1,a.id2,
--decode(lmode,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',lmode) lmode,
--decode(request,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',request) request,a.ctime,b.lockwait,b.last_call_et
from gv$lock a, gv$session b,sys.obj$ c,sys.user$ d,(select a.id1 from gv$lock a where a.request  0) lock1
where a.id1    = c.OBJ# (+)
and a.sid      = b.sid
and c.owner#   = d.user# (+)
and a.inst_id=b.inst_id
and b.username is not null
and a.id1 = lock1.id1
order by id1,id2, lmode desc
/

get killlocker.sql:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' 
from v$session where sid in (select BLOCKING_SESSION from v$session where BLOCKING_SESSION is not null);

Now you can put in your Linkedin you are a JR DBA… 😛
haha

Matheus.