Page 1 of 3
Tablespace monitoring
Posted: Thu Jan 14, 2021 11:42 am
by kalyanpabolu
Hello Team,
We are monitoring tablespace free space in Nagios. We are monitoring two tablespace on one DB.
DB name: BIPRD
Tablespace1: BIPRD1117_BIPLATFORM
Tablespace2: BIPRD1117_IAS_TEMP
We have configured both the services in the same way. Below is the service definition:
define service{
use generic-service-basic
host_name chaprdbn01-cgpch-kxyvq1
service_description sv:DB Check Oracle Tbl BIPRD1117_BIPLATFORM_BIPRD
check_command check_oracle_tablespace!BIPRD!"nagios"!"*****"!BIPRD1117_BIPLATFORM!90!85
retry_interval 5
}
define service{
use generic-service-basic
host_name chaprdbn01-cgpch-kxyvq1
service_description sv:DB Check Oracle Tbl BIPRD1117_IAS_TEMP_BIPRD
check_command check_oracle_tablespace!BIPRD!"nagios"!"******"!BIPRD1117_IAS_TEMP!90!85
retry_interval 5
}
I am getting correct output for one but not getting proper output for other. Both the services are in OK state.
Below is the correct output:
BIPRD : BIPRD1117_BIPLATFORM OK - 80.70% used [ 116488 / 601083 MB available ]
Below is the incorrect output:
BIPRD : BIPRD1117_IAS_TEMP OK - % used [ / MB available ]
If you see, the one giving incorrect output is giving incomplete information with OK state.
Could you please help us to understand this issue?
Re: Tablespace monitoring
Posted: Fri Jan 15, 2021 3:09 pm
by vtrac
Hi kalyanpabolu,
Could you please run the below commands and upload the results to this post/ticket?
Code: Select all
cd /usr/local/nagios/libexec
./check_oracle_tablespace.sh -s BIPRD -d 'BIPRD1117_BIPLATFORM' -w 80 -c 90
./check_oracle_tablespace.sh -s BIPRD -d 'BIPRD1117_IAS_TEMP' -w 80 -c 90
and
./check_oracle_tablespace.sh -s BIPRD -d 'BIPRD1117_*' -w 80 -c 90
To further investigate the issue, could you please send me the profile.zip and the exact name of the host and services having issues.
To send us your system profile.
Login to the Nagios XI GUI using a web browser.
Click the "Admin" > "System Profile" Menu
Click the "Download Profile" button
Save the profile.zip file and share in a private message or upload it to the post/ticket, and then reply to this post to bring it up in the queue.
Regards,
Vinh
Re: Tablespace monitoring
Posted: Mon Jan 18, 2021 9:21 am
by kalyanpabolu
Hello,
Below is the output:
[root@monprdmgtss03 plugins]# ./check_oracle --tablespace BIPRD "nagios" "n46105" 'BIPRD1117_IAS_TEMP' 90 85
./check_oracle: line 287: [: : integer expression expected
./check_oracle: line 291: [: : integer expression expected
./check_oracle: line 295: [: : integer expression expected
BIPRD : BIPRD1117_IAS_TEMP OK - % used [ / MB available ]|BIPRD1117_IAS_TEMP=%;85;90;0;100
[root@monprdmgtss03 plugins]#
[root@monprdmgtss03 plugins]#
[root@monprdmgtss03 plugins]#
[root@monprdmgtss03 plugins]# ./check_oracle --tablespace BIPRD "nagios" "n46105" 'BIPRD1117_BIPLATFORM' 90 85
BIPRD : BIPRD1117_BIPLATFORM OK - 80.70% used [ 116488 / 601083 MB available ]|BIPRD1117_BIPLATFORM=80.70%;85;90;0;100
[root@monprdmgtss03 plugins]#
[root@monprdmgtss03 plugins]#
[root@monprdmgtss03 plugins]# ./check_oracle --tablespace BIPRD "nagios" "n46105" 'BIPRD1117_*' 90 85
./check_oracle: line 287: [: : integer expression expected
./check_oracle: line 291: [: : integer expression expected
./check_oracle: line 295: [: : integer expression expected
BIPRD : BIPRD1117_* OK - % used [ / MB available ]|BIPRD1117_*=%;85;90;0;100
[root@monprdmgtss03 plugins]#
Re: Tablespace monitoring
Posted: Tue Jan 19, 2021 12:05 pm
by vtrac
Hi kalyanpabolu,
I'm guessing you might have USER privilege (or filesystem permission) on tablespace "BIPRD1117_IAS_TEMP".
Could you please contact your oracle DBA on this tablespace?
Here are the error provided (from you last replied):
./check_oracle: line 287: [: : integer expression expected
./check_oracle: line 291: [: : integer expression expected
./check_oracle: line 295: [: : integer expression expected
Here are line 287, 291 and 295 inside the "check_oracle" script:
Ora1.png
Could you please temporary add the following "echo" to line number "282" inside the "check_oracle" script?
We just can see the output of "result" for each tablespace.
Now, please run the below commends and update the "results" to this post/ticket:
Code: Select all
./check_oracle --tablespace BIPRD "nagios" "n46105" 'BIPRD1117_IAS_TEMP' 90 85
./check_oracle --tablespace BIPRD "nagios" "n46105" 'BIPRD1117_BIPLATFORM' 90 85
Now, please remember to remove line "282" we just added above.
From the "check_oracle" script, here's how its get the "result" from the sqlplus command for "BIPRD1117_IAS_TEMP":
Code: Select all
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`
Regards,
Vinh
Re: Tablespace monitoring
Posted: Wed Jan 20, 2021 11:54 am
by kalyanpabolu
Hello,
I just copied check_oracle script to check_oracle_test script for testing purpose and made the changes there in line 282.
Below is the output:
[root@monprdmgtss03 plugins]# ./check_oracle_test --tablespace BIPRD "nagios" "n46105" 'BIPRD1117_BIPLATFORM' 90 85
TEST-OUTPUT: 116480.73 601083.83 80.70
BIPRD : BIPRD1117_BIPLATFORM OK - 80.70% used [ 116480 / 601083 MB available ]|BIPRD1117_BIPLATFORM=80.70%;85;90;0;100
[root@monprdmgtss03 plugins]#
[root@monprdmgtss03 plugins]# ./check_oracle_test --tablespace BIPRD "nagios" "n46105" 'BIPRD1117_IAS_TEMP' 90 85
TEST-OUTPUT:
./check_oracle_test: line 288: [: : integer expression expected
./check_oracle_test: line 292: [: : integer expression expected
./check_oracle_test: line 296: [: : integer expression expected
BIPRD : BIPRD1117_IAS_TEMP OK - % used [ / MB available ]|BIPRD1117_IAS_TEMP=%;85;90;0;100
[root@monprdmgtss03 plugins]#
Re: Tablespace monitoring
Posted: Thu Jan 21, 2021 3:42 pm
by vtrac
Hi kalyanpabolu,
Our test showed that the return of "result" (TEST-OUTPUT) is empty for tablespace 'BIPRD1117_IAS_TEMP'.
Please talk to your Oracle DBA regarding this.
There might be permission issue for this tablespace.
Regards,
Vinh
Re: Tablespace monitoring
Posted: Wed Jan 27, 2021 8:45 am
by kalyanpabolu
Hello,
I have checked for the access. DBA said that they have given same user level access to all databases.
PFA the screenshot for the same.
Re: Tablespace monitoring
Posted: Thu Jan 28, 2021 11:05 am
by vtrac
Hi kalyanpabolu,
The "check_oracle" script did work for one tablespace "BIPRD1117_BIPLATFORM", which tell me that the script is fine.
Please check with your DBA and see if the BIPRD1117_IAS_TEMP existed on that database (might be a typo, sometime).
I know that the script executes the below select statement to get the "result":
Code: Select all
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`
Please work with your DBA on that select statement above.
Maybe the tablespace is defined but has zero size or the filesystem where the tablespace reside is not mounted ..... don't know!! ....
Best Regards,
VInh
Re: Tablespace monitoring
Posted: Mon Feb 15, 2021 9:00 am
by kalyanpabolu
Hello,
Actually, for some business reasons, we have removed the tablespace that I was referring in my previous conversation from DB.
But, there are multiple other DBs and their tablespaces where we are getting the same issue. One example is EBSPRD DB in which there are three tablespaces (CHCUS,CHCUS_IDX and CHCUS_TMP) and we are getting the same issue there.
As per your suggestion, we checked with DBA Admin and asked the to run the command an we can see that tablespace has size in GB. Also, it s properly mounted in the filesystem. PFA the output for your reference. And, there is no issue at all with the permission.
Could you please help us on it?
Re: Tablespace monitoring
Posted: Mon Feb 15, 2021 12:36 pm
by vtrac
Hi kalyanpabolu,
From what I know, the "check_oracle" script did work for one of the first two tablespaces, which means it is fine.
As to your last three tablespaces, please work with your oracle DBA to see if the user login ID /Password and databases are correct. It could also be permission on the filesystem side as well.
Regards,
Vinh