Recovery after killing a large transaction

Summary 

After killing a large running transaction, either by killing the process or aborting the database, the database may appear to hang and/or the SMON background process is taking all the available cpu. Basically SMON is in the progress of rolling back this terminated large transaction, which can be time consuming. As a result of this users often try to restart the database, but because a shutdown immediate will hang, the database is often aborted. Please note that stopping the database will actually make things worse and will not decrease the amount of work SMON needs to perfom to complete the rollback. 

The message 'Waiting for smon to disable tx recovery' will posted in the alert log as well. 

First we need to determine the progress SMON is making. It is very possible the SMON process to work with one rollback segment. You can find it using the following query:

SELECT r.NAME "RB Segment Name", dba_seg.size_mb,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || 
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, 
v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE, 
v$session.PROGRAM, v$session.module, action 
FROM v$lock l, v$process p, v$rollname r, v$session, 
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments 
WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg 
WHERE l.SID = p.pid(+) AND 
v$session.SID = l.SID AND 
TRUNC (l.id1(+)/65536)=r.usn AND 
l.TYPE(+) = 'TX' AND 
l.lmode(+) = 6 
AND r.NAME = dba_seg.segment_name
--AND v$session.username = 'SYSTEM'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;
After killing the shadow process or aborting the database the v$transaction entry is lost, so you cannot estimate by examining v$transaction.used_ublk how the rollback procedure proceeds. 

The following queries are available to monitor the progress of the transaction recovery:

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done", 
undoblockstotal-undoblocksdone "ToDo", 
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) 
"Finish at" FROM v$fast_start_transactions; 

       USN STATE                 Total       Done       ToDo Finish at
---------- ---------------- ---------- ---------- ---------- --------------------
        88 RECOVERED             36591      36591          0 17-NOV-2018 17:02:44
        36 RECOVERING              464        100        364 10-DEC-2018 03:32:06
        71 RECOVERING              309        193        116 20-NOV-2018 19:19:23
         8 RECOVERING          2350785     189363    2161422 18-NOV-2018 00:51:51

Run the above query several times in a row, this will give you a good idea on how SMON is progressing. 

- In some versions the cputime does not work (always 0), hence the estimated completion time will not be displayed 
- In some cases the v$fast_start_transactions view will not work. If this is the case then you can query the internal data dictionary 
view x$ktuxe, the 'ktuxesiz' column represents the remaining number of undo blocks required for rollback:

   select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
   from x$ktuxe
   where ktuxecfl = 'DEAD'; 

Useful EBS R12 Queries for Apps DBA

Below are some useful EBS R12 Queries for Apps DBA. We can modify these queries as per our requirement.

To find SQL ID, SQL_TEXT from Request ID / Others
=========================================
col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30
select /*+ ordered */
fcr.request_id,
fcp.user_concurrent_program_name
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      inst.inst_name
,      sa.SQL_ID
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      gv$process pro
,      gv$session sess
,      gv$sqlarea sa
,      sys.v_$active_instances inst
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    sess.inst_id = inst.inst_number (+)
and    request_id in (select request_id from fnd_amp_requests_v)
and sa.SQL_ID='2xzwjprnn80x3'
;

To Kill Any Inactive Session in RAC Database
========================================
select 'alter system kill session ''' ||c.sid||','||c.serial#||''||',@' || inst_id || '''' || ' immediate; ' from gv$session c
where program like 'frmweb%' and module like '%frm%' and seconds_in_wait > 18000 and c.status='INACTIVE' ;


Find Concurrent Request from sql_id from AWR Report
============================================
select c.request_id, status_code, phase_code, USER_CONCURRENT_PROGRAM_NAME,d.user_name requestor, s.sid,p.spid,s.process,s.osuser
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct, apps.fnd_user d
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and c.requested_by = d.user_id
and s.sid in (select sid from gv$session where sql_id='2xzwjprnn80x3');

Kill Inactive Forms Sessions
======================
set pagesize 1200;
set linesize 1200;
select 'kill -9 ' || p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid in (select sid from v$session where status like 'INACTIVE' and logon_time < sysdate-0.33 and action like 'FRM:%');

Find details on any specific Inactive Program / Action 
===================================================
select distinct b.sid,b.serial# ,b.status,b.program,b.username,b.action,b.module,
to_char( b.logon_time, 'dd-MON-yyyy hh24:mi:ss' ) logon_time,
trunc( sysdate-b.logon_time ) "Dy",
trunc( mod( (sysdate-b.logon_time)*24, 24 ) ) "Hr",
trunc( mod( (sysdate-b.logon_time)*24*60, 60 ) ) "Mi",
trunc( mod( (sysdate-b.logon_time)*24*60*60, 60 ) ) "Sec"
from gV$access a,gv$session b, gv$process c
where a.sid=b.sid
and b.paddr=c.addr
and b.status='INACTIVE'
and (b.action like '%FRM%' or b.action like '%frm%' or b.program like '%TOAD%' or b.program like '%toad%' or b.program like
'SQL%' or b.program like '%sql%' or b.program like '%FRM%'
or b.program like '%frm%' or b.action like 'SQL%' or b.action like 'sql%' or b.action like 'TOAD%' or b.action like 'toad%')
and (trunc( mod( (sysdate-b.logon_time)*24,24)) >=12 or trunc( sysdate-b.logon_time )>=1);


Find Top CPU Consuming Inactive / Active Sessions
============================================
SELECT s.SID, s.serial#, p.spid AS "OS PID",s.username, s.status, s.module, st.VALUE/100 AS "CPU sec"
FROM gv$sesstat st, gv$statname sn, gv$session s, gv$process p
WHERE sn.NAME = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.SID = s.SID
AND s.paddr = p.addr
AND s.last_call_et > 18000
--and s.status='INACTIVE' and rownum < 25
ORDER BY st.VALUE DESC ;

To check pfile/spfile parameters Change History
======================================
set linesize 155
col time for a20
col parameter_name format a50
col value for a20
col snap_id for 9999999
break on instance skip 3
select a.snap_id,to_char(end_interval_time,'DD-MON-YY HH24:MI:SS') TIME, parameter_name, value
from dba_hist_parameter a, dba_Hist_snapshot b, v$instance v
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and parameter_name like nvl('&parameter_name',parameter_name)
and v.instance_number = a.instance_number
order by 1,2
/

To check all hidden parameters

======================================
set lines 200
col "Parameter" for a60
col "Session_Value" for a30
col "Instance_Value" for a30
SELECT a.ksppinm "Parameter",
       b.ksppstvl "Session_Value",
       c.ksppstvl "Instance_Value"
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE '/_%' escape '/'; 

10046 Trace with Binds & Waits for concurrent request
=============================================
1) select oracle_process_id from fnd_concurrent_requests where request_id='&req_id';
2) select inst_id,pid,addr from gv$process where spid='&oracle_process_id'; (provide spid from 1st query)
3) select sid,serial#,paddr,sql_id from gv$session where paddr='&addr' and inst_id='&inst_id'; (provide addr from 2nd query)  ## add other columns, if you would like to
4) select sql_fulltext from v$sql where sql_id='&sql_id' and inst_id='&inst_id';  ## add other columns, if you would like to

==== OR =====

3) oradebug setorapid <pid> (Provide pid – from 2nd query)
4) oradebug unlimit
5) oradebug event 10046 trace name context forever,level 12
6) oradebug tracefile_name (It will show the trace file location)
7) oradebug event 10046 trace name context off; (Execute this command once Concurrent request is completed)

Repeat above steps for as many concurrent requests.

==> tkprof <tracefile_name> <outfile_name> explain=apps/<pwd> sort=exeela,fchela sys=no