Apps - Concurrent Queries

Request Details with Status/Phase Info
======================================
select b.user_concurrent_program_name, cp.request_id, sc.MEANING,pc.MEANING ,
to_char(cp.actual_start_date,'dd-mon-yyyy hh24:mi'),
to_char(cp.actual_completion_date,'dd-mon-yyyy hh24:mi')
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs_vl b, apps.fnd_lookups sc , apps.fnd_lookups pc
WHERE
cp.request_id between = ReqId and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id and
sc.LOOKUP_TYPE = 'CP_STATUS_CODE' and sc.LOOKUP_CODE = cp.status_code
and
pc.LOOKUP_TYPE = 'CP_PHASE_CODE' and pc.LOOKUP_CODE = cp.phase_code
order by cp.request_id


Conc. Req. Detl's when Req.ID known
============================
select request_id,phase_code ,status_code ,oracle_process_id ,oracle_session_id ,
to_char( actual_start_date,'dd-mm-yyyy hh24:mi'),
to_char( actual_completion_date,'dd-mm-yyyy hh24:mi') ,
(actual_completion_date-actual_start_date) *24*60 MINS
from apps.fnd_concurrent_requests where
request_id in() ;


Conc. Req. Detl's History for Specific Programme
=====================================
select cp.oracle_process_id ,usr.USER_NAME,usr.EMAIL_ADDRESS,cp.request_id,cp.status_code,cp.phase_code ,to_char(cp.actual_start_date,'dd-mm-yyyy hh24:mi'),
to_char(cp.actual_completion_date,'dd-mm-yyyy hh24:mi')
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs b ,apps.fnd_user usr
WHERE
lower(b.user_concurrent_program_name) like '%%' and
trunc(cp.actual_start_date) >=trunc(sysdate-10) and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id and
usr.USER_ID=cp.REQUESTED_BY;


Request which ran and which are scheduled
=================================
select b.user_concurrent_program_name, cp.oracle_process_id, cp.request_id, cp.status_code,cp.phase_code ,
to_char(cp.REQUESTED_START_DATE,'dd-mm-yyyy hh24:mi') Schedule_Date,to_char(cp.actual_start_date,'dd-mm-yyyy hh24:mi'),
to_char(cp.actual_completion_date,'dd-mm-yyyy hh24:mi')
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs_vl b
WHERE
lower(b.user_concurrent_program_name) like '%%' and
( trunc(cp.actual_start_date) >=trunc(sysdate-7) or cp.actual_start_date is null ) and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id ;


Request and its Manager
==================
select r.request_id, pg.concurrent_program_name,r.os_process_id req_pid, pc.os_process_id mgr_pid,
q.concurrent_queue_name, pc.node_name node, r.logfile_name req_log, pc.logfile_name mgr_log from
apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs pg, apps.fnd_concurrent_processes pc, apps.fnd_concurrent_queues q
where r.controlling_manager = pc.concurrent_process_id and
pc.queue_application_id = q.application_id and
pc.concurrent_queue_id = q.concurrent_queue_id and
r.program_application_id = pg.application_id and
r.concurrent_program_id = pg.concurrent_program_id
and r.request_id =;


Long running requests
( Requests exceeding 240 Mins execution time and only run by STD Manager )
==============================
select q.concurrent_queue_name, pc.node_name node,b.user_concurrent_program_name, cp.request_id, cp.status_code,cp.phase_code ,
to_char(cp.actual_start_date,'dd-mon-yyyy hh24:mi'),
to_char(cp.actual_completion_date,'dd-mon-yyyy hh24:mi') ,
(cp.actual_completion_date-cp.actual_start_date) *24*60 MINS ,
(cp.actual_completion_date-cp.actual_start_date) *24 HRS
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs_vl b,
apps.fnd_concurrent_processes pc, apps.fnd_concurrent_queues q
WHERE
cp.actual_start_date >= to_date(FromDate,'dd-mon-yy hh24:mi:ss') and
cp.actual_start_date <= to_date(ToDate,'dd-mon-yy hh24:mi:ss') and
cp.actual_completion_date >= to_date(FromDate,'dd-mon-yy hh24:mi:ss') and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id and
q.concurrent_queue_name like '%STANDARD%' and
b.user_concurrent_program_name not in('Gather Schema Statistics','Gather Statistics for Non-Apps Schema') and
cp.status_code ='C' and cp.phase_code ='C' and
pc.queue_application_id = q.application_id and
pc.concurrent_queue_id = q.concurrent_queue_id and
cp.controlling_manager = pc.concurrent_process_id and
cp.program_application_id = b.application_id and
cp.concurrent_program_id = b.concurrent_program_id and
((cp.actual_completion_date-cp.actual_start_date) *24*60) >=240
order by 9 ;

Long running requests
( Requests which ran Max no of times )
==============================
select rpad(substr(b.user_concurrent_program_name,1,73),74,' ') ||count(1)
FROM apps.fnd_concurrent_requests cp ,apps.fnd_concurrent_programs_vl b,
apps.fnd_concurrent_processes pc, apps.fnd_concurrent_queues q
WHERE
cp.actual_start_date >= to_date(FromDate,'dd-mon-yy hh24:mi:ss') and
cp.actual_start_date <= to_date(ToDate,'dd-mon-yy hh24:mi:ss') and
cp.actual_completion_date >= to_date(FromDate,'dd-mon-yy hh24:mi:ss') and
and
cp.concurrent_program_id = b.concurrent_program_id and
b.application_id = cp.program_application_id and
q.concurrent_queue_name like '%STANDARD%' and
b.user_concurrent_program_name not in('Gather Schema Statistics','Gather Statistics for Non-Apps Schema') and
cp.status_code ='C' and cp.phase_code ='C' and
pc.queue_application_id = q.application_id and
pc.concurrent_queue_id = q.concurrent_queue_id and
cp.controlling_manager = pc.concurrent_process_id and
cp.program_application_id = b.application_id and
cp.concurrent_program_id = b.concurrent_program_id
group by b.user_concurrent_program_name having
count(1) >50 order by count(1);


Con Pgm and its executable
======================
select b.user_concurrent_program_name,a.EXECUTABLE_NAME,a.EXECUTION_FILE_PATH,a.EXECUTION_FILE_NAME from
apps.FND_EXECUTABLES_VL a, apps.fnd_concurrent_programs_vl b
where b.user_concurrent_program_name like '%MRP%' and b.EXECUTABLE_ID=a.EXECUTABLE_ID ;


Managers along with Status
======================
col user_concurrent_queue_name for a45
col node_name for a15
col node_name2 for a15
set linesize 150

select a.concurrent_queue_id id, b.user_concurrent_queue_name,
a.enabled_flag, a.node_name, a.node_name2 , MEANING, a.TARGET_NODE Current_Node
from apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_tl b ,apps.fnd_lookups c where
a.concurrent_queue_id=b.concurrent_queue_id
and b.language='US' and
a.control_code=c.LOOKUP_CODE and
c.LOOKUP_TYPE like 'CP_CONTROL_CODE';



Conc PGM Library Name
===================
select fcprocs.CONCURRENT_PROCESSOR_NAME Library, b.concurrent_queue_name,b.user_concurrent_queue_name,
a.enabled_flag, a.node_name, a.node_name2
from apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_tl b , apps.FND_CONCURRENT_PROCESSORS fcprocs where
a.concurrent_queue_id=b.concurrent_queue_id
and b.language='US' and
b.user_concurrent_queue_name like 'Standard Manager' and
fcprocs.CONCURRENT_PROCESSOR_ID = a.CONCURRENT_PROCESSOR_ID


Application Name : Manager along with its application
=========================================
column CONCURRENT_QUEUE_NAME format a15
column USER_CONCURRENT_QUEUE_NAME format a20
column LIBRARY a15
column LIBRARY format a15
column APPLICATION format a10
column APPLICATIONNAME format a30

select b.concurrent_queue_name,b.user_concurrent_queue_name,
fcprocs.CONCURRENT_PROCESSOR_NAME Library,
fappv.APPLICATION_SHORT_NAME Application, fappv.APPLICATION_NAME ApplicationName
from apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_tl b ,
apps.fnd_application fapp,apps.fnd_application_vl fappv ,
apps.FND_CONCURRENT_PROCESSORS fcprocs
where
a.concurrent_queue_id=b.concurrent_queue_id
and b.language='US' and
b.user_concurrent_queue_name like 'Standard Manager' and
fapp.APPLICATION_ID=a.APPLICATION_ID and
fappv.APPLICATION_ID=fapp.APPLICATION_ID and
fcprocs.CONCURRENT_PROCESSOR_ID = a.CONCURRENT_PROCESSOR_ID



Manager Types
============
LOOKUP_TYPE LOOKUP_CODE MEANING
---------------------------------------------------------
CP_MANAGER_TYPE 0 Internal Concurrent Manager
CP_MANAGER_TYPE 1 Concurrent Manager
CP_MANAGER_TYPE 2 Internal Monitor
CP_MANAGER_TYPE 3 Transaction Manager
CP_MANAGER_TYPE 4 Conflict Resolution Manager
CP_MANAGER_TYPE 5 Scheduler/Prereleaser Manager
CP_MANAGER_TYPE 6 Service Manager

DB Links

Query for getting DB Link name,password,username
========================================

set lines 1000
column Owner format a10
column DbLinkName format a30
column CONN_USER format a10
column CONN_PWD format a10
column CONN_STRING format a20

• select l.name DbLinkName , du.username Owner ,l.userid CONN_User, l.password CONN_Pwd, l.host Conn_String from sys.link$ l,dba_users du where l.owner#=du.user_id (+)
  

----Public Link will have Owner as Null

• select l.name Name , l.userid CONN_User, l.password CONN_Pwd, l.host Conn_String, l.ctime from sys.link$ l
  

Standard DB Queries

Session details when SPID is known
=============================
select a.machine,a.process,a.last_call_et,a.module,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss'),a.sid,a.serial#,status,
substr(osuser,1,20) osuser,
b.program,
schemaname, pid, spid
from v$session a, v$process b
where b.spid in( )
and a.paddr = b.addr;


Session details when CLIENT PROCESS is known
========================================
select a.machine,a.process,a.last_call_et,a.module,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss'),a.sid,a.serial#,status,
substr(osuser,1,20) osuser,
b.program,
schemaname, pid, spid
from v$session a, v$process b
where a.process like '%%'
and a.paddr = b.addr;


Wait Event details when SPID is known
=================================

select a.sid,a.serial#,a.status,(sysdate-logon_time)*24*60 MINSDURATN,b.event,b.p1,b.p2,b.p3
from v$session a ,v$session_wait b
where
a.spid= and
a.sid=b.sid ;


LongOps (Long Operations)
======================

• select sid, serial#, opname, qcsid, to_char(start_time,'DD-MON-YYYY HH24:MI:SS') "start", (sofar/totalwork)*100 "%_complete" from v$session_longops order by sid, start_time;
• Same query with Null and Zeros taken care of
  

select sid, serial#, opname, qcsid,
to_char(start_time,'DD-MON-YYYY HH24:MI:SS') "start",
(sofar/decode(totalwork,0,1,totalwork))*100 "%_complete"
from v$session_longops where
sid=1922 and
((sofar/decode(totalwork,0,1,totalwork))*100) <> 100 and
((sofar/decode(totalwork,0,1,totalwork))*100) <> 0
order by sid, start_time;

Printers

For Linux

• lpstat -t : will provide list of printers with their status .
  
• lpq -P bolprinter1 : Will fire a print job
  
• lp -d HPLJ1200 : will delete a print job .
  

Cups
====
Key CUPS configuration files .

• /etc/cups/passwd.md5 :Holds uname:pwd details of printer admin
  
• /etc/cups/cupsd.conf : Needs to have "hostname" against "ServerName"
• /etc/cups/printers.conf :Contains actual printer definitions
  

CUPS Services

• /etc/init.d/cups stop
• /etc/init.d/cups start

DB Advisories

DB Cache Advisor (9i and above)
=========================
Query below will provide advisory data depending on usage history :

SELECT a.size_for_estimate From_Size , a.buffers_for_estimate Buffers
, a.estd_physical_read_factor Phy_Read_Factor , a.estd_physical_reads Phy_Reads
FROM V$DB_CACHE_ADVICE a
WHERE a.name = 'DEFAULT'
AND a.block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
AND a.advice_status = 'ON'


Shared Pool Advisor (9i and above)
=========================
1)
SELECT a.shared_pool_size_for_estimate "Size of Shared Pool in MB",
a.shared_pool_size_factor "Size Factor",
a.estd_lc_time_saved "Time Saved in sec"
FROM v$shared_pool_advice a where
a.shared_pool_size_for_estimate = b.shared_pool_size_for_estimate - 128 ;
2) Above query using rank :

SELECT a.shared_pool_size_for_estimate "Size of Shared Pool in MB",
a.shared_pool_size_factor "Size Factor",
a.estd_lc_time_saved "Time Saved in sec" ,
abs(a.estd_lc_time_saved-b.estd_lc_time_saved) SECS_DIFF
FROM v$shared_pool_advice a,v$shared_pool_advice b where
a.shared_pool_size_for_estimate = b.shared_pool_size_for_estimate -
(
( select shared_pool_size_for_estimate from
(select shared_pool_size_for_estimate,rank()
over (order by shared_pool_size_for_estimate ) rnk
from v$shared_pool_advice ) where rnk = 2 )
-
(select shared_pool_size_for_estimate from
(select shared_pool_size_for_estimate,rank()
over (order by shared_pool_size_for_estimate ) rnk
from v$shared_pool_advice ) where rnk = 1 )
) ;

PGA advisory
===========
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
PGA_TARGET_FACTOR FACTOR,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;

• V$PGA_TARGET_ADVICE view predicts how the statistics cache hit percentage and
  over allocation count in V$PGASTAT will be impacted if you change the value of
  the initialization parameter PGA_AGGREGATE_TARGET.
• set the PGA_AGGREGATE_TARGET parameter to a
  value where we avoid any over allocation .

Analyzed Schemas

For Oracle Apps 11i
Custom Schemas That Have Been Analyzed
=================================
Select Distinct Owner From Dba_Tables Where
Owner In ( Select Username Sname From Dba_Users Where Username Not
In('SCOTT','SYSTEM','SYS','DBSNMP','OUTLN','ORDSYS','MDSYS','OPS$ORIGOLD', 'AURORA$JIS$UTILITY$','PERFSTAT','AURORA$ORB$UNAUTHENTICATED', 'OEMADM','BOL_BILLING','RAC_ACCNT','BOL_DBA','XML','GIT_REPORTS','OSE$HTTP$ADMIN')
Minus
Select Distinct Upper(Oracle_Username) Sname
From Apps.Fnd_Oracle_Userid A, Apps.Fnd_Product_Installations B
Where A.Oracle_Id = B.Oracle_Id
Minus
Select Upper(Oracle_Username) Sname From Apps.Fnd_Oracle_Userid
Where Oracle_Id Between 900 And 999 And Read_Only_Flag = 'U'
)
And Nvl(Last_Analyzed, Sysdate-100) >= Sysdate-7
And Duration Is Null And Tablespace_Name Is Not Null And Temporary != 'Y'
And ( Iot_Type Is Null Or Iot_Type <> 'IOT_OVERFLOW')
Union
Select Distinct Owner From Dba_Indexes Where
Owner In ( Select Username Sname From Dba_Users Where Username Not
In('SCOTT','SYSTEM','SYS','DBSNMP','OUTLN','ORDSYS','MDSYS','OPS$ORIGOLD', 'AURORA$JIS$UTILITY$','PERFSTAT','AURORA$ORB$UNAUTHENTICATED', 'OEMADM','BOL_BILLING','RAC_ACCNT','BOL_DBA','XML','GIT_REPORTS','OSE$HTTP$ADMIN')
Minus
Select Distinct Upper(Oracle_Username) Sname
From Apps.Fnd_Oracle_Userid A, Apps.Fnd_Product_Installations B
Where A.Oracle_Id = B.Oracle_Id
Minus
Select Upper(Oracle_Username) Sname From Apps.Fnd_Oracle_Userid
Where Oracle_Id Between 900 And 999 And Read_Only_Flag = 'U'
)
And Nvl(Last_Analyzed, Sysdate-100) >= Sysdate-7
And Duration Is Null And Tablespace_Name Is Not Null And Temporary != 'Y'
And Index_Type Not In ('LOB','DOMAIN')
And Table_Name Not In('WWSEC_ENABLER_CONFIG_INFO$') ;

Custom Schemas That Have Not Been Analyzed
===================================
Select Distinct Owner From Dba_Tables Where
Owner In ( Select Username Sname From Dba_Users Where Username Not
In('SCOTT','SYSTEM','SYS','DBSNMP','OUTLN','ORDSYS','MDSYS','OPS$ORIGOLD', 'AURORA$JIS$UTILITY$','PERFSTAT','AURORA$ORB$UNAUTHENTICATED', 'OEMADM','BOL_BILLING','RAC_ACCNT','BOL_DBA','XML','GIT_REPORTS','OSE$HTTP$ADMIN')
Minus
Select Upper(Oracle_Username) Sname From Apps.Fnd_Oracle_Userid
Where Oracle_Id Between 900 And 999 And Read_Only_Flag = 'U'
Minus
Select Distinct Upper(Oracle_Username) Sname
From Apps.Fnd_Oracle_Userid A, Apps.Fnd_Product_Installations B
Where A.Oracle_Id = B.Oracle_Id
)
And Nvl(Last_Analyzed, Sysdate-100) < Sysdate-7
And Duration Is Null And Tablespace_Name Is Not Null And Temporary != 'Y'
And ( Iot_Type Is Null Or Iot_Type <> 'IOT_OVERFLOW')
Union
Select Distinct Owner From Dba_Indexes Where
Owner In ( Select Username Sname From Dba_Users Where Username Not
In('SCOTT','SYSTEM','SYS','DBSNMP','OUTLN','ORDSYS','MDSYS','OPS$ORIGOLD', 'AURORA$JIS$UTILITY$','PERFSTAT','AURORA$ORB$UNAUTHENTICATED', 'OEMADM','BOL_BILLING','RAC_ACCNT','BOL_DBA','XML','GIT_REPORTS','OSE$HTTP$ADMIN')
Minus
Select Upper(Oracle_Username) Sname From Apps.Fnd_Oracle_Userid
Where Oracle_Id Between 900 And 999 And Read_Only_Flag = 'U'
Minus
Select Distinct Upper(Oracle_Username) Sname
From Apps.Fnd_Oracle_Userid A, Apps.Fnd_Product_Installations B
Where A.Oracle_Id = B.Oracle_Id
)
And Nvl(Last_Analyzed, Sysdate-100) < Sysdate-7
And Duration Is Null And Tablespace_Name Is Not Null And Temporary != 'Y'
And Index_Type Not In ('LOB','DOMAIN')
And Table_Name Not In('WWSEC_ENABLER_CONFIG_INFO$')
order by 2 ;

CRS Issues

How to enable Tracing for CRS
=======================
Tracing for CRS can be enabled using the below steps :

set or export ORA_CRSDEBUG=1 in init.crsd
set mesg_logging_level=5 in ocrlog.ini

File and its location : /etc/init.crsd file ,
$ORACLE_CRS_HOME/srvm/admin/ocrlog.ini


Checking CRS health
=================
1) "ps -ef | grep d.bin"
must show all the CRS processes up and running.
2) "crs_stat -t"
must shows the CRS resources (nodeapps) online in each node.
3) "crsctl check css"
must return css is healthy.
4) "crsctl check crs"
must return crs is healthy.
5) "crsctl check boot"


Tracking what CRS is doing
=======================

$cd CRS_HOME/bin
$evmwatch -A -t "@timestamp @@"