[database::oracle::plugin] mode=tablespaceusage: returned values are wrong

[joschi99]

Centreon Plugins 20191016 Oracle 11g

If we monitor a Oracle TS with Centreon Plugins we have to following output:

 /usr/lib/centreon/plugins/centrepn_plugins.pl --plugin=database::oracle::plugin --hostname=x.x.x.x --port=1521 --sid=SID --username='xxx' --password='xxx' --mode=tablespace-usage --filter-tablespace='TS' --warning-tablespace=80 --critical-tablespace=90
CRITICAL: Tablespace 'ts' Total: 383.98 GB Used: 349.53 GB (91.03%) Free: 34.46 GB (8.97%) | 'tbs_sensda_usage'=375301799936B;0:329838152908;0:371067922022;0;412297691136

But if we check the TS usage by SQL or for example with Quest Oracle Navigator we got another output:

select df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
       round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
  from (select tablespace_name,
               round(sum(bytes) / 1048576) TotalSpace
          from dba_data_files 
         group by tablespace_name) df,
       (select round(sum(bytes)/(1024*1024)) totalusedspace,
               tablespace_name
          from dba_segments 
         group by tablespace_name) tu
 where df.tablespace_name = tu.tablespace_name 
   and df.totalspace <> 0;


Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
TS                                 357960      16900     374860          5

The Centreon Plugin returns wrong on Total, usage and free_prct.

[joschi99]

I have tried out another script with same output:

SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;

TABLESPACE_NAME                AUT MAX_TS_SIZE MAX_TS_PCT_USED CURR_TS_SIZE USED_TS_SIZE TS_PCT_USED FREE_TS_SIZE TS_PCT_FREE
------------------------------ --- ----------- --------------- ------------ ------------ ----------- ------------ -----------
TS                             YES      458752           78.03       374860    357982.69        95.5     16877.31           5

[garnier-quentin]

what did you get with that request ?

SELECT
              tum.tablespace_name "Tablespace",
              t.status "Status",
              t.contents "Type",
              t.extent_management "Extent Mgmt",
              tum.used_space*t.block_size bytes,
              tum.tablespace_size*t.block_size bytes_max
             FROM
              DBA_TABLESPACE_USAGE_METRICS tum
             INNER JOIN
              dba_tablespaces t on tum.tablespace_name=t.tablespace_name

[joschi99]

Tablespace                     Status    Type      Extent Mgm      BYTES  BYTES_MAX
------------------------------ --------- --------- ---------- ---------- ----------
TS                         ONLINE    PERMANENT LOCAL      3.7541E+11 4.1230E+11

[garnier-quentin]

Do you know why 'DBA_TABLESPACE_USAGE_METRICS' table doesn't provide the same result ?

[joschi99]

Hi @garnier-quentin, I have done some research on Oracle Metalink and it seem's a bug related to some Oracle versions. For the version 11.2.0.4 in our case I have found more issues like https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=570258753082989&id=22076509.8&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=50d2q4h2x_508 https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=570438206998654&id=19267095.8&displayIndex=6&_afrWindowMode=0&_adf.ctrl-state=50d2q4h2x_565

But it seem's that there are different issues also on newer version of Oracle related to DBA_TABLESPACE_USAGE_METRICS.

[garnier-quentin]

I cant see support oracle page. Could you provide exact ranges of oracle versions ? I could exclude it

[joschi99]

Hi @garnier-quentin, attached you will find 2 issue about them, but on metalink are still more issues related to this view regarding different Oracle Versions, but Oracle will also provide patches for this. At the moment we will verify if the encountered problem on 11.2.0.4 can be fixed applying the Patchset from Oracle. I will update you asap.

Document 19267095.8.pdf Document 22076509.8.pdf

[garnier-quentin]

So the only version is '11.2.0.4' if i understand.

[joschi99]

Not sure about them. There seems a lot of different problems with this view related on different Oracle versions:

[garnier-quentin]

Ouch....

[joschi99]

Maybe you have to consider to substitute this view generally.

[garnier-quentin]

Yes. But we have some performances issues when we don't use that view with many tablespaces. Maybe we can enhance the request performance.

[joschi99]

I understand. Let me know if I can help, is a priority to have correct and fast tablespace monitoring.

[BenPls]

It seems also that this view has not all the tablespaces, prior to version 12.1.0.2.4.

[joschi99]

Hi @garnier-quentin, did you have done some evaluation how it could be possible to have a correct tablespace usage monitoring without performance issues?

[garnier-quentin]

I don't. But if you have a request i could update it.

[fmattesct]

Hi, Thanks for your interest in Centreon. Requests for new features and enhancements must be suggested here. Troubleshooting and questions must now be asked here (cf our new issue template.

Thank you for your understanding.