How to Kill Long Running Concurrent Job in R12

 

How to Kill Long Running Concurrent Job in R12

Step 1) Find SID and Serial Number for Concurrent Job

Step 2) Kill the long running session

Step 3) Update fnd_concurrent_requests table to mark the request in error and completed

STEP1:

SELECT a.request_id, d.sid, d.serial# ,d.osuser,

d.process , c.SPID ,d.inst_id

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id = 344625347--------------req id

AND a.phase_code = 'R';

STEP2:

select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID=&REQUEST_ID;

select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID=&REQUEST_ID;

alter system kill session ‘sid,serial#’ immediate:

STEP3:

 UPDATE fnd_concurrent_requests

SET phase_code = 'C', status_code = 'D'

WHERE Request_id=&request_id;

If this does not solve your problem then try restarting Standard Concurrent Manager and Output Post Processor Concurrent Manager.

Enable Diagnostics in Oracle Apps

 

Enable Diagnostics in Oracle Apps

The below steps can be followed for enabling diagnostics at user level.

1. Navigate to System Administrator responsibility> Profile> System>

 

 

2. Enter profile name: Utilities:Diagnostics .
    Enter Application User for whom you want to enable Diagnostics.

 

 

Enter value Yes at User level and Save the Changes . You can set yes at site level for all application users.

 

3. Navigate to System Administrator responsibility> Profile> System>

Enter profile name: Hide Diagnostics menu entry.

Enter Application User for whom you do not want to hide Diagnostics menu entry .

Enter value No at User level and Save the Changes .

 

 

 

 

 

4. Logout from Oracle Application and login again.Diagnostic would be enabled.

 

 

 

Current Running SQLs

 

OCT

31

How to find SQL,SQL_ID history on Oracle

How to find SQL,SQL_ID history on Oracle

Session related Queries

Last/Latest Running SQL
-----------------------
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value
/

Current Running SQLs
--------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15

select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
--  and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/

Query to find the Concurrent Request Trace File Path

 

Query to find the Concurrent Request Trace File Path

In Oracle Applications, we all submit concurrent requests.

As a developer when we need to debug a specific part in the program we will check the enable trace check box in the concurrent program definition window. This will generate a trace file in the server.

Here is the query to find the trace file path in the server for a particular Request Id.

SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id,

       'Trace Flag: ' || req.enable_trace,

          'Trace Name: '

       || dest.VALUE

       || '/'

       || LOWER (dbnm.VALUE)

       || '_ora_'

       || oracle_process_id

       || '.trc',

       'Prog. Name: ' || prog.user_concurrent_program_name,

       'File Name: ' || execname.execution_file_name

       || execname.subroutine_name,

          'Status : '

       || DECODE (phase_code, 'R', 'Runnin')

       || '-'

       || DECODE (status_code, 'R', 'Normal'),

       'SID Serial: ' || ses.SID || ',' || ses.serial#,

       'Module : ' || ses.module

  FROM fnd_concurrent_requests req,

       v$session ses,

       v$process proc,

       v$parameter dest,

       v$parameter dbnm,

       fnd_concurrent_programs_vl prog,

       fnd_executables execname

 WHERE req.request_id = &REQUEST_ID

   AND req.oracle_process_id = proc.spid(+)

   AND proc.addr = ses.paddr(+)

   AND dest.NAME = 'user_dump_dest'

   AND dbnm.NAME = 'db_name'

   AND req.concurrent_program_id = prog.concurrent_program_id

   AND req.program_application_id = prog.application_id

   AND prog.application_id = execname.application_id

   AND prog.executable_id = execname.executable_id;

long running concurrent request

 @RR to check the long run concurrent:

set pages 1000 lines 1000 pagesize 1000;
col program for a80;
select a.request_id ,decode(a.phase_code,'R','Running','Normal') Phase,
to_char(a.actual_start_date,'DD-MON-RRRR HH24:MI:SS')
Start_time , substr(b.user_concurrent_program_name,1,80) Program
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b
where a.concurrent_program_id = b.concurrent_program_id and
a.status_code = 'R'
order by Start_time;

@RR_SID to find the sid and serial number

set lines 200;
set pages 300;
col program for a50;
SELECT a.request_id, substr(e.user_concurrent_program_name,1,50) Program,d.sid, d.serial# ,d.status,to_char(d.logon_time,'DD-MON-YY HH24:MI:SS')logon, round(d.last_call_et/60) LCT
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,v$session d,
apps.fnd_concurrent_programs_tl e
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id=e.concurrent_program_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R'
--and d.sql_id='fnpyvpk41nd5s'
order by 7 desc;

@RR.det to check the parameter of concurrent;

set lines 200;
set pages 1000;
col program for a40;
col user_name for a20;
select a.request_id ,decode(a.phase_code,'R','Running','Normal') Phase,
to_char(a.actual_start_date,'DD-MON-RRRR HH24:MI:SS')
Start_time , substr(b.user_concurrent_program_name,1,40) Program, substr(a.argument_text,1,60) argument, c.user_name
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c
where a.concurrent_program_id = b.concurrent_program_id
and a.status_code = 'R'
and a.requested_by=c.user_id
order by Start_time;

@prerun to check the parameter of concurrent completed sucessfully 

set lines 200;
set pages 1000;
col user_name for a20;
col argument_text for a60;
SELECT a.request_id, To_Char(a.actual_start_date,'DD-MON HH24:MI') START_DATE,
  To_Char(a.actual_completion_date,'DD-MON HH24:MI') END_DATE, round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
 a.phase_code P,a.status_code S, a.argument_text,d.user_name
FROM   apps.fnd_concurrent_requests a,
            apps.fnd_concurrent_programs b ,
            apps.FND_CONCURRENT_PROGRAMS_TL c,
            apps.fnd_user d
WHERE       a.concurrent_program_id= b.concurrent_program_id AND
            b.concurrent_program_id=c.concurrent_program_id AND
            a.requested_by =d.user_id AND
       trunc(a.actual_completion_date) >= trunc(sysdate-7) AND
c.USER_CONCURRENT_PROGRAM_NAME like '%&Concurrent_program_name%'
    order by start_date desc;

@sess_io to check the session block changing

select * from v$sess_io where sid=&sid;

lock on table:

select c.owner,c.object_name,c.object_type,c.status,b.sid,b.serial#,b.status,b.osuser,b.machine,a.process,a.locked_mode
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

block Session 

SELECT
  blocking_session "BLOCKING_SESSION",
  sid "BLOCKED_SESSION",
  serial# "BLOCKED_SERIAL#",
  seconds_in_wait/60 "WAIT_TIME(MINUTES)"
FROM v$session
WHERE blocking_session is not NULL
ORDER BY blocking_session;

Block session with details:

  select a.process,a.sql_id,a.sid,a.serial#,a.blocking_session,a.final_blocking_session,to_char(a.logon_time,'DD-MON-YY HH24:MI:SS')logon,round(a.last_call_et/60) LCT,a.username,a.status,a.machine,a.program,a.module,a.action,a.event,a.client_identifier,sql_text
    from gv$sqlarea sqlarea, gv$session a
    where a.sql_hash_value= sqlarea.hash_value(+)
    and a.sql_address= sqlarea.address(+)
    and a.username is not null
    ---and a.status = 'ACTIVE'
    --and a.module like '%SQL De%'
    and a.sid in ('1050','')
    --and a.client_identifier='JDESAI'
    --and sql_text='begin FND_CP_OPP_REQ.postprocess (:reqid, :groupid, :flag, :errmsg); end;'
    --and a.module ='e:MSC:cp:po/POXRSR'
    and a.type='USER'
    order by 8 desc;

Request xxxxxx can no longer be canceled

 Problem:

when we try to cancel a concurrent request using the frontend “Cancel Request” button from the Administer > Concurrent > Manager form.
We are getting the following message:

“Request xxxxxx can no longer be canceled. The Concurrent Manager Process that was running this request has exited abnormally. The ICM will mark this request as completed with the error”

Solution.

connect to APPS,

1. Backup of FND_CONCURRENT_REQUESTS.

create table fnd_concurrent_requests_backup_12_Apr_22 as select * from fnd_concurrent_requests;

2. UPDATE.

UPDATE fnd_concurrent_requests

SET phase_code = ‘C’, status_code = ‘D’

WHERE Request_id=&request_id

3. Commit.

DB level collecting an traces.

Collect SID and Serial and enable the trace.

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>3258, serial#=>10207, sql_trace=>TRUE)

PL/SQL procedure successfully completed.

SQL> SELECT p.tracefile FROM   v$session s  JOIN v$process p ON s.paddr = p.addr WHERE  s.sid = 3258;

TRACEFILE

--------------------------------------------------------------------------------

/u02/app/oracle/diag/rdbms/ebsts1/EBSTS11/trace/EBSTS11_ora_233861.trc

------------------------------------------------------------------------------------------------------------

1. Create Tigger for particular user.

2. enable the trace and collect the trace 

3. Disable the traces.

CREATE OR REPLACE TRIGGER ON_MY_SCHEMA_LOGIN

AFTER LOGON ON DATABASE

WHEN ( USER = 'XXBPC_INTF' )

BEGIN  execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER = "XXBPC_INTF_TRACE"';

execute immediate 'alter session set events ''10046 trace name context forever, level 12''';

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

 

alter trigger ON_MY_SCHEMA_LOGIN enable;

 

 alter trigger ON_MY_SCHEMA_LOGIN disable;