appsdba_ very important queries in day to day apps dba life.pdf

Wednesday, February 8, 2012

selects.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_etfrom v$session s,v$process p where s.paddr=p.addr and p.spid=&1;

selects.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_etfrom v$session s,v$process p where s.paddr=p.addr and s.sid=&1;

selects.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_etfrom v$session s,v$process p where s.paddr=p.addr and s.process='&1';

selects.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_etfrom v$session s,v$process p where s.paddr=p.addr and s.action like'Concurrent Program';

selects.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_etfrom 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/54000mins_running,q.sql_textfrom v$session s join v$sqltext_with_newlines qon s.sql_address = q.addresswhere status='ACTIVE'and type 'BACKGROUND'and last_call_et> 54000order by sid,serial#,q.piece

module ~ conc. prog. short name

Followers

with Google Friend Connect

Members (11)

Blog Archive 2014 (1) 2013 (1) 2012 (3) September (1) April (1) February (1)

Very important queries in day to dayapps dba life...

2011 (38) 2010 (44) 2009 (3)

Plus Blog suivant Crer un blog Connexion

AppsDBA: Very important queries in day to day apps dba life http://appsdba-sai.blogspot.com/2012/02/very-important-queries-in-da...

1 sur 53 06/09/2014 08:52

col MODULE for a15col PROGRAM for a15col username for a16col schemaname for a16col osuser for a12

selects.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_etfrom 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 > 1order 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$sessionwhere username='ABC';

select do.object_name,do.owner,lo.object_id,lo.oracle_username "LockingDbuser",lo.session_id,lo.process from dba_objects do, v$locked_object lowherelo.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 wherest.hash_value=s.sql_hash_value and s.sid=22 order by piece ;

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

select a.inst_id, a.sid --mL#461480.1, a.username


2 sur 53 06/09/2014 08:52

, 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_mbytesfrom gv$session a, gv$transaction b, gv$parameter cwhere a.saddr = b.ses_addrand b.inst_id = c.inst_idand 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_serialfrom v$lock a, v$lock bwhere a.block = 1and b.request > 0and a.id1 = b.id1and 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$ oWHERE lo.OBJECT_ID = o.OBJ#AND lo.SESSION_ID = s.SIDORDER BY lo.ORACLE_USERNAME, o.NAME;


3 sur 53 06/09/2014 08:52

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 fromapps.fnd_concurrent_requestswhere REQUEST_ID in (22890825,22907089);

selects.sid,s.serial#,s.username,s.osuser,s.program,s.module,s.status,p.spid,s.logon_time,s.last_call_etfrom 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) svrprocFROM apps.fnd_concurrent_requests req,apps.fnd_concurrent_processes proc,apps.fnd_lookups look,apps.fnd_lookups look1,v$process vproc,v$session vsessWHERE req.controlling_manager = proc.concurrent_process_id(+)AND req.status_code = look.lookup_codeAND look.lookup_type = 'CP_STATUS_CODE'AND req.phase_code = look1.lookup_codeAND 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 fromv$locked_object where OBJECT_ID=XXXXXX

select sql_text from v$sqltext st,v$session s wherest.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


4 sur 53 06/09/2014 08:52

V$ROLLNAME rn, v$rollstat rs where rs.usn=rn.usn;

selectSEGMENT_NAME,TABLESPACE_NAME,INITIAL_EXTENT,MIN_EXTENTS,MAX_EXTENTS,sum(bytes/1024/1024) from dba_segments whereSEGMENT_TYPE = 'ROLLBACK' group bySEGMENT_NAME,TABLESPACE_NAME,INITIAL_EXTENT,MAX_EXTENTS,MIN_EXTENTS;

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

select OBJECT_ID,SESSION_ID,ORACLE_USERNAME,PROCESS fromv$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_objectswhere owner='CUOWN' andto_date(last_ddl_time,'DD-MON-YYYY HH24:MI:SS')>'20-MAY-2007 10:00:00';

# hostnamecidcsmbiwprd01#

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


5 sur 53 06/09/2014 08:52

/CIDC_CG200G_003/cdserv /cdserv

select * from v$sess_io;

select * from v$waitstat where count > 0;v$sesstatv$sysstatv$session_eventv$system_eventv$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.namecpu,f.value cpuvaluefrom 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#) gwhere b.sid(+) = a.sidand c.sid(+) = a.sidand d.addr=a.paddrand e.sid=a.sidand e.statistic#=8and f.sid=a.sidand f.statistic#=177and g.sid=a.sidand g.statistic#=12and event not in ('rdbms ipc reply', 'rdbms ipc message', 'SQL*Net messagefrom client',


6 sur 53 06/09/2014 08:52

'SQL*Net message to client', 'SQL*Net more data to client', 'SQL*Netmessage to dblink','SQL*Net message from dblink','SQL*Net more data from dblink','SQL*Netmore 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.programPROGRAM,b.terminal TERMINAL,c.sql_text SQL_TEXTfrom v$process a, v$session b,v$sqlarea cwhere 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) GBfrom v$datafilegroup 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 wherecreation_time > SYSDATE-365 group by to_char(creation_time, 'RRRRMonth');

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-ddhh24:mi:ss'))*24*60)) rows_per_minfrom sys.v_$sqlareawhere sql_text like 'INSERT %INTO "%'and command_type = 2and open_versions > 0;


7 sur 53 06/09/2014 08:52

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

select a.sid, a.username, b.name, c.valuefrom v$session a, v$statname b, v$sesstat cwhere a.sid=c.sidand 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_textFROM v$sqlarea a, v$session sWHERE a.address = s.sql_addressAND a.hash_value = s.sql_hash_valueAND s.sid = 66AND s.serial# = 1292/

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

select a.sid, a.username, s.sql_textfrom v$session a, v$sqltext swhere a.sql_address = s.addressand a.sql_hash_value = s.hash_valueand 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_textfrom dba_users a


8 sur 53 06/09/2014 08:52

,v$sql bwhere b.buffer_gets/decode(b.rows_processed,0,1,b.rows_processed) >1000000and a.user_id = b.parsing_user_idorder 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.valuereccpuvalue,f.name cpu,f.value cpuvaluefrom 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#) gwhere b.sid(+) = a.sidand c.sid(+) = a.sidand d.addr=a.paddrand e.sid=a.sidand e.statistic#=8and f.sid=a.sidand f.statistic#=177and g.sid=a.sidand g.statistic#=12and event not in ('rdbms ipc reply', 'rdbms ipc message', 'SQL*Net messagefrom client','SQL*Net message to client', 'SQL*Net more data to client', 'SQL*Netmessage to dblink','SQL*Net message from dblink','SQL*Net more data from dblink','SQL*Netmore 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,blocksfrom dba_extentswhere file_id = 204and 77626 between block_id + 1 and block_id + blocks ;

FILE_ID BLOCK_ID EXTENT_ID SEGMENT_NAME


9 sur 53 06/09/2014 08:52

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 whered.file#=f.file# order by f.phywrts;

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

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

Some more rollback segments needs to be added if the number of waits foranyof the rollback segment blocks or headers exceeds more than one percentof thetotal number of requests.

select c.name,a.max_extents,b.extents extentsallocated,b.rssizerollbacksegsize,b.curext extentready from dba_rollback_segs a,v$rollstatb,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 ('dbblock gets', 'consistent gets');

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

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

\\mdcxsdced12\netlogon\oracle

\\KPITSDCED11\netlogon\oracle

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


10 sur 53 06/09/2014 08:52

select file_id ,sum(bytes/1024/1024) MB from dba_free_space wherefile_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_fileswhere file_id in (3,67,101) group by file_name,file_id;

select a.file_id,file_name,sum(a.bytes/1024/1024) MB fromdba_free_space a, dba_data_files b where a.file_id=b.file_id group bya.file_id,file_name

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

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

SELECTA.TABLE_NAME table_name,A.CONSTRAINT_NAME key_name,B.TABLE_NAME referencing_table,B.CONSTRAINT_NAME foreign_key_name,B.STATUS fk_statusFROM DBA_CONSTRAINTS A, DBA_CONSTRAINTS BWHEREA.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME andB.CONSTRAINT_TYPE = 'R' and A.TABLE_NAME='xxxxx'AND A.OWNER='XXXXX'ORDER BY 1, 2, 3, 4;

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

col owner for a12

selectowner,constraint_name,constraint_type,table_name,r_owner,r_constraint_namefrom dba_constraintswhere constraint_type='R'and r_constraint_name in (select constraint_name from dba_constraintswhere constraint_type in ('P','U') andtable_name='DSCSM_CUST_CONTACT');

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

SQL> CREATE UNIQUE INDEX DSCSM.DSCSM_CUST_CONTACT_IND1 ON


11 sur 53 06/09/2014 08:52

DSCSM.DSCSM_CUST_CONTACT2 (DIST_ID, CONTACT_ID)3 LOGGING4 TABLESPACE DSCSM_LM4M_IDXPCTFREE 105 6 INITRANS 27 MAXTRANS 2558 STORAGE (9 INITIAL 4M10 NEXT 512KMINEXTENTS 111 12 MAXEXTENTS 214748364513 PCTINCREASE 014 FREELISTS 115 FREELIST GROUPS 116 BUFFER_POOL DEFAULT17 )18 NOPARALLEL;CREATE UNIQUE INDEX DSCSM.DSCSM_CUST_CONTACT_IND1 ONDSCSM.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) fromDSCSM.DSCSM_CUST_CONTACT group by DIST_ID,CONTACT_ID havingcount(*) > 1);

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

delete from DSCSM.DSCSM_CUST_CONTACT where rowid not in ( selectmin(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))+


12 sur 53 06/09/2014 08:52

(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 cangreatlyenhance throughput. Redo log space requests to the number of redoentriesshould not be greater than 1 in 5000.

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

SORTS======

The ratio of sorts (disk) to sorts (memory) should be < 5%. Increasethe 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 diskwhere 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') || '%') usageFROM(SELECTMAX(s.value) usedFROMv$statname n,v$sesstat sWHEREn.name = 'session cursor cache count' ands.statistic# = n.statistic#),


13 sur 53 06/09/2014 08:52

(SELECTvalueFROMv$parameterWHEREname = 'session_cached_cursors')UNION ALLSELECT'open_cursors',LPAD(value, 5),to_char(100 * used / value, '990') || '%'FROM(SELECTMAX(sum(s.value)) usedFROMv$statname n,v$sesstat sWHEREn.name in ('opened cursors current', 'session cursor cache count') ands.statistic# = n.statistic#GROUP BYs.sid),(SELECTvalueFROMv$parameterWHEREname = 'open_cursors')/

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

SELECT b.SID, UPPER(a.NAME), b.VALUEFROM v$statname a, v$sesstat b, v$session cWHERE a.statistic# = b.statistic#AND c.SID = b.SIDAND LOWER(a.NAME) LIKE '%' || LOWER('CURSOR')||'%'AND b.SID=20UNIONSELECT SID, 'v$open_cursor opened cursor', COUNT(*)FROM v$open_cursorWHERE SID=20GROUP BY SIDORDER BY SID/

Soft_Hard_Parses and Cursor Cache Hit Ratio


14 sur 53 06/09/2014 08:52

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

SELECTTO_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_parsesFROM(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 cachehits' )/

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

SELECT S.username USER_NAME,S.sid SID,S.serial# SERIAL, S.osuserOS_USER, P.spid UNIX_PID,P.PROGRAM,SUM (T.blocks) * TBS.block_size / 1024 / 1024 TEMP_USED_MB,T.TABLESPACE,COUNT(*) STATEMENTSFROM v$sort_usage T, v$session S, (select value block_size fromv$parameter where name = 'db_block_size') TBS, v$process PWHERE T.session_addr = S.saddrAND S.paddr = P.addrGROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid,S.module,P.program, TBS.block_size, T.tablespacehaving (SUM (T.blocks) * TBS.block_size / 1024 / 1024) > 20ORDER BY TEMP_USED_MB desc;

Temp segment usage per session.

col USERNAME for a12col OSUSER for a10col TABLESPACE for a12col 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(*) statementsFROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process PWHERE T.session_addr = S.saddrAND S.paddr = P.addrAND T.tablespace = TBS.tablespace_nameGROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,P.program, TBS.block_size, T.tablespaceORDER 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_freeFROM v$sort_segment A,


15 sur 53 06/09/2014 08:52

(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_totalFROM v$tablespace B, v$tempfile CWHERE B.ts#= C.ts#GROUP BY B.name, C.block_size) DWHERE A.tablespace_name = D.nameGROUP by A.tablespace_name, D.mb_total;

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

select tablespace_name, sum(bytes)/1024/1024 mbfrom dba_temp_filesgroup by tablespace_name;

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

select tablespace_name, sum(bytes_cached)/1024/1024 mbfrom v$temp_extent_poolgroup by tablespace_name;

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

select ss.tablespace_name,sum((ss.used_blocks*ts.blocksize))/1024/1024mbfrom gv$sort_segment ss, sys.ts$ tswhere ss.tablespace_name = ts.namegroup 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.STATUSfrom v$session s, v$transaction t, v$rollname r, v$process p,(select valueblock_size from v$parameter where name = 'db_block_size') TBSwhere s.taddr=t.addr(+)and t.xidusn=r.usnand p.addr=s.paddrand (t.used_ublk * TBS.block_size)/1024/1024 > 100order by 10 desc;

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


16 sur 53 06/09/2014 08:52

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

The query you can use is:SELECT s.sid, s.serial#, s.username, s.program,i.block_changesFROM v$session s, v$sess_io iWHERE s.sid = i.sidORDER BY 5 desc;

Run the query multiple times and examine the delta between eachoccurrenceof BLOCK_CHANGES. Large deltas indicate high redo generation by thesession.

2) Query V$TRANSACTION. This view contains information about theamount ofundo blocks and undo records accessed by the transaction (as found in theUSED_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_urecFROM v$session s, v$transaction tWHERE s.taddr = t.addrORDER 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 rWHERE 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 DJWHERE DRC.NAME = DS.NAME AND DJ.BROKEN='N' AND DRC.JOB = DJ.JOBAND 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)/1024high_kb,optimal_executions, onepass_executions, multipasses_executionsFROM v$sql_workarea_histogramWHERE total_executions != 0;


17 sur 53 06/09/2014 08:52

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

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

select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name,max(vsst.value) valuefrom v$sesstat vsst, v$statname vstt, v$session vseswhere vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.namein('session pga memory','session pga memory max','session ugamemory','session uga memory max','session cursor cache count','session cursor cache hits','session storedprocedure space','opened cursors current','opened cursors cumulative') and vses.username isnot nullgroup by vses.username, vsst.sid, vses.serial#, vstt.name order byvses.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 %" FROMsys.dba_tablespaces d,(select tablespace_name, sum(bytes) bytes from dba_data_files group bytablespace_name) a,(select tablespace_name, sum(bytes) bytes from dba_free_space group bytablespace_name) fWHERE d.tablespace_name = a.tablespace_name(+) ANDd.tablespace_name = f.tablespace_name(+)AND NOT (d.extent_management like 'LOCAL' AND d.contents like'TEMPORARY')UNION ALLSELECTd.status "Status", d.tablespace_name "Name", d.contents "Type",d.extent_management "Extent Management",


18 sur 53 06/09/2014 08:52

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 bytablespace_name) a,(select tablespace_name, sum(bytes_cached) bytes fromv$temp_extent_pool group by tablespace_name) tWHERE d.tablespace_name = a.tablespace_name(+) ANDd.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 bytablespace_name) a,(select tablespace_name, sum(bytes) bytes from dba_free_space group bytablespace_name) fWHERE d.tablespace_name = a.tablespace_name(+) ANDd.tablespace_name = f.tablespace_name(+)AND NOT (d.extent_management like 'LOCAL' AND d.contents like'TEMPORARY')UNION ALLSELECTd.status "Status", d.tablespace_name "Name", d.contents "Type",d.extent_management "ExtentManagement",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 bytablespace_name) a,(select tablespace_name, sum(bytes_cached) bytes fromv$temp_extent_pool group by tablespace_name) tWHERE d.tablespace_name = a.tablespace_name(+) ANDd.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) "OSUser",substr(vsn.name,1,15) "Operation", sum(vss.value) "Value" from v$sessionvs, v$sesstat vss,v$statname vsnwhere (vss.statistic#= vsn.statistic#) and


19 sur 53 06/09/2014 08:52

(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 findingthe buffer busy wait.===============================================================================================

select * from (select DECODE(GROUPING(a.object_name),1,'AllObjects',a.object_name) AS "Object ",sum(casewhen a.statistic_name = 'ITL waits' thena.valueelsenullend) "ITL Waits",sum(casewhen a.statistic_name = 'buffer busy waits' thena.valueelsenullend) "Buffer Busy Waits",sum(casewhen a.statistic_name = 'row lock waits' thena.valueelsenullend) "Row Lock Waits",sum(casewhen a.statistic_name = 'physical reads' thena.valueelsenullend) "Physical Reads",sum(casewhen a.statistic_name = 'logical reads' thena.valueelsenullend) "Logical Reads" from v$segment_statistics a where a.owner likeupper('&owner')group by rollup(a.object_name)) b where (b."ITL Waits" > 0 or b."BufferBusy Waits" > 0) ;

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

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


20 sur 53 06/09/2014 08:52

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(*) FROMDBA_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 valueAS UR FROM v$parameter WHERE name = 'undo_retention'),(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPSFROM 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 fromv$undostat;

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

selecthostname,os_user_name,database_user_name,ddl_date,ddl_type,object_type,object_owner,object_name,ticket_no,remarks fromsox_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;


21 sur 53 06/09/2014 08:52

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_jobswhere 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_DETAILSwhere 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 ) savingsfrom dba_data_files a,( select file_id, max(block_id+blocks-1) hwmfrom dba_extentsgroup by file_id ) bwhere a.file_id = b.file_id(+);

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

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

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


22 sur 53 06/09/2014 08:52

Command 1 lists the candidate files. "type -f" makes sure that only files getconsidered (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_fileswhere tablespace_name='APPS_TS_TX_DATA' group by tablespace_name;

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

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

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

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

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

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


23 sur 53 06/09/2014 08:52

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

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 oracleapps 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 SchemaPassword Change Utility (FNDCPASS)? [ID 437260.1]

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

Note: 160337.1 - How To Manually Change The APPS, APPLSYS andAPPLSYSPUB Passwords in Oracle Applicationshttps://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 theAPPS password, and run AutoConfig then.

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

select 'drop table bkup.'|| table_name || ' ; ' from dba_tables whereowner='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');


24 sur 53 06/09/2014 08:52

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_NAMEFROM ad_bugs a,ad_patch_run_bugs b,ad_patch_runs c,ad_patch_drivers d ,ad_applied_patches eWHERE a.bug_id = b.bug_idAND b.patch_run_id = c.patch_run_idAND c.patch_driver_id = d.patch_driver_idAND d.applied_patch_id = e.applied_patch_idAND a.bug_number in ('&1') ORDER BY 1 DESC;

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

For example you apply merged patch merge123 it contains 5 patches10001 10002 10003 ad_applied_patches will have one record about merge123ad_bugs 5 records

Another example: you apply maintenance patch which includes 51 otherpatches. In that casead_applied_patches will have one record about maintenance patchad_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 oranother 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_DATEfrom AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS Bwhere A.APPLIED_PATCH_ID = B.APPLIED_PATCH_IDand 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.' ||


25 sur 53 06/09/2014 08:52

v.APPLICATION_SHORT_NAME || '.?', I.PATCH_LEVEL) patchsetFROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I,FND_LOOKUPS LWHERE (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_languageswhere installed_flag in ('I','B');

B --> BaseI --> Installed

FNDCPASS apps/oraprd101 0 Y system/xetaprdsys USER VIKAS_MITTALfujitsu1234

SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;COMMIT;

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

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

Startup the environment.

touch -t mmddyyyy file_name

select profile_option_value from apps.fnd_profile_option_values whereprofile_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;


26 sur 53 06/09/2014 08:52

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

SQL> select patch_level from fnd_product_installations where patch_levellike '%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 *dorm -f $idone

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 childrequest first and then on parent request.

FNDCPASS apps/oraprd101 0 Y system/xetaprdsys USER VIKAS_MITTALfujitsu1234

SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;COMMIT;

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

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


27 sur 53 06/09/2014 08:52

Startup the environment.

touch -t mmddyyyy file_name

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

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

FND_PRODUCT_GROUPS

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

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

select product_name, product_abbreviation, pseudo_product_flag,application_short_name, product_family_abbreviationfrom ad_pm_product_infowhere pseudo_product_flag = Norder 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' andmail_status='FAILED';

select * from wf_notifications order by begin_date desc; --- Use after


28 sur 53 06/09/2014 08:52

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 whereconcurrent_program_name like '%FND%';

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

col RESPONSIBILITY_NAME for a49col

select distinct fr.USER_ID,fu.user_name,frtl.RESPONSIBILITY_NAME,fr.START_DATE,fr.END_DATEfromFND_USER_RESP_GROUPS_DIRECT fr,fnd_user fu,PER_ALL_PEOPLE_F hr,fnd_responsibility_tl frtlwhere fu.user_name = 'VIYER'and fr.user_id=fu.user_idand fu.EMPLOYEE_ID=hr.PERSON_IDand frtl.RESPONSIBILITY_ID=fr.RESPONSIBILITY_ID;

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

set linesize 190

col user_name format a11col s.sid format a4col s.serial# format a4col s.serial# format a6col p.spid format a6col USER_CONCURRENT_PROGRAM_NAME format a40col 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 pwhere phase_code = 'R'and status_code = 'R'and s.paddr = p.addrand p.spid = oracle_process_idand requested_by = u.user_idorder by 7 desc;

select u.user_name,request_id, s.sid,s.serial#,


29 sur 53 06/09/2014 08:52

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 pwhere s.paddr = p.addrand p.spid = oracle_process_idand requested_by = u.user_idand request_id =order by 7 desc;

column process heading "FNDLIBR PID"

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPIDFROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_processes b,v$process c,v$session dWHERE a.controlling_manager = b.concurrent_process_idAND c.pid = b.oracle_process_idAND b.session_id=d.audsidAND a.request_id = &Request_IDAND 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.paddrfrom fnd_concurrent_requests r,v$session swhere request_id = &reqidand 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,


30 sur 53 06/09/2014 08:52

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_PIDfrom apps.fnd_conc_req_summary_v fcrv,apps.fnd_concurrent_requests fcrwhere fcrv.phase_code in ('R','P','I')and round(((sysdate - fcrv.actual_start_date)*1440),2) > 30and fcrv.request_id = fcr.request_idand 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_idfrom apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs b, apps.fnd_concurrent_programs_tl cwhere a.program_application_id = b.application_idand a.concurrent_program_id = b.concurrent_program_idand b.application_id = c.application_idand b.concurrent_program_id = c.concurrent_program_idand a.status_code = 'D'and a.request_date > sysdate-17and c.user_concurrent_program_name like 'Workflow Background Process%'order by request_date

select * from v$sess_io where sid =


31 sur 53 06/09/2014 08:52

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

SELECTfcr.REQUEST_ID,FU.USER_NAME,fcr.PHASE_CODE,fcr.STATUS_CODE,(fcr.ACTUAL_COMPLETION_DATE - fcr.ACTUAL_START_DATE) * 24 * 60TIME_MINS,fcpt.USER_CONCURRENT_PROGRAM_NAME,fcp.CONCURRENT_PROGRAM_NAME,fcr.ACTUAL_START_DATE,fcr.ACTUAL_COMPLETION_DATE,fcp.CONCURRENT_PROGRAM_NAMEFROMapps.FND_CONCURRENT_REQUESTS FCR,apps.FND_CONCURRENT_PROGRAMS FCP,apps.FND_CONCURRENT_PROGRAMS_TL FCPT,apps.FND_USER FUWHERE 1=1AND FCP.CONCURRENT_PROGRAM_ID=FCR.CONCURRENT_PROGRAM_IDAND FCPT.CONCURRENT_PROGRAM_ID=FCP.CONCURRENT_PROGRAM_IDAND FCR.REQUESTED_BY = FU.USER_IDAND 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_nameFROM fnd_request_groups frg,fnd_request_group_units frgu,fnd_concurrent_programs fcp,fnd_concurrent_programs_tl fcpt,fnd_responsibility_vl frWHERE frgu.request_unit_type = Pand UPPER(fcpt.user_concurrent_program_name) = UPPER(Journal EntriesReport)AND frgu.request_group_id = frg.request_group_idAND frgu.request_unit_id = fcp.concurrent_program_idAND fr.request_group_id = frg.request_group_idAND fcp.CONCURRENT_PROGRAM_ID = fcpt.CONCURRENT_PROGRAM_IDORDER BY 1/

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

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


32 sur 53 06/09/2014 08:52

select REQUEST_ID, LAST_UPDATE_DATE, REQUEST_DATE, REQUESTED_BY,PHASE_CODE, STATUS_CODE,ORACLE_PROCESS_ID, ORACLE_SESSION_ID, OS_PROCESS_ID fromapps.fnd_concurrent_requestswhere REQUEST_ID in (22890825,22907089);

selects.sid,s.serial#,s.username,s.osuser,s.program,s.module,s.status,p.spid,s.logon_time,s.last_call_etfrom 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) svrprocFROM apps.fnd_concurrent_requests req,apps.fnd_concurrent_processes proc,apps.fnd_lookups look,apps.fnd_lookups look1,v$process vproc,v$session vsessWHERE req.controlling_manager = proc.concurrent_process_id(+)AND req.status_code = look.lookup_codeAND look.lookup_type = 'CP_STATUS_CODE'AND req.phase_code = look1.lookup_codeAND 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 WaitingB ResumingC NormalD CancelledE ErrorF ScheduledG WarningH On HoldI NormalM No ManagerQ StandbyR NormalS SuspendedT TerminatingU DisabledW PausedX TerminatedZ Waiting

.


33 sur 53 06/09/2014 08:52

PHASE_CODE column.C CompletedI InactiveP PendingR Running

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

Pending - Normal, Standby, Scheduled, WaitingRunning - Normal, Paused, Resuming, TerminatingCompleted - Normal, Error, Warning, Cancelled, Terminated, DisabledInactive - Disabled , On Hold, No Manager

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

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

PENDING/Normal- -If any concurrent programs get added in the "StandardManager" under "INCLUDE" instead of "EXCLUDE" , the concurrent requestswould 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 filescannot 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 HoldRequest button in the Requests window.-Issue due to possible locking of same table record by another form sessionOR 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, -- spidOS_PROCESS_ID, -- apps tier processORACLE_SESSION_ID


34 sur 53 06/09/2014 08:52

fromfnd_concurrent_requests fcr,fnd_user fuwhere fu.USER_ID = fcr.REQUESTED_BYand 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) donehere. ++++

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

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

SQL> update fnd_concurrent_requestsset 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 ornot, 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 Bwhere A.APPLIED_PATCH_ID = B.APPLIED_PATCH_IDand b.creation_date >= sysdate -90- and A.PATCH_NAME = ''


35 sur 53 06/09/2014 08:52

*************************************************************************************************/* To know that if the patch is applied successfully, applied on both nodeor 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 ANDA.PATCH_DRIVER_ID = C.PATCH_DRIVER_IDand C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_IDin (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS whereAPPLIED_PATCH_IDin (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES wherePATCH_NAME = '&patch_naumber'))ORDER BY 3 ;

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

/* To find the latest application version */select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSIONversion, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it isdone", 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_VERSIONversion, START_DATE_ACTIVEwhen updated,ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES whereBASE_RELEASE_FLAG = 'Y'

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

/* To find all available application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSIONversion, START_DATE_ACTIVEwhen 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 patchapplication */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 = 86291group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME ;*************************************************************************************************


36 sur 53 06/09/2014 08:52

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

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

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

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

select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "RunId",D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE actionfrom AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONSC, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS Fwhere A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID =C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID andE.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_IDand A.BUG_NUMBER = '&Patch_Number' and B.PATCH_RUN_BUG_ID ='&Patch_Number' andC.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 ofPATCH */

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 actionfromAD_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 Jwhere A.BUG_ID = B.BUG_IDand B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_IDand C.FILE_ID = D.FILE_IDand E.COMMON_ACTION_ID = C.COMMON_ACTION_IDand D.FILE_ID = F.FILE_IDand G.APPL_TOP_ID = H.APPL_TOP_IDand G.PATCH_DRIVER_ID = I.PATCH_DRIVER_IDand I.APPLIED_PATCH_ID = J.APPLIED_PATCH_IDand B.PATCH_RUN_ID = G.PATCH_RUN_IDand C.EXECUTED_FLAG = 'Y'and G.PATCH_DRIVER_IDin (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS whereAPPLIED_PATCH_IDin (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES wherePATCH_NAME = '&Patch_Number'))GROUP BYJ.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME,I.DRIVER_FILE_NAME, D.APP_SHORT_NAME,


37 sur 53 06/09/2014 08:52

D.SUBDIR, D.FILENAME, E.ACTION_CODE ;*************************************************************************************************

col PATCH_NAME format a10col PATCH_TYPE format a10col DRIVER_FILE_NAME format a15col PLATFORM format a10select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME,AD.CREATION_DATE, AD.PLATFORM,AL.LANGUAGEfrom AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD,AD_PATCH_DRIVER_LANGS ALwhere AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_IDand AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_IDand 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_DATEfrom AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS Bwhere A.APPLIED_PATCH_ID = B.APPLIED_PATCH_IDand B.creation_date >= ( sysdate -90 )order by b.creation_date

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

col PRD format a40 trunccol PRDID format a5col PRDSTATUS format a10col PATCHSET format a20SELECT 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) patchsetFROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I,FND_LOOKUPS LWHERE (V.APPLICATION_ID = I.APPLICATION_ID)AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')AND (L.LOOKUP_CODE = I.Status )ORDER BY 1;

==================Password change scriptDo the following as scriptstty -echoread SYSPASS?"Enter the password for system > "echoread APPPASS?"Enter the password for apps > "stty echo;echoread EXT?"Enter the Suffix > "if [ ${EXT} != "" ]thenEXT="_${EXT}"fisqlplus -s

from fnd_oracle_useridwhere oracle_username not in ('APPS','APPLSYS','APPLSYSPUB','SYS','');spool offEOFecho "sh /tmp/pass.$$"sh /tmp/pass.$$echo "rm /tmp/pass.$$"rm /tmp/pass.$$echo "Spool file name is :/tmp/pass.$$"====================

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

set line 150column APPLICATION_NAME format a50column PRODUCT_VERSION format a10column APPLICATION_SHORT_NAME format a10column PATCH_LEVEL format a15set pagesize 100select application_name ,APPLICATION_SHORT_NAME,PRODUCT_VERSION,PATCH_LEVELfrom fnd_product_installations a,fnd_application b , fnd_application_tl cwhere a.APPLICATION_ID = b.APPLICATION_IDand b.APPLICATION_ID = c.APPLICATION_IDorder by 1 ;

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

select application_name ,APPLICATION_SHORT_NAME,PRODUCT_VERSION,PATCH_LEVELfrom fnd_product_installations a,fnd_application b , fnd_application_tl cwhere a.APPLICATION_ID = b.APPLICATION_IDand b.APPLICATION_ID = c.APPLICATION_IDand 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_DATEfrom AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B


39 sur 53 06/09/2014 08:52

where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_IDand B.creation_date >= ( sysdate -90 )order by b.creation_date

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

set pages 1000 lines 140col Product for a58col Prod_Status for a20col Prod_Id for a8col 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_LevelFROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I,FND_LOOKUPS LWHERE (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_datefrom apps.ad_applied_patches aap,apps.ad_patch_drivers apd,apps.ad_patch_runs apr,apps.ad_appl_tops aatwhere aap.applied_patch_id = apd.applied_patch_idand apd.patch_driver_id = apr.patch_driver_idand aat.appl_top_id = apr.appl_top_idand 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 whereBUG_NUMBER='4334965'; -- RUP3




40 sur 53 06/09/2014 08:52

BUG_NUMBER

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

5473858



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

SPOOL /tmp/fnd_nodes.txt

set pagesize 50col node_name format a15col server_id format a8col server_address format a15col platform_code format a4col webhost format a12col domain format a20col virtual_ip format a12set linesize 132selectnode_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_ipfromfnd_nodesorder by node_id;

SPOOL OFF

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

Cm hold--------


41 sur 53 06/09/2014 08:52

update applsys.FND_CONCURRENT_REQUESTS set hold_FLAG='Y'where request_id in(SELECT R.REQUEST_IDFROMapplsys.FND_CONCURRENT_PROGRAMS_TL PT,applsys.FND_CONCURRENT_PROGRAMS PB,applsys.FND_USER U,applsys.FND_PRINTER_STYLES_TL S,applsys.FND_CONCURRENT_REQUESTS RWHEREPB.APPLICATION_ID = R.PROGRAM_APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_IDAND PB.APPLICATION_ID = PT.APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_IDAND PT.LANGUAGE = USERENV('LANG')AND U.USER_ID = R.REQUESTED_BYAND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLEAND 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_IDFROMapplsys.FND_CONCURRENT_PROGRAMS_TL PT,applsys.FND_CONCURRENT_PROGRAMS PB,applsys.FND_USER U,applsys.FND_PRINTER_STYLES_TL S,applsys.FND_CONCURRENT_REQUESTS RWHEREPB.APPLICATION_ID = R.PROGRAM_APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_IDAND PB.APPLICATION_ID = PT.APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_IDAND PT.LANGUAGE = USERENV('LANG')AND U.USER_ID = R.REQUESTED_BYAND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLEAND 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 fromapps.fnd_nodes ;


42 sur 53 06/09/2014 08:52

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

SELECTb.user_concurrent_queue_nameFROMfnd_concurrent_processes a,fnd_concurrent_queues_vl b,fnd_concurrent_requests cWHERE 1=1AND a.concurrent_queue_id = b.concurrent_queue_idAND a.concurrent_process_id = c.controlling_managerAND 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 99999999999col PARENT_REQUEST_ID format 9999999999col REQUESTOR format a10col 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 a15SELECT 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')StatuSFROM apps.FND_CONC_REQ_SUMMARY_V fcrsv,(SELECT LEVEL lvl, request_id req_idFROM apps.fnd_concurrent_requestsCONNECT BY PRIOR request_id = parent_request_idSTART WITH request_id = &REQ_ID ) fcrlWHERE fcrsv.request_id = fcrl.req_idORDER BYfcrsv.request_id,fcrl.lvl,fcrsv.actual_start_date/


43 sur 53 06/09/2014 08:52

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_idfrom apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs b, apps.fnd_concurrent_programs_tl cwhere a.program_application_id = b.application_idand a.concurrent_program_id = b.concurrent_program_idand b.application_id = c.application_idand b.concurrent_program_id = c.concurrent_program_idand a.status_code = 'D'and a.request_date > sysdate-17and c.user_concurrent_program_name like 'Workflow Background Process%'order by request_date;

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


44 sur 53 06/09/2014 08:52

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_idfrom apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs b, apps.fnd_concurrent_programs_tl cwhere a.program_application_id = b.application_idand a.concurrent_program_id = b.concurrent_program_idand b.application_id = c.application_idand b.concurrent_program_id = c.concurrent_program_idand (phase_code = 'P' or phase_code = 'R')and a.request_date > sysdate-7and c.user_concurrent_program_name like '%Workflow BackgroundProcess%'order by request_date;

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

create table fnd_conc_requests_bak_030210 as select * fromfnd_concurrent_requestswhere request_id in(select a.request_idfrom apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs b, apps.fnd_concurrent_programs_tl cwhere a.program_application_id = b.application_idand a.concurrent_program_id = b.concurrent_program_idand b.application_id = c.application_idand b.concurrent_program_id = c.concurrent_program_idand (phase_code = 'P' or phase_code = 'R')


45 sur 53 06/09/2014 08:52

and a.request_date > sysdate-7and c.user_concurrent_program_name like '%Workflow BackgroundProcess%');

SQL> create table fnd_conc_requests_bak_030210 as select * fromfnd_concurrent_requests2 where request_id in(3 select a.request_id4 5 from apps.fnd_concurrent_requests a6 , apps.fnd_concurrent_programs b7 , apps.fnd_concurrent_programs_tl c8 where a.program_application_id = b.application_id9 and a.concurrent_program_id = b.concurrent_program_id10 and b.application_id = c.application_id11 and b.concurrent_program_id = c.concurrent_program_idand (phase_code = 'P' or phase_code = 'R')and a.request_date > sysdate-7and c.user_concurrent_program_name like '%Workflow BackgroundProcess%'); 12 13 14 15

Table created.

SQL> selet count(*) from fnd_conc_requests_bak_030210;SP2-0734: unknown command beginning "selet coun..." - rest of lineignored.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_idfrom apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs b, apps.fnd_concurrent_programs_tl cwhere a.program_application_id = b.application_idand a.concurrent_program_id = b.concurrent_program_idand b.application_id = c.application_idand b.concurrent_program_id = c.concurrent_program_idand (phase_code = 'P' or phase_code = 'R')and a.request_date > sysdate-7and c.user_concurrent_program_name like '%Workflow BackgroundProcess%');

SQL> update fnd_concurrent_requests set hold_flag='Y' where request_id in2 (3 select a.request_id4 from apps.fnd_concurrent_requests a5 , apps.fnd_concurrent_programs b6 , apps.fnd_concurrent_programs_tl c


46 sur 53 06/09/2014 08:52

7 where a.program_application_id = b.application_id8 and a.concurrent_program_id = b.concurrent_program_id9 and b.application_id = c.application_id10 and b.concurrent_program_id = c.concurrent_program_id11 and (phase_code = 'P' or phase_code = 'R')12 and a.request_date > sysdate-713 and c.user_concurrent_program_name like '%Workflow BackgroundProcess%'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_IDFROMapplsys.FND_CONCURRENT_PROGRAMS_TL PT,applsys.FND_CONCURRENT_PROGRAMS PB,applsys.FND_USER U,applsys.FND_PRINTER_STYLES_TL S,applsys.FND_CONCURRENT_REQUESTS RWHEREPB.APPLICATION_ID = R.PROGRAM_APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_IDAND PB.APPLICATION_ID = PT.APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_IDAND PT.LANGUAGE = USERENV('LANG')AND U.USER_ID = R.REQUESTED_BYAND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLEAND 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_IDFROMapplsys.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


47 sur 53 06/09/2014 08:52

WHEREPB.APPLICATION_ID = R.PROGRAM_APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_IDAND PB.APPLICATION_ID = PT.APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_IDAND PT.LANGUAGE = USERENV('LANG')AND U.USER_ID = R.REQUESTED_BYAND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLEAND 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_IDFROMapplsys.FND_CONCURRENT_PROGRAMS_TL PT,applsys.FND_CONCURRENT_PROGRAMS PB,applsys.FND_USER U,applsys.FND_PRINTER_STYLES_TL S,applsys.FND_CONCURRENT_REQUESTS RWHEREPB.APPLICATION_ID = R.PROGRAM_APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_IDAND PB.APPLICATION_ID = PT.APPLICATION_IDAND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_IDAND PT.LANGUAGE = USERENV('LANG')AND U.USER_ID = R.REQUESTED_BYAND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLEAND S.LANGUAGE(+) = USERENV('LANG')and (phase_code = 'P' or phase_code = 'R')--and PB.CONCURRENT_PROGRAM_NAME 'FNDGSCST') --- FNDGSCST isthe Gather Schema Stats (GSC) joband R.REQUESTED_BY='SYSADMIN'/

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

column USER_CONCURRENT_PROGRAM_NAME format a35column 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 uwhere phase_code = 'C'and status_code = 'C'and requested_by = u.user_idand USER_CONCURRENT_PROGRAM_NAME like '%Gather Schema Stat%'and actual_start_date like '%18-SEP%';


48 sur 53 06/09/2014 08:52

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 inventorytransactionPROMPT 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.modulefrom fnd_concurrent_requests req, v$session ses, v$process proc,v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,fnd_executables execnamewhere req.request_id = &requestand 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_idand req.program_application_id = prog.application_idand prog.application_id = execname.application_idand prog.executable_id=execname.executable_id;

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

On the standby database, query the gv$archived_log view to verify the logsare 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) completionfrom 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;


49 sur 53 06/09/2014 08:52

select LINE,TEXT from RC_STORED_SCRIPT_LINE whereSCRIPT_NAME='backup_inc0_p03erp';

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

select WORKER_ID , CONTROL_CODE , STATUS , FILENAME fromfnd_install_processesorder by worker_id;

WORKER_ID C S FILENAME---------- - - ------------------------------------------------------------0 W W UNDEF1 Q R adobjcmp.sql2 R W cebtapib.pls3 R W cejecrnb.pls4 R W cepmtcob.pls5 R W cexlaevb.pls6 R W fem_data_loader.plb7 R W fem_signage_utl.plb8 R W fem_ud_eng.plb9 R W jai_ap_utils.plb10 R W jai_cmn_rgm_stl.plb

WORKER_ID C S FILENAME---------- - - ------------------------------------------------------------11 R W jai_cmn_hook.plb12 R W jai_cmn_rcv_mach.plb13 R W jai_general.plb14 R W jai_cmn_rg_23p1.plb15 R W jai_rcv_rnd.plb16 R W jai_po_hook_pkg.plb

set line 200col SUBDIRECTORY format a20col filename format a20col command format a10alter 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 .00012731514 R W sqlplus jai_cmn_rg_23p1.plb 75 02/09/010 18:17:51 .00009259315 R W sqlplus jai_rcv_rnd.plb 75 02/09/010 18:17:55 .00028935216 R W sqlplus jai_po_hook_pkg.plb 75 02/09/010 18:17:54 .0003819444 R W package cepmtcob.pls 78 02/09/010 18:18:33 .000034722


50 sur 53 06/09/2014 08:52

11 R W sqlplus jai_cmn_hook.plb 75 02/09/010 18:17:53 .000057876 R W package fem_data_loader.plb 78 02/09/010 18:18:33 .0000231485 R W package cexlaevb.pls 78 02/09/010 18:18:33 .0000347228 R W package fem_ud_eng.plb 78 02/09/010 18:18:33 .0000347222 R W package cebtapib.pls 78 02/09/010 18:18:33 00 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 0ngle

3 R W package cejecrnb.pls 78 02/09/010 18:18:33 .0000115747 R W package fem_signage_utl.plb 78 02/09/010 18:18:33 .0000347229 R W sqlplus jai_ap_utils.plb 75 02/09/010 18:17:43 .00030092610 R W sqlplus jai_cmn_rgm_stl.plb 75 02/09/010 18:17:50 .0002546312 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}' | xargskill -9ps -ef | grep appptl2 | grep -v grep | grep defunct | awk '{print $2}' | xargskill -9

update fnd_install_processesset 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_SERVICESwhere SERVICE_ID = (select MANAGER_TYPE fromFND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME ='FNDCPOPP');

update FND_CP_SERVICES set DEVELOPER_PARAMETERS ='J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'


51 sur 53 06/09/2014 08:52

where SERVICE_ID = (select MANAGER_TYPE fromFND_CONCURRENT_QUEUESwhere 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','InternalManager','FNDCRM','Conflict Resolution Manager','AMSDMIN','Marketing DataMining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormulaTransaction Manager','FNDCPOPP','Output PostProcessor','FNDSCH','Scheduler/PrereleaserManager','FNDSM_AQHERP','Service Manager:AQHERP','FTE_TXN_MANAGER','Transportation Manager','IEU_SH_CS','SessionHistory Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashedsession','INVMGR','Inventory Manager','INVTMRPM','INV Remote ProcedureManager','OAMCOLMGR','OAM Metrics Collection Manager','PASMGR','PAStreamline Manager','PODAMGR','PO Document ApprovalManager','RCVOLTM','Receiving Transaction Manager','STANDARD','StandardManager','WFALSNRSVC','Workflow Agent ListenerService','WFMLRSVC','Workflow Mailer Service','WFWSSVC','WorkflowDocument Web Services Service','WMSTAMGR','WMS Task Archiving

AppsDBA: Very importa

appsdba_ very important queries in day to day apps dba life.pdf

Documents