Tablespace Usage issue for DB servers

[hoegh]

Hi All,

The Tablespace usage of DB Server went to critical and unknown state
with Status Information as (Service check timed out after 480.03 seconds)
Suggest us What can be done?

Thanks and Regards,
Mayur

[cdienger]

What is the full command that is being run for this check? What results do you get if you run it from the command line? Is there a problem with the remote host, database, or service?

[hoegh]

[[email protected] ~]$ /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib ORACLE_HOME=/usr/lib/oracle/12.1/client64 /usr/local/nagios/libexec/check_oracle_health --connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.23.43)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=linktest.lhc4net.lhc.com)(INSTANCE_NAME=linktest)))" --user dbtestusr --password dbtestpwd --mode tablespace-usage --warning 85 --critical 90

[hoegh]

[nagios@OSLMSnagios root]$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
[nagios@OSLMSnagios root]$ export ORACLE_HOME=/usr/lib/oracle/12.1/client64
[nagios@OSLMSnagios root]$ /usr/local/nagios/libexec/check_oracle_health --connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.23.43)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=linktest.lhc4net.lhc.com)(INSTANCE_NAME=linktest)))" --user dbtestusr --password ********** --mode tablespace-usage --warning 85 --critical 90

----I waited for 5 min then i did CTRL+C and i got error

^CUNKNOWN - check_oracle_health timed out after 60 seconds

whereas same command for other tablespace check is working fine.

[nagios@OSLMSnagios root]$ /usr/local/nagios/libexec/check_oracle_health --connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.23.43)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=linkqa.lhc4net.lhc.com)(INSTANCE_NAME=linkqa)))" --user dbtestusr --password ********** --mode tablespace-usage --warning 85 --critical 90
OK - tbs USERS usage is 0.00%, tbs UNDOTBS1 usage is 0.04%, tbs TEMP usage is 0.04%, tbs SYSTEM usage is 2.37%, tbs SYSAUX usage is 5.74%, tbs INDX_LINK usage is 19.75%, tbs INDX_EVENTS usage is 3.00%, tbs DATA_LINK usage is 27.22%, tbs DATA_EVENTS usage is 2.31% | 'tbs_users_usage_pct'=0.00%;85;90 'tbs_users_usage'=1MB;27852;29491;0;32767 'tbs_users_alloc'=5MB;;;0;32767 'tbs_undotbs1_usage_pct'=0.04%;85;90 'tbs_undotbs1_usage'=12MB;27852;29491;0;32767 'tbs_undotbs1_alloc'=24510MB;;;0;32767 'tbs_temp_usage_pct'=0.04%;85;90 'tbs_temp_usage'=14MB;27852;29491;0;32767 'tbs_temp_alloc'=26661MB;;;0;32767 'tbs_system_usage_pct'=2.37%;85;90 'tbs_system_usage'=777MB;27852;29491;0;32767 'tbs_system_alloc'=780MB;;;0;32767 'tbs_sysaux_usage_pct'=5.74%;85;90 'tbs_sysaux_usage'=1880MB;27852;29491;0;32767 'tbs_sysaux_alloc'=2330MB;;;0;32767 'tbs_indx_link_usage_pct'=19.75%;85;90 'tbs_indx_link_usage'=6320MB;27200;28800;0;32000 'tbs_indx_link_alloc'=11264MB;;;0;32000 'tbs_indx_events_usage_pct'=3.00%;85;90 'tbs_indx_events_usage'=60MB;1700;1800;0;2000 'tbs_indx_events_alloc'=2000MB;;;0;2000 'tbs_data_link_usage_pct'=27.22%;85;90 'tbs_data_link_usage'=8712MB;27200;28800;0;32000 'tbs_data_link_alloc'=12288MB;;;0;32000 'tbs_data_events_usage_pct'=2.31%;85;90 'tbs_data_events_usage'=2261MB;83300;88200;0;98001 'tbs_data_events_alloc'=39061MB;;;0;98001

[hoegh]

there are 3 service added for each database and 1 tablespace monitoring service is only giving error for 1 database only.
we haven't change anything earlier.

[hoegh]

connection to database worked very well , only challenge is tablespace monitoring service.

[nagios@OSLMSnagios bin]$ ./sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 30 15:27:56 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: dbtestusr@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.23.43)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=linktest.lhc4net.lhc.com)(INSTANCE_NAME=linktest)))"
Enter password:
Last Successful login time: Mon Jul 30 2018 15:27:54 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name , open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
LINKTEST READ WRITE

[scottwilkerson]

is it possible that the password you are using for the dbtestusr contains a special char that is being interpreted by the CLI such as a $ > | etc?

If so these would need to be properly escaped

[hoegh]

1. in Nagios tablespace service was being monitored earlier with query :

--connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.23.43)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=linktest.lhc4net.lhc.com) (INSTANCE_NAME=linktest)))" --user dbtestusr --password dbtestpwd --mode tablespace-usage --warning 85 --critical 90

Which I changed with the following query, now database is connecting with SID instead of service name

--connect "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.23.43)(PORT=1521)) (CONNECT_DATA=(SID=LINKTEST)))" --user dbtestusr --password dbtestpwd --mode tablespace-usage --warning 85 --critical 90

2. In both database (linktest and hoeghdb) , I have cleared database recyclebin object.

After apply configuration , both services are now OK and being monitored by Nagios.

Thanks for your help. please close the case.

Thanks

vijay

[scottwilkerson]

Thanks for your help. please close the case.

Thanks

vijay

Glad it is working

Locking