Home » » Scripts

Scripts

Written By Srikrishna Murthy Annam on Monday, September 21, 2009 | 5:34 PM

Find trace file for a Concurrent Request :
SQL>

prompt
accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:'
prompt

column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off

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','Running')
||'-'||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
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;

Concurrent Request Status Query :
SQL>
set linesize 300;
set head on;
col sid_serial for a13;
col db_pid for a6;
col CMGR_Program for a65;
col user_name for a13;
col phase for a10;
col status for a10;
col start_time for a11;

break on USER_NAME

SELECT fu.user_name user_name,
TO_CHAR(NVL(cr.actual_start_date, cr.requested_start_date), 'DD.MM HH24:MI') start_time,
cr.request_id request_id,
decode(cr.parent_request_id, -1, 0, cr.parent_request_id) par_req_id,
DECODE(cr.phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running', cr.phase_code) phase,
DECODE(cr.status_code, 'A', 'Waiting', 'B', 'Resuming', 'C', 'Normal', 'D', 'Cancelled', 'E', 'Errored', 'F', 'Scheduled',
'G', 'Warning', 'H', 'On Hold', 'I', 'Normal', 'M', 'No Manager', 'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended', 'T',
'Terminating', 'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated', 'Z', 'Waiting', cr.status_code) status,
cr.oracle_process_id db_pid,
vs.SID || ',' || vs.serial# sid_serial,
(SELECT SUBSTR(cp.concurrent_program_name || '-' || cpl.user_concurrent_program_name, 1, 65)
FROM APPS.fnd_concurrent_programs cp,
APPS.fnd_concurrent_programs_tl cpl
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cpl.application_id = cr.program_application_id
AND cpl.concurrent_program_id = cr.concurrent_program_id
AND cpl.LANGUAGE = USERENV('LANG')
) CMGR_Program
FROM APPS.fnd_concurrent_requests cr,
v$process vp,
v$session vs,
APPS.fnd_user fu
WHERE
cr.phase_code <> 'I'
AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'C', 'I')
AND cr.oracle_process_id = vp.spid (+)
AND cr.oracle_session_id = vs.audsid (+)
AND fu.user_id = cr.requested_by
ORDER BY sid_serial,
phase_code desc,
STATUS_CODE,
2 DESC,
1
;

Sample output

USER_NAME     START_TIME  REQUEST_ID PAR_REQ_ID PHASE      STATUS     DB_PID SID_SERIAL    CMGR_PROGRAM
------------- ----------- ---------- ---------- ---------- ---------- ------ ------------- -----------------------------------------------------------------
501282944     26.09 21:00   13737882   12462882 Pending    Standby           ,             FNDGSCST-Gather Schema Statistics
SYSADMIN      22.09 01:30   13892885   13694882 Pending    Standby           ,             FNDCPPUR-Purge Concurrent Request and/or Manager Data
21.09 18:39   13921062   13921061 Pending    Standby           ,             FNDOAMCOL-OAM Applications Dashboard Collection

How to Check the Product Installation Status (Installed Modules)


  1. Run the script $AD_TOP/adutconf.sql.

  2. This script will generate a file called adutconf.lst. Find this section in the file "Product Installation Status and other product information."



Script to extract the information about the nodes
set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set pagesize 132
set linesize 80
col PLATFORM_CODE form a5
col HOST form a20
col DOMAIN form a30
col WEBHOST form a30
col VIRTUAL_IP form a20
col status form a20
col ConcMgr form a8
col Forms form a8
col WebServer form a8
col Admin form a8
col Database form a8
col last_monitored form a40
--
select
NODE_NAME,
to_char(CREATION_DATE, 'DD-MON-RR HH24:MI') creation_date,
PLATFORM_CODE,
decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr,
decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms,
decode(SUPPORT_WEB,'Y','Web', 'No') WebServer,
decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin,
decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database,
to_char(LAST_MONITORED_TIME, 'DD-MON-RR HH24:MI:SS') last_monitored,
NODE_MODE,
SERVER_ADDRESS,
HOST,
DOMAIN,
WEBHOST,
VIRTUAL_IP,
SERVER_ID
from fnd_nodes
where node_name != 'AUTHENTICATION;

Which FND_USER is locking that table
SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins      fl
,fnd_user        fu
,v$locked_object vlocked
,v$process       vp
,v$session       vs
,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';

To determine what request groups and concurrent program is assigned to
select request_group_name from FND_REQUEST_GROUPS where REQUEST_GROUP_ID =(SELECT request_group_id FROM FND_REQUEST_GROUP_UNITS WHERE REQUEST_UNIT_ID=(select CONCURRENT_PROGRAM_ID from
FND_CONCURRENT_PROGRAMS where CONCURRENT_PROGRAM_NAME ='<report short name>'));

To Check The Profile Value At All Levels
clear col
clear breaks

set pages 9000
set lines 132
set verify off

col pov    format a45 word_wrapped  heading "Profile Value"
col lo     format a5                heading "Level"
col lov    format a40               heading "Value"
col pon    noprint    new_value n_pon
col upon   noprint    new_value n_upon
col sda    noprint    new_value n_sda
col eda    noprint    new_value n_eda
col cd     noprint    new_value n_cd
col cb     noprint    new_value n_cb
col d      format a78 word_wrapped noprint    new_value n_d

break on pon skip page

ttitle -
"Creation Date:   "      n_cd    "    Created By: "          n_cb   -
skip 1 -
"Date Active From:"      n_sda   "    To:"     n_eda  -
skip 1 -
"Profile Option Name: "  n_pon   -
skip 1 -
"User Profile Name:   "  n_upon  -
skip 1 -
"Profile Description: "          -
skip 1 -
n_d                              -

select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Site'                                   lo
,             'SITE'                                   lov
,             fpov.profile_option_value                pov
from       FND_PROFILE_OPTIONS_TL      fpot
,          FND_PROFILE_OPTIONS         fpo
,          FND_PROFILE_OPTION_VALUES   fpov
,          FND_USER                    fu
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.application_id       = fpov.application_id
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpot.language            = Userenv('Lang')
and      fpov.level_id            = 10001                  /* Site Level */
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Apps'                                   lo
,             fa.application_name                      lov
,             fpov.profile_option_value                pov
from      FND_PROFILE_OPTIONS_TL      fpot
,         FND_PROFILE_OPTIONS         fpo
,         FND_PROFILE_OPTION_VALUES   fpov
,         FND_USER                    fu
,         FND_APPLICATION_TL          fa
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpot.language            = Userenv('Lang')
and      fpov.level_id            = 10002                  /* Application Level */
and      fpov.level_value         = fa.application_id
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'Resp'                                   lo
,             frt.responsibility_name                   lov
,             fpov.profile_option_value                pov
from     FND_PROFILE_OPTIONS_TL      fpot
,        FND_PROFILE_OPTIONS         fpo
,        FND_PROFILE_OPTION_VALUES   fpov
,        FND_USER                    fu
,        FND_RESPONSIBILITY_TL          frt
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name        = fpo.profile_option_name
and      fpo.profile_option_id           = fpov.profile_option_id
and      fpo.created_by                  = fu.user_id
and      frt.language                    = Userenv('Lang')
and      fpot.language                   = Userenv('Lang')
and      fpov.level_id                   = 10003                  /* Responsibility Level */
and      fpov.level_value                = frt.responsibility_id
and      fpov.level_value_application_id = frt.application_id
union all
select        lpad(fpo.profile_option_name,55)         pon
,             lpad(fpot.user_profile_option_name,55)   upon
,             fpot.description                          d
,             lpad(fpo.start_date_active,15)           sda
,             lpad(fpo.end_date_active,15)             eda
,             lpad(fpo.creation_date,15)               cd
,             lpad(fu.user_name,20)                    cb
,             'User'                                   lo
,             fu2.user_name                            lov
,             fpov.profile_option_value                pov
from     FND_PROFILE_OPTIONS_TL      fpot
,        FND_PROFILE_OPTIONS         fpo
,        FND_PROFILE_OPTION_VALUES   fpov
,        FND_USER                    fu
,        FND_USER                    fu2
where    fpot.user_profile_option_name like '&&profile_like'
and      fpot.profile_option_name = fpo.profile_option_name
and      fpo.profile_option_id    = fpov.profile_option_id
and      fpo.created_by           = fu.user_id
and      fpov.level_id            = 10004                  /* User Level */
and      fpov.level_value         = fu2.user_id
and      fpot.language            = Userenv('Lang')
order by upon, lo, lov;
Share this article :

Related Articles By Category



+ comments + 1 comments

[...] Concurrent Program Tracing without bind variables 1) Follow the following navigation to enable logging for conc prog Goto Sysadmin > Concurrent > Program > Define Query the concurrent program Check the trace box to enable trace 2) Execute the concurrent program using the following navigation and note down the request id 3) Collect the trace file using the script provided here [...]

Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger