Killing rman session in Oracle database post killing OS process
Verify instance name from SQLplus
SQL> define
DEFINE _DATE = "05-MAR-23" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "TEST_INSTANCE" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR = "ed" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1201000200" (CHAR)
DEFINE _RC = "10" (CHAR)
SQL>
Use below dynamic query to identify and create sql to kill session from database.
SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where MODULE = 'backup incr datafile' and program like 'rman%';
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
------------------------------------------------------------------------------------------------------------------------
alter system kill session '15,12523' immediate;
alter system kill session '242,39314' immediate;
alter system kill session '245,25995' immediate;
alter system kill session '356,65221' immediate;
alter system kill session '573,9644' immediate;
alter system kill session '699,1237' immediate;
alter system kill session '806,9422' immediate;
alter system kill session '921,17419' immediate;
alter system kill session '1380,64265' immediate;
alter system kill session '2635,13278' immediate;
alter system kill session '2738,35565' immediate;
alter system kill session '2853,58663' immediate;
alter system kill session '2976,18516' immediate;
alter system kill session '3094,13888' immediate;
alter system kill session '3207,41676' immediate;
alter system kill session '3309,56282' immediate;
alter system kill session '3426,39836' immediate;
alter system kill session '3549,4586' immediate;
18 rows selected.
SQL> alter system kill session '15,12523' immediate;
alter system kill session '242,39314' immediate;
alter system kill session '245,25995' immediate;
alter system kill session '356,65221' immediate;
alter system kill session '573,9644' immediate;
alter system kill session '699,1237' immediate;
alter system kill session '806,9422' immediate;
alter system kill session '921,17419' immediate;
alter system kill session '1380,64265' immediate;
alter system kill session '2635,13278' immediate;
alter system kill session '2738,35565' immediate;
alter system kill session '2853,58663' immediate;
alter system kill session '2976,18516' immediate;
alter system kill session '3094,13888' immediate;
alter system kill session '3207,41676' immediate;
alter system kill session '3309,56282' immediate;
alter system kill session '3426,39836' immediate;
alter system kill session '3549,4586' immediate;
alter system kill session '15,12523' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL>
System altered.
SQL> alter system kill session '245,25995' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL>
System altered.
SQL> alter system kill session '573,9644' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL>
System altered.
SQL>
System altered.
SQL> alter system kill session '921,17419' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> alter system kill session '1380,64265' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL>
System altered.
SQL> alter system kill session '2738,35565' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> alter system kill session '2853,58663' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL>
System altered.
SQL>
System altered.
SQL> alter system kill session '3207,41676' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL>
System altered.
SQL>
System altered.
SQL> alter system kill session '3549,4586' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> SQL>
SQL>
SQL>
SQL> alter system kill session '15,12523' immediate;
alter system kill session '242,39314' immediate;
alter system kill session '245,25995' immediate;
alter system kill session '356,65221' immediate;
alter system kill session '573,9644' immediate;
alter system kill session '699,1237' immediate;
alter system kill session '806,9422' immediate;
alter system kill session '921,17419' immediate;
alter system kill session '1380,64265' immediate;
alter system kill session '15,12523' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.
SQL> alter system kill session '2635,13278' immediate;
alter system kill session '2738,35565' immediate;
alter system kill session '242,39314' immediate
*
ERROR at line 1:
ORA-00030: User session ID does not exist.
SQL>
Comments
Post a Comment