Monitoring Oracle Multitenant database

Supported versions

Instana supports the following Oracle database versions for metrics and configuration data:

• Oracle Database 18c (18.1.0 // 12.2.0.2)
• Oracle Database 19c (19.1.0 // 12.2.0.3)
• Oracle Database 21c (21.1.0)
• Oracle Database 23ai (23.4.0)

Supported character sets

The Oracle Multitenant database sensor supports all character sets that are supported by Oracle to fetch metrics from the Oracle database. For more information about the character sets that the Oracle database supports, see Character Sets.

Required DB permissions

To connect the sensor to Oracle and monitor the metrics and configuration data, you need to have the read permission for the following tables:

#Create a role
create ROLE INSTANAROLE;

#Grant privileges to this role for following tables and viewes

V_$SESSION, V_$BGPROCESS, V_$ACTIVE_SESSION_HISTORY, V_$SYSSTAT, V_$SGASTAT, V_$LIBRARYCACHE,  
V_$LATCH, V_$ROWCACHE, V_$STATNAME, V_$SESSTAT, V_$PROCESS, V_$SQLAREA,  
V_$PARAMETER, V_$SQL, V_$VERSION, V_$SYS_TIME_MODEL, V_$SYSTEM_WAIT_CLASS, V_$INSTANCE,  
V_$LOCK, DBA_HIST_SQLTEXT, DBA_HIST_SQLSTAT, DBA_HIST_SNAPSHOT, DBA_HIST_ACTIVE_SESS_HISTORY,  
DBA_USERS, DBA_OBJECTS, DBA_TABLESPACE_USAGE_METRICS, DBA_DATA_FILES, DBA_TEMP_FILES  

eg. GRANT SELECT ON V_$SESSION to INSTANAROLE;

#Assign this role to a user. Same user needs to be configured in the configuration yaml (see sensor configuration section). 
GRANT INSTANAROLE to <user>;

Configuring the sensor

Instana supports remote monitoring for the Oracle Multitenant database.

To monitor Oracle Multitenant database, you need to provide the credentials through the <instana-agent-dir>/etc/instana/configuration.yaml agent configuration file.

com.instana.plugin.oracledb:
  remote: # we support listing multiple DBs here, both CDB's and PDB's!
    - host: '<ORCL_HOST_1>'
      port: '<ORCL_PORT>'
      user: '<ORCL_USER>' # default is 'sys as sysdba'
      password: '<ORCL_PASSWORD>'
      databaseSID: '<ORCL_DB_SID>' # either databaseSID or databaseServiceName is allowed
      databaseServiceName: '<ORCL_DB_SERVICE_NAME>'
      availabilityZone: 'Remote Monitoring'
      poll_rate: 20
    - host: '<ORCL_HOST_2>'
      port: '<ORCL_PORT>'
      user: '<ORCL_USER>' # default is 'sys as sysdba'
      password: '<ORCL_PASSWORD>'
      databaseSID: '<INSERT_DB_SID>' # either databaseSID or databaseServiceName is allowed
      databaseServiceName: '<ORCL_DB_SERVICE_NAME>'
      availabilityZone: 'Remote Monitoring'
      poll_rate: 5

com.instana.plugin.oracledb:
  remote: # we support listing multiple DBs here, both CDB's and PDB's!
    - host: '<ORCL_HOST_1>'
      port: '<ORCL_PORT>'
      user: '<ORCL_USER>' # default is 'sys as sysdba'
      password: '<ORCL_PASSWORD>'
      databaseSID: '<ORCL_DB_SID>' # either databaseSID or databaseServiceName is allowed
      databaseServiceName: '<ORCL_DB_SERVICE_NAME>'
      availabilityZone: 'Remote Monitoring'
      poll_rate: 20
      customPolling: # Restricted to maximum 3 nonzero poll rates
        - poll_rate: 30 # in seconds
          metrics:
            - TOP_CPU_CONSUMING_SESSION_LAST_10_MIN
            - ACTIVE_SESSIONS_RUNNING_MORE_THAN_600_SECS
            - LIBRARY_CACHE_HIT_RATIO
            - CURRENT_BLOCKING_SESSIONS
        - poll_rate: 60 # in seconds
          metrics:
            - TOP_ELAPSED_TIME_QUERIES
            - TOP_TEN_CPU_CONSUMING_SESSION
            - SQL_ID_CONSUMING_MORE_CPU
            - USER_FOREGROUND_SESSIONS
            - TOP_10_SQL_HIGH_IO_LAST_1_HR
            - ACTIVE_SESSION_HISTORY
        - poll_rate: 3600 # in seconds
          metrics:
            - TOP_10_SQL_HIGH_IO_LAST_24_HR
            - TOP_CPU_QUERIES_LAST_24_HRS
            - QUERIES_NOT_USING_BINDING_VARIABLES

Configuration data

• Version
• SID
• Service names
• Start time
• CPU count
• Max sessions
• DB block size

Performance metrics

Health signatures

For each sensor, a curated knowledge base of health signatures is evaluated continuously against the incoming metrics and is used to raise issues or incidents that impact the user.