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