Queries Arsenel for Oracle Apps DBA - Part 9

Queries Arsenel for Oracle Apps DBA - Part 9

 

**********************************************************

FORMS RUNAWAY SESSIONS

**********************************************************

--Dealing with Forms Runaway session






-- On Web tier, With the help of prstat -a check if any session is unusally running for long hours






--do ps -ef
grep 20034 to check if this is from session or not






--Run strace on pid strace –p






--$ strace -p 20034Process 20034 attached - interrupt to quit


--read(47, 0xaffcc2e, 2064) = -1 EAGAIN (Resource temporarily unavailable)


--read(47, 0xaffcc2e, 2064) = -1 EAGAIN (Resource temporarily unavailable)


--read(47, 0xaffcc2e, 2064) = -1 EAGAIN (Resource temporarily unavailable)


--read(47, 0xaffcc2e, 2064) = -1 EAGAIN (Resource temporarily unavailable)


--read(47, 0xaffcc2e, 2064) = -1 EAGAIN (Resource temporarily unavailable)


--read(47, 0xaffcc2e, 2064) = -1 EAGAIN (Resource temporarily unavailable)


--read(47, 0xaffcc2e, 2064) = -1 EAGAIN (Resource temporarily unavailable)






--Note: Some times strace output will ECONN (Connection refused ) instead of EAGAIN ( Resource temporarily unavailable)


--in such situation also you can kill the process if you get output similar to above you can kill the process






--$ kill -9 20034






--Run following sql and when asks for CleintPId give forms runaway (f60webmx process pid)






select s.sid, s.serial#, '*'


s.process


'*' Client,


p.spid Server, s.sql_address, s.sql_hash_value,


s.username, s.program


s.module, s.action, s.terminal,


s.machine, s.status, s.last_call_et/3600


from v$session s, v$process p


where p.addr=s.paddr and


nvl(s.process,-1) = nvl('8657',nvl(s.process,-1));






--If output of above query is null then from session has no contact with database means its just a runaway process


--with no respective session on database. It confirms about runaway session. Kill it on OS.

 

 

Queries Arsenel for Oracle Apps DBA - Part 8

Queries Arsenel for Oracle Apps DBA - Part 8

-- Responsibilities attached to User

 
SELECT

 
substr(fr.responsibility_name,1,35) Responsibility

 
,fu.user_name

 
,fur.end_date

 
FROM

 
apps.fnd_user fu,

 
apps.fnd_user_resp_groups_direct fur,

 
apps.fnd_responsibility_vl fr,

 
apps.fnd_application_vl fa

 
WHERE

 
fur.user_id = fu.user_id

 
AND fu.user_name NOT IN ('YOUR_DBA_NAME')

 
AND fur.responsibility_application_id = fa.application_id

 
AND fur.responsibility_application_id = fr.application_id

 
AND fur.responsibility_id = fr.responsibility_id

 
AND fu.end_date is null -- only show active users

 
AND nvl(fur.end_date, sysdate+1) > sysdate -- only show active resp assignment

 
AND fr.responsibility_name in ('System Administrator'

 
,'Application Developer'

 
,'Application Developer Common Modules'

 
,'CRM HTML Administration'

 
,'FICO Sys Admin Conversion'

 
,'FICO System Administration GUI'

 
,'Functional Administrator' -- can chg Profiles

 
,'Functional Developer'

 
,'Knowledge Administrator' --use FICO instead

 
,'Oracle Sales Administrator' --use FICO instead

 
,'Sales Administrator' --use FICO instead

 
,'SFM System Administrator'

 
,'TeleSales Administration' --use FICO instead

 
)

 

 

 
--show currently logged in Forms connections, multiple connections per user

 
--approximately half as many Forms users as DB forms connections

 

 

 
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI') rundatetime

 
,(SELECT COUNT(*)

 
FROM apps.fnd_logins fl -- only has Forms login rows

 
, apps.fnd_user fu

 
, v$session s

 
, v$process p

 
WHERE fl.end_time IS NULL

 
AND fl.user_id = fu.user_id(+)

 
AND fl.process_spid = p.spid

 
AND fl.pid = p.pid

 
AND fl.serial# = p.serial#

 
AND p.addr = s.paddr

 
AND s.program IS NULL) formsusers

 
--

 
--

 
-- SelfServ Activity last 60 min based on icx_sessions

 
,(SELECT COUNT(*)

 
FROM apps.icx_sessions icx

 
, apps.fnd_user fu

 
, apps.fnd_responsibility_vl frv

 
WHERE icx.user_id = fu.user_id(+)

 
AND icx.disabled_flag = 'N'

 
AND icx.last_connect > SYSDATE - 1/24

 
--Preferences

 
AND NVL(icx.responsibility_id,20873) = frv.responsibility_id(+)

 
AND icx.responsibility_application_id = frv.application_id(+)

 
-- only web self service responsibilities

 
AND frv.version(+) = 'W') selfservusers

 
--

 

 

 
--Terminated Users with Active account in E-BIZ

 

 

 
SELECT DISTINCT(fu.user_name)

 
, papf.employee_number emp_num

 
, to_char(ppos.actual_termination_date, 'DD-Mon-YY') term_date

 
, period_of_service_id per_serv_id

 
FROM apps.fnd_user fu

 
, apps.per_all_people_f papf

 
, apps.per_periods_of_service ppos

 
WHERE fu.employee_id = papf.person_id

 
AND papf.person_id = ppos.person_id

 
AND fu.end_date IS NULL

 
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id)

 
FROM per_periods_of_service

 
WHERE person_id = papf.person_id)

 
AND ppos.actual_termination_date < TRUNC(SYSDATE)

 

 

 
--GSM Enabled or not

 

 

 
Select 'GSM' "Setting", NVL(v.profile_option_value, 'N') "Status"

 
from fnd_profile_options p, fnd_profile_option_values v

 
where p.profile_option_name = 'CONC_GSM_ENABLED'

 
and p.profile_option_id = v.profile_option_id;

 

 

 
--RAC ENABLED or NOT

 
Select 'RAC' "Setting", decode(count(*), 0, 'N', 1, 'N', 'Y') "Status" from V$thread

 

 

 
--PCP ENABLED or NOT

 
Select 'PCP' "Setting", value "Status" from fnd_env_context where variable_name = 'APPLDCP'

 
and concurrent_process_id = (select max(concurrent_process_id) from fnd_concurrent_processes

 
where concurrent_queue_id = 1)

 

 

 
--GSM Enabled Processes running on different node.

 
Select n.node_name "Node", NVL(l.meaning, n.node_mode) "Mode",

 
n.status "Status",

 
count(p.concurrent_process_id) "Current GSM Processes"

 
from fnd_nodes n, fnd_concurrent_processes p, fnd_lookups l

 
where p.node_name(+) = n.node_name

 
and p.process_status_code(+) not in ('S', 'K')

 
and l.lookup_type(+) = 'CP_NODE_MODE'

 
and l.lookup_code(+) = n.node_mode

 
group by n.node_name, NVL(l.meaning, n.node_mode), n.status;

 

 

Queries Arsenel for Oracle Apps DBA - Part 7

Queries Arsenel for Oracle Apps DBA - Part 5

 

-- Concurrent Program with Trace Enabled

 

SELECT fcpt.user_concurrent_program_name prog_name

 

, fcp.enable_trace

 

, fcp.last_update_date

 

FROM apps.fnd_concurrent_programs fcp,

 

applsys.fnd_concurrent_programs_tl fcpt

 

WHERE NVL(fcp.enable_trace,'N') = 'Y'

 

and fcp.CONCURRENT_PROGRAM_ID = fcpt.CONCURRENT_PROGRAM_ID;

 

 

select to_char(START_TIME,'DD-MON-YYYY') Login_Time, count(*) cnt

 

from fnd_logins where START_TIME > (select to_date('15-JUN-2009 00:00:00','DD-MON-YYYY HH24:MI:SS') from dual)

 

and login_type is not null

 

and end_time is null

 

group by to_char(START_TIME,'DD-MON-YYYY');

 

 

 

 

 

select distinct d.user_name, a.START_TIME

 

from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d

 

where b.paddr = c.addr

 

and a.pid=c.pid

 

and a.spid = b.process

 

and d.user_id = a.user_id

 

and (d.user_name = 'USER_NAME' OR 1=1);

 

 

 

select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')

 

from apps.fnd_user

 

where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');

 

 

 

 

 

--current EBS Form session

 

 

select vs.action, fft.user_form_name, vs.status session_status, vs.sid

 

From v$session vs, applsys.fnd_form ff,

 

applsys.fnd_form_tl fft

 

where vs.module like ff.form_name

 

and ff.FORM_ID = fft.form_id

 

and ff.application_id = fft.application_id

 

and vs.action like 'FRM%'

 

 

 

 

 

select oracle_username, install_group_num, read_only_flag

 

from apps.fnd_oracle_userid

 

where oracle_username = 'APPS'

 

and (install_group_num = 0 -- value should be 1

 

or read_only_flag != 'U') -- should be U

 

 

 

select oracle_id, oracle_username, read_only_flag, install_group_num

 

from fnd_oracle_userid

 

where oracle_username = 'APPS';

 

 

 

 

 

SELECT user_name

 

, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon

 

, ENCRYPTED_USER_PASSWORD

 

, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date

 

FROM apps.fnd_user

 

WHERE ENCRYPTED_USER_PASSWORD = 'INVALID'

 

AND USER_NAME IN ('SYSADMIN')

 

ORDER BY user_name

Queries Arsenel for Oracle Apps DBA - Part 6

Queries Arsenel for Oracle Apps DBA - Part 5

**********************************************************

APPS HEALTH CHECK

**********************************************************

--Confirm Alert Periodic Scheduler is okay, should see no rows



select 'PROBLEM: No pending Alert Periodic Scheduler request' from dual


where not exists (


select


cp.concurrent_program_name Program,


cr.request_id Request,


phase_code S,


status_code C,


cr.argument1


', '






cr.argument2


', '






cr.argument3


', '






cr.argument4


', '






cr.argument5


', '






cr.argument6


', '






cr.argument7


', '






cr.argument8 Arguments


from


apps.fnd_concurrent_requests cr,


apps.fnd_concurrent_programs cp


where


cr.concurrent_program_id = cp.concurrent_program_id


and cp.application_id >= 0 -- force on index leading edge


and cr.program_application_id = cp.application_id


and cp.concurrent_program_name = 'ALEPPE' -- Alert scheduler con pgm


and cr.phase_code = 'P'


and cr.status_code != 'H' -- not on hold


)










--Look for programs that should usually be disabled based on description



SELECT


cp.concurrent_program_name


, cp.user_concurrent_program_name


, cp.description


FROM


apps.fnd_concurrent_programs_vl cp


WHERE


UPPER(cp.description) like '%USUALLY%DISABLED%'


AND cp.enabled_flag = 'Y' -- should be N to be disabled










--Show any requests by USER_NAME that ended in error in last 5 days


select request_id Request


,fu.user_name User_Name


,to_char(cr.actual_start_date, 'DD HH24:MI') When_start


,to_char(cr.actual_completion_date, 'DD HH24:MI') When_end


,cp.concurrent_program_name Program


,phase_code


,status_code


from


apps.fnd_concurrent_programs cp,


apps.fnd_user fu,


apps.fnd_concurrent_requests cr


where


cr.concurrent_program_id = cp.concurrent_program_id


and cr.program_application_id = cp.application_id


and cr.actual_completion_date is not null


and cr.requested_by = fu.user_id


and fu.user_name = 'user_name'


and cr.status_code = 'E' -- requests that ended in error


and trunc(cr.actual_start_date) >= trunc(sysdate-5) -- started recently


order by cr.actual_start_date desc

Queries Arsenel for Oracle Apps DBA - Part 5

Queries Arsenel for Oracle Apps DBA - Part 5

**********************************************************

PROFILE OPTION

**********************************************************

-- Get Profile option value at site,appl,resp & User level using below query



SELECT pot.user_profile_option_name "Profile"

, DECODE( a.profile_option_value

, '1', '1 (may be "Yes")'

, '2', '2 (may be "No")'

, a.profile_option_value) "Value"

, DECODE( a.level_id

, 10001, 'Site'

, 10002, 'Appl'

, 10003, 'Resp'

, 10004, 'User'

, '????') "Levl"

, DECODE( a.level_id

, 10002, e.application_name

, 10003, c.responsibility_name

, 10004, d.user_name

, '-') "Location"

FROM applsys.fnd_application_tl e

, applsys.fnd_user d , applsys.fnd_responsibility_tl c

, applsys.fnd_profile_option_values a , applsys.fnd_profile_options b

, applsys.fnd_profile_options_tl pot

WHERE

UPPER( pot.user_profile_option_name) LIKE UPPER( '%&&v_profile%') AND

pot.profile_option_name = b.profile_option_name

AND b.application_id = a.application_id (+)

AND b.profile_option_id = a.profile_option_id (+)

AND a.level_value = c.responsibility_id (+)

AND a.level_value = d.user_id (+) AND a.level_value = e.application_id

ORDER BY "Profile", "Levl", "Location", "Value"

(+)

AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')

OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')

OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'));



-- Profile option with modification date and user who changed it:-

SELECT t.user_profile_option_name, profile_option_value,

v.creation_date,

v.last_update_date,

v.creation_date - v.last_update_date "Change Date",

(SELECT UNIQUE user_name

FROM fnd_user

WHERE user_id = v.created_by) "Created By",

(SELECT user_name

FROM fnd_user

WHERE user_id = v.last_updated_by) "Last Update By"

FROM fnd_profile_options o,

fnd_profile_option_values v,

fnd_profile_options_tl t

WHERE o.profile_option_id = v.profile_option_id

AND o.application_id = v.application_id

AND start_date_active <= SYSDATE

AND NVL (end_date_active, SYSDATE) >= SYSDATE

AND o.profile_option_name = t.profile_option_name

AND level_id = 10003 --for resp level, change it to 10001 for site level, 10002 for'Appl' and 10004 for'User'

and v.last_update_date > '10-SEP-2009'

AND t.LANGUAGE IN (SELECT language_code

FROM fnd_languages

WHERE installed_flag = 'B'

UNION

SELECT nls_language

FROM fnd_languages

WHERE installed_flag = 'B')

ORDER BY user_profile_option_name;

Queries Arsenel for Oracle Apps DBA - Part 4

Queries Arsenel for Oracle Apps DBA - Part 4

**********************************************************

User getting locking errors in Application:- Identify User locks

**********************************************************

--Locate User-locks

--Find the username from Incident/or check from user



select user_name,description from fnd_user where user_name in ('001955');



--Below query will help you finding active sessions of the User along with FRM session it is using.



select status,sid,serial#,action from v$session where action like '1955%';



--Find if any of users session is visible in v$locked_object



SELECT lo.session_id "SID",

lo.object_id,

ob.owner,

ob.object_name,

ob.object_type

FROM v$locked_object lo,

dba_objects ob

WHERE lo.object_id = ob.object_id



and lo.session_id in ('3605','3645','3609','3794');



--If that is the case then confirm from name from user and kill the session



--Also below query can be used to associate user name with Form session



select vs.action, fft.user_form_name, vs.status session_status, vs.sid

From v$session vs, applsys.fnd_form ff,

applsys.fnd_form_tl fft

where vs.module like ff.form_name

and ff.FORM_ID = fft.form_id

and ff.application_id = fft.application_id

and vs.action like 'FRM1955%'



SELECT s.saddr, s.sid, s.serial#, s.username, s.osuser, s.machine, s.program,

s.logon_time, s.status, p.program, p.spid FROM v$session s, v$process p

WHERE s.paddr = p.addr AND s.sid IN ('3828');



After identifying and analysing session, corrective action can be taken



-- Other Query to find 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';

Queries Arsenel for Oracle Apps DBA - Part 3

Queries Arsenel for Oracle Apps DBA - Part 3

**********************************************************

CONCURRENT MANAGER

**********************************************************

--- Script to display status of all concurrent managers

select distinct Concurrent_Process_Id CpId, PID Opid,

Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,

P.process_status_code Status,

To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At

from Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process

where Q.Application_Id = Queue_Application_ID

and Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID

and Spid = Os_Process_ID

and Process_Status_Code not in ('K','S')

order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name;



-- Check which all concurrent manager is down

select * from fnd_concurrent_queues where max_processes != running_processes;



--Find Conc Manager designated to run your Conc request



SELECT user_concurrent_program_name, user_concurrent_queue_name

FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_queue_content cqc,

apps.fnd_concurrent_queues_tl cq WHERE cqc.type_application_id(+) = cp.application_id

AND cqc.type_id(+) = cp.concurrent_program_id AND cqc.type_code(+) = 'P'

AND cqc.include_flag(+) = 'I' AND cp.LANGUAGE = 'US'

AND cp.user_concurrent_program_name = 'Credit Card Outstanding Transactions Management (Details)'

AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id

AND NVL (cqc.queue_application_id, 0) = cq.application_id AND cq.LANGUAGE = 'US';



-- After request has been run:-

Select

'The '

Concurrent_Queue_Name



' concurrent manager ran your request from',

to_char(Actual_Start_date, ' MON-DD-YY HH:MI:SS AM')

' - to - '



to_char(Actual_COMPLETION_date, 'MON-DD-YY HH:MI:SS AM'),

'The '

Concurrent_Queue_Name



' concurrent manager log file is '

P.Logfile_Name,

'Request log file is '

R.Logfile_Name

From Fnd_Concurrent_Queues Q,

Fnd_Concurrent_requests R,

Fnd_Concurrent_Processes P

Where

(P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID And

Queue_Application_ID = Q.Application_ID )

And R.Controlling_Manager = P.Concurrent_Process_ID

And R.Phase_Code = 'C'

And Request_ID = &Request_ID

 

Queries Arsenel for Oracle Apps DBA - Part 2

Queries Arsenel for Oracle Apps DBA - Part 2

**********************************************************

 INVALID OBJECTS

**********************************************************

How to check for invalid objects in a particular module?

select OWNER, OBJECT_NAME, OBJECT_TYPE

from DBA_OBJECTS

where OBJECT_NAME like ‘FND_%’

and STATUS = ‘INVALID’;

select OWNER, OBJECT_NAME, OBJECT_TYPE

from DBA_OBJECTS

where OBJECT_NAME like ‘AP_%’

and STATUS = ‘INVALID’;


How to check for invalid objects in all the modules?

select owner, object_name, object_type from dba_objects

where status = ‘INVALID’

order by object_name, object_type;



**********************************************************

CONCURRENT REQUEST

**********************************************************

-- To find out scheduled concurrent request



SELECT cr.request_id,

DECODE (cp.user_concurrent_program_name,

'Report Set', 'Report Set:'

cr.description,

cp.user_concurrent_program_name

) NAME,

argument_text, cr.resubmit_interval,

NVL2 (cr.resubmit_interval,

'PERIODICALLY',

NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',

'ONCE')

) schedule_type,

DECODE (NVL2 (cr.resubmit_interval,

'PERIODICALLY',

NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS',

'ONCE')

),

'PERIODICALLY', 'EVERY '



cr.resubmit_interval



' '



cr.resubmit_interval_unit_code



' FROM '



cr.resubmit_interval_type_code



' OF PREV RUN',

'ONCE', 'AT :'



TO_CHAR (cr.requested_start_date, 'DD-MON-RR

HH24:MI'),

'EVERY: '

fcr.class_info

) schedule,

fu.user_name, requested_start_date

FROM apps.fnd_concurrent_programs_tl cp,

apps.fnd_concurrent_requests cr,

apps.fnd_user fu,

apps.fnd_conc_release_classes fcr

WHERE cp.application_id =

cr.program_application_id

AND cp.concurrent_program_id =

cr.concurrent_program_id

AND cr.requested_by = fu.user_id

AND cr.phase_code = 'P'

AND cr.requested_start_date > SYSDATE

AND cp.LANGUAGE = 'US'

AND fcr.release_class_id(+) = cr.release_class_id

AND fcr.application_id(+) =

cr.release_class_app_id;



-- Currently running concurrent request:-

select

sess.sid,sess.serial#,

oracle_process_id OS_PROCESS_ID,

fusr.description user_name ,

fcp.user_concurrent_program_name progName,

to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,

request_id RequestId,

TRUNC(((sysdate-fcr.actual_start_date)/(1/24))*60) exec_time

from

fnd_concurrent_requests fcr,

fnd_concurrent_programs_tl fcp,

fnd_user fusr,

v$session sess

where

fcp.concurrent_program_id = fcr.concurrent_program_id

and fcr.program_application_id = fcp.application_id

and fcp.language = 'US'

--and fcr.phase_code = 'R'

and fcr.status_code = 'R'

and fcr.requested_by = fusr.user_id

and fcr.oracle_session_id = sess.audsid (+)

order by 5 DESC



How to find database SID from a Concurrent request.

column process heading “FNDLIBR PID”

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

v$process c,

v$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 = &Request_ID

AND a.phase_code = ‘R’;

You need your concurrent request ID as an input.

c.SPID= is the operating system process id

d.sid= is the Oracle process id



The following SQL scripts located under $FND_TOP/sql are useful when diagnosing concurrent manager problems:

afimchk.sql Tells the status of the ICM and PMON method

afcmstat.sql Lists active manager processes

afrqrun.sql Lists all the running, waiting and Terminating requests

afrqwait.sql Lists requests that are constrained and waiting for the ICM to release them.

afrqscm.sql Prints log file name of managers that can run a given request. It can be used to check for possible errors when a request stays in pending status. It requires a request id value.

afcmcreq.sql Prints the log file name of the manager that processed the request

afrqstat.sql Summary of completed concurrent requests grouped by completion status and execution type. It requires number of days prior to today on which to report parameter.

afimlock.sql Lists locks that the ICM is waiting to get

afcmrrq.sql Lists managers that currently are running a request