HPA installation and deployment HPA agent Calypso Infrastructure Agent - Oracle extension

It is possible to have some more detailed execution on the Oracle instance (RAC or not).

PostgreSQL is not supported yet for the advanced database monitoring.

This extension connect to database with a readonly user and gathers some statistics that are displayed on the HPA portal.

Prerequisites

This is an extension of the Infrastructure agent, this one must be activated APMIA/Infrastructure Agent.

Oracle monitoring

If you activate the Oracle monitoring, set the following values in {{hpa_agent_dir}}/hpa_agent_config.sh :

# For Oracle Monitoring
export HPA_CALYPSO_DATABASE_ENABLED=0
export HPA_CALYPSO_DATABASE_RAC_ENABLED=0
export HPA_CALYPSO_DATABASE_INSTANCE_NAME=CALYPSO
export HPA_CALYPSO_DATABASE_HOST=localhost
export HPA_CALYPSO_DATABASE_PORT=1521
export HPA_CALYPSO_DATABASE_USER=calypso
  • if you access Oracle with SID, set HPA_CALYPSO_DATABASE_ENABLED=1
  • if you access Oracle with RAC service name, set HPA_CALYPSO_DATABASE_RAC_ENABLED=1
  • set the others properties

To set the password, run the {{hpa_agent_dir}}/deploy.sh <version> command :

  • either password is copied from the previous configuration
  • otherwise the file and properties to update are displayed > Write password in clear text, once the agent started, it encrypts the password.

Oracle User - Option #1

To monitor Oracle database, ensure that the configured user has the access privileges of all the following database tables:

v_$... tables
dba_data_files
DBA_TABLESPACE_USAGE_METRICS

Oracle User - Option #2

Create a user, here iscope_oracledb represents a user name.

CREATE USER iscope_oracledb identified by iscope_oracledb
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
GRANT CONNECT TO iscope_oracledb;

create role iscope_role;
 
grant select, insert on PLAN_TABLE to iscope_role;
grant select on dba_data_files to iscope_role;
grant select on dba_segments to iscope_role;
grant select on dba_tablespace_usage_metrics to iscope_role;
grant create session to iscope_role;
grant iscope_role to iscope_oracledb;

For Oracle RAC instance :

grant select on gv_$statname to iscope_role;
grant select on gv_$system_wait_class to iscope_role;
grant select on gv_$sql to iscope_role;
grant select on gv_$waitclassmetric to iscope_role;
grant select on gv_$db_object_cache to iscope_role;
grant select on gv_$filestat to iscope_role;
grant select on gv_$instance to iscope_role;
grant select on gv_$librarycache to iscope_role;
grant select on gv_$lock to iscope_role;
grant select on gv_$mystat to iscope_role;
grant select on gv_$parameter to iscope_role;
grant select on gv_$resource_limit to iscope_role;
grant select on gv_$rollstat to iscope_role;
grant select on gv_$rowcache to iscope_role;
grant select on gv_$session to iscope_role;
grant select on gv_$session_wait to iscope_role;
grant select on gv_$sesstat to iscope_role;
grant select on gv_$sga to iscope_role;
grant select on gv_$sql_plan to iscope_role;
grant select on gv_$sqlarea to iscope_role;
grant select on gv_$sysmetric to iscope_role;
grant select on gv_$sysstat to iscope_role;
grant select on gv_$system_event to iscope_role;

For Oracle NOT RAC instance :

grant select on v_$statname to iscope_role;
grant select on v_$system_wait_class to iscope_role;
grant select on v_$sql to iscope_role;
grant select on v_$waitclassmetric to iscope_role;
grant select on v_$db_object_cache to iscope_role;
grant select on v_$filestat to iscope_role;
grant select on v_$instance to iscope_role;
grant select on v_$librarycache to iscope_role;
grant select on v_$lock to iscope_role;
grant select on v_$mystat to iscope_role;
grant select on v_$parameter to iscope_role;
grant select on v_$resource_limit to iscope_role;
grant select on v_$rollstat to iscope_role;
grant select on v_$rowcache to iscope_role;
grant select on v_$session to iscope_role;
grant select on v_$session_wait to iscope_role;
grant select on v_$sesstat to iscope_role;
grant select on v_$sga to iscope_role;
grant select on v_$sql_plan to iscope_role;
grant select on v_$sqlarea to iscope_role;
grant select on v_$sysmetric to iscope_role;
grant select on v_$sysstat to iscope_role;
grant select on v_$system_event to iscope_role;

Other information

Infrastructure agent log files can be found here : {{hpa_agent_dir}}/apmia/logs