Scripts for Apps DBA

Connect as sysdba and execute below Query to find out blocking sessions

set lines 130
set pages 200
column module format a20
column program format a20
column username format a15

select s.sid,s.serial#,s.status,p.spid,s.module,s.action,s.program
from v$session s,v$process p
where s.sid in (select session_id from dba_locks where blocking_others='Blocking')
and s.paddr=p.addr

Note: Kill only INACTIVE blocking sessions. For Inactive blocking sessions,
Event will be as shown below:
SQL> select EVENT from gv$session_wait where sid=&sid;
Enter value for sid: 19
old   1: select EVENT from gv$session_wait where sid=&sid
new   1: select EVENT from gv$session_wait where sid=19

EVENT
----------------------------------------------------------------
SQL*Net message from client

Locks in AP Module
column object_name format a40
SELECT a.object_id, a.session_id, b.object_name
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.owner in ('AP')

Another Query with more details
select s.sid sid_no,s.serial#,p.spid,s.action  action,a.user_name user_name,
to_char(b.start_time,'DD-MON-YYYY HH24:MI') stime
from fnd_user a, fnd_logins b, v$session s, v$process p
  where a.user_id = b.user_id
  and p.addr = s.paddr
  and s.process = b.spid
  and b.end_time is null
  and b.start_time > trunc(sysdate) - 1
  and a.user_name = '&login_id'
  and s.module like 'AP%'


set lines 130
set pages 200
column userinfo heading "ORACLE/OS User" format a25
column terminal heading "Term" format a6
column process heading "Parent|Process ID" format a10
column "sid (audsid)" format a20
column spid heading "Shadow|Process ID" format a10
column event heading "Waiting" format a30

select s.username||' '||s.osuser||' ('||decode(s.terminal,'unknown','?','UNKNOWN','?',s.terminal) ||')' userinfo,
s.sid||','|| s.serial# ||' ('|| s.audsid ||')' "sid (audsid)",   p.spid, w.event , w.p1
from v$session s, v$process p ,v$session_wait w
where p.addr = s.paddr and s.sid = w.sid and s.process = '&form_process_id' ;

Sample Output:
Enter value for form_process_id: 19721
old   5:      and s.process = '&form_process_id'
new 5:      and s.process = '19721'

                                               Shadow                 
ORACLE/OS User            sid (audsid)         Process ID                Waiting                                P1
------------------------- -------------------- ----------     ------------------------------ ----------
APPS applmgr ()           22,21485 (615657)    27872                    SQL*Net message from client    1952673792

Concurrent jobs Details

set lines 130
set pages 200
col os form A7 head AppProc
col spid form a6 head SPID
col program form A50 trunc
set pages 38
col time form 99999.99 head Elapsed
col "Req Id" form 99999999
col "Parent" form a8
col "Prg Id" form 9999999
col serial# form 999999 head Serial#
col qname head "Manager" format a10 trunc
col sid format 9999 head SID
col user_name form A12 head User trunc
set recsep off




select
       q.concurrent_queue_name qname
      ,f.user_name
      ,a.request_id "Req Id"
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,a.phase_code,a.status_code
--      ,b.os_process_id "OS"
--      ,vs.sid
--      ,vs.serial# "Serial#"
--      ,vp.spid
      ,a.oracle_process_id "spid"
      ,(nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name||' '||a.description "Program"
from APPLSYS.fnd_Concurrent_requests a
    ,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_user f
--    ,v$session vs
--    ,v$process vp
where
      a.controlling_manager = b.concurrent_process_id
  and a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and c2.concurrent_program_id = c.concurrent_program_id
  and c2.application_id = c.application_id
  and a.phase_code in ('I','P','R','T')
  and a.requested_by = f.user_id
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and c2.language = 'US'
--  and vs.process (+) = b.os_process_id
--  and vs.paddr = vp.addr (+)
order by 9 desc;

set lines 130
col USER_CONCURRENT_QUEUE_NAME format a39
SELECT b.USER_CONCURRENT_QUEUE_NAME , count(*)
FROM apps.FND_CONCURRENT_WORKER_REQUESTS a,apps.FND_CONCURRENT_QUEUES_VL b
WHERE (Phase_Code = 'P' ) and a.hold_flag != 'Y'
and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND
STATUS_CODE IN ('I'))) and a.CONCURRENT_QUEUE_ID!=1003
and a.CONCURRENT_QUEUE_ID=b.CONCURRENT_QUEUE_ID  
group by b.USER_CONCURRENT_QUEUE_NAME ,status_code;

col program_description format a60
col user_concurrent_queue_name format a40



SELECT count(b.request_id) count, b.program_description, a.user_concurrent_queue_name
FROM apps.FND_CONCURRENT_QUEUES_VL a, apps.FND_CONCURRENT_WORKER_REQUESTS b
WHERE a.enabled_flag='Y'
AND  a.concurrent_queue_id = b.concurrent_queue_id
AND  (b.Phase_Code = 'P' OR b.Phase_Code = 'R') AND b.hold_flag != 'Y'
AND  b.Requested_Start_Date <= SYSDATE
AND  1=1
-- and a.user_concurrent_queue_name like 'Inventory%'
group by a.user_concurrent_queue_name, b.program_description order by 3,1 asc

à Long Running Requests (over a specified amount of time hard coded as 4 hours)

set lines 130
column start_time format a15
column USER_CONCURRENT_PROGRAM_NAME format a40
select b.REQUEST_ID, a.USER_CONCURRENT_PROGRAM_NAME, b.phase_code,
       (sysdate - b.actual_start_date) * 24 "running",
       to_char(b.request_date, 'mm/dd/yyyy hh24:mi') "request_date",
       to_char(b.actual_start_date, 'mm/dd/yyyy hh24:mi') "start_time"
   from APPS.FND_CONCURRENT_PROGRAMS_VL a,
       APPS.FND_CONCURRENT_REQUESTS b
 where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
   and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
   and b.STATUS_CODE = 'R'
   and b.PHASE_CODE = 'R'
   and ((sysdate - b.actual_start_date) * 24) > 4
   and a.CONCURRENT_PROGRAM_ID NOT IN(36887,43393,38121,42789,31556);

set lines 130
select 'Scheduled requests:' schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE (requested_start_date > sysdate OR
status_code = 'P')
AND phase_code = 'P';

SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd,
to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') requestd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND (fcr.requested_start_date >= sysdate OR
status_code = 'P')
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY pname, request_date;

à pending requests Kept (on hold)

set lines 130
select 'Requests on hold:' schedt, count(*) schedcnt
from fnd_concurrent_requests
WHERE hold_flag = 'Y'
AND phase_code = 'P';

SELECT request_id id,
nvl(meaning, 'UNKNOWN') status,
user_concurrent_program_name pname,
to_char(request_date, 'DD-MON-RR HH24:MI:SS') submitd
FROM fnd_concurrent_requests fcr,
fnd_lookups fl,
fnd_concurrent_programs_vl fcpv
WHERE phase_code = 'P'
AND hold_flag = 'Y'
AND fcr.requested_start_date <= sysdate
AND status_code != 'P'
AND LOOKUP_TYPE = 'CP_STATUS_CODE'
AND lookup_code = status_code
AND fcr.concurrent_program_id = fcpv.concurrent_program_id
AND fcr.program_application_id = fcpv.application_id
ORDER BY request_date, request_id;

à List of pending Requests (used for Pending jobs threshold Monitor)

set lines 130
set pages 200
column REQUEST heading 'Request' format 9999999999
column PHASE heading 'Phase' format A8
column STATUS heading 'Status' format A8
column PROGRAM heading 'Program Name' format A40
column SHORT heading 'Short Name' format A15
column REQUESTOR heading 'Requestor' format A15
column START_TIME heading 'Start Time' format A15
column RUN_TIME justify left heading 'Time(m)' format 999999.9
column description format a75
 select  b.REQUEST_ID "Request", a.description,
       to_char(b.request_date, 'mm/dd/yyyy hh:mi:ss') "request_date",
       to_char(b.requested_start_date, 'mm/dd/yyyy hh:mi:ss') "request_start"
  from APPS.FND_CONCURRENT_PROGRAMS_VL a,
       APPS.FND_CONCURRENT_REQUESTS b
 where a.CONCURRENT_PROGRAM_ID = b.CONCURRENT_PROGRAM_ID
   and a.APPLICATION_ID = b.PROGRAM_APPLICATION_ID
   and b.PHASE_CODE = 'P'
   and b.HOLD_FLAG !='Y'
   and b.requested_start_date <= sysdate



set lines 130
set pages 200

A)
select oracle_process_id ,
decode(status_code,'R','Running','D','Canceled','E','Error','X','Terminated','G',       'Warning','T','Terminating')"Status_code",
 phase_code,to_char(actual_start_date,'DD-MON-YYYY=>hh24:mi:ss') "Login Time"
from apps.fnd_concurrent_requests where request_id='&Enter_conn_req_id'

B)
select s.sid,s.serial#,module,s.status from v$session s,v$process p
where s.paddr=p.addr
and p.spid=&oracle_process_id

C)
update fnd_concurrent_requests
set  phase_code='C',
status_code='X'
where request_id=&Enter_REQUESTID;
commit;

Make sure that database session for the request id is killed using the output from step A and B.


update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P' AND status_code in ('Q','I');
Commit;

update fnd_concurrent_requests
set phase_code='C',status_code='X'
WHERE phase_code = 'P' AND status_code in ('Q','I');
Commit;

set lines 130
select LANGUAGE_CODE from fnd_languages where INSTALLED_FLAG='I';
select NLS_LANGUAGE,LANGUAGE_CODE ,INSTALLED_FLAG from fnd_languages
where INSTALLED_FLAG in ('I','B');




set lines 130
set pages 400
col application_id      format 99990    heading "ID"
col application_name    format a40      heading "Name"
col application_prefix  format a6       heading "Prefix"
col application_short_name format a10   heading "Short name"
col apps                format a8       heading "Product"
col install_group_num   format 90       heading "Inst Grp"
col installed_flag      format a9       heading "Type"
col language_code       format a4       heading "Code"
col module_short_name   format a8       heading "Module"
col module_version      format a8       heading "Version"
col product_group_id    format 990      heading "ID"
col product_group_name  format a28      heading "Product Group Name"
col product_group_type  format a10      heading "Type"
col product_version     format a8       heading "Version"
col argument1           format a20      heading "Arguments"
col release_name        format a12      heading "Release"
col updated             format a11      heading "Updated"
col patchset            format a20      heading "Patchset Level"
col status              format a14      heading "Appl Status"

prompt --> Product Installation Status, Version Info and Patch Level
select decode(nvl(a.APPLICATION_short_name,'Not Found'), 'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) apps,
decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status,
fpi.product_version,nvl(fpi.patch_level,'-- Not Available --') Patchset,
to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
  and fpi.oracle_id = o.oracle_id(+)
order by 1,2;

set lines 130
set pages 200
col value format a30

select p.parameter_id,p.parameter_name,v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;           



connect to database as sysdba
set lines 130
set pages 200
select  'DB link for '||b.username||chr(10)||'  create  database  link '||c.name||' connect to '||c.userid|| ' identified  by '||c.password||'   using '''||c.host||''';'
from dba_users b ,link$ c
where c.owner#=b.user_id
union
select  'DB link for Public' ||chr(10)|| '  create public  database  link '||c.name||' connect to
'||c.userid|| ' identified  by '||c.password||'   using '''||c.host||''';'
from link$ c
where owner#=1;

8 comments:

  1. Very Usefull Scripts thanxxx a lot...

    ReplyDelete
  2. very useful scripts thanks much...

    ReplyDelete
  3. it is very easy to under standing and very usefull for tryning people thank u sooo mush sir

    ReplyDelete
  4. it is very easy to under standing and very usefull for tryning people thank u sooo mush sir

    ReplyDelete
  5. Great Scripts and very friendly

    ReplyDelete
  6. useful scripts. thanks a lot.it will help me to monitor ebs database and application.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete