Get null result with queries for session-usage and process-usage in multitenant architecture

[daniel1808]

Hi,

I have an issue with the plugin when I want to check session-usage and process-usage. I figured out that issue is related to multitenant architecture. It cannot check those metrics from PDB perspective as it is described in oracle document Doc ID 2303360.1. It is no longer possible query v$resource_limit from inside the PDB since it is global level information. It can be queried from a CDB or non-CDB database.

My environment is Oracle database standard edition, version 19c and configured as multitenant. I have created a Nagios database user in PDB where are placed application data.

My version of plugin check_oracle_health is 3.2.1.1.

Example of error:
Use of uninitialized value $value in numeric gt (>) at /usr/local/nagios/libexec/check_oracle_health line 5545. Use of uninitialized value $value in numeric lt (<) at /usr/local/nagios/libexec/check_oracle_health line 5545. Use of uninitialized value $value in numeric gt (>) at /usr/local/nagios/libexec/check_oracle_health line 5566. Use of uninitialized value $value in numeric lt (<) at /usr/local/nagios/libexec/check_oracle_health line 5566. Use of uninitialized value in sprintf at /usr/local/nagios/libexec/check_oracle_health line 2248. Use of uninitialized value in sprintf at /usr/local/nagios/libexec/check_oracle_health line 2251. CRITICAL - unable to aquire session info, 0.00% of session resources used | session_usage=0.00%;80;100

Queries from the plugin:
} elsif ($params{mode} =~ /server::instance::sessionusage/) { $self->{session_usage} = $self->{handle}->fetchrow_array(q{ SELECT current_utilization/limit_value*100 FROM v$resource_limit WHERE resource_name = 'sessions' -- FROM v$resource_limit WHERE resource_name LIKE '%sessions%' }); } elsif ($params{mode} =~ /server::instance::processusage/) { $self->{process_usage} = $self->{handle}->fetchrow_array(q{ SELECT current_utilization/limit_value*100 FROM v$resource_limit WHERE resource_name LIKE '%processes%' });

May I ask you if you have a plan to customize the plugin for multitenant architecture?
Thank you in advance.

Daniel

[daniel1808]

The workaround using a database link from PDB to CDB.

• Create an entry in tnsnames.ora. ORCL is CDB name.
  ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )

• Connect to PDB as user SYSTEM. PDBORCL is a reference to PDB.
  conn system/oracle@pdborcl

• Create a database link. CDB is the name of the database link.
  create database link cdb connect to system identified by oracle using 'ORCL';

• Create a view. RESOURCE_LIMIT_CDB is the name of a view.
  create view RESOURCE_LIMIT_CDB as select * from SYS.V_$RESOURCE_LIMIT@CDB;

• Grant select rights on view to database monitoring user
  grant select on RESOURCE_LIMIT_CDB to nagios;

• Replace select from v$resource_limit to system.resource_limit_cdb in check_oracle_health