Friday, November 19, 2021

How To Monitor Rollback in Oracle Database

 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: