Oracle Monitoring issue

[tgriep]

The folders and permissions look good for the Oracle Client and the perl modules.
When we ran the check with the wrong username, password or permissions for the Oracle database, we did see errors similar to what you are getting.
Can you show us how the nagios account was created on the Oracle server and also, what version is the Oracle database?

[raamardhani7]

The folders and permissions look good for the Oracle Client and the perl modules.
When we ran the check with the wrong username, password or permissions for the Oracle database, we did see errors similar to what you are getting.
Can you show us how the nagios account was created on the Oracle server and also, what version is the Oracle database?

Hi Tom,

Below are the permissions that is given, and the oracle DB is 11g.

Code: Select all

grant create session to nagios;
grant select any dictionary to nagios;
grant select on V_$SYSSTAT to nagios;
grant select on V_$INSTANCE to nagios;
grant select on V_$LOG to nagios;
grant select on SYS.DBA_DATA_FILES to nagios;
grant select on SYS.DBA_FREE_SPACE to nagios;
--

grant read on directory BDUMP_DIR to nagios;
grant create session to nagios;
grant select on dba_data_files to nagios;
grant select on dba_free_space to nagios;
grant select on filext$ to nagios;
grant select on v_$librarycache to nagios;
grant select on v_$process to nagios;
grant select on v_$sesstat to nagios;
grant select on v_$sort_segment to nagios;
grant select on v_$sysstat to nagios;
grant select on v_$instance to nagios;
grant select on v_$parameter to nagios;
grant select on v_$session to nagios;
grant select on dba_tablespaces to nagios;
grant select on sys.alert_log to nagios;
grant select on system.alert_log to nagios;
grant select on system.alert_log_external to nagios;
grant execute on system.alert_log_date to nagios;
grant select on V_$RMAN_BACKUP_JOB_DETAILS to nagios;
grant select on V_$LOGSTDBY_PROGRESS to nagios;
grant select on DBA_LOGSTDBY_LOG to nagios;
grant select on v_$archived_log to nagios;
grant select on v_$archive_gap to nagios;
grant select on dba_autotask_job_history to nagios;
grant select on DBA_SCHEDULER_JOB_RUN_DETAILS to nagios;
grant select on dba_tables to nagios;
grant select on V_$DATAGUARD_STATS to nagios;
grant select on v_$backup_set to nagios;
grant select on v_$backup_piece to nagios;
grant select on V_$BACKUP_DATAFILE to nagios;
grant select on DBA_TAB_STATS_HISTORY to nagios;

Code: Select all

Grant ALTER SESSION to nagios;
Grant CONNECT to nagios;
Grant CREATE SESSION to nagios;
Grant SELECT ANY TABLE to nagios;

And I could see data collection happening. But there is one issue here again.

Code: Select all

Oracle Query - Database Status
UNKNOWN - got no valid response for select open_mode from v - ORA-04044: procedure, function, package, or type is not allowed here (DBD ERROR: error possibly near <*> indicator at char 22 in 'select open_mode from <*>v')

Command used is:

Code: Select all

/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health --connect 'xx.xx.xx.xx:1521/dbinstance' --username 'userid' --password 'passwd' --mode sql --name="select open_mode from v\$database" --critical DOWN
OUTPUT: UNKNOWN - got no valid response for select open_mode from v$database

Looks like the command used is wrong, please correct me if my command used is wrong, thanks.

[tgriep]

Is this the actual name of the database you are running the query against?

Code: Select all

v$database

If so, edit your command and instead of using \$, try this instead.

Code: Select all

"select open_mode from v$"$"database"

Try that and let us know if this helps.

[raamardhani7]

Is this the actual name of the database you are running the query against?

Code: Select all

v$database

If so, edit your command and instead of using \$, try this instead.

Code: Select all

"select open_mode from v$"$"database"

Try that and let us know if this helps.

Hi Tom,

Tried the below..

Code: Select all

/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health --connect 'xx.xx.xx.xx:1521/dbinstance' --username 'user' --password 'passwd' --mode sql --name="select open_mode from v\$"\$"database" --critical DOWN
OUTPUT: UNKNOWN - got no valid response for select open_mode from v$$database - ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 22 in 'select open_mode from <*>v$$database')

Code: Select all

/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health --connect 'xx.xx.xx.xx:1521/JDAAPDEV' --username 'user' --password 'passwd' --mode sql --name="select open_mode from v"\$"database" --critical DOWN
OUTPUT: UNKNOWN - got no valid response for select open_mode from v$database

Both of them are not giving favorable results..

[tgriep]

What is the name of the database you are trying to run the query against?

Remove the \ from the command, try it again and let us know if it works.

[raamardhani7]

What is the name of the database you are trying to run the query against?

Remove the \ from the command, try it again and let us know if it works.

Name of the DB is:JDAAPDEV

Code: Select all

--connect 'xx.xx.xx.xx:1521/JDAAPDEV' --username 'user' --password 'nagpasswdios' --mode sql --name="select open_mode from v"$"database" --critical DOWN

But when testing this command, the output is :

Code: Select all

COMMAND: /bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health --connect 'xx.xx.xx.xx:1521/JDAAPDEV' --username 'user' --password 'passwwd' --mode sql --name="select open_mode from v"\$"database" --critical DOWN
OUTPUT: UNKNOWN - got no valid response for select open_mode from v$database

[tgriep]

I found this on the plugins web site, It looks like when using the mode sql, the query can only return a number.
The open_mode query returns a string so it will not work.

[raamardhani7]

I found this on the plugins web site, It looks like when using the mode sql, the query can only return a number.
The open_mode query returns a string so it will not work.

Hi Tom,

Could you please suggest what can be done here, thanks.

[tgriep]

I did a quick search but couldn't find a plugin to do a query that returns a string but you can take a look at our exchange site to see if you can find something.
https://exchange.nagios.org/directory/P ... ses/Oracle