Tablespace monitoring

[vtrac]

Hi,
First, the "check_oracle" script check against both "DBA_DATA_FILES" and "DBA_FREE_SPACE" tables.

Here's what being called inside the "check_oracle" script with the "select/from":
result=`sqlplus -s "nagios"/"n46105"@"BIPRD" << EOF
set pagesize 0
set numf '9999999.99'
select NVL(b.free,0.0),a.total,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 prc
from (
select tablespace_name,sum(bytes)/1024/1024 total
from DBA_DATA_FILES group by tablespace_name) A
LEFT OUTER JOIN
( select tablespace_name,sum(bytes)/1024/1024 free
from DBA_FREE_SPACE group by tablespace_name) B
ON a.tablespace_name=b.tablespace_name WHERE a.tablespace_name='BIPRD1117_IAS_TEMP';
EOF`


Based on those screenshots, we know why "BIPRD1117_IAS_TEMP" came back empty from "check_oracle".

Here are what we know today:
BIPRD1117_IAS_TEMP defined in DBA_TABLESPACES table and is "ONLINE"
BIPRD1117_IAS_TEMP defined in DBA_DATA_FILES table but has (o byte) in the "BYTES" field.
BIPRD1117_IAS_TEMP defined in DBA_FREE_SPACE table but also has (o byte) in the "BYTES" field as well

Compared with "BIPRD1117_BIPLATFORM",
BIPRD1117_BIPLATFORM also defined in DBA_TABLESPACES table and also "ONLINE"
BIPRD1117_BIPLATFORM also defined in DBA_DATA_FILES table and the "BYTES" field has lots of values in bytes
BIPRD1117_BIPLATFORM also defined in DBA_FREE_SPACE table and the "BYTES" field also has lots of values in bytes.

So, this concluded our investigation as to why you get no value return for "BIPRD1117_IAS_TEMP" when running the "check_oracle" script.

Please talk to your DBA as to why "BIPRD1117_IAS_TEMP" has no value defined for the "BYTES" field belong to "DBA_DATA_FILES" and "DBA_FREE_SPACE" tables.


Regards,
Vinh

[kalyanpabolu]

Hello,

We checked those and yes we agree that it is working fine. But, the other database called EBSPRD have the same issue. We checked the commands and we can see the data files are present, still the issues exists.

[vtrac]

Hi,
I think we have proved that the script worked.

Please work with your DBA, since this might be related to your settings.

Below is the sql call from that check_oracle script that you can try.
Please cut and paste the whole thing onto your Nagios XI command prompt. That way we can check and see if user/password and SID are working on the Nagios XI machine.

If you manual logged into the server and then run the command, then it is not a valid check anymore since I don't know if the user/password are correct as stated.

Code: Select all

result=`sqlplus -s "nagios"/"n46105"@"EBSPRD" << EOF
set pagesize 0
set numf '9999999.99'
select NVL(b.free,0.0),a.total,100 - trunc(NVL(b.free,0.0)/a.total * 1000) / 10 prc
from (
select tablespace_name,sum(bytes)/1024/1024 total
from DBA_DATA_FILES group by tablespace_name) A
LEFT OUTER JOIN
( select tablespace_name,sum(bytes)/1024/1024 free
from DBA_FREE_SPACE group by tablespace_name) B
ON a.tablespace_name=b.tablespace_name WHERE a.tablespace_name='APPS_TS_MEDIA';
EOF`

echo "RESULT:  $result"

I also have talked to my team here, they all think you should talk to your database team since we have already proved that it works.


Regards,
Vinh