Oracle Database Monitoring Scripts

Normal Database and Application Maintenance monitoring scripts.

===========================Database Related Scripts============================

Check Database Size In MB:
======================
SELECT d.name,d.open_mode,d.log_mode, a.data_size+b.temp_size+c.redo_size "DB_Total_Size_MB"
FROM ( SELECT SUM(bytes)/1024/1024 data_size
FROM dba_data_files )a,
( SELECT NVL(SUM(bytes),0)/1024/1024 temp_size
FROM dba_temp_files) b,
( SELECT SUM(bytes)/1024/1024 redo_size
FROM sys.v_$log ) c,v$database d;

Check Tablespace Sizes ( Without Temp Tablespace):
==========================================

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
NVL(ROUND(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
ROUND((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes;

Check the Temp Tablespace Free Size:
==============================

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;

Check Default temp tablespace:
=========================

SELECT property_value FROM database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

Check temp tblspace free space:
=========================

SELECT tablespace_name,bytes_used/1024/1024 AS USED,bytes_free/1024/1024 AS FREE FROM v$temp_space_header WHERE tablespace_name='TEMP01';

Check Invalid Status:
=================

Select count(*) from dba_objects  where status='INVALID';

Script for Archive log generation per day:
=================================

SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
where to_date(first_time) > sysdate -2
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time);

**********************************End of Database Related Queries*******************************

 ========================== Application Tier Related Scripts========================

Workflow Service Components Status:
===============================

SELECT DECODE(component_type,
‘WF_AGENT_LISTENER’,'Agent Listener’,
‘WF_DOCUMENT_WEB_SERVICES’,’ Doc. Web Services’,
‘WF_JAVA_AGENT_LISTENER’,'JAVA Agent Listener’,
‘WF_MAILER’, ‘Mailer’,
‘default’
) “TYPE”,
component_name “NAME”,
Component_status “STATUS”,
COMPONENT_STATUS_INFO “ERROR”
FROM fnd_svc_components
WHERE component_type LIKE ‘WF%’
ORDER BY 3,1 DESC,2;

Checking the WF NOTIFICATION OUT table for the total number of notifications in different states:
===============================================================================

SELECT   DECODE (wfo.state,
0, ‘Ready’,
1, ‘Delayed’,
2, ‘Processed’,
3, ‘Exception’,
TO_CHAR (SUBSTR (wfo.state, 1, 12))
) state,
COUNT (*) COUNT
FROM applsys.wf_notification_out wfo
GROUP BY wfo.state;

Checking the Long Running Requests:
==============================

SELECT FND_CONCURRENT_REQUESTS.REQUEST_ID “Request ID”,
FND_USER.USER_NAME “Requestor”,
fnd_concurrent_programs_vl.user_concurrent_program_name “User Conc.Program Name”,
TO_CHAR(FND_CONCURRENT_REQUESTS.ACTUAL_START_DATE , ‘DD-MON-YYYY HH24:MI:SS’) ” Actual Start Date”,
TO_CHAR(SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’) ” System Date”,
round(((SYSDATE-FND_CONCURRENT_REQUESTS.ACTUAL_START_DATE)*1440),2) “Minutes”,
‘Running’ Phase,
DECODE(FND_CONCURRENT_REQUESTS.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’) “Status”
FROM applsys.FND_USER, applsys.FND_CONCURRENT_REQUESTS, apps.fnd_concurrent_programs_vl
WHERE (FND_CONCURRENT_REQUESTS.PHASE_CODE = ‘R’
AND FND_CONCURRENT_REQUESTS.REQUESTED_BY = FND_USER.USER_ID
AND FND_CONCURRENT_REQUESTS.concurrent_program_id = fnd_concurrent_programs_vl.concurrent_program_id)
ORDER BY 6 desc,2, 5;

Checking the Concurrent Queues Details:
=================================

SELECT fcq.concurrent_queue_name || ‘ – ‘ || target_node “Concurrent Queue Name” ,
fcq.running_processes “Actual”,
fcq.max_processes “Target”,
SUM(DECODE(fcr.phase_code,’R',1,0)) – SUM(DECODE(fcr.status_code,’W',1,0)) “Running”,
SUM(DECODE(fcr.phase_code,’P',1,0)) “Pending”,
NVL(SUM(DECODE(fcr.status_code,’W',1,’H',1,’S',1,’A’ ,1,’Z',1,0)),0) “Paused”,
(case
when  AVG((NVL(fcr.actual_start_date,fcr.requested_start_date) – fcr.requested_start_date)*1440) < 0 then 0
else round(nvl(AVG((NVL(fcr.actual_start_date,fcr.requested_start_date) – fcr.requested_start_date)*1440),0),2)
end)
“Average Q Time”
FROM applsys.fnd_concurrent_requests fcr,
applsys.fnd_concurrent_processes fcp,
applsys.fnd_concurrent_queues fcq
WHERE fcr.controlling_manager (+) = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcq.max_processes > 0
AND ((fcr.phase_code IN (‘R’,'P’,'I’) AND UPPER(‘&dispmethod’) = ‘Y’) OR UPPER(‘&dispmethod’) != ‘Y’)
GROUP BY fcq.concurrent_queue_name || ‘ – ‘ || target_node, fcq.running_processes, fcq.max_processes
order by  7 desc;

Active Processes Status in Standard / CRM / ICM Managers:
================================================

SELECT   DECODE (fcq.concurrent_queue_name,
‘STANDARD’, ‘Standard Manager’,
‘FNDCRM’, ‘Conflict Resolution Manager’,
‘FNDICM’, ‘Internal Concurrent Manager’
) “Concurrent Queue”,
‘Active’ “Process Status”, COUNT (*) “Count”
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcp.process_status_code = ‘A’
AND fcq.concurrent_queue_name IN (‘STANDARD’, ‘FNDCRM’, ‘FNDICM’)
GROUP BY fcq.concurrent_queue_name, fcp.process_status_code;

********************************End of Application Related Queries****************************

Best regards,
Vijay.