Finally something that I can contribute to ...
Dennis,
I put together this script to be run every two hours through 'cron' (we
don't trust dbms_job).
------------------- start script ---------------------------
set serveroutput on
set trimspool on
set feedback off
SET lin 200
set pagesize 100
spool ncs_health.log
PROMPT
set hea off
select 'Starting: ' || to_char(sysdate,'MM-DD-YYYY HH:MI:SS AM') from dual
/
prompt
prompt **** Server Information ****
set hea off
SELECT 'DATABASE: ' || instance_name||'@'||host_name || ' running ' ||
version || ' since ' ||
TO_CHAR(startup_time,'MM-DD-YYYY HH:MI:SS AM') "Startup Time"
FROM sys.V_$INSTANCE;
prompt
set hea on
prompt **** Buffer Busy Waits ****
SELECT 'Block Class' "Class Type",
w.class block_class,
w.COUNT total_waits,
w.TIME time_waited
FROM
sys.V_$WAITSTAT w
WHERE
w.COUNT > 0
UNION
SELECT 'Tablespace' "Class Type",
d.tablespace_name,
SUM(x.COUNT) total_waits,
SUM(x.TIME) time_waited
FROM
sys.X_$KCBFWAIT x,
sys.DBA_DATA_FILES d
WHERE
x.inst_id = USERENV('Instance') AND
x.COUNT > 0 AND
d.file_id = x.indx + 1
GROUP BY
d.tablespace_name
UNION
SELECT 'Buffer Pool' "Class Type",
p.bp_name BUFFER_POOL,
SUM(s.bbwait) total_waits,
0
FROM
sys.X_$KCBWDS s,
sys.X_$KCBWBPD p
WHERE
s.inst_id = USERENV('Instance') AND
p.inst_id = USERENV('Instance') AND
s.set_id >= p.bp_lo_sid AND
s.set_id <= p.bp_hi_sid AND
p.bp_size != 0
GROUP BY
p.bp_name
HAVING
SUM(s.bbwait) > 0
ORDER BY 1, 4 DESC
/
prompt
prompt **** Shared Pool LRU Stats ****
SELECT
kghlurcr "RECURRENT_CHUNKS",
kghlutrn "TRANSIENT_CHUNKS",
kghlufsh "FLUSHED_CHUNKS",
kghluops "PINS AND_RELEASES",
kghlunfu "ORA-4031_ERRORS",
kghlunfs "LAST_ERROR_SIZE"
FROM
sys.X_$KGHLU
WHERE
inst_id = USERENV('Instance')
/
prompt
prompt **** Basic Library Cache Stats ****
select
namespace,
gets locks,
gets - gethits loads,
pins,
reloads,
invalidations
from
sys.v_$librarycache
where
gets > 0
order by
2 desc
/
prompt
prompt **** Cursor Version Counts ****
select
substr(to_char(min(v)) ||
decode( max(v) - min(v), 0, null, ' to ' || to_char(max(v))),1,40)
version_count,
count(*) cursors
from
( select count(*) v from sys.x_$kglcursor
where inst_id = userenv('Instance') and
kglhdadr != kglhdpar
group by kglhdpar, kglnahsh)
group by
trunc(round(log(2, v), 37))
/
prompt
prompt
prompt **** Currently Executing Packages ****
--column type format a9
--column owner format a25
--column name format a30
--column sid format 9999
--column serial format 999999
SELECT
substr(DECODE(o.kglobtyp,
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,
'CLASS'),1,15) "TYPE",
substr(o.kglnaown,1,30) "OWNER",
substr(o.kglnaobj,1,30) "NAME",
s.indx "SID",
s.ksuseser "SERIAL"
FROM
sys.X_$KGLOB o,
sys.X_$KGLPN p,
sys.X_$KSUSE s
WHERE
o.inst_id = USERENV('Instance') AND
p.inst_id = USERENV('Instance') AND
s.inst_id = USERENV('Instance') AND
o.kglhdpmd = 2 AND
o.kglobtyp IN (7, 8, 9, 12, 13) AND
p.kglpnhdl = o.kglhdadr AND
s.addr = p.kglpnses
ORDER BY 1, 2, 3
/
prompt
prompt **** List of Objects That Will Fail To Extend ****
SELECT /*+ RULE ORDERED */
substr(a.tablespace_name,1,30) "Tablespace"
,substr(a.owner,1,30) "Object Owner"
,substr(a.segment_name,1,30) "Object Name"
,a.extents "# Of Extents"
,ROUND(next_extent/1024) "Next Req(KB)"
,ROUND(b.free / 1024) "Max Avail(KB)"
FROM DBA_SEGMENTS a,
(SELECT df.tablespace_name, MAX(fs.bytes) free
FROM DBA_DATA_FILES df,
DBA_FREE_SPACE fs
WHERE df.file_id = fs.file_id
AND df.tablespace_name NOT IN
(SELECT ts.tablespace_name
FROM DBA_TABLESPACES ts
WHERE EXISTS
(SELECT 1
FROM DBA_DATA_FILES df2
WHERE df2.tablespace_name = ts.tablespace_name
AND df2.autoextensible = 'YES'))
GROUP BY df.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name <> 'TEMP_SEGS'
AND a.next_extent > b.free
ORDER BY 1,2,3
/
prompt
prompt **** List of INVALID Objects ****
SELECT substr(OWNER,1,30) "Owner"
,substr(OBJECT_NAME,1,30) "Object Name"
,substr(OBJECT_TYPE,1,30) "Object Type"
FROM sys.DBA_OBJECTS
WHERE status = 'INVALID'
ORDER BY 1,2,3
/
prompt
prompt **** List of DISABLED TRIGGERS ****
SELECT /*+ RULE ORDERED */
owner
, NVL(table_name, '<system trigger>') table_name
, trigger_name
FROM DBA_TRIGGERS
WHERE status = 'DISABLED'
ORDER BY 1,2,3
/
prompt
prompt **** List of DISABLED Constraints ****
SELECT substr(owner,1, 30) "Owner"
,substr(table_name,1, 30) "Table Name"
,substr(constraint_name,1, 30) "Constraint Name"
,substr(DECODE(CONSTRAINT_TYPE,
'C', '(CHECK CONSTRAINT)',
'P', '(PRIMARY KEY)',
'U', '(UNIQUE KEY)',
'R', '(REFERENTIAL INTEGRITY)',
'V', '(WITH CHECK OPTION, ON A VIEW)',
'O', '(WITH READ ONLY, ON A VIEW)'),1, 30) "Constraint Type"
FROM DBA_CONSTRAINTS
WHERE status = 'DISABLED'
ORDER BY 1,2,3
/
prompt
set hea off
prompt **** List of JOBS BROKEN ****
SELECT rtrim('Job: ' || to_char(job) || ', Last Executed: ' ||
to_char(last_date,'MM-DD-YYYY HH:MI:SS AM')
|| chr(10) ||
'==> Marked BROKEN' || chr(10) ||
'==> Owner: ' || schema_user || chr(10) ||
'==> What : ' || replace(replace(replace(what,chr(10),' '),chr(9),'
'),' ',' ')) "Job Desc"
FROM DBA_JOBS
WHERE broken='Y'
OR next_date < SYSDATE
/
prompt
prompt **** List of JOBS EXECUTING > 60 Mins ****
SELECT rtrim('Job: ' || to_char(job) || ', Last Executed: ' ||
to_char(last_date,'MM-DD-YYYY HH:MI:SS AM')
|| chr(10) ||
'==> Since: ' || to_char(this_date, 'MM-DD-YYYY HH:MI:SS AM') ||
chr(10) ||
'==> Owner: ' || schema_user || chr(10) ||
'==> What : ' || replace(replace(replace(what,chr(10),' '),chr(9),'
'),' ',' ')) "Job Desc"
FROM DBA_JOBS
WHERE broken <> 'Y'
and (this_date IS NOT NULL AND this_date < SYSDATE - 60/1440)
/
prompt
exec dbms_output.put_line('*** END OF REPORT ***');
set hea off
select 'Ending: ' || to_char(sysdate,'MM-DD-YYYY HH:MI:SS AM') from dual
/
prompt
------------------- end script ---------------------------
Some of the scripts came from other sources like Steve, Jonathan etc (Thanks
you all), some we wrote.
The cron job also emails the output of these scripts to all the DBAs, and
yes we run these on 5 databases there is no noticable impact.
HTH
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
Sent: Tuesday, April 23, 2002 12:01 PM
To: Multiple recipients of list ORACLE-L
Greetings -
I am planning to centralize our Oracle monitoring process by using one
PL/SQL procedure to query database extents, invalid objects, alert logs etc
through database links. I wonder if anybody has done it before and if there
is any cons with it. The pros would be ease of administration, ease of
standardization etc.
TIA
Dennis
*******************************************************************************1
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and
delete this e-mail message from your computer, Thank you.
*******************************************************************************1
