Recover from missing UNDO Tablespace

Solution
Kill user sessions in RAC 10g

Goal
The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID,

Kill_Session_User(p_username VARCHAR2)
– This takes the schema/user name and will loop through all sessions for this user in gv$sessions
-- Then uses supplies this SID, SERIAL#, INST_ID to Kill_Session_Job

Kill_Session_Job(p_sid NUMBER, p_serial NUMBER, p_inst_id NUMBER)
-- This takes the session SID and SERIAL# along with the INSTANCE_ID number and schedules the local Kill_Session jobs on the RAC nodes

SERIAL#); as a local session on a local node.
To kill from one node sessions across multiple nodes the following script has been created…

 

CREATE OR REPLACE PACKAGE BODY GISDBA.DBA_10g_RAC IS

  PROCEDURE Kill_Session_User(p_username VARCHAR2) IS
    d_stmt VARCHAR2(4000);
  BEGIN
    FOR c_stmt IN (
      SELECT t1.sid, t1.serial#, t1.inst_id
        FROM gv$session t1
       WHERE t1.username = p_username
     ) LOOP
    DBMS_OUTPUT.Put_Line(' +++  USER = "'||p_username||'"  SID = "'||c_stmt.sid||'"  SERAL = "'||c_stmt.serial#||'"  INSTANCE = "'||c_stmt.inst_id||'"  +++');
      Kill_Session_Job(p_sid     => c_stmt.sid,
                       p_serial  => c_stmt.serial#,
                       p_inst_id => c_stmt.inst_id);
    END LOOP;
    DBMS_OUTPUT.Put_Line('===== All the database sessions of user "'||p_username||'" killed successfully ! =====');

  END Kill_Session_User;

  PROCEDURE Kill_Session_Job(p_sid     NUMBER,
                             p_serial  NUMBER,
                             p_inst_id NUMBER) IS
    JOB BINARY_INTEGER;
    d_stmt VARCHAR2(4000);
  BEGIN
    d_stmt := 'begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => '||p_sid||', p_serial => '||p_serial||'); end;';
    SYS.DBMS_JOB.Submit(job => job, what => d_stmt, instance => p_inst_id);
    COMMIT;
    DBMS_OUTPUT.Put_Line(d_stmt);
  END Kill_Session_Job;

  PROCEDURE Kill_Session(p_sid     NUMBER,
                         p_serial  NUMBER) IS
    d_stmt VARCHAR2(4000);
  BEGIN
    d_stmt := 'ALTER SYSTEM KILL SESSION '''||p_sid||', '||p_serial||''' IMMEDIATE';
    DBMS_OUTPUT.Put_Line(d_stmt);
    EXECUTE IMMEDIATE d_stmt;
  END Kill_Session;

  END DBA_10g_RAC;
/

Kill_Session(p_sid NUMBER, p_serial NUMBER)

— This just does a simple ALTER SYSTEM KILL SESSION (sid,serial#)
Solution

NOTE:

This can be run as SYS or GISDBA

GISDBA requires the following grants

grant alter system to GISDBA;

grant create job to GISDBA;

To Kill All Sessions For A UserName
set serveroutput on size 1000000
exec GISDBA.DBA_10g_RAC.Kill_Session_User(p_username => ‘SCOTT’);

Expected feedback

++ USER = “SCOTT” SID = “470” SERAL = “35488” INSTANCE = “2” +++

begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => 470, p_serial => 35488); end;

+++ USER = “SCOTT” SID = “507” SERAL = “40797” INSTANCE = “1” +++

begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => 507, p_serial => 40797); end;

===== All the database sessions of user “SCOTT” killed successfully ! =====

PL/SQL procedure successfully completed.

To Kill A Session on any node
define user=’SCOTT’
select username,SID, SERIAL#,INST_ID,program from gv$session where username=’&user’;

USERNAME SID SERIAL# INST_ID PROGRAM
—————————— ———- ———- ———- ————————————————
SCOTT 507 40797 1 sqlplus.exe
SCOTT 470 35488 2 sqlplus.exe

set serveroutput on size 1000000
exec GISDBA.DBA_10g_RAC.Kill_Session_Job(p_sid => 470, p_serial => 35488, p_inst_id => 2);

Expected feedback

begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => 470, p_serial => 35488); end;

PL/SQL procedure successfully completed.