A for Apps D for DBA: DBA Scripts used regularly

Session Details
==========

set verify off
set head off
set feedback on
set long 5000
select distinct
'========================================================='||chr(10)||
'Sid , Serial#     : '||S.Sid   ||' , '||S.Serial# ||Chr(10)||
'Server/Shadow     : '||P.Spid    ||Chr(10)||
'Client/Foreground : '||S.Process   ||Chr(10)||
'Terminal / Machine: '||S.terminal||' / '||S.Machine   ||Chr(10)||
'Username........: '||S.Username   ||Chr(10)||
'Osuser..........: '||S.Osuser   ||Chr(10)||
'Program.........: '||S.Program    ||Chr(10)||
'Module..........: '||S.Module    ||Chr(10)||
'Status..........: '||S.Status ||Chr(10)||
'Action..........: '||S.Action   ||Chr(10)||
'Wait_time.......: '||W.Wait_time   ||Chr(10)||
'State ..........: '||W.State   ||Chr(10)||
'Wait Event .....: '||W.Event   ||Chr(10)||
'Seconds_in_wait.: '||W.Seconds_in_wait   ||Chr(10)||
'Pga_alloc....: '|| To_char(P.Pga_alloc_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_used.....: '|| To_char(P.Pga_used_mem/1024/1024 ,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_free.....: '|| To_char(P.Pga_freeable_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_max......: '|| To_char(P.Pga_max_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Lock / Latch.: '|| Nvl(S.Lockwait, 'None')||'/ '||Nvl(P.Latchwait, 'None')   ||Chr(10)||
'Blocking Sessi=on: '||s.blocking_session||Chr(10)||
'Blocking Session Status: '|| s.blocking_session_status||Chr(10)||
'Latch Spin...: '|| Nvl(P.Latchspin, 'None')   ||Chr(10)||
'Logon Time...: '|| To_char(S.Logon_time, 'Dy Dd-Mon-Yy Hh24:Mi:Ss')   ||Chr(10)||
'Last Call....: '|| To_char(Sysdate-(S.Last_call_et/60/60/24), 'Dy Dd-Mon-Yy Hh24:Mi:Ss') || ' -> ' || To_char(S.Last_call_et/60, '99999.0') || ' Mins' || To_char(S.Last_call_et/60/60, '99999.0') || ' Hours' ||To_char(S.Last_call_et/60/60/24, '99.0') || ' Days' ||Chr(10)||
'Sql Address. : '||S.Sql_address   ||Chr(10)||
'Sql Hash.... : '||S.Sql_hash_value   ||Chr(10)||
'Prev Sql Hash: '||S.Prev_hash_value   ||Chr(10)||
'Trans Status : '|| Nvl(T.Status,'None') || Chr(10)||
'Trans Active : '|| Nvl(S.Taddr, 'None')||Chr(10)||
'Undo Generation: '||Nvl(T.Used_ublk,0) || ' Blocks'||Chr(10)||
'Changed Blocks : '||I.Block_changes||' Blocks'||Chr(10)||
'............    Current Sql Statment   .................: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q.Sql_text,'No Current Sql Statment')    ||Chr(10)||
'========================================================='||Chr(10)||
'................ Prev Sql Statment ..................: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q2.Sql_text,'No Sql Statment')    ||Chr(10)||
'========================================================='
from gv$session s, gv$process p , gv$sqlarea q , gv$sqlarea q2 ,gv$session_wait w ,
gv$transaction t, gv$sess_io i
where p.addr=s.paddr
and s.sid=i.sid
and s.sid=nvl('&sid',s.sid)
and s.sid=w.sid
and p.spid=nvl('&spid',p.spid)
and q.HASH_VALUE(+)=s.sql_hash_value
and q2.hash_value(+)=s.prev_hash_value
and s.taddr=t.addr(+)
and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

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

script to find concurrent req details------
==========================

set head on
   set verify off
   set echo off
   set pages 1500
   set linesize 132
   break on row skip 1

   prompt Checking for Concurrent program details run by this process id
   prompt **************************************************************
   prompt
   prompt
select /*+ CHOOSE*/
    'Node Name..............................: ' || q.node_name || chr(10) ||
    'Req id.................................: ' || Request_Id || chr(10) ||
    'Requestor..............................: ' || User_Name || chr(10) ||
    'Manager................................: ' || Q.User_Concurrent_Queue_Name || chr(10) ||
    'Status code............................: ' || decode(status_code,'A', 'Waiting','B',
    'Resuming','D', 'Cancelled','E', 'Error', 'G', 'Warning', 'H',
    'On Hold', 'R', 'Normal','S', 'Suspended', 'T', 'Terminating','W', 'Paused', 'X','Terminated', status_code) || chr(10) ||
           'Phase code.............................: ' || decode(phase_code, 'C', 'Completed',
           'I', 'Inactive', 'P', 'Pending', 'R', 'Running', phase_code) || chr(10) ||
           'Priority...............................: ' || Fcr.priority || chr(10) ||
           'Program................................: ' || Fcp.User_Concurrent_Program_Name || chr(10) ||
           'Time so far ...........................: ' ||trunc((sysdate-Fcr.actual_start_date)*24*60,2)||' min'|| chr(10) ||
           'Avg execution time in 30 days.........: ' || trunc(AVG_TIME,2) ||' min' || chr(10) ||
           'Max execution time in 30 days..........: ' || trunc(MAX_TIME,2) ||' min' || chr(10) ||
           'Fastest execution Time in 30 days......: ' ||trunc( MIN_TIME,2) || ' min' || chr(10) ||
           'Number of executions in last 30 days...: ' ||occurance || chr(10) ||
           'ClientPID..............................: ' || Fcr.OS_PROCESS_ID || chr(10) ||
           'ServerPID..............................: ' || Fcr.ORACLE_PROCESS_ID || chr(10) ||
           'Arguments passed to the program .......: ' ||Fcr.argument_text
from apps.Fnd_Concurrent_Requests Fcr,
apps.Fnd_Concurrent_Programs_vl Fcp,
    apps.Fnd_Oracle_Userid O,
    apps.Fnd_Concurrent_Processes P,
    apps.Fnd_Concurrent_Queues_vl Q,
    apps.Fnd_User,(select
    concurrent_program_id
    ,count(concurrent_program_id) occurance
    ,min(actual_completion_date-actual_start_date)*24*60 MIN_TIME
    ,max(actual_completion_date-actual_start_date)*24*60 MAX_TIME
    , avg(actual_completion_date-actual_start_date)*24*60 AVG_TIME
    from apps.fnd_concurrent_requests
   where status_code='C' and phase_code='C'and trunc(actual_start_date)>trunc(sysdate-30)
         group by concurrent_program_id
         having max(actual_completion_date-actual_start_date)*24*60 >5) CREQ
         where Controlling_Manager = Concurrent_Process_ID
         And ( P.Concurrent_Queue_ID = Q.Concurrent_Queue_ID AND
         P.Queue_Application_ID = Q.Application_ID )
         And O.Oracle_Id = Fcr.Oracle_Id and Fcr.ORACLE_PROCESS_ID in ('&Server_PID')
         And ( Fcr.Program_Application_Id = Fcp.Application_Id
         And Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id )
         And Requested_By = User_Id
         And Phase_Code = 'R' and status_code in ('R','T')
         --adding joins with new
         and Fcr.Concurrent_Program_Id=CREQ.Concurrent_Program_Id(+)
Order By Q.User_Concurrent_Queue_Name,q.node_name, Actual_Start_Date, Request_Id;

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

==== Running concurrent request details

col user_name format a20 word_wrapped
col ProgName format a25 word_wrapped
col requestId format 99999999
col StartDate format a20 word_Wrapped
col OS_PROCESS_ID format a6
col ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
set lines 200

select
sess.sid,sess.serial#,
oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
request_id RequestId,
decode(fcr.phase_code,
'C','Complete',
'I','Inactive',
'P','Pending',
'R','Running',
'Unknown') phase_code ,
decode(fcr.status_code,
'C','Normal',
'D','Cancelled',
'E','Error',
'F','Scheduled',
'G','Warning',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'Z','Waiting'
) status_code ,
(sysdate - actual_start_date)*24*60*60 ETime
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where
fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
order by 5 DESC
/
==========================================================
Running normal requests details
===============================
col phase for a10
col "Interval" for a12
col sid for 999999
col serial# for 999999
col process for a18
col spid for a10
col "Prog Name" for a45
set lines 300 pages 1000
select
fr.request_id
,fr.phase
,fr.actual_start_date
,substr(numtodsinterval(nvl(fr.actual_completion_date,sysdate)-fr.actual_start_date,'DAY'),12,8) "Interval"
,se.sid
,se.serial#
,pr.spid
,se.process
,fr.user_concurrent_program_name "Prog Name"
from   APPS.fnd_amp_requests_v fr,
gv$session se,
gv$process pr
where 1=1
and    fr.phase_code ='R'
and    fr.oracle_session_id = se.audsid (+)
and    se.inst_id    =pr.inst_id(+)
and    se.paddr         = pr.addr(+)
/

++++++

PARENT AND CHILD Request details
==================================

set lines 300
col user_concurrent_program_name for a45 print
col concurrent_program_name for a20
col status for a10
col sid for a25 head "Sid|Serial#"
select   parent_request_id
    ,request_id
    ,(select sid||','||serial# from v$session where audsid=fav.ORACLE_SESSION_ID) sid
    ,user_concurrent_program_name
    ,status
    ,substr(numtodsinterval(nvl(actual_completion_date,sysdate)-actual_start_date,'DAY'),12,8) "Interval"
from   fnd_amp_requests_v fav
where phase_code='R'
start with parent_request_id = -1
connect by prior request_id = parent_request_id
/
========================================================================

Conc Request details with req-id
========================

SELECT a.request_id, d.sid, d.serial# , 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';

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

concurrent requests details with manager details
************************************

set lines 300
col os form A7 head AppProc
col spid form a6 head SPID
col program form A50 trunc
set pages 38
col time form 9999999.99 head Elapsed
col "Req Id" form 99999999
col "Parent" form a10
col "Prg Id" form 9999999
col serial# form 99999999 head Serial#
col qname head "Manager" format a10 trunc
col sid format 9999 head SID
col user_name form A10 head User trunc
set recsep off
select
       q.concurrent_queue_name qname
      ,f.user_name
      ,a.request_id "Req Id"
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,a.phase_code,a.status_code
     ,b.os_process_id "OS"
     ,vs.sid
     ,vs.serial# "Serial#"
    ,vp.spid
      ,a.oracle_process_id "spid"
      ,(nvl(a.actual_completion_date,sysdate)-a.actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name||' '||a.description "Program"
from APPLSYS.fnd_Concurrent_requests a
    ,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_user f
   ,v$session vs
   ,v$process vp
where
      a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and c2.application_id = c.application_id
and a.phase_code in ('I','P','R','T')
and a.requested_by = f.user_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
order by 1,3
/
========================================================================
Concurrent program history
**************************

set pause off
set pagesize 2000
set linesize 120
set wrap off
column user_concurrent_program_name format a45 noprint
column argument_text format a45 print
column user_name format a15
column start_time format a15
column end_time format a15
column comp_time format 9999.99
select request_id,
       user_concurrent_program_name,
       to_char(actual_start_date,'DD/MON HH24:MI:SS') START_TIME,
       to_char(ACTUAL_COMPLETION_DATE,'DD/MON HH24:MI:SS') END_TIME,
       (actual_completion_date-actual_start_date)*24*60 comp_time, argument_text,user_name, status_code, phase_code
from apps.fnd_concurrent_requests, apps.fnd_concurrent_programs_tl,apps.fnd_user
where fnd_concurrent_requests.concurrent_program_id = fnd_concurrent_programs_tl.concurrent_program_id
and user_concurrent_program_name like '%TSSA%Auto%Service%Contract%Update%Child%'
and fnd_concurrent_programs_tl.language='US'
and requested_by=user_id
order by actual_start_date desc,ACTUAL_COMPLETION_DATE desc;
========================================================================
concurrent request with request No
-> give below command to get (SID / SPID / Client PID / Module ) - Just give "concurrent request no"

**************
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off

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;

========================================================================
Concurrent manager details
*************************
set echo off
set linesize 130
set serveroutput on size 50000
set feed off
set veri off
DECLARE
running_count NUMBER := 0;
pending_count NUMBER := 0;
crm_pend_count NUMBER := 0;
--get the list of all conc managers and max worker and running workers
CURSOR conc_que IS
SELECT concurrent_queue_id,
concurrent_queue_name,
user_concurrent_queue_name,
max_processes,
running_processes
FROM apps.fnd_concurrent_queues_vl
WHERE enabled_flag='Y' and
concurrent_queue_name not like 'XDP%' and
concurrent_queue_name not like 'IEU%' and
concurrent_queue_name not in ('ARTAXMGR','PASMGR') ;
BEGIN
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
DBMS_OUTPUT.PUT_LINE('QueueID'||' '||'Queue          '||
'Concurrent Queue Name               '||' '||'MAX '||' '||'RUN '||' '||
'Running '||' '||'Pending   '||' '||'In CRM');
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
FOR i IN conc_que
LOOP
--for each manager get the number of pending and running requests in each queue
SELECT /*+ RULE */ nvl(sum(decode(phase_code, 'R', 1, 0)), 0),
nvl(sum(decode(phase_code, 'P', 1, 0)), 0)
INTO running_count, pending_count
FROM fnd_concurrent_worker_requests
WHERE
requested_start_date <= sysdate
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y';
--for each manager get the list of requests pending due to conflicts in each manager
SELECT /*+ RULE */ count(1)
INTO crm_pend_count
FROM apps.fnd_concurrent_worker_requests a
WHERE concurrent_queue_id = 4
AND hold_flag != 'Y'
AND requested_start_date <= sysdate
AND exists (
SELECT 'x'
FROM apps.fnd_concurrent_worker_requests b
WHERE a.request_id=b.request_id
and concurrent_queue_id = i.concurrent_queue_id
AND hold_flag != 'Y'
AND requested_start_date <= sysdate);
--print the output by joining the outputs of manager counts,
DBMS_OUTPUT.PUT_LINE(
rpad(i.concurrent_queue_id,8,'_')||
rpad(i.concurrent_queue_name,15, ' ')||
rpad(i.user_concurrent_queue_name,40,' ')||
rpad(i.max_processes,6,' ')||
rpad(i.running_processes,6,' ')||
rpad(running_count,10,' ')||
rpad(pending_count,10,' ')||
rpad(crm_pend_count,10,' '));
--DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('====================================================================================================');
END;
/
========================================================================
PHASE CODE Meaning:
==================
    C    Completed
    I    Inactive
    P    Pending
    R    Running

STATUS CODE Meaning:
===================
    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
put conc req on HOLD
CREATE TABLE apps.tmp_program_on_hold AS
SELECT
request_id
FROM
apps.fnd_concurrent_requests
WHERE
phase_code = 'P' and
nvl(hold_flag,'N') = 'Y';

update apps.fnd_concurrent_requests
set hold_flag = 'Y'
WHERE
phase_code = 'P' and
request_id not in (SELECT request_id FROM apps.tmp_program_on_hold) ;
commit;
exit

====
update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';

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

Inactive sessions
**************

SQL> select count(status) Count, status from v$session group by status;

     COUNT STATUS
---------- --------
        94 ACTIVE
       538 INACTIVE

select substr(v$session.username,1,10) "DBUSER"
       ,substr(v$session.osuser,1,15) "OSUSER"
       ,substr(v$session.sid,1,4)      "SID#"
       ,substr(v$session.serial#,1,6) "SER#"
       ,substr(v$session.process,1,5) "CLNT_PROCESS"
       ,substr(v$session.module,1,10) "Module"
       ,v$session.status   "STATUS"
       from v$session,v$process
       where v$session.serial# > 1
and v$session.paddr = v$process.addr
and v$session.status ='INACTIVE' and v$session.username ='APPS';

1 : inactive jdbc session details
--------------

select sid,serial#,status,program,last_call_et from v$session where program like '%JDBC%' and last_call_et > 8 * 3600 and status = 'INACTIVE';

2- Kill the DB Session ( create script to kill sessions)

   spool inactive.sh
   select 'alter system kill session '''||a.sid|| ',' ||a.serial#|| ''';' from v$session a,
    v$process b where a.paddr =b.addr and a.last_call_et > 21600 and status='INACTIVE' ;
    spool off
or

alter system kill session '4976,31'; ->( ' SID,SERIAL#')

========================================================================
To find the SQL_TEXT being run by a particular session ===
***************

    select s.username, s.sid, t.sql_text "Last SQL"
    from gv$session s, gv$sqlarea t
    where s.sql_address =t.address and
    s.sql_hash_value =t.hash_value and
    s.sid = '&sid';
========================================================================
WORKFLOW STATUS
******************

set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
========================================================================
check middle tiers session
**************************

col umachine format a20 trunc head UserSrvr
col totact format 999999 head ACTIVE
col totinact format 999999 head INACTIVE
col totsess format 999999 head TOTAL
col earliest_logon format a12 head Earliest
col latest_logon format a12 head Latest
break on report
compute sum of totact on report
compute sum of totinact on report
compute sum of totsess on report
select replace(s.machine,'GEIPS-AMER\',null) umachine,
sum(decode(s.status,'ACTIVE',1,0)) totact,
sum(decode(s.status,'INACTIVE',1,0)) totinact,
count(*) totsess,
min(to_char(s.logon_time,'mm/dd hh24:mi')) earliest_logon,
max(to_char(s.logon_time,'mm/dd hh24:mi')) latest_logon
from v$session s
where s.type = 'USER'
group by s.machine
order by 4,1
/
========================================================================

Blocking session
***************
1:
column sess format A20
    SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,
           id1, id2, lmode, request, type, inst_id
     FROM GV$LOCK
    WHERE (id1, id2, type) IN
       (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
         ORDER BY id1, request;

2:
select * from gv$lock where (ID1,ID2,TYPE) in (select ID1,ID2,TYPE from gv$lock where
    request>0);

3:
select distinct holding_session from dba_waiters where holding_session not in (select
    waiting_session from dba_waiters);

objects Locks
****************
col "O/SUser" FOR a10
col OraUser FOR a20
col Blocking FOR a12
col ObjLocked FOR a15
col sid FOR 99999999
col pid FOR a8
SELECT s.osuser "O/SUser", s.username "OraUser", s.sid "SID",
s.serial# "Serial", s.process "PID", s.status "Status",l.name "ObjLocked",
l.mode_held "Lock Held" FROM V$SESSION s,DBA_DML_LOCKS l,V$PROCESS p
WHERE l.session_id = s.sid AND p.addr = s.paddr;

Just another way to find the locks

SELECT VLO.OS_USER_NAME "OS USERNAME", VLO.ORACLE_USERNAME "DB USER",
VP.SPID "SPID", AO.OWNER "OWNER", AO.OBJECT_NAME "OBJECT LOCKED",AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, 'NO LOCK',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCL',
6, 'EXCLUSIVE',
NULL
) "MODE OF LOCK",
VS.STATUS "CURRENT STATUS"
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> 'KILLED'
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;

LIBRARY CACHE LOCK
*******************
select /*+ ordered */ w1.sid waiting_session, h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 where
(
(
(h.kgllkmod != 0)
and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)) )
and
(
(
(w.kgllkmod = 0)
or (w.kgllkmod= 1)
)
and (
(
w.kgllkreq != 0)
and (w.kgllkreq != 1)
)
)
)
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr;

====================================================================
Objects Analyzed
-----------------
select count(*),trunc(last_analyzed) from dba_tables group by trunc(last_analyzed) order by 1;

select count(*),trunc(last_analyzed) from dba_tables where owner in ('SYS','SYSTEM') group by trunc(last_analyzed) order by 1;

select table_name , last_analyzed from dba_tables where owner like 'GMI';

delete statics
-------------
analyze table earnings_transaction delete statistics;
====================================================================

Rename datafiles
****************

Moving Datafiles while the Instance is Mounted

SQL> shutdown immediate
SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf
SQL> startup mount
SQL> alter database rename file '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';

Moving Datafiles while the Instance is Open

% sqlplus "/ as sysdba"
SQL> alter tablespace INDX offline;
SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf
SQL> alter tablespace INDX
2 rename datafile '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';
SQL> alter tablespace INDX online;
====================================================================

Backup status on datafiles
***************************
select STATUS,count(FILE#) from v$backup group by status;

select distinct 'alter tablespace '||tablespace_name||' end backup ;'
from dba_data_files
where file_id in
(select FILE# from v$backup where status='ACTIVE');

====================================================================
calculation of huge pages
**************************

let SGA= X

Y= SGA
-------
   2048*1024

Huge page = y + 3% of y
====================================================================

Datafiles details
************

SQL> select sum(bytes)/1024/1024 from dba_data_files;

select TABLESPACE_NAME, file_name, (maxbytes-bytes)/(1024*1024), autoextensible, bytes, maxbytes
        from dba_data_files
       where TABLESPACE_NAME = 'APPS_TS_TX_DATA';
       --and (maxbytes-bytes)/(1024*1024) < 100;
====================================================================
Delete Files Older Than x Days on Linux
*******************************

find . -name * -mtime +30 -exec rm -f {} \;

find . -mtime +30 -exec ls -l {} \;|wc -l

find . -name "*.tmp" -mtime +30 -exec ls -l {} \; | wc -l

ex

find . -name "*.trc" -mtime +30 -exec rm -rf {} \;
====================================================================
enable Diagnostics
------------------
1) system Administrator Responsibility > Profile > System
2) Choose the Profile Option "Hide Diagnostics Menu Entry "
3) Set it to 'No' at Site level
4) Log out of Oracle Applications .
5) Log in again
6) Go to the desired responsibility
7) Navigate to any screen
8) In the 'Help' option , the user will be able to see 'Diagnostics'

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

tkprof
======

tkprof <trace file name> <output file name> explain=apps/apps_pwd sort='(prsela,exeela,fchela)'

EXPLAIN PLAN FOR << Select stmt..>> SELECT * FROM TABLE(dbms_xplan.display);

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

Process running on file
=======================
lsof | grep <filename>

process runnin on port
----------------------
netstat -anp | grep -i 12513

root@ohsrcoh10 ~]# netstat -anp | grep -i <Port> ( run   as root will give pid)
root@ohsrcoh10 ~]# ps -aef | grep -i 8343

lsof -i :80

lsof -i TCP|grep <clientpid>

====================================================================
Action Plan to Re-Org Tablespace
***************************
1> Create new tablespace with the size of the tablespace being re-org'ed
2> Shutdown all middle tier processes
3> Migrate all objects from OLD tablespace to NEW tablespace created in step#1
4> Make sure all objects have been migrated to NEW tablespace
5> Recreate or coalesce OLD tablespace
6> Migrate all objects from NEW tablespace back to OLD tablespace
7> Restart Database
8> Restart all Middle Tier processes
9> Perform healthcheck

1.   Create a new tablespace

   SQL:> create tablespace <tablespace_name> datafile <datafile_name> size xxx MB;
2.   Grant resouce access to the new tablespace

   SQL:> alter user <user name> quota unlimited on <new tablespace>;

3.   Move the tables in question to the new tablespace by doing the following:

   SQL:> alter table table_name move TABLESPACE <new_tablespace>;

4.   Move the tables back to the original tablespace

   SQL:> alter table <table_name> move tablespace <original tablespace>;

5.   Re-build indexes (need to provide the index list)
   SQL:> alter INDEX xx rebuild;

6.   Drop the new tablespace

   SQL:> drop tablespace <new tablepace> including contents and datafiles;

7.   Rebudild all the indexes pertaining to these tables.
===============================================================

Fragmented tables
***************
select table_name,num_rows,last_analyzed,chain_cnt
    from all_tables
    where chain_cnt > 0
    order by chain_cnt desc;

chk for chain_cnt>0 tables

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

To find all environment url
*********************

select substr(t.PROFILE_OPTION_ID,0,6) ID,
      substr(z.USER_PROFILE_OPTION_NAME,0,40) User_Profile,
      substr(v.PROFILE_OPTION_VALUE,0,60) Value,
      substr(t.PROFILE_OPTION_NAME,0,30) value
from applsys.fnd_profile_options t,
    applsys.fnd_profile_option_values v,
    applsys.fnd_profile_options_tl z
where (v.PROFILE_OPTION_ID = t.PROFILE_OPTION_ID )
and (t.PROFILE_OPTION_NAME = z.PROFILE_OPTION_NAME)
and (v.PROFILE_OPTION_VALUE like '%http:%');
===========================================================
concurrent manager for a particular conc program
------------------------------------------------
select b.user_concurrent_program_name , c.USER_CONCURRENT_QUEUE_NAME
from
FND_CONCURRENT_QUEUE_CONTENT a ,
fnd_concurrent_programs_vl b,
fnd_concurrent_queues_vl c
where
a.CONCURRENT_QUEUE_ID=c.CONCURRENT_QUEUE_ID and
b.CONCURRENT_PROGRAM_ID=a.type_id
and a.include_flag='I'
and b.USER_CONCURRENT_PROGRAM_NAME like '%Active Responsibilities%'
==============================================================
Enable Trace for Front end user
***************************

Set at user level eg (PREKA or SYSADMIN)

FND: Debug Log Enabled Yes
FND: Debug Log Level   satement
FND: Debug Log Module %

==============================================================
shmax/shmin parameters
----------------------
cat /proc/sys/kernel/shmax
cat /proc/sys/kernel/shmin

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

redolog info
============
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
==============================================================
find out which sql's are generating more redo ##
******************

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24') WHEN,
dbms_lob.substr(sql_text,4000,1) SQL,
dhss.instance_number INST_ID,
dhss.sql_id,
executions_delta exec_delta,
rows_processed_delta rows_proc_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%USR_RACUNI_MV%'
AND ltrim(upper(dhst.sql_text)) NOT LIKE 'SELECT%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_number=dhs.instance_number
AND dhss.sql_id=dhst.sql_id
AND begin_interval_time BETWEEN to_date('11-01-28 13:00','YY-MM-DD HH24:MI')
AND to_date('11-01-28 14:00','YY-MM-DD HH24:MI')
;
==============================================================
## Following query will help you to find nummbers of archive generated per day .##

SELECT TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24'),
COUNT(*)
FROM V$LOGHIST
WHERE TO_CHAR(FIRST_TIME,'DD-MON-YY HH24') > '13-JUL-2008'
GROUP BY TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24')
ORDER BY TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24') ASC
OPP

==============================================================
1. Start the queues by logging into SQLPLUS as applsys:

DBMS_AQADM.START_QUEUE(queue_name => 'FND_CP_GSM_OPP_AQ');
DBMS_AQADM.START_QUEUE(queue_name => 'AQ$_FND_CP_GSM_OPP_AQTBL_E', enqueue =>FALSE, dequeue
=> TRUE);

==============================================================
memory parameters
sho parameter sga_max_size
sho parameter pga_aggregate_target
sho parameter olap_page_pool_size
sho parameter sga_target
sho parameter db_cache_size
sho parameter shared_pool_size
sho parameter shared_pool_reserved_size
sho parameter java_pool_size
sho parameter large_pool_size
sho parameter streams_pool_size

==============================================================
TEMP tablespace usage with datafiles
=====================================
set pages 1000
set lines 152
col tablespace_name for a20
col file_name for a40

set head off
select ' -- TEMP DETAILS -- ' from dual;
set head on
col file_name for a60
COLUMN DUMMY NOPRINT;
COMPUTE SUM OF MBYTES ON DUMMY;
COMPUTE SUM OF MAXMBYTES ON DUMMY;
BREAK ON DUMMY;
select null DUMMY,tablespace_name,file_name,autoextensible,bytes/1048576 Mbytes, maxbytes/1048576 maxMbytes,status
from dba_Temp_files
where tablespace_name like '%&TBSP%'
order by tablespace_name
/

Temp table space
=================

usage of temp tablespace
-------------------------
set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from
dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;

===============================================================
zipping a file
--------------

Zip xxx.log.zip xxx.log; > xxx.log

Ex:

zip bne.log.zip bne.log; > bne.log ( zipped bne.log and created new 0byte bne.log file)

find files of size xx
--------------------

find /auto_cmd_ctr -size +100000000c -xdev -exec du -sh {} \;
===============================================================

Index status
=============
SQL> SELECT index_name,INDEX_TYPE,TABLE_NAME,TABLE_TYPE,OWNER,status FROM all_indexes WHERE index_name like '%CSC_PROF_CHECK%';

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

Compile JSP's
-------------
11i
perl -x $JTF_TOP/admin/scripts/ojspCompile.pl --compile --quiet

R12
cd $FND_TOP/patch/115/bin perl ojspCompile.pl --compile --flush -p 2

for single jsp

ojspCompile.pl --compile -s 'AppsLocalLogin.jsp' --flush

===============================================================
vi command to replace
%s/ohspsnl08/ohspsnl09/g

==============================================================
For reoprt generation
======================
rwcon60 userid=scott/tiger@bs817 batch=yes source=$i stype=rdffile
dtype=repfile overwrite=yes compile_all=yes

rwcon60 userid=apps/AgwqrSKL source=XAHGR_TRUCKING_SCHEDULES.rdf stype=rdffile dtype=rdffile
dest=XAHGR_TRUCKING_SCHEDULES.rdf overwrite=yes batch=yes compile_all=yes

Convert from .rdf to rex
========================
rwconverter batch=yes apps/C0xKA2kh stype=rdffile source=ADDRESS_PROOF.rdf dtype=REXFILE dest=ADDRESS_PROOF.rex

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

Rebuild inventory
===============
./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/tsivai/oracle/product/111" ORACLE_HOME_NAME="Db11g_Home"

+ $ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

for conflict report
------------------
$ORACLE_HOME/OPatch/opatch apply -report -invPtrLoc $ORACLE_HOME/oraInst.loc

========================================================
Manually issue the adrelink command to relink one executable.

i.e - adrelink.sh force=y ranlib=y "FND FNDFS"
adrelink.sh force=y "ad adadmin"

To relink all AD executables:
'adrelink.sh force=y "ad all"'

****
opatch nrollback -ph 10031947 -id 8447875,9679852

===============================================================
Validate user/password
*********************

select FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE') from dual;
select fnd_message.get from dual;

Ex:
SQL> select FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','ubRD54x2') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','UBRD54X2')
--------------------------------------------------------------------------------
N
SQL> select fnd_message.get from dual;

GET
--------------------------------------------------------------------------------
PASSWORD_INVALID
===============================================================
Product information
--------------------

1 select a.APPLICATION_ID,a.APPLICATION_SHORT_NAME,a.application_name, decode(b.status,'I','Installed','S','Shared','N/A') STATUS,
2 PATCH_LEVEL from APPS.fnd_application_vl a, APPS.fnd_product_installations b
3* where a.application_id = b.application_id order by 2,1
===============================================================

Query hidden parameters
========================
SELECT a.ksppinm “Parameter”,
       b.ksppstvl “Session Value”,
       c.ksppstvl “Instance Value”
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE ‘/_%’ escape ‘/’
/

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

Partition name on a table
********************
SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='WF_LOCAL_USER_ROLES';

===============================================================
Cron job format (cronjob) (crontab)
************************************
Field Description Allowed Value
MIN Minute field 0 to 59
HOUR Hour field 0 to 23
DOM Day of Month 1-31          or * indicates every day of the month
MON Month field 1-12
DOW Day Of Week 0-6            or 1,3 means ( mon,wed) or 1-3 means mon,tue,wed)

@reboot = run at boot and reboot only
@yearly = run at midnight Jan 1 (0 0 1 1 *)
@annually = run at midnight Jan 1(0 0 1 1 *)
@monthly = run at midnight on the first day of every month (0 0 1 * *)
@weekly = run at midnight every Sunday (0 0 * * 0)
@daily = run at midnight every day (0 0 * * *)
@midnight = run at midnight (0 0 * * *)
@hourly = run on the first second of every hour (0 * * * *)

# Minute   Hour   Day of Month       Month          Day of Week        Command
# (0-59) (0-23)     (1-31)    (1-12 or Jan-Dec) (0-6 or Sun-Sat)
    0        2          12             *               0,6           /usr/bin/find

===============================================================
Find Front end active users
============================
select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');

============================================================
to change listner logfile
Action Plan
============

$> lsnrctl

LSNRCTL> set current_listener PFNA2I
LSNRCTL> SET LOG_FILE new.log
LSNRCTL> SAVE_CONFIG listener_name
LSNRCTL> exit

2)$mv /pfna2i/oracle/product/102/network/admin/pfna2i.log
/pfna2i/oracle/product/102/network/admin/pfna2i_bkp.log

LSNRCTL> SET LOG_FILE /pfna2i/oracle/product/102/network/admin/pfna2i.log
LSNRCTL> SAVE_CONFIG PFNA2I
LSNRCTL> exit

=============================================================
To copy responsibility for a user ex sysadmin
************************

To download

FNDLOAD apps/Tr1ology 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct abc.ldt FND_USER USER_NAME='xxxx'

to upload these responsibilities to other user(yyyy) open the above ldt(abc.ldt) and replace the the username xxxx by yyyy
=============================================================

Responsibilities for a user
*******************

COL USER_NAME FOR A17
COL RESPONSIBILITY_NAME FOR A30
COL END_DATE FOR A20

select a.USER_NAME, b.RESPONSIBILITY_ID, c.RESPONSIBILITY_NAME, b.END_DATE
from apps.FND_USER a, apps.FND_USER_RESP_GROUPS b, apps.FND_RESPONSIBILITY_VL c
where a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=c.RESPONSIBILITY_ID
and user_name = UPPER('&user_name');

===================================================================
submit concurrent program backend
**********************************

Syntax: CONCSUB <ORACLE ID> <Responsibility Application Short Name> <Responsibility Name> <User Name> [WAIT=<Wait Flag] CONCURRENT <Concurrent Program Application Short Name> <Concurrent Program Name> [START=<Requested Start Date>] [REPEAT_DAYS=<Repeat Interval>] [REPEAT_END=<Request Resubmission End Date>] <Concurrent Program Arguments ...>

Example: CONCSUB SCOTT/TIGER SYSADMIN 'System Administrator' SYSADMIN WAIT=Y CONCURRENT FND FNDMNRMT START='"01-JAN-2000 23:00:00"' REPEAT_DAYS=1 REPEAT_END='"01-JAN-2001 23:59:00"' Y 0 0

Example
========
+ ran below concurrent programs from backend

Sync responsibility role data into the WF table.

Workflow Directory Services User/Role Validation

CONCSUB APPS/*** SYSADMIN 'System Administrator' SYSADMIN WAIT=Y CONCURRENT FND AFFURGO2 START='"24-FEB-2012 01:00:00"' REPEAT_DAYS=1 REPEAT_END='"24-FEB-2012 23:59:00"' Y 0 0

CONCSUB APPS/*** SYSADMIN 'System Administrator' SYSADMIN WAIT=Y CONCURRENT FND FNDWFDSURV START='"24-FEB-2012 01:00:00"' REPEAT_DAYS=1 REPEAT_END='"24-FEB-2012 23:59:00"' Y 0 0

***

Submit concurrent request from backend
======================================

for ex : Workflow Directory Services User/Role Validation

SET SERVEROUTPUT ON;
EXEC DBMS_OUTPUT.ENABLE(1000000);
DECLARE
   x_conc_id                     NUMBER;
   l_user_id                     NUMBER;
   l_appl_id                     NUMBER;
   l_resp_id                     NUMBER;
   l_phase_code                  VARCHAR2(10);
BEGIN
   SELECT user_id, responsibility_id, responsibility_application_id
     INTO l_user_id, l_resp_id, l_appl_id
     FROM fnd_user_resp_groups
    WHERE user_id = (SELECT user_id
                       FROM fnd_user
                      WHERE user_name = 'SYSADMIN')
      AND responsibility_id =
                         (SELECT responsibility_id
                            FROM fnd_responsibility_vl
                           WHERE responsibility_name = 'System Administrator');

   fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

   /* Submit active user */

   x_conc_id :=
      fnd_request.submit_request (application                   => 'FND'
                                , program                       => 'FNDWFDSURV'
                                , description                   => NULL
                                , start_time                    => SYSDATE
                                , sub_request                   => FALSE
                                , argument1   => 10000
                                , argument2   => 'Y'
                                , argument3   => 'Y'
                                , argument4   => 'Y'
                                 );

   COMMIT;

   select phase_code
   into   l_phase_code
   from   fnd_amp_requests_v
   where request_id = x_conc_id;

    WHILE (l_phase_code = 'R' or l_phase_code = 'P' )
      LOOP

      dbms_output.put_line('Request is running ..............');

      select phase_code
      into   l_phase_code
      from   fnd_amp_requests_v
      where request_id = x_conc_id;

       dbms_lock.sleep(5.00);

      END LOOP;

   dbms_output.put_line('Request completed successfully ..............');

   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (   'Concurrent requests not submitted successfully : '
                               || SQLERRM
                              );
   END;
/

How To Run The Workflow Directory Services Concurrent Program From The SQLplus Prompt (Doc ID 1213304.1)

===================================================================
identify PSU (patchset) version
===============================
col ACTION for a12
col VERSION for a10
col ID for 99999999
col COMMENTS for a25
col BUNDLE_SERIES for a8 heading 'BUNDLE|SERIES'

select ACTION,VERSION,ID,COMMENTS,BUNDLE_SERIES
   from registry$history
where BUNDLE_SERIES like '%PSU%';

==================================================================
Clear FND_NODES
***************
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
      commit;

Node details
============
Set pagesize 100
set linesize 250
Col Node_name for a15
col Node_name2 for a15
col CONCURRENT_QUEUE_NAME for a40
Col VIRTUAL_IP for a18
col application_short_name format a11 heading 'Application|Short Name'
select
     NODE_NAME,
     decode(STATUS,'Y','ACTIVE','INACTIVE') Status,
     decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr,
     decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms,
     decode(SUPPORT_WEB,'Y','Web', 'No') WebServer,
     decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin,
     decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database,
     VIRTUAL_IP
   from APPS.fnd_nodes
   where node_name != 'AUTHENTICATION'
order by 1,2
/

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

Conc Manager details
****************
select CONCURRENT_QUEUE_NAME,NODE_NAME, NODE_NAME2, application_short_name
   from apps.fnd_concurrent_queues_vl t1,
         apps.fnd_application t2
where ENABLED_FLAG='Y'
      and t1.application_id = t2.application_id
order by 1,2
/

Application user sessions
*********************
(Doc ID 295206.1)

select b.SID, b.SERIAL#,B.STATUS,d.USER_NAME from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and d.user_name = 'AMARTIN';
=================================================================
Tablespace Usage with datafiles
************************
set echo off
set lines 152
set pages 1000
col tablespace_name for a20
col file_name for a60
COLUMN DUMMY NOPRINT;
COMPUTE SUM OF MBYTES ON DUMMY;
COMPUTE SUM OF MAXMBYTES ON DUMMY;
BREAK ON DUMMY;
select null DUMMY, tablespace_name,file_name,file_id,autoextensible,bytes/1048576 Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%&TBSP%'
order by tablespace_name,file_name
/
==============================================================

SMTP testing from nodelevel

$ telnet ohschmr01 25
Trying 140.85.183.107...
Connected to ohschmr01.
Escape character is '^]'.
220-ohschmr01.oracleoutsourcing.com ESMTP ready at Wed, 10 Oct 2012 02:09:02 -0500
220 Oracle Corporation - Unauthorized usage or access is prohibited
?
500 5.5.1 Command unrecognized: "?"
HELP
214-2.0.0 This is sendmail version 8.13.1
214-2.0.0 Topics:
214-2.0.0       HELO    EHLO    MAIL    RCPT    DATA
214-2.0.0       RSET    NOOP    QUIT    HELP    VRFY
214-2.0.0       EXPN    VERB    ETRN    DSN     AUTH
214-2.0.0       STARTTLS
214-2.0.0 For more info use "HELP <topic>".
214-2.0.0 To report bugs in the implementation send email to
214-2.0.0       sendmail-bugs@sendmail.org.
214-2.0.0 For local information send email to Postmaster at your site.
214 2.0.0 End of HELP info
HELO ohschmr01
250 ohschmr01.oracleoutsourcing.com Hello ohschmr01.oracleoutsourcing.com [140.85.183.107], pleased to meet you
MAIL
501 5.5.2 Syntax error in parameters scanning ""
Mail From
501 5.5.2 Syntax error in parameters scanning "From"
Mail From : wftchmri@atoracle.com
250 2.1.0 wftchmri@atoracle.com... Sender ok
RCPT to:abc.x.bbc@ORCL.com
250 2.1.5 abc.x.bbc@ORCL.com... Recipient ok
data
354 Enter mail, end with "." on a line by itself
Hi hello h r u
.
250 2.0.0 q9A792Jr003660 Message accepted for delivery
quit
221 2.0.0 ohschmr01.oracleoutsourcing.com closing connection
Connection closed by foreign host.

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

change sysadmin user password

FNDCPASS apps/V5bLD5lc 0 Y system/W4bMC3ob USER SYSADMIN A24d2412

==============================================================
Remove the 3rd line:
****************

sed '3d' fileName.txt

Remove the interval between lines 7 and 9:

sed '7,9d' filename.txt

===================================================================
datainstaller

$AFJVAPRG oracle.apps.per.DataInstall apps #APPSPWD# thin #CUST_DB_SERVER#:#INSTANCE_DB_PORT#:#UPPER_SID# (Note:140511.1)

===================================================================
PID details

/proc/<pid>/fd
Eg: /proc/28935/fd
===========================================================

rman backup status

This script will report on all backups – full, incremental and archivelog backups -

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups -

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;
=================================================================

++++ Logo +++

Note 174219.1 - How To Change The Logo In The Oracle Application Menu
Note 849752.1 - R12 Login Page: How to Personalize the Logo ?
Note 741459.1 - Tips For Personalizing The E-Business Suite r12 Login Page (MainLoginPG)
Note 602995.1 - How To Change The Standard Oracle Logo in R12 Forms
Note 551795.1 - How to change the default branding on the homepage which shown as "E-Business Suite" ?
Note 421636.1 - How to replace the default Oracle Logo with a Customized Logo?

==============================================================
change AUTHENTICATION to SECURE OR OFF
To login to forms directly

java oracle.apps.fnd.security.AdminAppServer apps/<apps_pwd> AUTHENTICATION OFF DBC=$FND_SECURE/<TWO_TASK>.dbc
==============================================================
Compile flexfield manually

fdfcmp <oracleid>/<password> 0 Y D <ApplShortName> <DescFlexName> ?

fdfcmp apps/H4aLG5cb 0 Y D 'CHV' '$SRS$.CHVPRSCH'

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

adop phase=<prepare, apply> abandon=no restart=yes workers=4 wait_on_failed_job=yes -> restarts from where it stopped and wait if fails
adop phase=<prepare, apply> abandon=yes restart=yes workers=4 wait_on_failed_job=yes -> restarts from scratch and wait if fails
adop phase=<prepare, apply> abandon=yes restart=yes workers=4 autoskip=yes -> restarts from scratch and skips the errors and record in autoskip.log

=================================================================
decrypt weblogic password

1. Create a script decrypt_password.py in $DOMAIN_HOME/security directory and paste the following code into it:

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

from weblogic.security.internal import *
from weblogic.security.internal.encryption import *
encryptionService = SerializedSystemIni.getEncryptionService(".")
clearOrEncryptService = ClearOrEncryptedService(encryptionService)

# Take encrypt password from user
pwd = raw_input("Paste encrypted password ({AES}fk9EK...): ")

# Delete unnecessary escape characters
preppwd = pwd.replace("\\", "")

# Display password
print "Decrypted string is: " + clearOrEncryptService.decrypt(preppwd)

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

2. Set domain environment variables

source $DOMAIN_HOME/bin/setDomainEnv.sh
3. Get encrypted password, in this example from boot.properties file of AdminServer

4. Navigate to $DOMAIN_HOME/security directory and run the following command to start decryption:

cd $DOMAIN_HOME/security

java weblogic.WLST decrypt_password.py

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

Please enter encrypted password (Eg. {AES}fk9EK...): {AES}jkIkkdh693dsyLt+DrKUfNcXryuHKLJD76*SXnPqnl5oo\=
Decrypted string is: welcome01

example:

[apdmsa1c@vmohsmsan004 <RUN> ]$ pwd
/dmsa1c/applmgr/fs1/FMW_Home/user_projects/domains/EBS_domain_dmsa1c
[apdmsa1c@vmohsmsan004 <RUN> ]$ cd security/
[apdmsa1c@vmohsmsan004 <RUN> ]$ vi decrypt_password.py
[apdmsa1c@vmohsmsan004 <RUN> ]$ java weblogic.WLST decrypt_password.py

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

Paste encrypted password ({AES}fk9EK...): {AES}nRwZgIecHhpFdol/YtnExJNbySzalAI+Zm+Hg3maeR8=
Decrypted string is: Bfv2rtQs
[apdmsa1c@vmohsmsan004 <RUN> ]$

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

ad_adop_sessions
                       'N' - 'NOT STARTED',
                       'R' - 'RUNNING',
                       'F' - 'FAILED',
                       'C' - 'COMPLETED',
                       'P' - 'ACTIVE',
                       'Y' - 'COMPLETED',
                       'X' - 'NOT APPLICABLE',
                       '0' - 'FORCE_SHUTDOWN_BEGIN',
                       '1' - 'FORCE_SHUTDOWN_COMPLETED_DB_CUTOVER_BEGIN',
                       '3' - 'DB_CUTOVER_COMPLETED_FLIP_SNAPSHOTS_BEGIN',
                       'D' - 'FLIP_SNAPSHOTS_COMPLETED_FS_CUTOVER_BEGIN',
                       '4' - 'FS_CUTOVER_COMPLETED_ADMIN_STARTUP_BEGIN',
                       '5' - 'ADMIN_STARTUP_COMPLETED_FORCE_STARTUP_BEGIN',
                       '6' - 'FORCE_STARTUP_COMPLETED'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

fnd_oam_context_files entries for patch fs
===========================================

SELECT extractValue(XMLType(TEXT),'//host[@oa_var="s_hostname"]'),
extractValue(XMLType(TEXT),'//oacore_server_ports'),
extractValue(XMLType(TEXT),'//forms_server_ports'),
extractValue(XMLType(TEXT),'//oafm_server_ports'),
extractValue(XMLType(TEXT),'//forms-c4ws_server_ports'),
extractValue(XMLType(TEXT),'//oaea_server_ports')
from fnd_oam_context_files
where name not in ('TEMPLATE','METADATA')
and (status is null or status !='H')
and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type')='patch'
and CTX_TYPE = 'A';

fnd_oam_context_files entries for run fs
===========================================
SELECT extractValue(XMLType(TEXT),'//host[@oa_var="s_hostname"]'),
extractValue(XMLType(TEXT),'//oacore_server_ports'),
extractValue(XMLType(TEXT),'//forms_server_ports'),
extractValue(XMLType(TEXT),'//oafm_server_ports'),
extractValue(XMLType(TEXT),'//forms-c4ws_server_ports'),
extractValue(XMLType(TEXT),'//oaea_server_ports')
from fnd_oam_context_files
where name not in ('TEMPLATE','METADATA')
and (status is null or status !='H')
and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type')='run'
and CTX_TYPE = 'A';

===================================================================
load context entries to database (12.2)
This command is executed on the RUN file system to upload the PATCH context file:
$ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer \
action=upload contextfile=<full path to patch context file> \
logfile=/tmp/patchctxupload.log

===================================================================
MWA
****

start mwa server

cd SCRIPT_TOP or $MWA_TOP/bin

mwactl.sh start 30002 &
mwactl.sh start_dispatcher &

===============================================================
Product details
================
select decode(nvl(a.APPLICATION_short_name,'Not Found'),
          'SQLAP','AP','SQLGL','GL','OFA','FA',
          'Not Found','id '||to_char(fpi.application_id),
          a.APPLICATION_short_name) apps,
          decode(nvl(o.ORACLE_username,'Not Found'),
                 'Not Found','id '||to_char(fpi.oracle_id),
                 o.ORACLE_username) ORACLE_username,
          decode(fpi.db_status,'I','Installed','L','Custom','N','Not Installed',
          'S','Shared',fpi.db_status) "DB Status",
          fpi.install_group_num,
          fpi.sizing_factor,
          fpi.tablespace, fpi.index_tablespace
from apps.fnd_oracle_userid o, apps.fnd_application a, apps.fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
    and fpi.oracle_id = o.oracle_id(+)
order by 1,2
/

Product Schema                    DB Status     Inst Grp Size% Main TS              Index TS
-------- ------------------------- ------------- -------- ------ -------------------- --------------------
ABM      ABM                       Installed            0    100 ABMD                 ABMX

================================================================
##################################################
Used/Free % Tablespace
#################################################

undefine tbsp
set lines 152
set echo off

col tablespace_name for a20
select     utbs.tablespace_name,
    round(utbs.mb) "Allocated Used/Unused MB",
    round(Ftbs.mb) "Allocated_Free MB",
    round((100/utbs.mb)*Ftbs.mb) "%Allocated_Free MB",
    decode(sign(round(utbs.Maxmb-utbs.mb)),-1,0,round(utbs.Maxmb-utbs.mb)) "Space_AutoExtensible MB",
    Ftbs.MaxBytes "MaxChunk MB"
from
(select ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from dba_data_files ddf
group by ddf.tablespace_name) Utbs,
(select dfs.tablespace_name,sum(dfs.bytes)/1048576 MB,max(dfs.bytes)/1048576 MaxBytes
from dba_free_space dfs
group by dfs.tablespace_name) Ftbs
where utbs.tablespace_name=ftbs.tablespace_name
and utbs.tablespace_name like '%&&TBSP%'
order by round(Ftbs.mb)
/

set echo off
set lines 152
set pages 1000
col tablespace_name for a20
col file_name for a60
COLUMN DUMMY NOPRINT;
COMPUTE SUM OF MBYTES ON DUMMY;
COMPUTE SUM OF MAXMBYTES ON DUMMY;
BREAK ON DUMMY;
select null DUMMY, tablespace_name,file_name,file_id,autoextensible,bytes/1048576 Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%&TBSP%'
order by tablespace_name,file_name
/

--------------------------------------------------------
Tablespace & Datafile Size Information
--------------------------------------------------------
set lines 152
col tablespace_name for a20
col file_name for a50

select tablespace_name,file_name,autoextensible,bytes/1048576 Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%&TBSP%'
order by tablespace_name
/

select file_id,sum(bytes)/1048576 Free_Mbytes
from dba_free_space
where tablespace_name like '%&TBSP%'
group by file_id
order by Free_mbytes desc
/

A for Apps D for DBA

Saturday, 12 March 2016

DBA Scripts used regularly

1 comment: