Oracle Apps DBA - Useful Queries

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';