More often than not, the Oracle database administrators struggle with monitoring with certainty the status and progress of rollback happening in the database. Following is one way, I have found quite reliable in finding that information:
select host_name, instance_name into hostname, instance_name from v$instance where instance_number=1;
--
dbms_output.put_line('in_blocker_threshold_minutes: '||in_blocker_threshold_minutes);
dbms_output.put_line('in_idle_threshold_minutes: '||in_idle_threshold_minutes);
dbms_output.put_line('in_notification_list: '||in_notification_list);
--
-- kill any session in the exclusion list that are blockers for over x minutes. (kill only blockers not being blocked).
-- And send a notification email with their details.
for bses in
(select s.sid, s.serial#, p.spid, s.username, s.program, machine, osuser,
logon_time, last_call_et,
nvl(sbc.ses_blocking_cnt,0) ses_blocking_cnt,
nvl2(t.used_ublk,to_char(used_ublk),'none') used_ublk, sa.sql_text
last_command
from v$session s, v$process p, v$transaction t, v$sqlarea sa,
(select blocking_session, count(*) ses_blocking_cnt from v$session
where blocking_session is not null group by blocking_session) sbc
where last_call_et > in_blocker_threshold_minutes * 60
and s.username is not null -- if username is null then it's a background process.
and (s.username in ('WWW', 'ONCALL') and osuser in ('apache','track','STNG')
and machine in ('bart','lisa','ned','marge','shelbyville','springfield',
'ottprodweb01','ottproddb01','ukdrdb02','shelbyville.pythian.com',
'springfield.pythian.com','ukdrdb01','ukdrdb01.pythian.com',
'ottprodweb01.pythian.com','ottproddb01.pythian.com','ukdrdb02.pythian.com'))
-- and s.program in ('httpd@ned (TNS V1-V3)',
-- 'restri@bart (TNS V1-V3)',
-- 'httpd@lisa (TNS V1-V3)') -- only excluded programs.
and s.type <> 'BACKGROUND' -- don't touch any background processes.
and s.program not like '%(J___)%' -- don't kill db jobs (dbms_job and scheduler)
and s.status <> 'KILLED' -- don't try to kill an already killed session
and s.blocking_session is null -- kill root blockers only, not blockers being blocked.
and s.paddr = p.addr
and s.taddr = t.addr(+)
and s.sql_id = sa.sql_id(+)
and s.sid = sbc.blocking_session
) loop
BEGIN
--
if print_header = 1 then
print_header := 0;
message := lpad('sid',7)||' '||
lpad('serial#',7)||' '||
lpad('spid',7)||' '||
rpad('username',20)||' '||
rpad('program',15)||' '||
rpad('machine',20)||' '||
rpad('osuser',10)||' '||
rpad('logon_time',18)||' '||
lpad('last_call_et',12)||' '||
lpad('ses_blocking_cnt',16)||' '||
lpad('used_ublk',9)||' '||
'last_command'||
chr(10);
--
writeappend_lob(p_clob => v_Body,p_string=>message);
dbms_output.put_line(lpad('sid',7)||' '||
lpad('serial#',7)||' '||
lpad('spid',7)||' '||
rpad('username',20)||' '||
rpad('program',15)||' '||
rpad('machine',20)||' '||
rpad('osuser',10)||' '||
rpad('logon_time',18)||' '||
lpad('last_call_et',12)||' '||
lpad('ses_blocking_cnt',16)||' '||
lpad('used_ublk',9)||' '||
'last_command');
end if;
--
message := lpad(to_char(bses.sid),7)||' '||
lpad(bses.serial#,7)||' '||
lpad(bses.spid,7)||' '||
rpad(bses.username,20)||' '||
rpad(bses.program,15)||' '||
rpad(bses.machine,20)||' '||
rpad(bses.osuser,10)||' '||
to_char(bses.logon_time,'DD-MON-YY HH24:MI:SS')||' '||
lpad(bses.last_call_et,12)||' '||
lpad(bses.ses_blocking_cnt,16)||' '||
lpad(bses.used_ublk,9)||' '||
bses.last_command||
chr(10);
writeappend_lob(p_clob => v_Body,p_string=>message);
dbms_output.put_line(lpad(to_char(bses.sid),7)||' '||
lpad(bses.serial#,7)||' '||
lpad(bses.spid,7)||' '||
rpad(bses.username,20)||' '||
rpad(bses.program,15)||' '||
rpad(bses.machine,20)||' '||
rpad(bses.osuser,10)||' '||
to_char(bses.logon_time,'DD-MON-YY HH24:MI:SS')||' '||
lpad(bses.last_call_et,12)||' '||
lpad(bses.ses_blocking_cnt,16)||' '||
lpad(bses.used_ublk,9)||' '||
bses.last_command);
--
kill_cmd := 'alter system kill session '''||bses.sid||','||bses.serial#||''' immediate';
dbms_output.put_line(kill_cmd);
any_blockers_killed := 1;
execute immediate kill_cmd;
exception
when marked_for_kill then
dbms_output.put_line(bses.sid||','||bses.serial# ||' marked for kill.');
continue;
END;
end loop;
--
if any_blockers_killed = 1 then --IF ANY BLOCKER WAS KILLED, GIVEA CHANCE TO OTHER IDLE SESSIONS TO RUN..
track.send_email('oracle@'||hostname,
in_notification_list,
'killed blocking sessions on '||instance_name,
'<pre>'||v_Body||'</pre>');
dbms_output.put_line(v_Body);
else
-- kill idle sessions. Send a notification email with their details.
for ises in
(select s.sid, s.serial#, p.spid, s.username, s.program, machine, osuser,
logon_time, last_call_et,
nvl(sbc.ses_blocking_cnt,0) ses_blocking_cnt,
nvl2(t.used_ublk,to_char(used_ublk),'none') used_ublk, sa.sql_text last_command
from v$session s, v$process p, v$transaction t, v$sqlarea sa,
(select blocking_session, count(*) ses_blocking_cnt from v$session
where blocking_session is not null group by blocking_session) sbc
where last_call_et > in_idle_threshold_minutes * 60
and s.username is not null -- if username is null then it's a background process.
and s.username <> 'APEX_PUBLIC_USER' -- don't kill Oracle APEX sessions.
and s.username <> 'RMAN' -- don't kill RMAN sessions.
and s.username <> 'RMAN_UK' -- don't kill RMAN_UK session from ukdrdb01 server.
and s.username <> 'RMAN_SP' -- don't kill RMAN_UK session from springfield server.
and s.username <> 'RMAN_SH' -- don't kill RMAN_UK session from shelbyville server.
and s.username <> 'DBADEPLOY' -- don't kill DBADEPLOY sessions.
and not (s.username in ('WWW','ONCALL') and osuser in ('apache','track','STNG')
and machine in ('bart','lisa','ned','marge','shelbyville','springfield',
'ottprodweb01','ottproddb01','ukdrdb02','shelbyville.pythian.com',
'springfield.pythian.com','ukdrdb01','ukdrdb01.pythian.com',
'ottprodweb01.pythian.com','ottproddb01.pythian.com','ukdrdb02.pythian.com'))
-- and s.program not in ('httpd@ned (TNS V1-V3)',
-- 'restri@bart (TNS V1-V3)',
-- 'httpd@lisa (TNS V1-V3)') -- allow some programs to idle (like httpd).
and s.type <> 'BACKGROUND' -- don't touch any background processes.
and s.program not like '%(J___)%' -- don't kill db jobs(dbms_job and scheduler)
and s.status <> 'KILLED' -- don't try to kill an already killed session
and s.machine <> hostname -- filter out local connections
--and blocking_session is null -- kill blockers only. Raj and I aggreed to kill all long idle sessions.
and s.paddr = p.addr
and s.taddr = t.addr(+)
and s.sql_id = sa.sql_id(+)
and s.sid = sbc.blocking_session(+)
) loop
BEGIN
--
if print_header = 1 then
print_header := 0;
message := lpad('sid',7)||' '||
lpad('serial#',7)||' '||
lpad('spid',7)||' '||
rpad('username',20)||' '||
rpad('program',15)||' '||
rpad('machine',20)||' '||
rpad('osuser',10)||' '||
rpad('logon_time',18)||' '||
lpad('last_call_et',12)||' '||
lpad('ses_blocking_cnt',16)||' '||
lpad('used_ublk',9)||' '||
rpad('last_command',60)||
chr(10);
writeappend_lob(p_clob => v_Body,p_string=>message);
dbms_output.put_line(lpad('sid',7)||' '||
lpad('serial#',7)||' '||
lpad('spid',7)||' '||
rpad('username',20)||' '||
rpad('program',15)||' '||
rpad('machine',20)||' '||
rpad('osuser',10)||' '||
rpad('logon_time',18)||' '||
lpad('last_call_et',12)||' '||
lpad('ses_blocking_cnt',16)||' '||
lpad('used_ublk',9)||' '||
rpad('last_command',60));
end if;
--
message := lpad(to_char(ises.sid),7)||' '||
lpad(ises.serial#,7)||' '||
lpad(ises.spid,7)||' '||
rpad(ises.username,20)||' '||
rpad(ises.program,15)||' '||
rpad(ises.machine,20)||' '||
rpad(ises.osuser,10)||' '||
to_char(ises.logon_time,'DD-MON-YY HH24:MI:SS')||' '||
lpad(ises.last_call_et,12)||' '||
lpad(ises.ses_blocking_cnt,16)||' '||
lpad(ises.used_ublk,9)||' '||
rpad(ises.last_command,60)||
chr(10);
writeappend_lob(p_clob => v_Body,p_string=>message);
dbms_output.put_line(lpad(to_char(ises.sid),7)||' '||
lpad(ises.serial#,7)||' '||
lpad(ises.spid,7)||' '||
rpad(ises.username,20)||' '||
rpad(ises.program,15)||' '||
rpad(ises.machine,20)||' '||
rpad(ises.osuser,10)||' '||
to_char(ises.logon_time,'DD-MON-YY HH24:MI:SS')||' '||
lpad(ises.last_call_et,12)||' '||
lpad(ises.ses_blocking_cnt,16)||' '||
lpad(ises.used_ublk,9)||' '||
rpad(ises.last_command,60));
--
kill_cmd := 'alter system kill session '''||ises.sid||','||ises.serial#||''' immediate';
dbms_output.put_line(kill_cmd);
any_idlers_killed := 1;
execute immediate kill_cmd;
exception
when marked_for_kill then
dbms_output.put_line(ises.sid||','||ises.serial# ||' marked for kill.');
continue;
END;
end loop;
--
if any_idlers_killed = 1 then
track.send_email('oracle@'||hostname,
in_notification_list,
'killed idle sessions on '||instance_name,
'<pre>'||v_Body||'</pre>');
dbms_output.put_line(v_Body);
end if;
end if;
--
close_lob(v_Body);
exception
when others then
dbms_output.put_line('error checking idle and blocking sessions in'||instance_name);
track.send_email('oracle@'||hostname,
in_notification_list,
'error checking idle and blocking sessions in'||instance_name,
'<pre>'||SQLERRM||'</pre>');
raise;
end;
/
No comments:
Post a Comment