Status of Concurrent Manager & Requests:
How to Find Session Details of
conurrent requet id:
select
a.sid,a.event,a.sql_id,a.blocking_session,a.action from v$session a
,V$process b,apps.fnd_concurrent_requests c where a.paddr=b.addr and b.spid=c.oracle_process_id
and c.request_id='&request_id';
How to Find Running Concurrent Programs:
select
user_concurrent_program_name from apps.fnd_conc_req_summary_v where
status_code='R' and phase_code='R';
Concurrent Manager Status:
set verify off
set lines 256
set trims ON
set pages 60
col concurrent_queue_id format
99999 heading "QUEUE Id"
col concurrent_queue_name format
a20 trunc heading "QUEUE Code"
col user_concurrent_queue_name
format a30 trunc heading "Concurrent Queue Name"
col max_processes format 999
heading "Max"
col running_processes format 999
heading "Act"
col running format 999 heading
"Run"
col target_node format a15 heading
"Node"
col status format a12 trunc
heading "Status"
col run format 9999 heading 'Run'
col pend format 9999 heading
'Pending'
col cmgr_program FOR a65;
SELECT 'Instance : '
||NAME instance_name
FROM v$database;
Prompt ===========================
Prompt concurrent manager status
Prompt ===========================
SELECT q.concurrent_queue_id,
q.concurrent_queue_name,
q.user_concurrent_queue_name,
q.target_node,
q.max_processes,
q.running_processes,
running.run running,
pending.pend,
Decode(q.control_code, 'D',
'Deactivating',
'E', 'Deactivated',
'N', 'Node unavai',
'A', 'Activating',
'X', 'Terminated',
'T', 'Terminating',
'V', 'Verifying',
'O', 'Suspending',
'P', 'Suspended',
'Q', 'Resuming',
'R', 'Restarting') status
FROM (SELECT
concurrent_queue_name,
COUNT(phase_code) run
FROM
fnd_concurrent_worker_requests
WHERE phase_code = 'R'
AND hold_flag != 'Y'
AND requested_start_date <=
SYSDATE GROUP BY concurrent_queue_name) running, (SELECT
concurrent_queue_name, COUNT(phase_code) pend FROM
fnd_concurrent_worker_requests WHERE phase_code = 'P' AND hold_flag != 'Y'
AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name)
pending, apps.fnd_concurrent_queues_vl q WHERE q.concurrent_queue_name =
running.concurrent_queue_name(+) AND q.concurrent_queue_name =
pending.concurrent_queue_name(+) AND q.enabled_flag = 'Y' ORDER BY Decode(q.application_id,
0, Decode(q.concurrent_queue_id, 1, 1,4, 2)), Sign(q.max_processes) DESC,q.concurrent_queue_name,
q.application_id;
List of Concurrent Requests
Completed with Error in last two hours:
set linesize 200 pagesize 200
feedback on;
col USER_CONCURRENT_PROGRAM_NAME
for a40
col COMPLETION_TEXT for a60
select
a.user_concurrent_program_name ,
REQUEST_ID,DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D',
'Cancelled' ,'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T',
'Terminating', 'M', 'No Manager' , 'X','Terminated', 'C', 'Normal',
status_code) status_code , to_char(ACTUAL_START_DATE,'DD-MON-YYYY
HH24:MI:SS') START_DATE ,to_char(actual_completion_date,'DD-MON-YYYY
HH24:MI:SS') END_DATE,COMPLETION_TEXT from fnd_concurrent_programs_tl a,
fnd_concurrent_requests b where
a.concurrent_program_id=b.concurrent_program_id and actual_completion_date
> sysdate - (2/24) and phase_code = 'C' and status_code = 'E';
List of Concurrent Requests
Completed with Warninng in last two hours:
set linesize 200 pagesize 200 feedback on;
col USER_CONCURRENT_PROGRAM_NAME for a40
col COMPLETION_TEXT for a50
select a.user_concurrent_program_name , REQUEST_ID
,PHASE_CODE , STATUS_CODE , to_char(ACTUAL_START_DATE,'DD-MON-YYYY
HH24:MI:SS') START_DATE ,to_char(actual_completion_date,'DD-MON-YYYY
HH24:MI:SS') END_DATE,COMPLETION_TEXT from fnd_concurrent_programs_tl a,
fnd_concurrent_requests b where
a.concurrent_program_id=b.concurrent_program_id and actual_completion_date
> sysdate - (2/24) and phase_code = 'C' and status_code = 'G' order by
actual_completion_date;
List of Long Running Requests:
set linesize 200 pagesize 200 feedback on;
col USER_CONCURRENT_PROGRAM_NAME for a40;
select a.user_concurrent_program_name , REQUEST_ID
,PHASE_CODE , STATUS_CODE , to_char(ACTUAL_START_DATE,'DD-MON-YYYY
HH24:MI:SS') START_DATE ,to_char(actual_completion_date,'DD-MON-YYYY
HH24:MI:SS') END_DATE,COMPLETION_TEXT from fnd_concurrent_programs_tl a,
fnd_concurrent_requests b
where a.concurrent_program_id=b.concurrent_program_id and
actual_start_date > sysdate - 1 and phase_code = 'R' and
status_code = 'R';
Determine which concurrent Manager
ran the particular request:
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
Determine the details of particular
CM request:
set linesize 200 pagesize 200;
col USER_CONCURRENT_PROGRAM_NAME for a40
col completion_text for a50
SELECT request_id, user_concurrent_program_name,
DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating',
'M', 'No Manager' , 'X','Terminated', 'C', 'Normal',
status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss')
Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss')
completion_date,completion_text FROM apps.fnd_conc_req_summary_v WHERE
request_id = '&req_id' ORDER BY 6 DESC;
TRACE Enabled Concurrent Porgrams:
select a.CONCURRENT_PROGRAM_ID,
b.USER_CONCURRENT_PROGRAM_NAME "Program_Name", a.CONCURRENT_PROGRAM_NAME
"Short_Name", a.APPLICATION_IDfrom apps.fnd_concurrent_programs a,
apps.fnd_concurrent_programs_tl b where
a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and a.ENABLE_TRACE='Y';
Workflow Mailer:
Check the status of the
componenet:
set pagesize 400
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15
select
fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL
fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id =
fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS ,
STARTUP_MODE , COMPONENT_NAME;
Find Workflow Mailer logfile:
select
fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,'WFMLRSVC','maile
r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id,
fcp.logfile_name
from fnd_concurrent_queues fcq,
fnd_concurrent_processes fcp , fnd_lookups fl
where
fcq.concurrent_queue_id=fcp.concurrent_queue_id and
fcp.process_status_code='A'
and
fl.lookup_type='CP_PROCESS_STATUS_CODE' and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name
in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;
Ready Count :
select msg_state,count(*) from
applsys.aq$wf_notification_out group by msg_state;
Check The even't status in wf_deferred queue:
Ready = Activity is ready to be
processed
Delayed = Activity will be
processed later
Retained = Activity was already
processed
Exception = Activity had an error
col event_name for a40
col state for a13
select wfd.user_data.event_name
EVENT_NAME,
decode(wfd.state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(substr(wfd.state,1,12))) State,
count(*) COUNT
from applsys.wf_deferred wfd
group by wfd.user_data.event_name, wfd.state
order by 3 desc, 1 asc;
select
decode(wfd.state,
0, '0 = Ready',
1, '1 = Delayed',
2, '2 = Retained',
3, '3 = Exception',
to_char(substr(wfd.state,1,12))) State,
count(*) COUNT
from applsys.wf_deferred wfd
group by wfd.state
order by 2 desc, 1 asc;
R122 adop queries:
adop session status:
set pagesize 200;
set linesize 160;
col PREPARE_STATUS format a15
col node_name format a30
col APPLY_STATUS format a15
col CUTOVER_STATUS format a15
col ABORT_STATUS format a15
col STATUS format a15
select NODE_NAME,ADOP_SESSION_ID,
PREPARE_STATUS , APPLY_STATUS ,CUTOVER_STATUS , CLEANUP_STATUS ,
ABORT_STATUS , STATUS from AD_ADOP_SESSIONS where ADOP_SESSION_ID=66;
Note: Y denotes that the phase is
done
N denotes that the phase has not
been completed
X denotes that the phase is not
applicable
R denotes that the phase is
running (in progress)
F denotes that the phase has
failed
P (is applicable only to APPLY
phase) denotes at least one patch is already applied for the session id
C denotes that the status of this
ADOP session has completed
cutover statuses:
cutover_status='Y' 'COMPLETED'
cutover_status not in
('N','Y','X') and status='F' 'FAILED'
cutover_status='0' 'CUTOVER
STARTED'
cutover_status='1' 'SERVICES
SHUTDOWN COMPLETED'
cutover_status='3' 'DB CUTOVER
COMPLETED'
cutover_status='D' 'FLIP SNAPSHOTS
COMPLETED'
cutover_status='4' 'FS CUTOVER
COMPLETED'
cutover_status='5' 'ADMIN STARTUP
COMPLETED'
cutover_status='6' 'SERVICES
STARTUP COMPLETED'
cutover_status='N' 'NOT STARTED'
cutover_status='X' 'NOT
APPLICABLE'
fs_clone status:
set linesize 300
col node_name format a15
col clone_status format a20
col driver_file_name format a20
col clone_status format a20
select
node_name,clone_status,driver_file_name,status,to_char(start_date,'DD-MON-YYYY
HH:MI:SS PM'),to_char(end_date,'DD-MON-YYYY HH:MI:SS PM') from
ad_adop_session_patches where adop_session_id=89;
To find the patches applied in the
particular adop session:
select distinct BUG_NUMBER from
AD_ADOP_SESSION_PATCHES where adop_session_id='&session_id';
set linesize 300;
select distinct
bug_number,node_name,to_char(start_date ,'DD-MON-YYYY HH24:MI:SS')
START_DATE,to_char(end_date,'DD-MON-YYYY HH24:MI:SS')END_DATE from
ad_adop_session_patches where adop_session_id=67 group by bug_number,node_name,start_date,end_date
order by bug_number;
select distinct
bug_number,node_name,to_char(start_date ,'DD-MON-YYYY HH24:MI:SS')
START_DATE,to_char(end_date,'DD-MON-YYYY HH24:MI:SS')END_DATE from
ad_adop_session_patches where adop_session_id=&session_id and
bug_number='&bug_number' group by
bug_number,node_name,start_date,end_date order by bug_number;
AD,TXK VERSIONS:
SELECT abbreviation, codelevel
FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');
ETCC QUERIES:
set linesize 300;
col node_name format a10;
col database_name format a10;
col check_message format a50;
select database_name,node_name
,check_date ,component_name,component_version,check_message from
apps.TXK_TCC_RESULTS where database_name='&DB_NAME';
|
