USEFULL APPS DBA Queries

**************************************************************
**********USEFULL APPS DBA Queries******************
**************************************************************
##################### To get nls & merged patches ##############
set lines 120
col PATCH_NAME for a30
col FILE_NAME for a15
select b.driver_file_name as "FILE_NAME",b.orig_patch_name as "PATCH_NAME",
done.language as "LANGUAGE", c.success_flag as "SUCCESS",
to_char(c.start_date,'dd-MON-yyyy hh24:mi:ss') as "START DATE",
to_char(c.end_date,'dd-MON-yyyy hh24:mi:ss') as "END DATE"
from
ad_appl_tops a,
ad_patch_drivers b,
ad_patch_runs c,
ad_patch_driver_langs done
where
b.patch_driver_id=c.patch_driver_id
and c.appl_top_id=a.appl_top_id
and done.patch_driver_id=c.patch_driver_id and rownum < 10
order by 1;
and b.orig_patch_name like '%&patch_number'


++++++++++++++++++++++++++++++++++++++++++++++++++
################## To get APPS & DB VERSION ######################
SQL> select i.instance_name, i.host_name, f.release_name release,      i.version 
from v$instance i, fnd_product_groups f
where i.instance_name = f.applications_system_name;


+++++++++++++++++++++++++++++++++++++++++++++++++++
###################### FAMILY PACK OUERIES #############
select patch_level from fnd_product_installations where patch_level like '%JTT%';


select application_id,patch_level,product_version from fnd_product_installations where patch_level like '%JTT%';


++++++++++++++++++++++++++++++++++++++++++++++++++++++++
######################### To check OWF VERSION #################


To see the OWF version
------------------------
col patch format a50
col bug format 99999999
col app format a5
select bug_number "BUG", decode(bug_number, 2728236, 'Patch 11i.OWF.G',
3031977, 'Post 11i.OWF.G Rollup 1',
3061871, 'Post 11i.OWF.G Rollup 2',
3124460, 'Post 11i.OWF.G Rollup 3',
3316333, 'Post 11i.OWF.G Rollup 4.1',
3347417, 'Post 11i 3316333 Rollup patch (Advised to apply)',
3314376, 'Post 11i.OWF.G Rollup 5',
3409889, 'Post 11i 3314376 Consolidated Fixes for OWF.G Rollup 5',
3492743, 'Post 11i.OWF.G Rollup 6',
3672076,  'Post 11i..OWF.G ROLLUP 6.1',
3484474, 'OA Framework Rollup Patch 5.7H - V6',
3119911, 'OAM Rollup Patch A - Req. pre-req OAM.G - see Note 250346.1',
2737099, 'Patch 11i.OAM.G',
3868138, 'Post 11i.OWF.G Rollup 7',
3671463, 'OA Framework Rollup Patch 5.7H - V6+') "Patches Applied",
b.LAST_UPDATE_DATE "APPLIED" 
from AD_BUGS b
where b.BUG_NUMBER in
('2728236','3031977','3061871','3124460','3316333','3347417','3314376',
'3409889','3484474','3119911','2737099','3492743','3672076','3868138','3671463');


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
############### To get Apps & other Application paswwords ##############
set pages 100     
col owner form a10
col db_link  form a30
col "CONNECTING USR" form a20
col "CONNECT HOST" for a40
col password for a15


select substr(b.name,1,20)"db_link",substr(b.userid,1,15)"CONNECTING USR",
substr(b.password,1,15)"PASSWORD",
substr(b.host,1,15)"CONNECT HOST",substr(a.name,1,10)"OWNER"
from sys.user$ a,sys.link$ b
where a.user# = b.owner#
order by 4,5 


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
################# To check profile option values #######################


select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where 
PROFILE_OPTION_ID=(Select
PROFILE_OPTION_ID from fnd_profile_options where PROFILE_OPTION_NAME
='SIGNON_PASSWORD_HARD_TO_GUESS');


SQL> select profile_option_value from fnd_profile_option_values where
profile_option_value like '%floraap%';


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
################# To check App. Module & patch Level Installed #############


SELECT SUBSTR(a.application_name, 1,40) product_name, a.application_id ,  
SUBSTR(application_short_name, 1,4) short_name, DECODE(b.status,'I','INSTALLED', 
DECODE(b.status,'S','SHARED',DECODE(b.status,'N','NOTINSTALLED',b.status))) status, 
patch_level FROM fnd_application_vl a, fnd_product_installations b 
WHERE a.application_id = b.application_id ORDER BY 3;


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
################# To check line having error  ##########################


select text from user_source where NAME='FND_CONCURRENT' and line=<line nr>;


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
################# To get conc. request & os process id  #################
SET LINESIZE 500 
SET PAGESIZE 100 
SELECT V.REQUEST_ID "Request Id", 
substr(c.os_process_id,1,8) "OS ID", 
V.STATUS_CODE Status, 
TO_CHAR(V.ACTUAL_START_DATE,'DD-MON-YY-HH24:MI:SS') "Start Date", 
SUBSTR(V.REQUESTOR,1,20) "Requestor", 
((SYSDATE-V.ACTUAL_START_DATE)* 24)*60 TIME_TAKEN, 
SUBSTR(V.PROGRAM,1,120) "Program Name" 
FROM FND_CONC_REQ_SUMMARY_V V, FND_CONCURRENT_REQUESTS C 
WHERE V.STATUS_CODE LIKE 'R' 
AND V.REQUEST_ID = C.REQUEST_ID 
ORDER BY 1 DESC 
/ 
SET LINESIZE 80 
SET PAGESIZE 24 


select * from fnd_concurrent_requests where oracle_process_id= <process id>. 


select node_name,SUPPORT_CP from fnd_nodes;


################# Concurrent request details currently running ################
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language='US'
and a.requested_by=f.user_id
and b.sid=g.sid
and a.status_code='R'
and a.phase_code='R';




SQL> select REQUEST_ID,PHASE_CODE,STATUS_CODE from fnd_concurrent_requests where PHASE_CODE='R' and STATUS_CODE='R';


select REQUEST_ID , REQUESTED_BY ,  decode(phase_Code,'P','Pending','R','Running','C','Completed Normal'),decode(status_code,'C',' Completed','I',' Inactive ') from APPS.fnd_concurrent_requests where phase_code='R';


set linesize 1000; 
set head off; 
col "Request ID" format 99999999; 
col "Program Name" format a40; 
col "Requestor" format a8; 
col "Argument" format a50;  


select  distinct
a.request_id "Request ID",a.request_date "START_DATE", 
b.user_concurrent_program_name "Program Name", 
c.user_name "Requestor", 
a.argument_text "Argument"  
from  
fnd_concurrent_requests a, 
fnd_concurrent_programs_tl b, 
fnd_user c 
where  
a.concurrent_program_id = b.concurrent_program_id  
and 
c.user_id=a.requested_by 
and 
a.phase_code='R'  
and  
a.status_code='R'; 
/ 


select distinct a.request_id "Request ID",b.user_concurrent_program_name "Program Name", c.user_name "Requestor"
from  
fnd_concurrent_requests a, 
fnd_concurrent_programs_tl b, 
fnd_user c 
where  
a.concurrent_program_id = b.concurrent_program_id  
and 
c.user_id=a.requested_by 
and 
a.phase_code='P'  
and  
a.status_code='Q'; 
/ 





++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#######################To Check the Language Install ###################
SQL> select language_code, installed_flag from fnd_languages where installed_flag in
('B', 'I');


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
####################### To find Instance URL ########################
INSTANCE url
select profile_option_value from fnd_profile_option_values where profile_option_value like '%http%';


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Query to check patch set: -
------------------------------
SQL> SELECT SUBSTR(fat.application_name,1,48) "NAME",
SUBSTR(fpi.patch_level,1,12) "PATCHSET"
FROM fnd_product_installations fpi,
fnd_application_tl fat
WHERE fpi.application_id = fat.application_id
AND fat.application_name like 'Balance%'
AND fpi.patch_level IS NOT NULL
ORDER BY application_name; 

MRC Enabled:

============

select multi_currency_flag from fnd_product_groups;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Changing Apps Password

-----------------------

FNDCPASS apps/<apps_pwd> 0 Y system/<system_pwd> SYSTEM APPLSYS <new_apps_pwd>

And the new password has to be updated in the following files erp01.northford.com

1) $APACHE_TOP/modplsql/cfg/ wdbsvr.app

2) $ORACLE_HOME/reports60/server/CGIcmd.dat

The following need to be done in the order specified after running FNDCPASS:

1) Bounce concurrent Manager 

2) Change apps password in $APACHE_TOP/modplsql/cfg/ wdbsvr.app on all the MT’s 

3) Bounce apache

4) Change apps password in $ORACLE_HOME/reports60/server/ CGIcmd.dat on erp01.northford.com 

5) Bounce reports server 

Changing Sysadmin Password

---------------------------

FNDCPASS apps/********** 0 Y system/********** USER SYSADMIN ********

SYSADMIN ACTIVE

==============

>select user_name,end_date from fnd_user where user_name = 'SYSADMIN';

>select fnd_web_sec.validate_login('SYSADMIN','&pwd') from dual;

>select user_id from fnd_user where user_name = '????';

1) select end_date from fnd_user where user_name = 'SYSADMIN';

2) select expiration_date from wf_local_roles where name = 'SCOTSCO';

3) select USER_NAME, ROLE_NAME, USER_ORIG_SYSTEM, ROLE_ORIG_SYSTEM,

PARENT_ORIG_SYSTEM, START_DATE, END_DATE, USER_END_DATE, ROLE_END_DATE,

ASSIGNING_ROLE_END_DATE, EFFECTIVE_END_DATE

from WF_USER_ROLE_ASSIGNMENTS

where USER_NAME= 'SCOTSCO';

4) UPDATE FND_USER SET END_DATE=NULL WHERE USER_NAME='SYSADMIN';

Script to find Responsibilties 

===============================

select b.user_name,c.RESPONSIBILITY_NAME,a.END_DATE

from 

FND_USER_RESP_GROUPS_DIRECT a,

fnd_user b,

FND_RESPONSIBILITY_VL c

where b.USER_ID=a.user_id

and a.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID

and b.user_name ='SYSADMIN';