can't find libjava.so when running adcfgclone.pl appsTier

Problem: Getting below error while running adcfgclone.pl appsTier in one of my Client (MmCcDd) Environment (R12)
=======
apperps1> vi  /tmp/adcfgclone_57147544.err
apperps1> cat /tmp/adcfgclone_57147544.err
Error: could not find libjava.so
Error: could not find Java 2 Runtime Environment.
apperps1>


Solution:
=========

a. Find out which java with application user.

 /u01/apps/erps1/product/10.1.3/appsutil/jdk/jre/bin/java


b. cd  /oraprod/apps/prodcomn/clone/bin/../jre/bin/ and find out if any java directory exist's
apperps1> pwd
/u01/apps/erps1/common/clone/jre/bin
apperps1> ls -ld java
-rwxr-xr-x    1 apperps1 dba          311625 Sep 15 14:08 java

c. Now move/copy that directory
mv java java_old

d. Now, make a softlink to the step one.
ln -s java
apperps1> ln -s /u01/apps/erps1/product/10.1.3/appsutil/jdk/jre/bin/java java

e. Now, Run perl adcfgclone.pl appsTier again

Troubleshooting Workflow Notification Mailer Issues

Troubleshooting Workflow Notification Mailer Issues
Find Workflow Notification Mailer is up and Running?


SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';


Workflow log’s: FNDCPGSC*.txt under $APPLCSF/$APPLOG directory


Find the Failed One’s?


Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED';


Check pending e-mail notification that was pending for process.


Sql> SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'MAIL'
GROUP BY message_name;


Sql> SELECT * FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'SENT'
ORDER BY begin_date DESC




Check the Workflow notification has been sent or not?


select mail_status, status from wf_notifications where notification_id=


--If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by logging in application + click on preference + the notification preference


1. Verify whether the message is processed in WF_DEFERRED queue


select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= ''
- notification id


2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue


select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = '
To check what all mails have went and which all failed ?


Select from_user,to_user,notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS where status = 'OPEN';


Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
from WF_NOTIFICATIONS where status = 'OPEN';




Users complain that notifications are stuck ?


Use the following query to check to see whatever the users are saying is correct


SQL> select message_type, count(1) from wf_notifications
where status='OPEN' and mail_status='MAIL' group by message_type;


E.g o/p of query -


MESSAGE_Type COUNT(1)
-------- ----------
POAPPRV 11 --- 11 mails of Po Approval not sent ---
INVTROAP 12
REQAPPRV 9
WFERROR 45 --- 45 mails have error




If Mail not received by User ?


select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’xxx,yyy’ ;


Status – Active
Notification_preference-> Mailtext
Email Address should not be null


Notification not sent waiting to be mailed ?


SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?


$FND_TOP/sql
run wfmlrdbg.sql
******************************

Note: 1054215.1 - How to Check if the Workflow Mailer is Running
Note: 415516.1 - How to Check Whether Notification Mailer is Working or Not

Note: 831982.1 - 11i/R12 - A guide for troubleshoting Workflow Notification Emails - Inbound and Outbound
Note: 1012344.7 - Notifications Not Being Sent In Workflow
Note: 560472.1 - Workflow Mailers Not Sending Notifications

Please see (Note: 753845.1 - How to Perform a Meaningful SMTP Telnet Test to Troubleshoot Java Mailer Issues), the same error is reported in this doc.

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

Source:--http://waseemslearning.blogspot.in/2011/11/troubleshooting-workflow-notification.html

Very important queries in day to day apps dba life

select s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.program,s.module,s.action,s.status,p.pid,p.spid,s.process,s.logon_time,s.last_call_et
from v$session s,v$process p where s.paddr=p.addr and p.spid=&1;


select s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.program,s.module,s.action,s.status,p.pid,p.spid,s.process,s.logon_time,s.last_call_et
from v$session s,v$process p where s.paddr=p.addr and s.sid=&1;

select s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.program,s.module,s.action,s.status,p.pid,p.spid,s.process,s.logon_time,s.last_call_et
from v$session s,v$process p where s.paddr=p.addr and s.process='&1';



select s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.program,s.module,s.action,s.status,p.pid,p.spid,s.process,s.logon_time,s.last_call_et
from v$session s,v$process p where s.paddr=p.addr and s.action like 'Concurrent Program';

select s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.program,s.module,s.action,s.status,p.pid,p.spid,s.process,s.logon_time,s.last_call_et
from v$session s,v$process p where s.paddr=p.addr and s.program like '%JDBC Thin Client%';



select s.username,s.sid,s.serial#,s.last_call_et/54000 mins_running,q.sql_text
from v$session s join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 54000
order by sid,serial#,q.piece




module ~ conc. prog. short name




col MODULE for a15
col PROGRAM for a15
col username for a16
col schemaname for a16
col osuser for a12

select s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.program,s.module,s.action,s.status,p.pid,p.spid,s.process,s.logon_time,s.last_call_et
from v$session s,v$process p where s.paddr=p.addr and action like '%FRM%' and status ='INACTIVE' and s.last_call_et/60/60 > 1
order by s.last_call_et/60/60 desc;



To find one's own session id.
===============================
SELECT sid FROM v$session WHERE audsid = userenv('sessionid');


NVL(SYS_CONTEXT('USERENV','OS_USER'),'NULL')
, NVL(SYS_CONTEXT('USERENV','HOST'),'NULL')
, NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'),'NULL')
, SUBSTR(NVL(SYS_CONTEXT('USERENV','CURRENT_SQL')
SYS_CONTEXT('USERENV','SESSION_USER')



select 'alter system kill session '''||sid||','||serial#||''';' from v$session where username='ABC';



select do.object_name,do.owner,lo.object_id,lo.oracle_username "Locking Dbuser",lo.session_id,lo.process from dba_objects do, v$locked_object lo where
lo.object_id = do.object_id;


select session_id from dba_lock where blocking_others='Blocking';


select sql_text from v$sqltext st,v$session s where st.hash_value=s.sql_hash_value and s.sid=22 order by piece ;


SELECT s.username BLOCKER, p.spid SERVER_PID, s.SID BLOCKER_SID,s.serial# BLOCKER_SERIAL, s.machine BLOCKER_MACHINE, q.sql_text BLOCKING_QRY,ROUND(l.ctime/60) BLOCKING_MINS
FROM v$lock l,
v$session s,
v$process p,
v$sql q
WHERE s.SID = l.SID
AND s.paddr = p.addr
AND s.sql_address = q.address(+)
AND ROUND(l.ctime/60) >= 15
AND l.BLOCK = 1;



select a.inst_id
, a.sid --mL#461480.1
, a.username
, b.xidusn undo_segment_number
, b.xidslot undo_slot_number
, b.xidsqn undo_seq_number
, b.used_urec undo_records
, b.used_ublk undo_blocks
, (b.used_ublk*c.value)/(1024*1024) undo_mbytes
from gv$session a
, gv$transaction b
, gv$parameter c
where a.saddr = b.ses_addr
and b.inst_id = c.inst_id
and c.name = 'db_block_size'
order by 1,2,3;




SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess , id1, id2, lmode,request, type FROM V$LOCK WHERE (id1, id2, type)
IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request


select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;





SELECT lo.ORACLE_USERNAME ,
o.NAME,
DECODE(lo.LOCKED_MODE,
0, 'NONE',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE','NONE') LOCK_TYPE,
DECODE(s.LOCKWAIT,NULL,'LOCKED','WAITING') STATUS,
lo.OS_USER_NAME ,
s.MACHINE,
s.PROGRAM,
s.SID,
s.SERIAL#
FROM V$LOCKED_OBJECT lo,
V$SESSION s,
SYS.OBJ$ o
WHERE lo.OBJECT_ID = o.OBJ#
AND lo.SESSION_ID = s.SID
ORDER BY lo.ORACLE_USERNAME, o.NAME;



Find Oracle Database Session from the Concurrent Request #:
-----------------------------------------------------------

select REQUEST_ID, LAST_UPDATE_DATE, REQUEST_DATE, REQUESTED_BY, PHASE_CODE, STATUS_CODE,
ORACLE_PROCESS_ID, ORACLE_SESSION_ID, OS_PROCESS_ID from apps.fnd_concurrent_requests
where REQUEST_ID in (22890825,22907089);


select s.sid,s.serial#,s.username,s.osuser,s.program,s.module,s.status,p.spid,s.logon_time,s.last_call_et
from v$session s,v$process p where s.paddr=p.addr and p.spid=


SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
vsess.sid SID,
vsess.serial# serial#,
SUBSTR(vproc.spid,1,10) svrproc
FROM apps.fnd_concurrent_requests req,
apps.fnd_concurrent_processes proc,
apps.fnd_lookups look,
apps.fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.pid = vsess.sid(+);






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


select OBJECT_ID,SESSION_ID,ORACLE_USERNAME,PROCESS from v$locked_object where OBJECT_ID=XXXXXX



select sql_text from v$sqltext st,v$session s where st.hash_value=s.sql_hash_value and s.sid=22 order by piece ;



select sql_text from v$open_cursor where sid=XXXXXX


select rn.USN,rn.NAME,rs.RSSIZE,rs.extents ,rs.WAITS,rs.STATUS from V$ROLLNAME rn, v$rollstat rs where rs.usn=rn.usn;



select SEGMENT_NAME,TABLESPACE_NAME,INITIAL_EXTENT,MIN_EXTENTS,MAX_EXTENTS,sum(bytes/1024/1024) from dba_segments where
SEGMENT_TYPE = 'ROLLBACK' group by SEGMENT_NAME,TABLESPACE_NAME,INITIAL_EXTENT,MAX_EXTENTS,MIN_EXTENTS;




select tablespace_name,sum(bytes/1024/1024) M from dba_free_space where tablespace_name like '%RBS%' group by tablespace_name;




select OBJECT_ID,SESSION_ID,ORACLE_USERNAME,PROCESS from v$locked_object where OBJECT_ID in
(select object_id from dba_objects where object_id in
(select OBJECT_ID from v$locked_object));



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



ps -ef|grep RDT|awk '{ print $2 }'|xargs kill -9


ps -ef|grep LOC|awk '{ print $2 }'|xargs sudo kill -9



String Search and Replacement
-----------------------------
perl -pi -e 's/string_to_replace/replace_with/g' files

Eg : /usr/bin/perl -pi -e 's/ Tru/TRU/g'

Here " Tru" is replaced with "TRU" in all the files mentioned


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

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select owner,object_name,object_type,last_ddl_time from dba_objects where owner='CUOWN' and
to_date(last_ddl_time,'DD-MON-YYYY HH24:MI:SS')>'20-MAY-2007 10:00:00';





# hostname
cidcsmbiwprd01
#

# mount -F nfs cidconas02.cidc.cummins.com:/vol/vol25/CIDC_CG200G_003/cdserv /cdserv


select * from v$sess_io;

select * from v$waitstat where count > 0;
v$sesstat
v$sysstat
v$session_event
v$system_event
v$session_wait

ST Enqueue for Space Management Transaction

SQ Enqueue for Sequence Numbers

TX Enqueue for a Transaction


SELECT lo.ORACLE_USERNAME ,o.NAME,
DECODE(lo.LOCKED_MODE,
0, 'NONE',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE','NONE') LOCK_TYPE,
DECODE(s.LOCKWAIT,NULL,'LOCKED','WAITING') STATUS,lo.OS_USER_NAME ,s.MACHINE,s.PROGRAM,s.SID,s.SERIAL# FROM V$LOCKED_OBJECT lo,
V$SESSION s,SYS.OBJ$ o WHERE lo.OBJECT_ID = o.OBJ# AND lo.SESSION_ID = s.SID ORDER BY lo.ORACLE_USERNAME, o.NAME;





select ((sysdate-a.logon_time)*86400)*4 logonsecs, a.username, a.osuser, row_wait_obj#, row_wait_file# row_wait_block#, b.consistent_gets, b.block_gets,
c.*,d.spid,g.name cpusess, g.value cpusessused, g.value/((sysdate-a.logon_time)*86400)*4 pct,e.name reccpu,e.value reccpuvalue,f.name cpu,f.value cpuvalue
from v$session a
,v$sess_io b
,v$session_wait c
,v$process d
,(select ei1.sid,ei1.statistic#, ei1.value,ei2.name from v$sesstat ei1, v$statname ei2 where ei2.statistic#=ei1.statistic#) e
,(select fi1.sid,fi1.statistic#, fi1.value,fi2.name from v$sesstat fi1, v$statname fi2 where fi2.statistic#=fi1.statistic#) f
,(select gi1.sid,gi1.statistic#, gi1.value,gi2.name from v$sesstat gi1, v$statname gi2 where gi2.statistic#=gi1.statistic#) g
where b.sid(+) = a.sid
and c.sid(+) = a.sid
and d.addr=a.paddr
and e.sid=a.sid
and e.statistic#=8
and f.sid=a.sid
and f.statistic#=177
and g.sid=a.sid
and g.statistic#=12
and event not in ('rdbms ipc reply', 'rdbms ipc message', 'SQL*Net message from client',
'SQL*Net message to client', 'SQL*Net more data to client', 'SQL*Net message to dblink',
'SQL*Net message from dblink','SQL*Net more data from dblink','SQL*Net more data to dblink',
'SQL*Net more data from client','pmon timer','smon timer','pipe get')
order by 9 desc







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


select b.username APP_USER,a.spid SPID,b.process CLT_PID,b.program PROGRAM,b.terminal TERMINAL,c.sql_text SQL_TEXT
from v$process a, v$session b,v$sqlarea c
where a.addr = b.paddr(+)
and b.sql_address = c.address(+)



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


Database Growth (Datafiles should NOT be autoextensible in this case)
----------------------------------------------------------------------

select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from v$datafile
group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM')
order by 1, 2;




select to_char(creation_time, 'RRRR Month') "Month", sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile where creation_time > SYSDATE-365 group by to_char(creation_time, 'RRRR Month');




Database Import
----------------

select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;





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



select a.sid, a.username, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where a.sid=c.sid
and b.statistic#=c.statistic#
and a.username = 'CUOWN'



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



-- FUNCTION: Check the SQL statement which is currently executed by the -- given session ID
-- INPUT: SID and SERIAL#

SELECT s.sid,
s.serial# serial,
s.osuser,
a.first_load_time,
a.executions,
a.disk_reads,
a.buffer_gets,
a.rows_processed,
a.sql_text
FROM v$sqlarea a, v$session s
WHERE a.address = s.sql_address
AND a.hash_value = s.sql_hash_value
AND s.sid = 66
AND s.serial# = 1292
/

-- Find out what users are doing and RESOURCES THEY ARE USING ....

select a.sid, a.username, s.sql_text
from v$session a, v$sqltext s
where a.sql_address = s.address
and a.sql_hash_value = s.hash_value
and a.sid = XXX
--order by a.username, a.sid, s.piece





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

TOP OFFENDERS
-------------



select a.username
,round(b.buffer_gets/decode(b.rows_processed,0,1,b.rows_processed),0) "RATIO"
,b.buffer_gets
,b.rows_processed
,b.sql_text
from dba_users a
,v$sql b
where b.buffer_gets/decode(b.rows_processed,0,1,b.rows_processed) > 1000000
and a.user_id = b.parsing_user_id
order by 2 desc







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



select ((sysdate-a.logon_time)*86400)*4 logonsecs, a.username, a.osuser, row_wait_obj#, row_wait_file# row_wait_block#, b.consistent_gets, b.block_gets, c.*,d.spid,g.name cpusess, g.value cpusessused, g.value/((sysdate-a.logon_time)*86400)*4 pct,e.name reccpu,e.value reccpuvalue,f.name cpu,f.value cpuvalue
from v$session a
,v$sess_io b
,v$session_wait c
,v$process d
,(select ei1.sid,ei1.statistic#, ei1.value,ei2.name from v$sesstat ei1, v$statname ei2 where ei2.statistic#=ei1.statistic#) e
,(select fi1.sid,fi1.statistic#, fi1.value,fi2.name from v$sesstat fi1, v$statname fi2 where fi2.statistic#=fi1.statistic#) f
,(select gi1.sid,gi1.statistic#, gi1.value,gi2.name from v$sesstat gi1, v$statname gi2 where gi2.statistic#=gi1.statistic#) g
where b.sid(+) = a.sid
and c.sid(+) = a.sid
and d.addr=a.paddr
and e.sid=a.sid
and e.statistic#=8
and f.sid=a.sid
and f.statistic#=177
and g.sid=a.sid
and g.statistic#=12
and event not in ('rdbms ipc reply', 'rdbms ipc message', 'SQL*Net message from client',
'SQL*Net message to client', 'SQL*Net more data to client', 'SQL*Net message to dblink',
'SQL*Net message from dblink','SQL*Net more data from dblink','SQL*Net more data to dblink',
'SQL*Net more data from client','pmon timer','smon timer','pipe get')
order by 9 desc



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


Datablock Corruption
--------------------

select file_id, block_id, extent_id, segment_name, segment_type,
blocks
from dba_extents
where file_id = 204
and 77626 between block_id + 1 and block_id + blocks ;

FILE_ID BLOCK_ID EXTENT_ID SEGMENT_NAME
SEGMENT_TYPE BLOCKS
---------- ---------- ---------- ------------------------------
------------------ ----------
204 77625 3 WF_LOCAL_USER_ROLES TABLE





DATAFILE RELATED INFO:
======================

select f.phyrds,f.phywrts,d.name from v$datafile d, v$filestat f where d.file#=f.file# order by f.phywrts;





RBS Related:
--------------


select class, count from v$WAITSTAT where class in ('undo header','undo block','system undo header', 'system undo block');


Some more rollback segments needs to be added if the number of waits for any
of the rollback segment blocks or headers exceeds more than one percent of the
total number of requests.



select c.name,a.max_extents,b.extents extentsallocated,b.rssize rollbacksegsize,b.curext extentready from dba_rollback_segs a,v$rollstat b,v$rollname c where b.usn=c.usn and c.name=a.segment_name;



select sum(value) "Data Requests" from v$SYSSTAT where name in ('db block gets', 'consistent gets');


Data Requests
--------------
6100




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



\\mdcxsdced12\netlogon\oracle


\\KPITSDCED11\netlogon\oracle


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





select file_id ,sum(bytes/1024/1024) MB from dba_free_space where file_id in (select file_id from dba_data_files where file_name like '/u02%')
group by file_id;



select file_name,file_id,sum(bytes/1024/1024) MB from dba_data_files where file_id in (3,67,101) group by file_name,file_id;



select a.file_id,file_name,sum(a.bytes/1024/1024) MB from dba_free_space a, dba_data_files b where a.file_id=b.file_id group by a.file_id,file_name

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



Referential Constraints:
=========================


SELECT
A.TABLE_NAME table_name,
A.CONSTRAINT_NAME key_name,
B.TABLE_NAME referencing_table,
B.CONSTRAINT_NAME foreign_key_name,
B.STATUS fk_status
FROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS B
WHERE
A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME and
B.CONSTRAINT_TYPE = 'R' and A.TABLE_NAME='xxxxx'
AND A.OWNER='XXXXX'
ORDER BY 1, 2, 3, 4;


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

col owner for a12

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from dba_constraints
where constraint_type='R'
and r_constraint_name in (select constraint_name from dba_constraints
where constraint_type in ('P','U') and table_name='DSCSM_CUST_CONTACT');





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




SQL> CREATE UNIQUE INDEX DSCSM.DSCSM_CUST_CONTACT_IND1 ON DSCSM.DSCSM_CUST_CONTACT
2 (DIST_ID, CONTACT_ID)
3 LOGGING
4 TABLESPACE DSCSM_LM4M_IDX
PCTFREE 10
5 6 INITRANS 2
7 MAXTRANS 255
8 STORAGE (
9 INITIAL 4M
10 NEXT 512K
MINEXTENTS 1
11 12 MAXEXTENTS 2147483645
13 PCTINCREASE 0
14 FREELISTS 1
15 FREELIST GROUPS 1
16 BUFFER_POOL DEFAULT
17 )
18 NOPARALLEL;
CREATE UNIQUE INDEX DSCSM.DSCSM_CUST_CONTACT_IND1 ON DSCSM.DSCSM_CUST_CONTACT
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found





Find Duplicates:
================

select count(1) from (select DIST_ID, CONTACT_ID,count(1) from DSCSM.DSCSM_CUST_CONTACT group by DIST_ID,CONTACT_ID having count(*) > 1);


Remove Duplicates:
==================

delete from DSCSM.DSCSM_CUST_CONTACT where rowid not in ( select min(rowid) from DSCSM.DSCSM_CUST_CONTACT group by DIST_ID, CONTACT_ID);







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







Buffer Cache Hit Ratio :
========================


select round(((1-(sum(decode(name,
'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio"
from v$sysstat;




Shared Pool Hit Ratio
------------------------

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;





REDO LOG BUFFER
================

The redo log is small when compared to the SGA. A small increase can greatly
enhance throughput. Redo log space requests to the number of redo entries
should not be greater than 1 in 5000.

select (req.value*5000)/entries.value "Ratio"
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
and entries.name = 'redo entries'
/

SORTS
======

The ratio of sorts (disk) to sorts (memory) should be < 5%. Increase
the size of SORT_AREA_SIZE if it is less than 5%. Increments of 10%
should be fine.

select disk.value "Disk", mem.value "Mem", (disk.value/mem.value)*100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';



Query for Session Cached Cursors and Open Cursors
-------------------------------------------------


SELECT
'session_cached_cursors' parameter,
LPAD(value, 5) value,
DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
FROM
(SELECT
MAX(s.value) used
FROM
v$statname n,
v$sesstat s
WHERE
n.name = 'session cursor cache count' and
s.statistic# = n.statistic#
),
(SELECT
value
FROM
v$parameter
WHERE
name = 'session_cached_cursors'
)
UNION ALL
SELECT
'open_cursors',
LPAD(value, 5),
to_char(100 * used / value, '990') || '%'
FROM
(SELECT
MAX(sum(s.value)) used
FROM
v$statname n,
v$sesstat s
WHERE
n.name in ('opened cursors current', 'session cursor cache count') and
s.statistic# = n.statistic#
GROUP BY
s.sid
),
(SELECT
value
FROM
v$parameter
WHERE
name = 'open_cursors'
)
/







Information on Open Cursors:
==============================



SELECT b.SID, UPPER(a.NAME), b.VALUE
FROM v$statname a, v$sesstat b, v$session c
WHERE a.statistic# = b.statistic#
AND c.SID = b.SID
AND LOWER(a.NAME) LIKE '%' || LOWER('CURSOR')||'%'
AND b.SID=20
UNION
SELECT SID, 'v$open_cursor opened cursor', COUNT(*)
FROM v$open_cursor
WHERE SID=20
GROUP BY SID
ORDER BY SID
/






Soft_Hard_Parses and Cursor Cache Hit Ratio
----------------------------------------------


SELECT
TO_CHAR(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
TO_CHAR (100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
TO_CHAR (100 * hard / calls, '999990.00') || '%' hard_parses
FROM
(SELECT value calls FROM v$sysstat WHERE name = 'parse count (total)' ),
(SELECT value hard FROM v$sysstat WHERE name = 'parse count (hard)' ),
(SELECT value sess FROM v$sysstat WHERE name = 'session cursor cache hits' )
/




TEMP Usage:
===============

SELECT S.username USER_NAME,S.sid SID,S.serial# SERIAL, S.osuser OS_USER, P.spid UNIX_PID,P.PROGRAM,
SUM (T.blocks) * TBS.block_size / 1024 / 1024 TEMP_USED_MB, T.TABLESPACE,COUNT(*) STATEMENTS
FROM v$sort_usage T, v$session S, (select value block_size from v$parameter where name = 'db_block_size') TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,P.program, TBS.block_size, T.tablespace
having (SUM (T.blocks) * TBS.block_size / 1024 / 1024) > 20
ORDER BY TEMP_USED_MB desc;



– Temp segment usage per session.–

col USERNAME for a12
col OSUSER for a10
col TABLESPACE for a12
col MODULE for a18

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;




– Listing of temp segments.–

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;





This query will give the size of the temporary tablespace:
----------------------------------------------------------

select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;

This query will give the "high water mark" (= max used at one time) of the temporary tablespace
----------------------------------------------------------------------------------------------------

select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;


This query will give current usage
------------------------------------

select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;





RBS Usage:
============

select substr(r.name,1,5) ROLL_NAME,s.sid SID,s.serial# Serial ,substr(nvl(s.username, 'No Tran'),1,7) DB_USER,p.spid SVR_PID,
s.osuser OS_USER, s.terminal TERMINAL,s.PROGRAM, (t.used_ublk * TBS.block_size)/1024/1024 RB_USAGE_MB,round(LAST_CALL_ET/60) IDLE_MINS,S.STATUS
from v$session s, v$transaction t, v$rollname r, v$process p,(select value block_size from v$parameter where name = 'db_block_size') TBS
where s.taddr=t.addr(+)
and t.xidusn=r.usn
and p.addr=s.paddr
and (t.used_ublk * TBS.block_size)/1024/1024 > 100
order by 10 desc;


QUERIES GENERATING LOT OF REDO:
================================


1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates
how many blocks have been changed by the session. High values indicate a
session generating lots of redo.

The query you can use is:
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc;

Run the query multiple times and examine the delta between each occurrence
of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. This view contains information about the amount of
undo blocks and undo records accessed by the transaction (as found in the
USED_UBLK and USED_UREC columns).

The query you can use is:
SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;




Registered Snapshots:
=====================

SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, snaptime FROM sys.slog$ s, dba_registered_snapshots r
WHERE s.snapid=r.snapshot_id(+) AND mowner = 'CUOWN' AND MASTER ='PART_LOC';



SELECT 'EXEC DBMS_JOB.BROKEN('||DJ.JOB||',TRUE);'
FROM DBA_REFRESH_CHILDREN drc,DBA_SNAPSHOTS DS,DBA_JOBS DJ
WHERE DRC.NAME = DS.NAME AND DJ.BROKEN='N' AND DRC.JOB = DJ.JOB AND DS.MASTER_LINK LIKE '%RELIAB%' order by ds.owner;





PGA Related Stat Queries:
==========================



SELECT * FROM V$PGASTAT;


SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
optimal_executions, onepass_executions, multipasses_executions
FROM v$sql_workarea_histogram
WHERE total_executions != 0;



SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;





PGA and UGA Considerations
===========================


select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in
('session pga memory','session pga memory max','session uga memory','session uga memory max',
'session cursor cache count','session cursor cache hits','session stored procedure space',
'opened cursors current','opened cursors cumulative') and vses.username is not null
group by vses.username, vsst.sid, vses.serial#, vstt.name order by vses.username, vsst.sid, vses.serial#, vstt.name;










Tablespace Status:
===================

Free %
------

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %" FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT
d.status "Status", d.tablespace_name "Name", d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Free %" FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'




Used %:
-------

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type",
d.extent_management "Extent Management", d.SEGMENT_SPACE_MANAGEMENT "Segment_Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL(100 - (NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Used %" FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT
d.status "Status", d.tablespace_name "Name", d.contents "Type",
d.extent_management "Extent Management",d.SEGMENT_SPACE_MANAGEMENT "Segment_Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size (M)",
TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990D900') "Free (MB)",
TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Used %" FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY' order by "Used %" desc;

Sort Sessions:
================


select substr(vs.username, 1, 30) "Username", substr(vs.osuser, 1, 30) "OS User",
substr(vsn.name,1,15) "Operation", sum(vss.value) "Value" from v$session vs, v$sesstat vss,
v$statname vsn
where (vss.statistic#= vsn.statistic#) and
(vs.sid=vss.sid) AND
(vsn.name like '%sort%')
group by substr(vs.username, 1, 30), substr(vs.osuser, 1, 30), substr(vsn.name,1,15)
order by sum(vss.value) desc;










You need to increase the INITTRANS for the table on which you are finding the buffer busy wait.
===============================================================================================



select * from (select DECODE(GROUPING(a.object_name),1,'All Objects',a.object_name) AS "Object ",
sum(case
when a.statistic_name = 'ITL waits' then
a.value
else
null
end) "ITL Waits",
sum(case
when a.statistic_name = 'buffer busy waits' then
a.value
else
null
end) "Buffer Busy Waits",
sum(case
when a.statistic_name = 'row lock waits' then
a.value
else
null
end) "Row Lock Waits",
sum(case
when a.statistic_name = 'physical reads' then
a.value
else
null
end) "Physical Reads",
sum(case
when a.statistic_name = 'logical reads' then
a.value
else
null
end) "Logical Reads" from v$segment_statistics a where a.owner like upper('&owner')
group by rollup(a.object_name)) b where (b."ITL Waits" > 0 or b."Buffer Busy Waits" > 0) ;



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

ls -lrt *.rpx|/usr/xpg4/bin/grep -E ".*(Apr|May|Jun).*"| grep -v grep

ls -lrt *.fmx|/usr/xpg4/bin/grep -E ".*(Apr|May|Jun).*"| grep -v grep

ls -lrt *.rdf|/usr/xpg4/bin/grep -E ".*(Apr|May|Jun).*"| grep -v grep


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


ORA-01555:
==========

SQL> select sum(bytes) from dba_free_space where tablespace_name='';

SQL> select sum(bytes) from dba_data_files where tablespace_name='';

SQL> select autoextensible from dba_data_files where tablespace_name='';

SQl> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;


Run below query after the export fails with "ORA-01555" error.

SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name= (select value from v$parameter where name = 'undo_tablespace'));



SQL> select begin_time,end_time,undotsn,undoblks,maxquerylen from v$undostat;



SQX Evergreen Query:
=====================

select hostname,os_user_name,database_user_name,ddl_date,ddl_type,object_type,object_owner,object_name,ticket_no,remarks from sox_user.sox_ddl_change_log;


DBMS_ROWID.ROWID_BLOCK_NUMBER






DBMS_SCHEDULER :
=================

select * from dba_scheduler_jobs;

select * from dba_scheduler_programs;

select * from dba_scheduler_schedules;

select * from dba_scheduler_program_args;

select * from dba_scheduler_windows;

select * from dba_scheduler_window_details;

select * from dict where table_name like '%SCHEDULER%';

---- Status of DBMS_SCHEDULER jobs

select job_name, state, enabled, START_DATE, NEXT_RUN_DATE, run_count,failure_count from dba_scheduler_jobs
where job_name like '%WEEK%' or job_name like '%DAILY%' or job_name like '%ARCHIVE%' ;


----- Execution errors in DBMS_SCHEDULER jobs

select ERROR#, ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS
where job_name like '%WEEK%' or job_name like '%DAILY%' or job_name like '%ARCHIVE%'
order by ACTUAL_START_DATE;


Sequence : Window ---> Schedule ----> Program ----> Job
--------

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




Check the possibility of shrinking a datafile:
==============================================

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+);




Queries not using Bind Variables:
=================================

SELECT SUBSTR(sql_text, 1, 40) "SQL",
COUNT(*),
SUM(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY SUBSTR(sql_text, 1, 40) HAVING COUNT(*) > 30
ORDER BY 2;




Remove old files :
=================

Command 1 lists the candidate files. "type -f" makes sure that only files get considered (and not dirs).
Command 2 actually removes the files


[1] find ${ARCH_DEST}/*.log -mtime +7 -type f -exec ls -ltr {} \;

[2] find ${ARCH_DEST}/*.log -mtime +7 -type f -exec rm -f {} \;


find /u01/pkmpci/applcsf/log -name "*.log" -mtime +7 -exec rm -f {} \; -------> working


find $APACHE_DIR -mtime +15 -name "*_log.*" -type f -user ${USER} -exec ls -l {} \; ------> working


find . -mtime +7 -name "*.arc" -type f -exec ls -lrt {} \;

find . -mtime +7 -name "*.arc" -type f -exec rm -rf {} \;



unix.com
=========

trusharb/trushunix



find $1 -type f -size +100000k -exec ls -ltr {} \; | awk '{ print $5 ": " $9 }' | sort -rn






select tablespace_name,sum(bytes)/1024/1024 M from dba_data_files where tablespace_name='APPS_TS_TX_DATA' group by tablespace_name;

select tablespace_name,sum(bytes)/1024/1024 M from dba_free_space where tablespace_name='APPS_TS_TX_DATA' group by tablespace_name;

select file_name,sum(bytes)/1024/1024 M from dba_data_files where tablespace_name='APPS_TS_TX_DATA' group by file_name;



select tablespace_name,sum(bytes)/1024/1024 M from dba_data_files where tablespace_name='APPS_TS_TX_IDX' group by tablespace_name;

select tablespace_name,sum(bytes)/1024/1024 M from dba_free_space where tablespace_name='APPS_TS_TX_IDX' group by tablespace_name;

select file_name,sum(bytes)/1024/1024 M from dba_data_files where tablespace_name='APPS_TS_TX_IDX' group by file_name;



select file_name,sum(bytes)/1024/1024 M from dba_data_files where tablespace_name='APPS_TS_QUEUES' group by file_name;



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

MODES are SYSTEM/USER/ORACLE/ALLORACLE

FNDCPASS apps/prod_apps_pwd 0 Y system/system_pwd SYSTEM APPLSYS

FNDCPASS apps/new_apps_pwd 0 Y system/system_pwd USER SYSADMIN (users in FND_USER apps table)

FNDCPASS apps/new_apps_pwd 0 Y system/system_pwd ORACLE (all oracle apps database schemas)

FNDCPASS apps/new_apps_pwd 0 Y system/system_pwd ALLORACLE (option available only in R12)



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

How to Change Applications R12 Passwords using Applications Schema Password Change Utility (FNDCPASS)? [ID 437260.1]


Change the password of APPLSYS and APPS users manually by following the steps in the note referenced below
(remember that the APPLSYS and APPS passwords always have to be the same).

Note: 160337.1 - How To Manually Change The APPS, APPLSYS and APPLSYSPUB Passwords in Oracle Applications
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=160337.1

Once you change the passwords successfully, use FNDCPASS to change the APPS password, and run AutoConfig then.




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


select 'drop table bkup.'|| table_name || ' ; ' from dba_tables where owner='BKUP' and substr(table_name,-8) like to_char(sysdate,'mmddyyyy');



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


AD_BUGS
++++++++++


select BUG_ID, BUG_NUMBER,BASELINE_NAME,LAST_UPDATE_DATE, CREATION_DATE from ad_bugs where bug_number in ('6761391','6340867','7364555','8351609');



SELECT DISTINCT RPAD(a.bug_number, 11)|| RPAD(e.patch_name, 11)|| RPAD(TRUNC(c.end_date), 12)|| RPAD(b.applied_flag, 4) ,
a.BASELINE_NAME
FROM ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE a.bug_id = b.bug_id
AND b.patch_run_id = c.patch_run_id
AND c.patch_driver_id = d.patch_driver_id
AND d.applied_patch_id = e.applied_patch_id
AND a.bug_number in ('&1') ORDER BY 1 DESC;



NOTE: A specific bug maybe fixed by multiple patches so it might be good to look for the bug number,
instead of the patch number to see if that bug is fixed already on your system.
Another way is to look at the file version mentioned in the patch and check if you have that version or higher.


For example you apply merged patch merge123 it contains 5 patches
10001 10002 10003 …
ad_applied_patches – will have one record about merge123
ad_bugs – 5 records

Another example: you apply maintenance patch which includes 51 other
patches. In that case
ad_applied_patches – will have one record about maintenance patch
ad_bugs – 51 + 1

I hope my thought is clear for you.

PS: Anyway I use ad_bugs table in order to find out do I have one or
another patch to apply.





New patches applied in last 90 days alongwith detailed driver information
==========================================================================

select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.DRIVER_FILE_NAME,
B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATION_DATE, B.FILE_SIZE,
B.MERGED_DRIVER_FLAG, B.MERGE_DATE
from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B
where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID
and B.creation_date >= ( sysdate -90 )
order by b.creation_date



Find the Product, its status and patchset level
================================================

SELECT V.APPLICATION_NAME PRD, to_char(V.APPLICATION_ID) PRDID, L.MEANING PRDSTATUS, DECODE(I.PATCH_LEVEL, NULL, '11i.' || v.APPLICATION_SHORT_NAME || '.?', I.PATCH_LEVEL) patchset
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status )
ORDER BY 1;



Languages implemented in EBS
===============================


select nls_language, language_code, installed_flag from fnd_languages where installed_flag in ('I','B');

B --> Base
I --> Installed






FNDCPASS apps/oraprd101 0 Y system/xetaprdsys USER VIKAS_MITTAL fujitsu1234




SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;

This deletes data from a few FND tables such as FND_NODES but after AutoConfig has been executed they will contain the correct values.

Run AutoConfig ($COMMON_TOP/admin/scripts//adautocfg.sh) on each tier.

Startup the environment.








touch -t mmddyyyy file_name


select profile_option_value from apps.fnd_profile_option_values where profile_option_id=125;

update apps.fnd_profile_option_values set profile_option_value='ORATEST - Cloned from PKMPCI on 07-JUL-2009' where profile_option_id=125;










ps -ef|grep ort03ebs|grep LOCAL=NO|awk '{ print "kill -9 " $2 }' > kill.sh



SQL> select patch_level from fnd_product_installations where patch_level like '%AP%';
PATCH_LEVEL
------------------------------
11i.AP.O



Port Information:
=================

$APPL_TOP/admin/out/_/portpool.lst

For R12,

$INST_TOP/apps/_/admin/out/portpool.lst


Remove Files:
==============

for i in 1 *
do
rm -f $i
done



Terminate the conc request from backend.
========================================

SQL> update fnd_concurrent_requests set phase_code='C', status_code='X' where request_id = 7842301;


It marks the request as Completed Terminated. Always act on the child request first and then on parent request.





FNDCPASS apps/oraprd101 0 Y system/xetaprdsys USER VIKAS_MITTAL fujitsu1234




SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;

This deletes data from a few FND tables such as FND_NODES but after AutoConfig has been executed they will contain the correct values.

Run AutoConfig ($COMMON_TOP/admin/scripts//adautocfg.sh) on each tier.

Startup the environment.








touch -t mmddyyyy file_name




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

APPS PRODUCT LEVELS:
-------------------


FND_PRODUCT_GROUPS



SQL> select patch_level from apps.fnd_product_installations where patch_level like '%AP%';



PATCH_LEVEL
------------------------------
11i.AP.O



select product_name, product_abbreviation, pseudo_product_flag,
application_short_name, product_family_abbreviation
from ad_pm_product_info
where pseudo_product_flag = ‘N’
order by 1 ;


select BUG_ID, BUG_NUMBER,BASELINE_NAME, LAST_UPDATE_DATE, CREATION_DATE from ad_bugs where bug_number in ('6761391','6340867','7364555','8351609');



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

ps -ef|grep oracle|grep LOCAL=NO | awk '{ print "kill -9 " $2 }' > kill.sh

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




Work Flow:
==========

select * from wf_notifications where message_type='HRSSA' and mail_status='FAILED';

select * from wf_notifications order by begin_date desc; --- Use after sending "test mailer" to the user. (message_type='WFTESTS')


Concurrent Programs:
====================

select concurrent_program_name, user_concurrent_program_name, description from fnd_concurrent_programs_vl where concurrent_program_name like '%FND%';



Responsibilities assigned to a given user in EBS
=================================================


col RESPONSIBILITY_NAME for a49
col

select distinct fr.USER_ID,fu.user_name,frtl.RESPONSIBILITY_NAME ,fr.START_DATE,fr.END_DATE
from
FND_USER_RESP_GROUPS_DIRECT fr,
fnd_user fu,
PER_ALL_PEOPLE_F hr,
fnd_responsibility_tl frtl
where fu.user_name = 'VIYER'
and fr.user_id=fu.user_id
and fu.EMPLOYEE_ID=hr.PERSON_ID
and frtl.RESPONSIBILITY_ID=fr.RESPONSIBILITY_ID;





Long running concurrent Requests
======================================


set linesize 190

col user_name format a11
col s.sid format a4
col s.serial# format a4
col s.serial# format a6
col p.spid format a6
col USER_CONCURRENT_PROGRAM_NAME format a40
col Running_time format a13



select u.user_name,request_id, s.sid,s.serial#, p.spid,USER_CONCURRENT_PROGRAM_NAME,to_char(((sysdate - actual_start_date)*24*60 ),'99999999.99') "Running_time"
from FND_CONC_REQUESTS_FORM_V, fnd_user u, v$session s, v$process p
where phase_code = 'R'
and status_code = 'R'
and s.paddr = p.addr
and p.spid = oracle_process_id
and requested_by = u.user_id
order by 7 desc;


select u.user_name,request_id, s.sid,s.serial#, p.spid,USER_CONCURRENT_PROGRAM_NAME,to_char(((sysdate - actual_start_date)*24*60 ),'99999999.99') "Running_time"
from FND_CONC_REQUESTS_FORM_V, fnd_user u, v$session s, v$process p
where s.paddr = p.addr
and p.spid = oracle_process_id
and requested_by = u.user_id
and request_id =
order by 7 desc;




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';


select r.request_id,
r.oracle_process_id,
r.oracle_session_id,
r.os_process_id,
s.sid,
s.serial#,
s.paddr
from fnd_concurrent_requests r,
v$session s
where request_id = &reqid
and r.oracle_session_id = s.audsid(+);



Long Running Concurrent Requests:
---------------------------------
select fcrv.request_id REQUEST,
decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
decode(fcrv.status_code,
'A','Waiting',
'B','Resuming',
'C','Normal',
'F','Scheduled',
'G','Warning',
'H','On Hold',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'X','Terminated',
'Z','Waiting',fcrv.status_code)STATUS,
substr(fcrv.program,1,28)PROGRAM,
substr(fcrv.requestor,1,9)REQUESTOR,
to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI:SS AM')START_TIME,
round(((sysdate - fcrv.actual_start_date)*1440),2)RUN_TIME,
substr(fcr.oracle_process_id,1,7)OS_PID
from apps.fnd_conc_req_summary_v fcrv,
apps.fnd_concurrent_requests fcr
where fcrv.phase_code in ('R','P','I')
and round(((sysdate - fcrv.actual_start_date)*1440),2) > 30
and fcrv.request_id = fcr.request_id
and fcrv.concurrent_program_id not in ('40112','40113','36887','33733','31556','33708')
order by RUN_TIME desc
/





select a.request_id
, c.user_concurrent_program_name
, c.description ucp_description
, decode(a.phase_code
, 'C', 'Completed'
, 'I', 'Inactive'
, 'P', 'Pending'
, 'R', 'Running'
, a.phase_code) phase_code
, decode(a.status_code
, 'C', 'Normal'
, 'D', 'Cancelled'
, 'E', 'Error'
, 'G', 'Warning'
, 'X', 'Terminated'
, a.status_code) status_code
, a.hold_flag
, a.request_date
, a.requested_start_date
, a.actual_start_date
, a.actual_completion_date
, a.argument_text
, a.parent_request_id
, round((a.actual_completion_date - a.actual_start_date)*24*60*60, 2) runtime_seconds
, a.oracle_process_id db_os_process_id
, a.oracle_session_id
, a.os_process_id ap_os_process_id
from apps.fnd_concurrent_requests a
, apps.fnd_concurrent_programs b
, apps.fnd_concurrent_programs_tl c
where a.program_application_id = b.application_id
and a.concurrent_program_id = b.concurrent_program_id
and b.application_id = c.application_id
and b.concurrent_program_id = c.concurrent_program_id
and a.status_code = 'D'
and a.request_date > sysdate-17
and c.user_concurrent_program_name like 'Workflow Background Process%'
order by request_date




select * from v$sess_io where sid =


List concurrent requests submitted by a user
============================================


SELECT
fcr.REQUEST_ID,
FU.USER_NAME,
fcr.PHASE_CODE,
fcr.STATUS_CODE,
(fcr.ACTUAL_COMPLETION_DATE - fcr.ACTUAL_START_DATE) * 24 * 60 TIME_MINS,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
fcp.CONCURRENT_PROGRAM_NAME,
fcr.ACTUAL_START_DATE,
fcr.ACTUAL_COMPLETION_DATE,
fcp.CONCURRENT_PROGRAM_NAME
FROM
apps.FND_CONCURRENT_REQUESTS FCR,
apps.FND_CONCURRENT_PROGRAMS FCP,
apps.FND_CONCURRENT_PROGRAMS_TL FCPT,
apps.FND_USER FU
WHERE 1=1
AND FCP.CONCURRENT_PROGRAM_ID=FCR.CONCURRENT_PROGRAM_ID
AND FCPT.CONCURRENT_PROGRAM_ID=FCP.CONCURRENT_PROGRAM_ID
AND FCR.REQUESTED_BY = FU.USER_ID
AND FU.USER_NAME = '&user'
AND trunc(fcr.ACTUAL_START_DATE)='&dt'
ORDER BY FCR.REQUEST_ID DESC;




List responsibilities that can run a given concurrent program
==============================================================

SELECT fr.responsibility_name RN ,fcpt.user_concurrent_program_name
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_responsibility_vl fr
WHERE frgu.request_unit_type = ‘P’
and UPPER(fcpt.user_concurrent_program_name) = UPPER(’Journal Entries Report’)
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND fr.request_group_id = frg.request_group_id
AND fcp.CONCURRENT_PROGRAM_ID = fcpt.CONCURRENT_PROGRAM_ID
ORDER BY 1
/





OS_PROCESS_ID ----> Apps tier OS process
ORACLE_PROCESS_ID ---> Db tier OS process (spid)



Find Oracle Database Session from the Concurrent Request #:
-----------------------------------------------------------

select REQUEST_ID, LAST_UPDATE_DATE, REQUEST_DATE, REQUESTED_BY, PHASE_CODE, STATUS_CODE,
ORACLE_PROCESS_ID, ORACLE_SESSION_ID, OS_PROCESS_ID from apps.fnd_concurrent_requests
where REQUEST_ID in (22890825,22907089);


select s.sid,s.serial#,s.username,s.osuser,s.program,s.module,s.status,p.spid,s.logon_time,s.last_call_et
from v$session s,v$process p where s.paddr=p.addr and p.spid=


SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
vsess.sid SID,
vsess.serial# serial#,
SUBSTR(vproc.spid,1,10) svrproc
FROM apps.fnd_concurrent_requests req,
apps.fnd_concurrent_processes proc,
apps.fnd_lookups look,
apps.fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.pid = vsess.sid(+);



STATUS_CODE Column:

A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting




.
PHASE_CODE column
.
C Completed
I Inactive
P Pending
R Running



Mapping of Phase - Status
==========================

Pending - Normal, Standby, Scheduled, Waiting
Running - Normal, Paused, Resuming, Terminating
Completed - Normal, Error, Warning, Cancelled, Terminated, Disabled
Inactive - Disabled , On Hold, No Manager



Normal Issues
==============

PENDING/Standby- -Program to run request is incompatible with other program currently running.


PENDING/Normal- -If any concurrent programs get added in the "Standard Manager" under "INCLUDE" instead of "EXCLUDE" , the concurrent requests would get submitted but the status would stay as "PENDING NORMAL".
Need to change the status Include/Exclude to "EXCLUDE" to take care of it.

INACTIVE/No Manager- -Issue of 100% disk full and conc. log/out files cannot get created.
-Concurrent Manager Service is down
-No concurrent manager process because of workshift
-All managers are locked by run-alone requests
-No manager is defined to run the request

INACTIVE/On Hold- -Pending request is placed on hold by choosing the Hold Request button in the Requests window.
-Issue due to possible locking of same table record by another form session OR by the similar conc. request running in parallel.





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



Finding requests being printed by a Printer in Oracle Apps
===========================================================


select REQUEST_ID,
REQUEST_DATE,
USER_NAME,
PHASE_CODE,
STATUS_CODE,
PRINTER,
ORACLE_PROCESS_ID, -- spid
OS_PROCESS_ID, -- apps tier process
ORACLE_SESSION_ID
from
fnd_concurrent_requests fcr,
fnd_user fu
where fu.USER_ID = fcr.REQUESTED_BY
and fu.user_name = '&1'
and trunc(request_date)='23-SEP-2010';





#########################################3




Cancel the requests submitted by users other than SYSADMIN
===========================================================

update fnd_concurrent_requests set phase_code = 'C' , status_code = 'C'
where phase_code not in ('C')
and requested_by not in ( select user_id from fnd_user where user_name in ('SYSADMIN') ) ;


+++ Status_Code should be set to D (cancelled) instead of C (Normal) done here. ++++




SQL> select count(*) from fnd_concurrent_requests
where phase_code = 'P' and concurrent_program_id not in (32263, 32592, 38089, 38121) ;

COUNT(*)
----------
117

SQL> update fnd_concurrent_requests
set phase_code = 'C', status_code = 'D'
where concurrent_program_id not in (32263, 32592, 38089, 38121);





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


http://georgenet.net/oracle/
/* Query to find out if any patch except localisation patch is applied or not, if applied,
that what all drivers it contain and time of it's application*/
select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME,
B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE,
B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B
where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID
and b.creation_date >= sysdate -90
- and A.PATCH_NAME = ''

*************************************************************************************************
/* To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id */

select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.NAME, C.DRIVER_FILE_NAME,
A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE,
A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRIVERS C,
AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID
and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID
in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '&patch_naumber'))
ORDER BY 3 ;

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

/* To find the latest application version */
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done", BASE_RELEASE_FLAG "Base version"
FROM AD_RELEASES where END_DATE_ACTIVE IS NULL
/* ARU_RELEASE_NAME = 11i; MINOR_VERSION = 5; TAPE_VERSION = 7 (11i.5.7) */
*************************************************************************************************
/* to find the base application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE
when updated,
ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y'

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

/* To find all available application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE
when updated,
END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE
'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done"
from AD_RELEASES

*************************************************************************************************
/* To get file version of any application file which is changed through patch application */
select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A,
AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME ;
*************************************************************************************************

/* To get information related to how many time driver file is applied for bugs */

select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = '&Patch_Number' );

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

/* To find latest patchset level for module installed */
select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS
GROUP BY APP_SHORT_NAME ;
*************************************************************************************************
/* To find what is being done by the patch */

select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top,
D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action
from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F
where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '&Patch_Number' and B.PATCH_RUN_BUG_ID = '&Patch_Number' and
C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE ;

*************************************************************************************************
/* Second Query to know, what all has been done during application of PATCH */

Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action
from
AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D,
AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G, AD_APPL_TOPS H,
AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J
where A.BUG_ID = B.BUG_ID
and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID
and C.FILE_ID = D.FILE_ID
and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID
and G.APPL_TOP_ID = H.APPL_TOP_ID
and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID
and I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID
and B.PATCH_RUN_ID = G.PATCH_RUN_ID
and C.EXECUTED_FLAG = 'Y'
and G.PATCH_DRIVER_ID
in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '&Patch_Number'))
GROUP BY
J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME,
D.SUBDIR, D.FILENAME, E.ACTION_CODE ;
*************************************************************************************************

col PATCH_NAME format a10
col PATCH_TYPE format a10
col DRIVER_FILE_NAME format a15
col PLATFORM format a10
select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE, AD.PLATFORM,AL.LANGUAGE
from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL
where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID
and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID
and AP.PATCH_NAME = '4502962';

select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.DRIVER_FILE_NAME,
B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATION_DATE, B.FILE_SIZE,
B.MERGED_DRIVER_FLAG, B.MERGE_DATE
from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B
where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID
and B.creation_date >= ( sysdate -90 )
order by b.creation_date

PATCH_NAME PATCH_TYPE DRIVER_FILE_NAM CREATION_DATE PLATFORM LANG
---------- ---------- --------------- --------------- ---------- ----
4502962 PATCH-SET u4502962.drv 30-DEC-06 LINUX US

col PRD format a40 trunc
col PRDID format a5
col PRDSTATUS format a10
col PATCHSET format a20
SELECT V.APPLICATION_NAME PRD, to_char(V.APPLICATION_ID) PRDID, L.MEANING PRDSTATUS, DECODE(I.PATCH_LEVEL, NULL, '11i.' || v.APPLICATION_SHORT_NAME || '.?', I.PATCH_LEVEL) patchset
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status )
ORDER BY 1;

==================
Password change script
Do the following as script
stty -echo
read SYSPASS?"Enter the password for system > "
echo
read APPPASS?"Enter the password for apps > "
stty echo;echo
read EXT?"Enter the Suffix > "
if [ ${EXT} != "" ]
then
EXT="_${EXT}"
fi
sqlplus -s << EOF
apps/${APPPASS}
set pages 0 feed off verify off linesize 100
spool /tmp/pass.$$
select 'FNDCPASS apps/${APPPASS} 0 Y system/${SYSPASS} ORACLE '||
oracle_username||' '||oracle_username||'${EXT}'
from fnd_oracle_userid
where oracle_username not in ('APPS','APPLSYS','APPLSYSPUB','SYS','');
spool off
EOF
echo "sh /tmp/pass.$$"
sh /tmp/pass.$$
echo "rm /tmp/pass.$$"
rm /tmp/pass.$$
echo "Spool file name is :/tmp/pass.$$"
====================


Patch level
++++++++++++++
++++++++++++++



set line 150
column APPLICATION_NAME format a50
column PRODUCT_VERSION format a10
column APPLICATION_SHORT_NAME format a10
column PATCH_LEVEL format a15
set pagesize 100
select application_name , APPLICATION_SHORT_NAME,PRODUCT_VERSION,PATCH_LEVEL
from fnd_product_installations a,
fnd_application b , fnd_application_tl c
where a.APPLICATION_ID = b.APPLICATION_ID
and b.APPLICATION_ID = c.APPLICATION_ID
order by 1 ;



Checking Financial related modules
------------------------------------

select application_name , APPLICATION_SHORT_NAME,PRODUCT_VERSION,PATCH_LEVEL
from fnd_product_installations a,
fnd_application b , fnd_application_tl c
where a.APPLICATION_ID = b.APPLICATION_ID
and b.APPLICATION_ID = c.APPLICATION_ID
and application_name like '%Financial%'
order by 1 ;




################################################################




New patches applied in last 90 days alongwith detailed driver information
==========================================================================

select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.DRIVER_FILE_NAME,
B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATION_DATE, B.FILE_SIZE,
B.MERGED_DRIVER_FLAG, B.MERGE_DATE
from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B
where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID
and B.creation_date >= ( sysdate -90 )
order by b.creation_date



Find the Product, its status (installed/shared/Not installed) and patchset level
=================================================================================

set pages 1000 lines 140
col Product for a58
col Prod_Status for a20
col Prod_Id for a8
col Patch_Level for a15

SELECT V.APPLICATION_NAME Product,
to_char(V.APPLICATION_ID) Prod_Id,
L.MEANING Prod_Status,
DECODE(I.PATCH_LEVEL, NULL, '11i.' || v.APPLICATION_SHORT_NAME || '.?', I.PATCH_LEVEL) Patch_Level
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status )
ORDER BY 1;







select aap.patch_name, aat.name, apr.end_date
from apps.ad_applied_patches aap,
apps.ad_patch_drivers apd,
apps.ad_patch_runs apr,
apps.ad_appl_tops aat
where aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and aat.appl_top_id = apr.appl_top_id
and aap.patch_name = '1301043';


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

RUP (Release Update Patch) is synonymous to DELTA
===================================================

11.5.10.2 RUP5 means 11.5.10.2 Delta 5


SQL> select BUG_NUMBER from applsys.ad_bugs where BUG_NUMBER='4334965'; -- RUP3


SQL> select BUG_NUMBER from applsys.ad_bugs where BUG_NUMBER='4676589'; -- RUP4


SQL> select BUG_NUMBER from applsys.ad_bugs where BUG_NUMBER='5473858'; -- RUP5

BUG_NUMBER

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

5473858


SQL> select BUG_NUMBER from applsys.ad_bugs where BUG_NUMBER='5903765'; -- RUP6



SQL> select BUG_NUMBER from applsys.ad_bugs where BUG_NUMBER='6241631'; -- RUP7





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



SPOOL /tmp/fnd_nodes.txt


set pagesize 50
col node_name format a15
col server_id format a8
col server_address format a15
col platform_code format a4
col webhost format a12
col domain format a20
col virtual_ip format a12
set linesize 132
select
node_id,
platform_code,
support_db D,
support_cp C,
support_admin A,
support_forms F,
support_web W,
node_name,
server_id,
server_address,
domain,
webhost,
virtual_ip
from
fnd_nodes
order by node_id;

SPOOL OFF




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


Cm hold
--------


update applsys.FND_CONCURRENT_REQUESTS set hold_FLAG='Y'
where request_id in
(SELECT R.REQUEST_ID
FROM
applsys.FND_CONCURRENT_PROGRAMS_TL PT,
applsys.FND_CONCURRENT_PROGRAMS PB,
applsys.FND_USER U,
applsys.FND_PRINTER_STYLES_TL S,
applsys.FND_CONCURRENT_REQUESTS R
WHERE
PB.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
AND PB.APPLICATION_ID = PT.APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
AND PT.LANGUAGE = USERENV('LANG')
AND U.USER_ID = R.REQUESTED_BY
AND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLE
AND S.LANGUAGE(+) = USERENV('LANG')
and (phase_code = 'P' or phase_code = 'R')
and PB.CONCURRENT_PROGRAM_NAME <> 'FNDGSCST')
/



cm release
------------

update applsys.FND_CONCURRENT_REQUESTS set hold_FLAG='N'
where request_id in
(SELECT R.REQUEST_ID
FROM
applsys.FND_CONCURRENT_PROGRAMS_TL PT,
applsys.FND_CONCURRENT_PROGRAMS PB,
applsys.FND_USER U,
applsys.FND_PRINTER_STYLES_TL S,
applsys.FND_CONCURRENT_REQUESTS R
WHERE
PB.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
AND PB.APPLICATION_ID = PT.APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
AND PT.LANGUAGE = USERENV('LANG')
AND U.USER_ID = R.REQUESTED_BY
AND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLE
AND S.LANGUAGE(+) = USERENV('LANG')
and (phase_code = 'P' or phase_code = 'R')
and PB.CONCURRENT_PROGRAM_NAME <> 'FNDGSCST')
/




Tier details:
==============

select node_name, support_cp as "CMNODE" , support_forms as " Forms" , support_web as "WEB" , support_admin as "ADMIN", status from apps.fnd_nodes ;










HOW TO determine which manager ran a specific concurrent request
=================================================================


SELECT
b.user_concurrent_queue_name
FROM
fnd_concurrent_processes a
,fnd_concurrent_queues_vl b
,fnd_concurrent_requests c
WHERE 1=1
AND a.concurrent_queue_id = b.concurrent_queue_id
AND a.concurrent_process_id = c.controlling_manager
AND c.request_id = &request_id
/





To find child requests
=========================


col PROGRAM format a20 HEADING "Program"
col PROGRAM_SHORT_NAME format a8 HEADING "Sh_Name"
col REQUEST_ID format 99999999999
col PARENT_REQUEST_ID format 9999999999
col REQUESTOR format a10
col PHASE_CODE format a7 HEADING "Phase"
col STATUS_CODE format a7 HEADING "Status"
col ACTUAL_START_DATE format a15 HEADING "Start_Date"
col ACTUAL_COMPLETION_DATE format a15 HEADING "Complete_Date"
col ACTUAL_COMPLETION format a10 Heading "Duration"
col Sys_Date format a15
SELECT LPAD(' ',fcrl.lvl - 1) || fcrsv.PROGRAM Program,
fcrsv.PROGRAM_SHORT_NAME Short_Name,
fcrsv.REQUEST_ID req_id,
fcrsv.PARENT_REQUEST_ID Parent_id,
DECODE (fcrsv.PHASE_CODE,'C','Complete','R','Running','P','Pending','Others') Phase,
DECODE (fcrsv.STATUS_CODE,'C','Normal','E','* ERROR *','X','* TERMINATED *','R','Running','G','WARNING','Q','Scheduled','Other')StatuS
FROM apps.FND_CONC_REQ_SUMMARY_V fcrsv,
(SELECT LEVEL lvl, request_id req_id
FROM apps.fnd_concurrent_requests
CONNECT BY PRIOR request_id = parent_request_id
START WITH request_id = &REQ_ID ) fcrl
WHERE fcrsv.request_id = fcrl.req_id
ORDER BY
fcrsv.request_id,
fcrl.lvl,
fcrsv.actual_start_date
/





Cancelled requests
===================











select a.request_id
, c.user_concurrent_program_name
, c.description ucp_description
, decode(a.phase_code
, 'C', 'Completed'
, 'I', 'Inactive'
, 'P', 'Pending'
, 'R', 'Running'
, a.phase_code) phase_code
, decode(a.status_code
, 'C', 'Normal'
, 'D', 'Cancelled'
, 'E', 'Error'
, 'G', 'Warning'
, 'X', 'Terminated'
, a.status_code) status_code
, a.hold_flag
, a.request_date
, a.requested_start_date
, a.actual_start_date
, a.actual_completion_date
, a.argument_text
, a.parent_request_id
, round((a.actual_completion_date - a.actual_start_date)*24*60*60, 2) runtime_seconds
, a.oracle_process_id db_os_process_id
, a.oracle_session_id
, a.os_process_id ap_os_process_id
from apps.fnd_concurrent_requests a
, apps.fnd_concurrent_programs b
, apps.fnd_concurrent_programs_tl c
where a.program_application_id = b.application_id
and a.concurrent_program_id = b.concurrent_program_id
and b.application_id = c.application_id
and b.concurrent_program_id = c.concurrent_program_id
and a.status_code = 'D'
and a.request_date > sysdate-17
and c.user_concurrent_program_name like 'Workflow Background Process%'
order by request_date;






"Workflow Background Process" conc. prog requests that needs to be put on hold
=================================================================================



select a.request_id
, c.user_concurrent_program_name
, c.description ucp_description
, decode(a.phase_code
, 'C', 'Completed'
, 'I', 'Inactive'
, 'P', 'Pending'
, 'R', 'Running'
, a.phase_code) phase_code
, decode(a.status_code
, 'C', 'Normal'
, 'D', 'Cancelled'
, 'E', 'Error'
, 'G', 'Warning'
, 'X', 'Terminated'
, a.status_code) status_code
, a.hold_flag
, a.request_date
, a.requested_start_date
, a.actual_start_date
, a.actual_completion_date
, a.argument_text
, a.parent_request_id
, round((a.actual_completion_date - a.actual_start_date)*24*60*60, 2) runtime_seconds
, a.oracle_process_id db_os_process_id
, a.oracle_session_id
, a.os_process_id ap_os_process_id
from apps.fnd_concurrent_requests a
, apps.fnd_concurrent_programs b
, apps.fnd_concurrent_programs_tl c
where a.program_application_id = b.application_id
and a.concurrent_program_id = b.concurrent_program_id
and b.application_id = c.application_id
and b.concurrent_program_id = c.concurrent_program_id
and (phase_code = 'P' or phase_code = 'R')
and a.request_date > sysdate-7
and c.user_concurrent_program_name like '%Workflow Background Process%'
order by request_date;





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



create table fnd_conc_requests_bak_030210 as select * from fnd_concurrent_requests
where request_id in
(
select a.request_id
from apps.fnd_concurrent_requests a
, apps.fnd_concurrent_programs b
, apps.fnd_concurrent_programs_tl c
where a.program_application_id = b.application_id
and a.concurrent_program_id = b.concurrent_program_id
and b.application_id = c.application_id
and b.concurrent_program_id = c.concurrent_program_id
and (phase_code = 'P' or phase_code = 'R')
and a.request_date > sysdate-7
and c.user_concurrent_program_name like '%Workflow Background Process%'
);





SQL> create table fnd_conc_requests_bak_030210 as select * from fnd_concurrent_requests
2 where request_id in
(
3 select a.request_id
4 5 from apps.fnd_concurrent_requests a
6 , apps.fnd_concurrent_programs b
7 , apps.fnd_concurrent_programs_tl c
8 where a.program_application_id = b.application_id
9 and a.concurrent_program_id = b.concurrent_program_id
10 and b.application_id = c.application_id
11 and b.concurrent_program_id = c.concurrent_program_id
and (phase_code = 'P' or phase_code = 'R')
and a.request_date > sysdate-7
and c.user_concurrent_program_name like '%Workflow Background Process%'
); 12 13 14 15

Table created.

SQL> selet count(*) from fnd_conc_requests_bak_030210;
SP2-0734: unknown command beginning "selet coun..." - rest of line ignored.
SQL> select count(*) from fnd_conc_requests_bak_030210;

COUNT(*)
----------
59



update fnd_concurrent_requests set hold_flag='Y' where request_id in
(
select a.request_id
from apps.fnd_concurrent_requests a
, apps.fnd_concurrent_programs b
, apps.fnd_concurrent_programs_tl c
where a.program_application_id = b.application_id
and a.concurrent_program_id = b.concurrent_program_id
and b.application_id = c.application_id
and b.concurrent_program_id = c.concurrent_program_id
and (phase_code = 'P' or phase_code = 'R')
and a.request_date > sysdate-7
and c.user_concurrent_program_name like '%Workflow Background Process%'
);



SQL> update fnd_concurrent_requests set hold_flag='Y' where request_id in
2 (
3 select a.request_id
4 from apps.fnd_concurrent_requests a
5 , apps.fnd_concurrent_programs b
6 , apps.fnd_concurrent_programs_tl c
7 where a.program_application_id = b.application_id
8 and a.concurrent_program_id = b.concurrent_program_id
9 and b.application_id = c.application_id
10 and b.concurrent_program_id = c.concurrent_program_id
11 and (phase_code = 'P' or phase_code = 'R')
12 and a.request_date > sysdate-7
13 and c.user_concurrent_program_name like '%Workflow Background Process%'
14 );

59 rows updated.

SQL> commit;

Commit complete.






Cm hold
--------


update applsys.FND_CONCURRENT_REQUESTS set hold_FLAG='Y'
where request_id in
(SELECT R.REQUEST_ID
FROM
applsys.FND_CONCURRENT_PROGRAMS_TL PT,
applsys.FND_CONCURRENT_PROGRAMS PB,
applsys.FND_USER U,
applsys.FND_PRINTER_STYLES_TL S,
applsys.FND_CONCURRENT_REQUESTS R
WHERE
PB.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
AND PB.APPLICATION_ID = PT.APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
AND PT.LANGUAGE = USERENV('LANG')
AND U.USER_ID = R.REQUESTED_BY
AND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLE
AND S.LANGUAGE(+) = USERENV('LANG')
and (phase_code = 'P' or phase_code = 'R')
and PB.CONCURRENT_PROGRAM_NAME <> 'FNDGSCST')
/






cm release
------------

update applsys.FND_CONCURRENT_REQUESTS set hold_FLAG='N'
where request_id in
(SELECT R.REQUEST_ID
FROM
applsys.FND_CONCURRENT_PROGRAMS_TL PT,
applsys.FND_CONCURRENT_PROGRAMS PB,
applsys.FND_USER U,
applsys.FND_PRINTER_STYLES_TL S,
applsys.FND_CONCURRENT_REQUESTS R
WHERE
PB.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
AND PB.APPLICATION_ID = PT.APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
AND PT.LANGUAGE = USERENV('LANG')
AND U.USER_ID = R.REQUESTED_BY
AND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLE
AND S.LANGUAGE(+) = USERENV('LANG')
and (phase_code = 'P' or phase_code = 'R')
and PB.CONCURRENT_PROGRAM_NAME <> 'FNDGSCST')
/




Hold the jobs submitted by sYSADMIN
===================================


update applsys.FND_CONCURRENT_REQUESTS set hold_FLAG='Y'
where request_id in
(SELECT R.REQUEST_ID
FROM
applsys.FND_CONCURRENT_PROGRAMS_TL PT,
applsys.FND_CONCURRENT_PROGRAMS PB,
applsys.FND_USER U,
applsys.FND_PRINTER_STYLES_TL S,
applsys.FND_CONCURRENT_REQUESTS R
WHERE
PB.APPLICATION_ID = R.PROGRAM_APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
AND PB.APPLICATION_ID = PT.APPLICATION_ID
AND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
AND PT.LANGUAGE = USERENV('LANG')
AND U.USER_ID = R.REQUESTED_BY
AND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLE
AND S.LANGUAGE(+) = USERENV('LANG')
and (phase_code = 'P' or phase_code = 'R')
--and PB.CONCURRENT_PROGRAM_NAME <> 'FNDGSCST') --- FNDGSCST is the Gather Schema Stats (GSC) job
and R.REQUESTED_BY='SYSADMIN'
/



Runtime of the Gather Schema job
==================================

column USER_CONCURRENT_PROGRAM_NAME format a35
column ARGUMENT1 format a8

select distinct ARGUMENT1 ,request_id,USER_CONCURRENT_PROGRAM_NAME,
to_char(((ACTUAL_COMPLETION_DATE -actual_start_date )*24*60),'99999999.99') "time Taken"
from FND_CONC_REQUESTS_FORM_V , fnd_user u
where phase_code = 'C'
and status_code = 'C'
and requested_by = u.user_id
and USER_CONCURRENT_PROGRAM_NAME like '%Gather Schema Stat%'
and actual_start_date like '%18-SEP%';




IDENTIFY CONCURRENT REQUEST FILE
==================================


PROMPT Use the following query to identify the correct trace file:
PROMPT where "request" is the concurrent request id for the inventory transaction
PROMPT worker.


SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;



Primary/Standby in Sync
========================

On the standby database, query the gv$archived_log view to verify the logs are received and applied.

select sequence#, applied,
to_char(first_time, ‘mm/dd/yy hh24:mi:ss’) first,
to_char(next_time, ‘mm/dd/yy hh24:mi:ss’) next,
to_char(completion_time, ‘mm/dd/yy hh24:mi:ss’) completion
from gv$archived_log order by first_time;





RMAN Stored Scripts (in Recovery Catalog)
=========================================

Connect to RMAN catalog database..


SELECT script_name FROM rc_stored_script;


select LINE,TEXT from RC_STORED_SCRIPT_LINE where SCRIPT_NAME='backup_inc0_p03erp';











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



select WORKER_ID , CONTROL_CODE , STATUS , FILENAME from fnd_install_processes
order by worker_id;




WORKER_ID C S FILENAME
---------- - - ------------------------------------------------------------
0 W W UNDEF
1 Q R adobjcmp.sql
2 R W cebtapib.pls
3 R W cejecrnb.pls
4 R W cepmtcob.pls
5 R W cexlaevb.pls
6 R W fem_data_loader.plb
7 R W fem_signage_utl.plb
8 R W fem_ud_eng.plb
9 R W jai_ap_utils.plb
10 R W jai_cmn_rgm_stl.plb

WORKER_ID C S FILENAME
---------- - - ------------------------------------------------------------
11 R W jai_cmn_hook.plb
12 R W jai_cmn_rcv_mach.plb
13 R W jai_general.plb
14 R W jai_cmn_rg_23p1.plb
15 R W jai_rcv_rnd.plb
16 R W jai_po_hook_pkg.plb



set line 200
col SUBDIRECTORY format a20
col filename format a20
col command format a10
alter session set nls_date_format ='dd/mm/yyy hh24:mi:ss' ;
select WORKER_ID , CONTROL_CODE , STATUS, command, FILENAME ,
PHASE , START_TIME , ELAPSED_TIME from fnd_install_processes ;

WORKER_ID C S COMMAND FILENAME PHASE START_TIME ELAPSED_TIME
---------- - - ---------- -------------------- ---------- ------------------ ------------
13 R W sqlplus jai_general.plb 75 02/09/010 18:17:54 .000127315
14 R W sqlplus jai_cmn_rg_23p1.plb 75 02/09/010 18:17:51 .000092593
15 R W sqlplus jai_rcv_rnd.plb 75 02/09/010 18:17:55 .000289352
16 R W sqlplus jai_po_hook_pkg.plb 75 02/09/010 18:17:54 .000381944
4 R W package cepmtcob.pls 78 02/09/010 18:18:33 .000034722
11 R W sqlplus jai_cmn_hook.plb 75 02/09/010 18:17:53 .00005787
6 R W package fem_data_loader.plb 78 02/09/010 18:18:33 .000023148
5 R W package cexlaevb.pls 78 02/09/010 18:18:33 .000034722
8 R W package fem_ud_eng.plb 78 02/09/010 18:18:33 .000034722
2 R W package cebtapib.pls 78 02/09/010 18:18:33 0
0 W W UNDEF UNDEF 0

WORKER_ID C S COMMAND FILENAME PHASE START_TIME ELAPSED_TIME
---------- - - ---------- -------------------- ---------- ------------------ ------------
1 Q R sqlplus_si adobjcmp.sql 79 02/09/010 18:18:36 0
ngle

3 R W package cejecrnb.pls 78 02/09/010 18:18:33 .000011574
7 R W package fem_signage_utl.plb 78 02/09/010 18:18:33 .000034722
9 R W sqlplus jai_ap_utils.plb 75 02/09/010 18:17:43 .000300926
10 R W sqlplus jai_cmn_rgm_stl.plb 75 02/09/010 18:17:50 .00025463
12 R W sqlplus jai_cmn_rcv_mach.plb 75 02/09/010 18:17:43 .000497685

17 rows sel

ps -ef | grep appptl2 | grep -v grep | grep adwork | awk '{print $2}' | xargs kill -9
ps -ef | grep appptl2 | grep -v grep | grep defunct | awk '{print $2}' | xargs kill -9




update fnd_install_processes
set status = 'W' , control_code ='W'
where worker_id in ( 1 ) ;



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

McDonalds
----------

$ORACLE_HOME/appsutil/context/db/*.xml

s_db_files


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




JVM Details
=============


OPP Parameter size
--------------------

select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');


update FND_CP_SERVICES set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');



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



Validate EBS Login
---------------------


SQL> select fnd_web_sec.validate_login('SYSADMIN','pscprodsys') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','PSCPRODSYS')
--------------------------------------------------------------------------------
N




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



z03th41b:/opt/ort03erp/oraInventory/ContentsXML> grep "HOME NAME" inventory.xml













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


cmstatus.sql
-------------


select decode(CONCURRENT_QUEUE_NAME,'FNDICM','Internal Manager','FNDCRM','Conflict Resolution Manager','AMSDMIN','Marketing Data Mining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormula Transaction Manager','FNDCPOPP','Output Post Processor','FNDSCH','Scheduler/Prereleaser Manager','FNDSM_AQHERP','Service Manager: AQHERP','FTE_TXN_MANAGER','Transportation Manager','IEU_SH_CS','Session History Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashed session','INVMGR','Inventory Manager','INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR','OAM Metrics Collection Manager','PASMGR','PA Streamline Manager','PODAMGR','PO Document Approval Manager','RCVOLTM','Receiving Transaction Manager','STANDARD','Standard Manager','WFALSNRSVC','Workflow Agent Listener Service','WFMLRSVC','Workflow Mailer Service','WFWSSVC','Workflow Document Web Services Service','WMSTAMGR','WMS Task Archiving Manager','XDP_APPL_SVC','SFM Application Monitoring Service','XDP_CTRL_SVC','SFM Controller Service','XDP_Q_EVENT_SVC','SFM Event Manager Queue Service','XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service','XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service','XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service','XDP_Q_ORDER_SVC','SFM Order Queue Service','XDP_Q_TIMER_SVC','SFM Timer Queue Service','XDP_Q_WI_SVC','SFM Work Item Queue Service','XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name", max_processes as "TARGET Processes", running_processes as "ACTUAL Processes" from apps.fnd_concurrent_queues where CONCURRENT_QUEUE_NAME in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC');