I am still introducing Francesco's dbcheck script to my DBAs. I have multiple DBA groups, and setting monitoring up with thresholds & settings that they can all agree on is difficult sometimes. But since they are the ones getting page, I can understand their concern on having monitoring setup right. I still have a todo to email them the exact SQL queries being used by the checks, I have had reports from one group that some of the checks can be Database intensive. Currently I have all my DB checks running at 5 min intervals, so I am looking forward to identifying the 'intensive' queries and moving them to a much longer run interval (great feature). For now I have just stopped those particular test from running.
As to TableSpace checks, my DBAs take into account 3 parameters to determine if diskspace is an issue. FreeSpace, PercentFree, and Extend (I think). Some of our TableSpaces are very small, and when thresholds are just set to check Percents we get alot of false-positives. So I have made use of dbcheck's Custome query option to replace the TblSpace check. Basically the SQL performs all the check logic, and sets a field flag with a 1 (green), 2 (yellow), or 3 (red) value. dbchecks does a check only on this field to set the status of the test. Below I have provided the CUSTOM TblSpace check. Pardon the very long, one line SQL. It does not provide for great readability.
[CUSTOM TblSpace] Dbtype=Oracle Query=select a.tablespace_name, nvl(b.total_mb,0) tsize, nvl((b.total_mb
- c.free_mb),0) used, nvl(c.free_mb,0) free, nvl(round((((b.total_mb - c.free_mb)*100)/b.total_mb),2),0) Usedpct, d.autoextensible Autoextent, CASE WHEN nvl(round((((b.total_mb - c.free_mb)*100)/b.total_mb),2),0)>97 AND c.free_mb < 200 THEN '2' WHEN nvl(round((((b.total_mb - c.free_mb)*100)/b.total_mb),2),0)>94 AND c.free_mb < 400 THEN '1' ELSE '0' END Status from (select tablespace_name, (sum(bytes)/1024)/1024 total_mb from dba_data_files group by tablespace_name) b, (select tablespace_name, nvl(round(((sum(bytes)/1024)/1024),2),0) free_mb from dba_free_space group by tablespace_name) c, (select distinct tablespace_name,autoextensible from dba_data_files where autoextensible='NO') d, dba_tablespaces a where a.tablespace_name = b.tablespace_name (+) and a.tablespace_name = c.tablespace_name (+) and a.tablespace_name = d.tablespace_name and a.contents not in ('TEMPORARY','UNDO') and a.tablespace_name not like '%RBS%' and a.status <> 'READ ONLY' UNION ALL select a.tablespace_name, nvl(b.total_mb,0) tsize, nvl((b.total_mb - c.free_mb),0) used, nvl(c.free_mb,0) free, nvl(round((((b.total_mb - c.free_mb)*100)/b.total_mb),2),0) Usedpct, d.autoextensible Autoextent, CASE WHEN fr < 200 OR ex < 2 THEN '2' WHEN fr < 400 OR ex < 5 THEN '1' ELSE '0' END Status from (select tablespace_name, (sum(bytes)/1024)/1024 total_mb from dba_data_files group by tablespace_name) b, (select tablespace_name, nvl(round(((sum(bytes)/1024)/1024),2),0) free_mb from dba_free_space group by tablespace_name) c, (select distinct tablespace_name,autoextensible,(maxbytes - bytes)/1024/1024 fr,(maxbytes
- bytes)*blocks/(increment_by*bytes) ex from dba_data_files where autoextensible='YES') d, dba_tablespaces a where a.tablespace_name = b.tablespace_name (+) and a.tablespace_name = c.tablespace_name (+) and a.tablespace_name = d.tablespace_name and a.c ontents not in ('TEMPORARY','UNDO') and a.tablespace_name not like '%RBS%' and a .status <> 'READ ONLY' order by 1 Valtocheck=7:1:2:=:1 ColumnName=TableSpaceName;Size(MB);Used(MB);Available(MB);Used%;AutoExte nt Columnsize=;;;;;;0 Columnalign=l;r;r;r;r;r displaylegend=no docheckdefault=yes showmatchedrule=no
-----Original Message----- From: Eric van de Meerakker [mailto:eric-list-1 at softlution.com] Sent: Thursday, January 04, 2007 8:01 AM To: hobbit at hswn.dk Subject: Re: [hobbit] dbcheck.pl - Oracle - tablespace with AutoExtent
Hi Henrik,
My knowledge of Oracle is not too much higher, but IMO you DBA is right. If the autoextend property is set on a tablespace, these cases should not cause a red status. I think the best way would be to check the Used size as a percentage of the "Autoextent(Size)" size, not the current "Size" size. I've experienced the same issue with BB's Oracle montoring scripts.
As a secondary monitoring point, disk monitoring should be enabled on the disks containing the tablespaces. They may well fill up before the max size is reached, preventing Oracle from extending the tablespaces to the maximum size allowed. Of course, if the Oracle data files do not reside on a filesystem, this cannot be done.
Cheers,
Eric.
Henrik Stoerner wrote:
I've started using Francesco's dbcheck script - very nice package, really. However, one of my DBA's is a bit unhappy with the tablespace check - it reports yellow/red status for tables that have been defined with AutoExtent enabled.
Right now I get a report like this:
Tablespace check (def. warning Use% >= 90%, def. alert
Use% >= 95%) TableSpace/DBSpace Size Used Free Use% Autoextent(Size) BASIC_DATA2_1976_88 300.0M 215.0M 85.0M 72% YES (64.0G)
BASIC_DATA2_1989Q1 400.0M 382.0M 18.0M 96% YES (64.0G)
BASIC_DATA2_1989Q2 500.0M 483.0M 17.0M 97% YES (64.0G)with the last two tables causing a red status. My DBA thinks they should not trigger an alert, because they will automatically increase in size if needed.
Can anyone shed some light on this? My knowledge of Oracle is close to zero, so I really don't have any idea whether he is right or not.
Regards, Henrik
To unsubscribe from the hobbit list, send an e-mail to hobbit-unsubscribe at hswn.dk
To unsubscribe from the hobbit list, send an e-mail to hobbit-unsubscribe at hswn.dk