{"id":190,"date":"2018-06-04T16:46:48","date_gmt":"2018-06-04T06:46:48","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=190"},"modified":"2018-06-04T16:46:48","modified_gmt":"2018-06-04T06:46:48","slug":"recover-from-missing-undo-tablespace-2","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2018\/06\/04\/recover-from-missing-undo-tablespace-2\/","title":{"rendered":"Recover from missing UNDO Tablespace"},"content":{"rendered":"

Solution<\/strong><\/em>
\nKill user sessions in RAC 10g<\/p>\n

Goal<\/strong><\/em>
\nThe 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID, <\/p>\n

Kill_Session_User(p_username VARCHAR2)\r\n\u2013 This takes the schema\/user name and will loop through all sessions for this user in gv$sessions\r\n-- Then uses supplies this SID, SERIAL#, INST_ID to Kill_Session_Job\r\n\r\nKill_Session_Job(p_sid NUMBER, p_serial NUMBER, p_inst_id NUMBER)\r\n-- This takes the session SID and SERIAL# along with the INSTANCE_ID number and schedules the local Kill_Session jobs on the RAC nodes<\/pre>\n
SERIAL#); as a local session on a local node.
\nTo kill from one node sessions across multiple nodes the following script has been created…<\/p>\n
 <\/p>\n
CREATE OR REPLACE PACKAGE BODY GISDBA.DBA_10g_RAC IS\r\n\r\n  PROCEDURE Kill_Session_User(p_username VARCHAR2) IS\r\n    d_stmt VARCHAR2(4000);\r\n  BEGIN\r\n    FOR c_stmt IN (\r\n      SELECT t1.sid, t1.serial#, t1.inst_id\r\n        FROM gv$session t1\r\n       WHERE t1.username = p_username\r\n     ) LOOP\r\n    DBMS_OUTPUT.Put_Line(' +++  USER = \"'||p_username||'\"  SID = \"'||c_stmt.sid||'\"  SERAL = \"'||c_stmt.serial#||'\"  INSTANCE = \"'||c_stmt.inst_id||'\"  +++');\r\n      Kill_Session_Job(p_sid     => c_stmt.sid,\r\n                       p_serial  => c_stmt.serial#,\r\n                       p_inst_id => c_stmt.inst_id);\r\n    END LOOP;\r\n    DBMS_OUTPUT.Put_Line('===== All the database sessions of user \"'||p_username||'\" killed successfully ! =====');\r\n\r\n  END Kill_Session_User;\r\n\r\n  PROCEDURE Kill_Session_Job(p_sid     NUMBER,\r\n                             p_serial  NUMBER,\r\n                             p_inst_id NUMBER) IS\r\n    JOB BINARY_INTEGER;\r\n    d_stmt VARCHAR2(4000);\r\n  BEGIN\r\n    d_stmt := 'begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => '||p_sid||', p_serial => '||p_serial||'); end;';\r\n    SYS.DBMS_JOB.Submit(job => job, what => d_stmt, instance => p_inst_id);\r\n    COMMIT;\r\n    DBMS_OUTPUT.Put_Line(d_stmt);\r\n  END Kill_Session_Job;\r\n\r\n  PROCEDURE Kill_Session(p_sid     NUMBER,\r\n                         p_serial  NUMBER) IS\r\n    d_stmt VARCHAR2(4000);\r\n  BEGIN\r\n    d_stmt := 'ALTER SYSTEM KILL SESSION '''||p_sid||', '||p_serial||''' IMMEDIATE';\r\n    DBMS_OUTPUT.Put_Line(d_stmt);\r\n    EXECUTE IMMEDIATE d_stmt;\r\n  END Kill_Session;\r\n\r\n  END DBA_10g_RAC;\r\n\/\r\n\r\n\r\n<\/pre>\n
Kill_Session(p_sid NUMBER, p_serial NUMBER)<\/p>\n
— This just does a simple ALTER SYSTEM KILL SESSION (sid,serial#)
\nSolution<\/p>\n
NOTE:<\/p>\n
This can be run as SYS or GISDBA<\/p>\n
GISDBA requires the following grants<\/p>\n
grant alter system to GISDBA;<\/p>\n
grant create job to GISDBA;<\/p>\n
To Kill All Sessions For A UserName
\nset serveroutput on size 1000000
\nexec GISDBA.DBA_10g_RAC.Kill_Session_User(p_username => ‘SCOTT’);<\/p>\n
Expected feedback<\/p>\n
++ USER = “SCOTT” SID = “470” SERAL = “35488” INSTANCE = “2” +++<\/p>\n
begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => 470, p_serial => 35488); end;<\/p>\n
+++ USER = “SCOTT” SID = “507” SERAL = “40797” INSTANCE = “1” +++<\/p>\n
begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => 507, p_serial => 40797); end;<\/p>\n
===== All the database sessions of user “SCOTT” killed successfully ! =====<\/p>\n
PL\/SQL procedure successfully completed.<\/p>\n
To Kill A Session on any node
\ndefine user=’SCOTT’
\nselect username,SID, SERIAL#,INST_ID,program from gv$session where username=’&user’;<\/p>\n
USERNAME SID SERIAL# INST_ID PROGRAM
\n—————————— ———- ———- ———- ————————————————
\nSCOTT 507 40797 1 sqlplus.exe
\nSCOTT 470 35488 2 sqlplus.exe<\/p>\n
set serveroutput on size 1000000
\nexec GISDBA.DBA_10g_RAC.Kill_Session_Job(p_sid => 470, p_serial => 35488, p_inst_id => 2);<\/p>\n
Expected feedback<\/p>\n
begin GISDBA.DBA_10g_RAC.Kill_Session(p_sid => 470, p_serial => 35488); end;<\/p>\n
PL\/SQL procedure successfully completed.<\/p>\n","protected":false},"excerpt":{"rendered":"
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) \u2013 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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-190","post","type-post","status-publish","format-standard","hentry","category-oracle-database"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/190","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=190"}],"version-history":[{"count":2,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/190\/revisions"}],"predecessor-version":[{"id":299,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/190\/revisions\/299"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=190"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=190"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=190"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}