Notes

  --- 1 -- log sequence

select * from v$archived_log order by stamp desc;


select * from v$archive_dest_status
-----


---2 -- Workflow Mailer Status

SELECT component_name as Component, component_status as Status FROM apps.fnd_svc_components WHERE component_type = 'WF_MAILER';

----

---3 ---Gather schema statistic For Last week  For Apps schema :

select count(1)
from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r
where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id
and p.user_concurrent_program_name in (
'OnDemand Gather Schema Statistics',
'Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)'
)
and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-7);

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

--- Gather Schema Statistics


SELECT distinct a.request_id
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.ARGUMENT_TEXT
,a.phase_code,a.status_code,actual_start_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_concurrent_programs_vl p
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
and ctl.user_concurrent_program_name in (
'OnDemand Gather Schema Statistics',
'Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)'
)
and (a.phase_code = 'C' and a.status_code= 'C' and a.actual_start_date >= sysdate-100)
order by actual_start_date desc;


--- Gather Schema Statistics


SELECT distinct a.request_id
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.ARGUMENT_TEXT
,a.phase_code,a.status_code,actual_start_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_concurrent_programs_vl p
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
and ctl.user_concurrent_program_name in (
'OnDemand Gather Schema Statistics',
'Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)'
)
and (a.phase_code = 'C' and a.status_code= 'C' and a.actual_start_date >= sysdate-7);




---Gather schema statistic For Last week Non-Apps schemas :

---- set pages 0 echo off head off termout off verify off feed off lines 1000
select count(1)
from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r
where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id
and p.user_concurrent_program_name in (
'Gather Statistics for Non-Apps Schema'
)
and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-7)

-----------

---4 -----locked objects
SELECT c.owner,b.client_identifier,c.object_name,c.object_type, b.SID, b.serial#, b.status,
       b.osuser, b.machine
  FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

 ---5 -------check long running concurrent request

SELECT a.request_id
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name  concurrent_program_name
--,a.phase_code,a.status_code
,DECODE(a.phase_code,
            'C','Completed',
            'I','Incactive',
            'P','Pending',
            'R','Running'
           ) phase,
    DECODE(a.status_code,
            'D','Cancelled',
            'U','Disabled',
            'E','Error',
            'M','No Manager',
            'R','Normal',
            'I','Normal',
            'C','Normal',
            'H','On Hold',
            'W','Paused',
            'B','Resuming',
            'P','Scheduled',
            'Q','Standby',
            'S','Suspended',
            'X','Terminated',
            'T','Terminating',
            'A','Waiting',
            'Z','Waiting',
            'G','Warning','N/A'
            ) status
,actual_start_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >=15
ORDER BY a.actual_start_date DESC;


----------e.   To get the input the Concurrent request number and if it is running then give the Oracle Sid, serial# , unix process id, along with program or module it is linked with



SELECT d.sid, d.serial# ,d.process , c.SPID, fcpv.user_concurrent_program_name, a.REQUEST_ID, fa.APPLICATION_NAME,
d.osuser, replace(d.machine,'HEADLANDS\',null) user_srvr, a.REQUESTED_START_DATE
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d,
fnd_concurrent_programs_vl fcpv,
fnd_application_tl  fa
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND fcpv.concurrent_program_id = a.CONCURRENT_PROGRAM_ID
AND a.RESPONSIBILITY_APPLICATION_ID = fa.APPLICATION_ID
AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >=15
ORDER BY a.actual_start_date DESC;

-----AND a.request_id = &Request_ID





---6 -----Checking status of all the Concurrent Managers
--Concurrent Manager Status with Node

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


select CONCURRENT_QUEUE_NAME,MAX_PROCESSES,RUNNING_PROCESSES,TARGET_PROCESSES,CACHE_SIZE,SLEEP_SECONDS  from apps.fnd_concurrent_queues;


---7 ----- Invalid Objects.

select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS='INVALID' order by owner;

count(*) from


 
---8--Trace enabled Concurrent programs list

select a.CONCURRENT_PROGRAM_ID, b.USER_CONCURRENT_PROGRAM_NAME "Program_Name",a.ENABLE_TRACE, a.CONCURRENT_PROGRAM_NAME "Short_Name", a.APPLICATION_ID
from apps.fnd_concurrent_programs a, apps.fnd_concurrent_programs_tl b
where a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and a.ENABLE_TRACE='Y';

---9--- Wallet status


select * from gv$encryption_wallet

----10---- Table space check


SELECT   a.tablespace_name, (a.BYTES / 1024/1024) || 'MB' AS total,
           (b.BYTES / 1024/1024) || 'MB' AS free,
           ROUND(((a.BYTES-b.BYTES)/1024/1024) ,2) || 'MB' AS used,
           (b.largest / 1024/1024) || 'MB' AS largest_block,
           ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_used,
           ROUND (100 - ((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_free
      FROM (SELECT   tablespace_name, SUM (BYTES) BYTES
                  FROM dba_data_files
              GROUP BY tablespace_name) a,
             (SELECT   tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) largest
                 FROM dba_free_space
             GROUP BY tablespace_name) b
      WHERE a.tablespace_name = b.tablespace_name(+)
union
select sh.tablespace_name,
sum(sh.bytes_used + sh.bytes_free)/1048576 || 'MB' total,
sum(sh.bytes_free)/1048576 || 'MB' free,
sum(sh.bytes_used)/1048576 || 'MB' used,
max(BYTES_FREE)/1048576 || 'MB',
ROUND(sum(sh.bytes_used) /sum(sh.bytes_used + sh.bytes_free) *100, 2) percent_used,
ROUND(sum(sh.bytes_free) /sum(sh.bytes_used + sh.bytes_free) *100, 2) percent_free
from v$temp_space_header sh
group by tablespace_name
order by 7;

----11

Temp:




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




select * from v$process p,v$session s where s.paddr=p.addr and s.sid=171 and s.serial#=34515

select s.sid,s.serial#,s.event,s.module,status,s.sql_id,ss.sql_text,s.program  from v$session s ,v$sql ss where  status='ACTIVE' and username='DEMANTRA' and s.sql_id=ss.sql_id;

set linesize 80
col sess for a15

SELECT DECODE(request,0,'Holder: ','Waiter: ')||trim(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
/



--RAC Instance Status
select * from gv$instance;

-----WF pending open mails count :
select count(*) from apps.wf_notifications
where mail_status='MAIL'
and status='OPEN'
and trunc(begin_date)=trunc(sysdate);



--long Running request

SELECT a.request_id
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 run_mins
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
, fu.user_name
--,a.phase_code,a.status_code
,DECODE(a.phase_code,
            'C','Completed',
            'I','Incactive',
            'P','Pending',
            'R','Running'
           ) phase,
    DECODE(a.status_code,
            'D','Cancelled',
            'U','Disabled',
            'E','Error',
            'M','No Manager',
            'R','Normal',
            'I','Normal',
            'C','Normal',
            'H','On Hold',
            'W','Paused',
            'B','Resuming',
            'P','Scheduled',
            'Q','Standby',
            'S','Suspended',
            'X','Terminated',
            'T','Terminating',
            'A','Waiting',
            'Z','Waiting',
            'G','Warning','N/A'
            ) status
,to_char(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') actual_start_date
,to_char(actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') actual_completion_date
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
--AND to_char(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') >= '22-MAY-2012 09:00:00'
--AND to_char(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') <= '22-MAY-2012 10:00:00'
AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >=15
ORDER BY a.actual_start_date DESC;





select Ct.User_Concurrent_Queue_Name "Queue Name",
       decode(phase_code,'P','Pending', 'R','Running', 'C', 'Completed', 'I', 'Inactive' ) "Phase Code",
       decode(status_code, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'G','Warning',
        'H','On Hold', 'I','Normal', 'M','No Manager', 'P','Scheduled', 'Q','Standby', 'R','Normal', 'S','Suspended',
        'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting' ) "Status Code",
count(1) Total
from FND_CONCURRENT_WORKER_REQUESTS Wr, fnd_concurrent_queues_tl Ct
where Wr.concurrent_queue_name not like 'OAMGCS%'
and hold_flag <> 'Y'
and requested_start_date <= sysdate
and Wr.Concurrent_Queue_ID=Ct.Concurrent_Queue_Id
group by Ct.User_Concurrent_Queue_Name, phase_code, status_code
order by 1



--- Concurrent requests executions statistics of previous day

SELECT  
         fcpt.user_concurrent_program_name program_name
        ,COUNT (1) request_count
        ,MIN ( (fcr.actual_completion_date - fcr.actual_start_date) * 1440) "MIN_TIME Mins"
        ,MAX ( (fcr.actual_completion_date - fcr.actual_start_date) * 1440) "MAX_TIME Mins"
        ,AVG ((fcr.actual_completion_date - fcr.actual_start_date) * 1440) "AVG_TIME Mins"
    FROM apps.fnd_concurrent_requests fcr
        ,apps.fnd_concurrent_programs fcp
        ,apps.fnd_concurrent_programs_tl fcpt
        ,apps.fnd_user fu
   WHERE 1 = 1
     AND fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcp.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND fcp.application_id = fcpt.application_id
     AND fcr.requested_by = fu.user_id
     AND fcpt.LANGUAGE = 'US'
     AND trunc(fcr.actual_start_date) >= '12-Aug-2012'
     AND trunc(fcr.actual_start_date) <= '18-Aug-2012'
    AND (fcr.actual_completion_date - fcr.actual_start_date) * 1440 > 0.0001
     AND fcr.phase_code='C'
    AND fcr.status_code='C'
    GROUP BY fcpt.user_concurrent_program_name
order by 5 desc




----- Concurrent requests executions statistics of weekly

    SELECT  
         fcpt.user_concurrent_program_name program_name
        ,COUNT (1) request_count
        ,MIN ( (fcr.actual_completion_date - fcr.actual_start_date) * 1440) "MIN_TIME Mins"
        ,MAX ( (fcr.actual_completion_date - fcr.actual_start_date) * 1440) "MAX_TIME Mins"
        ,AVG ((fcr.actual_completion_date - fcr.actual_start_date) * 1440) "AVG_TIME Mins"
    FROM apps.fnd_concurrent_requests fcr
        ,apps.fnd_concurrent_programs fcp
        ,apps.fnd_concurrent_programs_tl fcpt
        ,apps.fnd_user fu
   WHERE 1 = 1
     AND fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcp.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND fcp.application_id = fcpt.application_id
     AND fcr.requested_by = fu.user_id
     AND fcpt.LANGUAGE = 'US'
     AND trunc(fcr.actual_start_date) >= '11-jun-2012'
     AND trunc(fcr.actual_start_date) <= '11-jun-2012'
     AND (fcr.actual_completion_date - fcr.actual_start_date) * 1440 > 0.001
     AND fcr.phase_code='C'
     AND fcr.status_code='C'
     GROUP BY fcpt.user_concurrent_program_name
order by 5 desc




-- Concurrent request details based concurrent program name

SELECT a.request_id
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
, fu.user_name,a.ARGUMENT_TEXT
--,a.logfile_name
--,a.phase_code,a.status_code
 ,DECODE(a.phase_code,
            'C','Completed',
            'I','Incactive',
            'P','Pending',
            'R','Running'
           ) phase,
    DECODE(a.status_code,
            'D','Cancelled',
            'U','Disabled',
            'E','Error',
            'M','No Manager',
            'R','Normal',
            'I','Normal',
            'C','Normal',
            'H','On Hold',
            'W','Paused',
            'B','Resuming',
            'P','Scheduled',
            'Q','Standby',
            'S','Suspended',
            'X','Terminated',
            'T','Terminating',
            'A','Waiting',
            'Z','Waiting',
            'G','Warning','N/A'
            ) status
,TO_CHAR(actual_start_date, 'DD-MON-YY HH24:MI') actual_start_date
,TO_CHAR(actual_completion_date, 'DD-MON-YY HH24:MI') actual_completion_date
--,actual_start_date,actual_completion_date
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
/*,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins */
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.phase_code = 'C'
--OR  a.phase_code = 'R' OR  a.phase_code = 'E'
----AND a.status_code = 'C'
--OR a.status_code = 'R'
--and  a.status_code = 'E'
AND a.actual_start_date >= sysdate-100  and a.actual_start_date <= sysdate
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
--and a.request_id='5035452';
--and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 20
and ctl.user_concurrent_program_name='PFC Bundle Invoices for Training Services Customers Program'
--and ctl.user_concurrent_program_name like 'PFC%Active%Users'
--and ctl.user_concurrent_program_name like 'PFC%'
--and ctl.user_concurrent_program_name='PFC DH BudgetHolder Commitments By Tasks'
--and ctl.user_concurrent_program_name='PFC DH BudgetHolder Commitments By Tasks'
--ORDER BY duration_in_hours DESC;
--and ctl.user_concurrent_program_name='PFC Populate Timesheet Exception'
--and ctl.user_concurrent_program_name='PFC UAE EMployee Assignments Conversion'
--ORDER BY actual_completion_date DESC;
order by a.request_id desc;






----Long running request with Incompatability



SELECT distinct a.request_id, frt.responsibility_name,
       c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name concurrent_program_name
--,a.phase_code,a.status_code
,case when INCOMPATIBILITY_TYPE is null then 'NO' else 'Yes' end Incompatability
       ,
       DECODE (a.phase_code,
               'C', 'Completed',
               'I', 'Incactive',
               'P', 'Pending',
               'R', 'Running'
              ) phase,
       DECODE (a.status_code,
               'D', 'Cancelled',
               'U', 'Disabled',
               'E', 'Error',
               'M', 'No Manager',
               'R', 'Normal',
               'I', 'Normal',
               'C', 'Normal',
               'H', 'On Hold',
               'W', 'Paused',
               'B', 'Resuming',
               'P', 'Scheduled',
               'Q', 'Standby',
               'S', 'Suspended',
               'X', 'Terminated',
               'T', 'Terminating',
               'A', 'Waiting',
               'Z', 'Waiting',
               'G', 'Warning',
               'N/A'
              ) status
      --,actual_start_date
       ,
       TO_CHAR (actual_start_date,
            'DD-MON-YYYY HH24:MI:SS') actual_start_date, fu.user_name,
       (NVL (actual_completion_date, SYSDATE) - actual_start_date)
            * 1440 mins,
       (SELECT   AVG (NVL (a2.actual_completion_date - a2.actual_start_date,0))
            * 1440 avg_run_time
       FROM applsys.fnd_concurrent_requests a2,
            applsys.fnd_concurrent_programs c2
       WHERE c2.concurrent_program_id = c.concurrent_program_id
            AND a2.concurrent_program_id = c2.concurrent_program_id
            AND a2.program_application_id = c2.application_id
            AND a2.phase_code || '' = 'C') avg_mins,
       ROUND ((actual_completion_date - requested_start_date),
                2
               )
       * 24 duration_in_hours,a.actual_start_date
       FROM applsys.fnd_concurrent_requests a,
       applsys.fnd_concurrent_processes b,
       applsys.fnd_concurrent_queues q,
       applsys.fnd_concurrent_programs c,
       applsys.fnd_concurrent_programs_tl ctl,
       apps.fnd_concurrent_program_serial cps,
       apps.fnd_user fu,
       apps.fnd_responsibility_tl frt
   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 a.phase_code IN ('P', 'R')
--AND a.phase_code = 'R'
--AND a.status_code in ('P','R','D','H','A','Z','G','Q','W','S','B','T','C','I')
--AND a.status_code = 'R' or a.status_code = 'P'
   AND b.queue_application_id = q.application_id
   AND b.concurrent_queue_id = q.concurrent_queue_id
   AND ctl.concurrent_program_id = c.concurrent_program_id
   AND a.requested_by = fu.user_id
   AND a.responsibility_id = frt.responsibility_id
   AND ctl.concurrent_program_id = cps.running_concurrent_program_id (+)
   AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >=15
   ORDER BY a.actual_start_date DESC;









SELECT  
         fcpt.user_concurrent_program_name program_name
        ,COUNT (1) request_count
        ,MIN ( (fcr.actual_completion_date - fcr.actual_start_date) * 1440) "MIN_TIME Mins"
        ,MAX ( (fcr.actual_completion_date - fcr.actual_start_date) * 1440) "MAX_TIME Mins"
        ,AVG ((fcr.actual_completion_date - fcr.actual_start_date) * 1440) "AVG_TIME Mins"
    FROM apps.fnd_concurrent_requests fcr
        ,apps.fnd_concurrent_programs fcp
        ,apps.fnd_concurrent_programs_tl fcpt
        ,apps.fnd_user fu
   WHERE 1 = 1
     AND fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcp.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND fcp.application_id = fcpt.application_id
     AND fcr.requested_by = fu.user_id
     AND fcpt.LANGUAGE = 'US'
     AND trunc(fcr.actual_start_date) >= '12-jun-2012'
     AND trunc(fcr.actual_start_date) <= '12-jun-2012'
    AND (fcr.actual_completion_date - fcr.actual_start_date) * 1440 > 0.001
     AND fcr.phase_code='C'
    AND fcr.status_code='C'
    GROUP BY fcpt.user_concurrent_program_name
order by 5 desc


select request_id
from   apps.fnd_concurrent_requests
where  status_code in ('Q','I')
and    requested_start_date > SYSDATE
and    hold_flag = 'N';



select * from apps.fnd_concurrent_requests;

select * from apps.fnd_concurrent_programs;

select * from apps.fnd_concurrent_programs_tl;

select * from apps.fnd_user where user_id = '4521';


select  request_id,requested_by,concurrent_program_name,user_name,
user_concurrent_program_name,phase_code,status_code,requested_start_date
from apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.fnd_concurrent_programs_tl c,apps.fnd_user d
where b.concurrent_program_id=c.concurrent_program_id
and a.requested_by = d.user_id
--and ;




====================================================================================================================
workflow for current date status:
---------------------------------

select count(*) from apps.wf_notifications
where mail_status='MAIL'
and status='OPEN'
and trunc(begin_date)=trunc(sysdate);

SELECT component_name as Component, component_status as Status FROM apps.fnd_svc_components WHERE component_type = 'WF_MAILER';


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

-- fnd_conc_req_summary_v

select fcr.USER_CONCURRENT_PROGRAM_NAME,fcr.ACTUAL_START_DATE,fcr.ACTUAL_COMPLETION_DATE,
trunc(mod((fcr.actual_completion_date-fcr.actual_start_date)*24,24))||':'||trunc(mod((fcr.actual_completion_date-fcr.actual_start_date)*24*60,60))||':'||trunc(mod((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60,60))  exec_duration,
fcr.ARGUMENT_TEXT, fcr.phase_code,FCR.STATUS_CODE
ARGUMENT_TEXT from apps.fnd_conc_req_summary_v fcr where
fcr.phase_code='C' AND
FCR.STATUS_CODE = 'C' AND
fcr.PARENT_REQUEST_ID=7021753

File version:

select instance_name SID, name, type, o.status, text
from v$instance, DBA_SOURCE, DBA_OBJECTS o
where name =o.object_name and name = 'PA_PROJ_ACCUM_MAIN'
and type = o.object_type and line=2
order by name, type;



GRANT SELECT ON <TABLE> [B]TO USER  [/B]






select * from dba_users where username='PETEREADONLY';

select * from dba_users order by created desc;


select d.name,f.phyrds,f.phyrds/f.readtim,f.phywrts,f.phywrts/f.writetim
From v$filestat f,  v$datafile d Where f.file#= d.file#;


grep -i appslogin $CONTEXT_FILE




SELECT SID, EVENT, P1, P2 , P3 FROM v$session_wait where event='db file sequential read';


SELECT owner , segment_name , segment_type, partition_name FROM dba_extents WHERE file_id = 145 AND 245172 BETWEEN block_id AND block_id + blocks -1;




ps -ef |grep -i local=no |grep -i EUCVSPCH|awk '{print $2}'|xargs kill -9

ps -ef |grep -i FNDLIBR |grep -i NAERPLA1|awk '{print $2}'|xargs kill -9

---- Lock out apps users other than DBAs

spool lock_accounts;

update applsys.fnd_user

set end_date = (SYSDATE -1)

where creation_date >= to_date('200902','YYYYMM')  <- Enter next day
from the date of  your Instance was created  else it will lock all
default users

and user_name not in ('SYSADMIN', 'user1','user2' , ...)

and  end_date is NULL

/



col USER_NAME format a30

select  user_name,START_DATE,end_date from applsys.fnd_user where
end_date is NULL order by START_DATE;



prompt verify the details above and issue a commit....

spool off;




----CONCURRENT_REQUESTS------------


SELECT TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') STARTDATE,
COUNT(*) COUNT, ROUND(SUM(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) RUNNING_HOURS,
ROUND(AVG(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) * 24, 2) AVG_RUNNING_HOURS,
ROUND(SUM(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) PENDING_HOURS,
ROUND(AVG(ACTUAL_START_DATE - REQUESTED_START_DATE) * 24, 2) AVG_PENDING_HOURS
FROM APPLSYS.FND_CONCURRENT_PROGRAMS P,APPLSYS.STS R
WHERE R.PROGRAM_APPLICATION_ID = P.APPLICATION_ID
AND R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
AND R.STATUS_CODE IN ('C','G')
AND TRUNC(ACTUAL_COMPLETION_DATE) > TRUNC(SYSDATE-6)
AND TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') IS NOT NULL
GROUP BY TRUNC(ACTUAL_START_DATE)
 ORDER BY TRUNC(ACTUAL_START_DATE) ASC;s



----CONCURRENT_REQUESTS------------Date wice ..

SELECT TO_CHAR(TRUNC(ACTUAL_START_DATE),'DD-MON-YY DY') STARTDATE,
COUNT(*) COUNT FROM apps.fnd_concurrent_requests GROUP BY  TRUNC(ACTUAL_START_DATE)
 ORDER BY TRUNC(ACTUAL_START_DATE) desc;


scp -pr prd1_2228[7-9][0-9]_* soma@:/a02/app/PRD1_DATAFILES/arch
scp -pr prd1_2229[0-7][0-9]_* soma@:/a02/app/PRD1_DATAFILES/arch


find . -mtime +7 -name "1*.dbf" -exec ls "{}" ";"
  448  find . -mtime +7 -name "1*.dbf" -exec ls "{}" ";"
  449  find . -mtime +5 -name "1*.dbf" -exec ls "{}" ";"
  450  find . -mtime +5 -name "1*.dbf" -exec rm "{}" ";"
  find . -mtime +45 -name "*.log" -exec ls "{}" ";"
   ls -ltrh|grep -i [0-9][0-9][0-9]M
 
   find . -type f -size +50000k -mtime +2 -exec ls -lh {} \;
find . -name "*.out" -mtime +2 -exec gzip
find . -name "*.*" -mtime +10 -exec gzip -f {} \;


  find . -mtime +20 -name "*.*" -exec ls "{}" ";"
  find . -mtime +5 -name "*.trm*" -exec ls "{}" ";"
  find . -mtime +5 -name "*.trm*" -exec rm "{}" ";"
  find . -mtime +30 -name "*.out" -exec rm "{}" ";"
  find . -mtime +30 -name "*.*" -exec ls "{}" ";"
  find . -mtime +30 -name "*.gz*" -exec rm "{}" ";"
 
  find . -mtime +20 -name "*.req*" -exec rm "{}" ";"
 
    find . -mtime +10 -name "*.req*" -exec ls "{}" ";"
 
  find . -mtime +30 -name "*.gz" -exec rm "{}" ";"
 
  find . -name "*.out*" -mtime +2 -exec gzip -f {} \;
 
   find . -mtime +10 -name "*.arc.gz" -exec ls "{}" ";"
   find . -mtime +5 -name "*.arc.gz" -exec rm "{}" ";"
 
   find . -mtime +20 -name "*.out.gz" -exec ls "{}" ";"
 
   find . -mtime +10 -name "*.out.*" -exec ls "{}" ";"
 
  295custarm applcust
 
  find . -mtime +30 -name "*.trc*" -exec rm "{}" ";"
 
   find . -mtime +1 -name "*.xml" -exec rm "{}" ";"
   find . -mtime +2 -name "*.gz" -exec ls "{}" ";"
    find . -mtime +3 -name "*.gz" -exec rm "{}" ";"

find . -mtime +3 -name "*.gz" -exec rm "{}" ";"

find . -mtime +30 -name "*.*" -exec ls "{}" ";"
find . -mtime +60 -name "*.*" -exec rm "{}" ";"

find . -mtime +40 -name "*.*" -exec rm "{}" ";"

find . -mtime +5 -name "*arc.gz" -exec ls "{}" ";"
find . -mtime +5 -name "*arc.gz" -exec rm "{}" ";"

find . -mtime +.5 -name "*arc.gz" -exec ls "{}" ";"
find . -mtime +.5 -name "*arc.gz" -exec rm "{}" ";"

 
 nohup gzip rlmdebug42415055

 zip -r 8AUG13.zip 8AUG13

select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;




 mkdir /u05/orapay/afhrmp
 nohup tar -cf - data | gzip > /u05/orapay/afhrmp/data_hrms.tar.gz &

nohup gunzip -c /r17/oracle/AFTPRD_APP_BINARIES/aftprdappl.tar.gz | tar xf - &
gunzip -c dir.tar.gz | tar xf - &


naerpprd_910447_460218699_1.arc.Z
uncompress naerpprd_910447_460218699_1.arc.Z

nohup gunzip -c /r17/oracle/AFTPRD_APP_BINARIES/aftprdappl.tar.gz | tar xf - &


rmcmp_batch.sh module=/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/forms/US/XXX.fmb
userid=APPS/APPS output_file=/u01/oracle/TEST/apps/apps_st/appl/inv/12.0.0/forms/US/XXX.fmx module_type=form compile_all=special


nohup tar cEf - erpupgdb | gzip -c > /d01/app/oracle/old_erpupgdb_09May2013.tar.gz &



SELECT POOL,NAME, ROUND(BYTES/(1024*1024),2) FREE_MB
FROM V$SGASTAT WHERE POOL='shared pool'
AND NAME='free memory'
ORDER BY BYTES DESC;

SELECT NAME, VALUE/(1024*1024) SHARED_POOL_SIZE_MB
FROM V$PARAMETER WHERE NAME LIKE '%shared_pool%';




SELECT sess.username,
sess.sid,sess.serial# serial,p.spid "System PID",
sess.program,sess.osuser,sess.machine,
t.used_ublk "Undo blocks",t.status "Trans. Status",
to_char(logon_time,'MM/DD/YYYY HH24:MI') "Logged In"
FROM v$session sess,v$transaction t,v$process p
WHERE sess.saddr=t.ses_addr and
sess.paddr=p.addr;



 EXEC DBMS_STATS.gather_table_stats('INV', 'MTL_SYSTEM_ITEMS_B');

---- --- UNZIP datafiles(REFERESH DB) -----

select 'nohup gunzip -c ' ||substr(name,19) || '.gz > /s06/oracle/aftqasdata/' ||substr(name,19) ||''  from v$datafile;

select 'nohup gunzip -c ' ||substr(name,19) || '.gz > /s06/oracle/aftqasdata/' ||substr(name,19) ||' &'  from v$datafile order by creation_time desc;

select 'nohup gunzip -c ' ||substr(name,19) || '.gz > /s06/oracle/aftqasdata/' ||substr(name,19) ||' &',creation_time from v$datafile order by creation_time desc;

select file_name from dba_data_files where tablespace_name not like 'OBT%'

select 'cp -pr  ' || file_name || '  ' || file_name || ' &' from dba_data_files a, v$datafile b where a.tablespace_name not like 'OBT%' and file_name not like '%MISSING%' and
a.FILE_ID =b.file# order by b.creation_time desc;

select 'ALTER SYSTEM KILL SESSION "' || sid||','||serial#||'"' from v$session where  status='INACTIVE';

SELECT username,REPLACE(rpad(substr(to_char(max(SAMPLE_TIME) - min(SAMPLE_TIME)),11),6,' '),':','.') Logintime FROM DBA_HIST_ACTIVE_SESS_HISTORY H,DBA_USERS U
WHERE  U.USER_ID=H.USER_ID AND
U.PROFILE IN ('ARM_PROFILE_OPERATIONS','ARM_PROFILE_SUPPORT')
GROUP BY USERNAME,SESSION_ID,SESSION_SERIAL#;

 SELECT username,REPLACE(rpad(substr(to_char(max(SAMPLE_TIME) - min(SAMPLE_TIME)),11),6,' '),':','.') Logintime FROM DBA_HIST_ACTIVE_SESS_HISTORY H,DBA_USERS U
WHERE  U.USER_ID=H.USER_ID AND
U.PROFILE IN ('ARM_PROFILE_OPERATIONS','ARM_PROFILE_SUPPORT')
GROUP BY USERNAME;


select ''''||name||''',' from v$datafile order by creation_time desc;


select creation_time,name from v$datafile order by creation_time desc;

select name,creation_time from v$datafile order by creation_time desc;

select a.file_name,b.creation_time from dba_data_files a,v$datafile b where tablespace_name not like 'OBT%' and tablespace_name not like 'PERFSTAT' and a.file_name=b.name order by b.creation_time desc;


select a.file_name,b.creation_time from dba_data_files a,v$datafile b where tablespace_name not like '%JAROS%' and a.file_name=b.name order by b.creation_time desc;

alter tablespace OBT_ARCH_INDX1 add datafile '/orb/oracle/naerpprddata/orb_obt_arch_indx_77.dbf' size 2g;


select a.file_name from dba_data_files a,v$datafile b where tablespace_name like 'APPS_TS_TX_DATA%';


APPS_TS_TX_DATA

-----OS Version:

 /usr/bin/isainfo -kv
64-bit sparcv9 kernel modules

 cat /etc/release

---------Patch--------------

SELECT * FROM ad_bugs WHERE bug_number = '8677378';

SELECT * FROM ad_applied_patches WHERE patch_name = '8677378';


adpatch options=hotpatch

select count(distinct user_id) "user" from icx_sessions where last_connect > sysdate – 1/24 and user_id != '-1';



@$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE     --->Enable maintance mode

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


select
c.OS_USERNAME,
a.USER_ID,
a.USER_NAME,
a.EMPLOYEE_ID,
a.EMAIL_ADDRESS,
a.SESSION_NUMBER,
b.LAST_CONNECT,
c.USERHOST,
c.TIMESTAMP
from fnd_user a,icx_sessions b,dba_audit_session c
 where b.last_connect > sysdate -30
 and a.USER_ID=b.USER_ID
or  b.SESSION_ID=c.SESSIONID;





select d.name,f.phyrds,f.phyrds/f.readtim AVG_READ_TIME,f.phywrts,f.phywrts/f.writetim AVG_WRITE_TIME
From v$filestat f , v$datafile d
Where f.file#= d.file# and f.readtim>0 and f.writetim>0 and f.phywrts/f.writetim>=20 and f.phyrds/f.readtim>=5 ;


uuencode naerpla1_ctl_26MAR14.txt  |mailx Mathan.Meyyzhagan@M.com
 ls -ltr *94619562*
XXWSHRDPAK.rdf
select * from all_db_links;




cp $POARM_TOP/lib/CUSTOM.pll $AU_TOP/resource/
cp $POARM_TOP/lib/CUSTOM.plx $AU_TOP/resource/




SELECT DECODE(request,0,'Holder: ','Waiter: ')||trim(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;


nohup gunzip -c /backup/afttst/AFTPRD_BACKUP/system08.dbf.gz > /s06/oracle/afttstdata/system08.dbf &
nohup gunzip -c /w03/oracle/apps_bkp_26mar/aftprdapplcust.tar.gz | tar xf - &
nohup zip -r /m19/bakup_5apr13/naerpprdcomn.zip naerpprdcomn -x 'naerpprdcomn/admin/log/*' -x 'naerpprdcomn/admin/out/*' -x 'naerpprdcomn/temp/*' &

nohup tar cEf - 10.2.0 | gzip -c > /u01/app/oracle/PGR1BK/ora_home/10.2.0.tar.gz &

nohup gzip -c $FILE_NAME > $BACKUP_LOC/`basename $FILE_NAME`.gz &

/u04/app/PRD1_bkp_13Feb14/u04

nohup gzip -c APPS_TS_MEDIA27.dbf > /u04/app/PRD1_bkp_13Feb14/Mfile/APPS_TS_MEDIA27.dbf.gz &

nohup cp -pr APPS_TS_MEDIA27.dbf /u04/app/PRD1_bkp_13Feb14/Mfile/ &

/bcvp099_u04/app/oradata/prd1

set noai
set nu


tar cEf - erpupgora | gzip -c > /d01/app/oracle/8MAY13ERPUPG/erpupgora.tar.gz &

cd /r03/oracle
nohup gunzip -c /r17/oracle/AFTPRD_APP_BINARIES/aftprdappl.tar.gz | tar xf - &


ps -ef |grep `ps -ef |grep trans |grep -v grep | awk '{print $3}'`

ps -ef |grep -i local=no |grep -i oerpdev |awk '{print $2}'|xargs kill -9

Maintance Window

purge recyclebin;

tar cEf - erpupgora | gzip -c > /d01/movebcvsmovebcvs/oracle/8MAY13ERPUPG/erpupgora.tar.gz &

tar cEf - naerpladapplcust | gzip -c > /d03/oracle/2AUG13/naerpladapplcust.tar.gz &


/d01/app/oracle/erpupgappl/admin/out/appsutil.zip

Generate concurrent processing environment information


  select SID,SERIAL#,ACTION,EVENT,STATUS from v$session where sid=&sid;
 
  set linesize 80
col sess for a15

SELECT DECODE(request,0,'Holder: ','Waiter: ')||trim(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
/
 
  dfaa naerpprd
 
  dfaa aftprd
 
 
  alter tablespace JAROSCHTRKIDX  add datafile '/u07/oradata/eucvsprd/jaroschtrkidx13.dbf' size 2g;
 
 
  Metalink Note - 246584.1 describes the issue, but it is a very rare case.

  /u02/app/applmgr/TEST11/iAS/Apache/modplsql/cfg/wdbsvr.app
 

 NDTWPRD1 b_MAIN_ODS_COSTDETAILS Suspended -create table

update fnd_profile_option_values
set profile_option_value = replace(profile_option_value,'PRD1','ERPPROJ')
where profile_option_value like '%PRD1%' ;


select username,account_status from dba_users where username='SFWIOAVM';

select username,account_status from dba_users where username='MSAFI';

alter user EASWAR account unlock;
alter user identified by

select sekarctusername,account_status from dba_users where username='SYS';

alter user system identified by syserpdev;

/sqlcom/outbound


ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;


select file_name from dba_data_files where tablespace_name not like 'JAROS%' and tablespace_name not like 'PERF%' and tablespace_name not like 'OBT%' ;

select count(*) from dba_data_files where tablespace_name not like 'JAROS%' and tablespace_name not like 'PERF%' and tablespace_name not like 'OBT%' ;

[:oracle]$ ls -ltr web*
-rwxrwxr-x   1 oracle   dba         3001 Aug 26  2010 webConsole.sh
-rwxrwxr-x   1 oracle   dba         1882 Aug 26  2010 webConsole.bat
[:oracle]$ ./webConsole.sh -p 8080 start
Failed to use port 8080 for the HTTP port: Address already in use
[:oracle]$ ./webConsole.sh -p 8080 stop
Web Console stopped
[:oracle]$ ./webConsole.sh -p 8080 start
Starting the Web Console...
Web Console started.
  http://localhost:8080/WebConsole
[:oracle]$ pwd
/m04/oracle/OBT/HPDBArchiving630/obt/bin
[:oracle]$

=EXACT(A410,B410)

[:oracle]$ ksh status.ksh
OC4J Server is NOT running.
[:oracle]$ pwd
/a03/oracle/ARM/naerpprdfbo/view1
[:oracle]$ ksh start.ksh
[:oracle]$ ksh status.ksh

OC4J Running with PID = 20565.


     UID   PID  PPID   C    STIME TTY         TIME CMD
  oracle 20565  4202   0 19:04:09 pts/1       0:03 java -Dserver.name=FBOnaerpprd1 -Ddbuser=APPS -Xms3000m -Xmx3200m -verbose:gc -
[:oracle]$ pwd
/a03/oracle/ARM/naerpprdfbo/view1
[:oracle]$





alter database backup controlfile to trace as '/d01/oracle/naerpdevdb/16aug13ctl.txt';
/d01/oracle/naerpdevdb

exp system/manager FILE=exp_u.dmp LOG=exp_u.log OWNER=oracle

exp system/gelatina2013 FILE=/u02/app/oradata/MSAFI_DMP/MSAFI.dmp LOG=/u02/app/oradata/MSAFI_DMP/MSAFI_LOG.log OWNER=MSAFI

imp userid=\'/ as sysdba\' FILE= exp.dmp FULL=Y log=imp.log
MSAFI

select directory_name from dba_directories;

[:oracle]$ sqlplus / as sysdba
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
[:oracle]$ env |grep -i tz
TZ=US/Eastern
ORA_TZFILE=/rd01/oracle/naapsupgdb/11.2.0/oracore/zoneinfo/timezlrg.dat
[:oracle]$ unset ORA_TZFILE
[:oracle]$  sqlplus / as sysdba


/t01/oracle/automagic/config/pwdtab_v11i
/t01/oracle/automagic/dbmove

select * from fnd_lookup_values where lower(description) like '%prd%'
select * from fnd_profile_option_values where upper(PROFILE_OPTION_VALUE) like '%PRD%'

select * from dba_db_links where upper(host) like '%PRD%'

perl -x /u01/app/oracle/erpdevappl/jtf/11.5.0/admin/scripts/ojspCompile.pl --compile --quiet





1. Check if the responsibility is not end dated.
2. Run the request " Syn responsibility role data into the WF table".
3. Run the request " Synchronize WF LOCAL tables" enter the parameters- Orig System: ALL- Parallel Process:0- Logging Mode: LOGGING (Logging Activated)- Temporary Tablespace : Blank- Riase Errors: Yes
4. Run the request "Workflow Directory Services User/Role Validation" enter the parameters- p_BatchSize:10000- Fix dangling user/roles : Yes- Add missing user/role assignments : Yes- Update WHO columns in WF tables: Yes
5. Log off and login back and verify that the responsibility appears.




1. Shutdown all the Application Services (adstpall.sh apps/<apps password>)
2. Clear Apache Cache
cd $COMMON_TOP
mv _pages _pages-BKP
mkdir _pages
3. Compile all the JSP's using the below command:
perl -x $FND_TOP/patch/115/bin/ojspCompile.pl --compile --quiet
4. Start all the application services (adstrtal.sh apps/<apps password>).
5. Submit the concurrent request "Gather Schema Statistics" with Schema Name: ALL and Estimate Percent: 40% and wait for the request completes.
6. Submit the concurrent request "Diagnostics Patching CP" wait for the request to complete and upload the log.
7. Clear all cache and Bounce the Middle tier services


Hello Luciana -
Please refer to below documents in order to determine fragmentation in your database:
Doc ID 1020182.6 contains scripts to detect fragmentation at 'Tablespace' level.
Doc ID 337651.1 contains scripts to determine fragmentation at 'Table' level.
To defragment a table, refer to the following notes and do the testing in testing environment first and backup the corresponding table
Can ALTER TABLE SHRINK Run Faster Than ALTER TABLE MOVE? (Doc ID 577375.1)
SEGMENT SHRINK and Details. (Doc ID 242090.1)
HOW TO SHRINK A TABLE USING ONLINE REDEFINITION (Doc ID 1357878.1)
How to Move Tables from One Tablespace to Another. (Doc ID 147356.1)
the business requirements, i.e. if the tables must be available while performing de-fragmentation then use online redefinition.
Note Generate COGS Recognition Events Not Completing Performance (Doc ID 1573860.1)" has the steps as well - just replace the tables below and the other tables with high extents revealed using the two notes 1020182.6 and 337651.1.

CST_AE_LINES
CST_AE_LINES_N1
CST_AE_LINES_U1
CST_PC_TXN_HISTORY
MTL_MATERIAL_TRANSACTIONS





SELECT TABLE_NAME ,  BLOCKS*8192/1024/1024 "Total Size MB" ,NUM_ROWS*AVG_ROW_LEN/1024/1024 "Actual Size MB" ,(BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB"   FROM  DBA_TABLES WHERE  UPPER(owner) =UPPER('INV') and table_name='MTL_MATERIAL_TRANSACTIONS';



.
update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id=&REQUEST_ID;