## Overview

The template is developed to monitor a single DBMS Oracle Database instance with ODBC and can monitor CDB or non-CDB installations.

## Supported versions

Oracle Database 12c2 and newer.

**Important! This integration queries the `V$ACTIVE_SESSION_HISTORY` dynamic performance view which is part of the Oracle Diagnostics Pack. Please make sure that you have the licence required for using this management pack.**

## Setup

1. Create an Oracle Database user for monitoring:

    In CDB installations, it is possible to monitor tablespaces from the CDB (container database) and all PDBs (pluggable databases). To do so, a common user is needed with the correct rights:

    ```
    CREATE USER c##zabbix_mon IDENTIFIED BY <PASSWORD>;
    -- Grant access to the c##zabbix_mon user.
    ALTER USER c##zabbix_mon SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
    GRANT CONNECT, CREATE SESSION TO c##zabbix_mon;
    GRANT SELECT_CATALOG_ROLE to c##zabbix_mon;
    GRANT SELECT ON v_$instance TO c##zabbix_mon;
    GRANT SELECT ON v_$database TO c##zabbix_mon;
    GRANT SELECT ON v_$sysmetric TO c##zabbix_mon;
    GRANT SELECT ON v_$system_parameter TO c##zabbix_mon;
    GRANT SELECT ON v_$session TO c##zabbix_mon;
    GRANT SELECT ON v_$recovery_file_dest TO c##zabbix_mon;
    GRANT SELECT ON v_$active_session_history TO c##zabbix_mon;
    GRANT SELECT ON v_$osstat TO c##zabbix_mon;
    GRANT SELECT ON v_$process TO c##zabbix_mon;
    GRANT SELECT ON v_$datafile TO c##zabbix_mon;
    GRANT SELECT ON v_$pgastat TO c##zabbix_mon;
    GRANT SELECT ON v_$sgastat TO c##zabbix_mon;
    GRANT SELECT ON v_$log TO c##zabbix_mon;
    GRANT SELECT ON v_$archive_dest TO c##zabbix_mon;
    GRANT SELECT ON v_$asm_diskgroup TO c##zabbix_mon;
    GRANT SELECT ON v_$asm_diskgroup_stat TO c##zabbix_mon;
    GRANT SELECT ON DBA_USERS TO c##zabbix_mon;
    ```
    This is needed because the template uses `CDB_*` views to monitor tablespaces from the CDB and different PDBs - the monitoring user therefore needs access to the container data objects on all PDBs.

    However, if you wish to monitor only a single PDB or a non-CDB instance, a local user is sufficient:
    
    ```
    CREATE USER zabbix_mon IDENTIFIED BY <PASSWORD>;
    -- Grant access to the zabbix_mon user.
    GRANT CONNECT, CREATE SESSION TO zabbix_mon;
    GRANT SELECT_CATALOG_ROLE to zabbix_mon;
    GRANT SELECT ON v_$instance TO zabbix_mon;
    GRANT SELECT ON v_$database TO zabbix_mon;
    GRANT SELECT ON v_$sysmetric TO zabbix_mon;
    GRANT SELECT ON v_$system_parameter TO zabbix_mon;
    GRANT SELECT ON v_$session TO zabbix_mon;
    GRANT SELECT ON v_$recovery_file_dest TO zabbix_mon;
    GRANT SELECT ON v_$active_session_history TO zabbix_mon;
    GRANT SELECT ON v_$osstat TO zabbix_mon;
    GRANT SELECT ON v_$process TO zabbix_mon;
    GRANT SELECT ON v_$datafile TO zabbix_mon;
    GRANT SELECT ON v_$pgastat TO zabbix_mon;
    GRANT SELECT ON v_$sgastat TO zabbix_mon;
    GRANT SELECT ON v_$log TO zabbix_mon;
    GRANT SELECT ON v_$archive_dest TO zabbix_mon;
    GRANT SELECT ON v_$asm_diskgroup TO zabbix_mon;
    GRANT SELECT ON v_$asm_diskgroup_stat TO zabbix_mon;
    GRANT SELECT ON DBA_USERS TO zabbix_mon;
    ```
    **Important! Ensure that the ODBC connection to Oracle includes the session parameter `NLS_NUMERIC_CHARACTERS= '.,'`. It is important for displaying the float numbers in Zabbix correctly.**

    **Important! These privileges grant the monitoring user `SELECT_CATALOG_ROLE`, which, in turn, gives access to thousands of tables in the database.**
    This role is required to access the `V$RESTORE_POINT` dynamic performance view.
    However, there are ways to go around this, if the `SELECT_CATALOG_ROLE` assigned to a monitoring user raises any security issues.
    One way to do this is using **pipelined table functions**:

      1. Log into your database as the `SYS` user or make sure that your administration user has the required privileges to execute the steps below;

      2. Create types for the table function:

          ```sql
          CREATE OR REPLACE TYPE zbx_mon_restore_point_row AS OBJECT (
            SCN                           NUMBER,
            DATABASE_INCARNATION#         NUMBER,
            GUARANTEE_FLASHBACK_DATABASE  VARCHAR2(3),
            STORAGE_SIZE                  NUMBER,
            TIME                          TIMESTAMP(9),
            RESTORE_POINT_TIME            TIMESTAMP(9),
            PRESERVED                     VARCHAR2(3),
            NAME                          VARCHAR2(128),
            PDB_RESTORE_POINT             VARCHAR2(3),
            CLEAN_PDB_RESTORE_POINT       VARCHAR2(3),
            PDB_INCARNATION#              NUMBER,
            REPLICATED                    VARCHAR2(3),
            CON_ID                        NUMBER
          );
          CREATE OR REPLACE TYPE zbx_mon_restore_point_tab IS TABLE OF zbx_mon_restore_point_row;
          ```

      3. Create the pipelined table function:

          ```sql
          CREATE OR REPLACE FUNCTION zbx_mon_restore_point RETURN zbx_mon_restore_point_tab PIPELINED AS
          BEGIN
            FOR i IN (SELECT * FROM V$RESTORE_POINT) LOOP
              PIPE ROW (zbx_mon_restore_point_row(i.SCN, i.DATABASE_INCARNATION#, i.GUARANTEE_FLASHBACK_DATABASE, i.STORAGE_SIZE, i.TIME, i.RESTORE_POINT_TIME, i.PRESERVED, i.NAME, i.PDB_RESTORE_POINT, i.CLEAN_PDB_RESTORE_POINT, i.PDB_INCARNATION#, i.REPLICATED, i.CON_ID));
            END LOOP;
            RETURN;
          END;
          ```

      4. Grant the Zabbix monitoring user the Execute privilege on the created pipelined table function and replace the monitoring user `V$RESTORE_POINT` view with the `SYS` user function (in this example, the `SYS` user is used to create DB types and function):

          ```sql
          GRANT EXECUTE ON zbx_mon_restore_point TO c##zabbix_mon;
          CREATE OR REPLACE VIEW c##zabbix_mon.V$RESTORE_POINT AS SELECT * FROM TABLE(SYS.zbx_mon_restore_point);
          ```

      5. Finally, revoke the `SELECT_CATALOG_ROLE` and grant additional permissions that were previously covered by the `SELECT_CATALOG_ROLE`.

          ```sql
          REVOKE SELECT_CATALOG_ROLE FROM c##zabbix_mon;
          GRANT SELECT ON v_$pdbs TO c##zabbix_mon;
          GRANT SELECT ON v_$sort_segment TO c##zabbix_mon;
          GRANT SELECT ON v_$parameter TO c##zabbix_mon;
          GRANT SELECT ON CDB_TABLESPACES TO c##zabbix_mon;
          GRANT SELECT ON CDB_DATA_FILES TO c##zabbix_mon;
          GRANT SELECT ON CDB_FREE_SPACE TO c##zabbix_mon;
          GRANT SELECT ON CDB_TEMP_FILES TO c##zabbix_mon;
          ```

      > Note that in these examples, the monitoring user is named `c##zabbix_mon` and the system user - `SYS`. Change these example usernames to ones that are appropriate for your environment.
    
    If this workaround does not work for you, there are more options available, such as __materialized views__, but look out for data refresh as `V$RESTORE_POINT` is a dynamic performance view.

2. Install the ODBC driver on Zabbix server or Zabbix proxy.
  See the [Oracle documentation](https://www.oracle.com/database/technologies/releasenote-odbc-ic.html) for instructions.

3. Configure Zabbix server or Zabbix proxy for using the Oracle environment:

    This step is required only when:
    
    * installing Oracle Instant Client with .rpm packages with a version < 19.3 (if Instant Client is the only Oracle software installed on Zabbix server or Zabbix proxy);
    
    * installing Oracle Instant Client manually with .zip files.
  
    There are multiple configuration options:
    
      1. Using the `LDCONFIG` utility **(recommended option)**:
  
          To update the runtime link path, it is recommended to use the ```LDCONFIG``` utility, for example:
    
          ```
          # sh -c "echo /opt/oracle/instantclient_19_18 > /etc/ld.so.conf.d/oracle-instantclient.conf"
          # ldconfig
          ```
  
      2. Using the application configuration file:
  
          An alternative solution is to export the required variables by editing or adding a new application configuration file:
    
           * ```/etc/sysconfig/zabbix-server # for server```
    
           * ```/etc/sysconfig/zabbix-proxy # for proxy```
    
          And then, adding:
        
          ```
          # Oracle Instant Client library
          LD_LIBRARY_PATH=/opt/oracle/instantclient_19_18:$LD_LIBRARY_PATH
          export LD_LIBRARY_PATH
          ```
    
    Keep in mind that the library paths will vary depending on your installation.

    This is a minimal configuration example. Depending on the Oracle Instant Client version, required functionality and host operating system, a different set of additional packages might need to be installed.
    For more detailed configuration instructions, see the [official Oracle Instant Client installation instructions for Linux](https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html).

4. Restart Zabbix server or Zabbix proxy.

5. Set the username and password in host wizard configuration fields `Username` and `Password`.

6. Set the `Oracle URL`, `Oracle driver path` and `Oracle Service Name` in host wizard configuration fields.

    * `Oracle URL` is a hostname or IP address of the Oracle DB instance.

    * `Oracle driver path` is a path to the driver location in the OS. The ODBC driver file should be found in the Instant Client directory and named `libsqora.so.XX.Y`.

    * `Oracle Service Name` is a service name to which the host will connect to. It is important as it determines if the connection is established to a non-CDB, CDB, or PDB. If you wish to monitor tablespaces of all PDBs, you will need to set a service name that points to the CDB.
      Active service names can be seen from the instance running Oracle Database with `lsnrctl status`.
      
    **Important! Make sure that the user created in step #1 is present on the specified service.**

    The "Service's TCP port state" item uses `{$ORACLE.HOST}` and `{$ORACLE.PORT}` macros to check the availability of the listener.