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