Database

Data is obtained via ODBC.

Prerequisites

  • Add this line to pg_hba.conf on PostgreSQL host:
    host   all   xormon   monitoring machine IP/hostname   md5
    
    Running this command on your DB can help you locate pg_hba.conf
    SHOW hba_file;
    
    After you complete this step restart the PostgresSQL service

  • Make sure you have installed perl modules: perl-DBI and perl-DBD-Pg on XorMon NG server
    Skip it in case of XorMon NG Virtual Appliance or Docker deployment
    $ rpm -q perl-DBI perl-DBD-Pg
      perl-DBI-1.627-4.el7.x86_64
      perl-DBD-Pg-2.19.3-4.el7.x86_64
    
    Install them if they are missing under root user
    • Linux RedHat, CentOS
      # yum install perl-DBD-Pg
      
    • Linux Debian, Ubuntu
      # apt-get install libdbd-pg-perl
      

Connectivity check

    Allow access from the XorMon NG host to PostgreSQL host on port 5432

Create DB user

    Create database user ("xormon" in this example) with these privileges on each database on host machine.
    CREATE ROLE xormon WITH PASSWORD 'your_passw0rd' LOGIN;
    GRANT SELECT ON TABLE pg_stat_bgwriter TO xormon;
    GRANT SELECT ON TABLE pg_stat_database TO xormon;
    GRANT SELECT ON TABLE pg_stat_activity TO xormon;
    GRANT SELECT ON TABLE pg_locks TO xormon;
    GRANT SELECT ON TABLE pg_cursors TO xormon;
    GRANT SELECT ON TABLE pg_stat_progress_vacuum TO xormon;
    GRANT SELECT ON TABLE pg_stat_replication TO xormon;
    GRANT SELECT ON TABLE pg_stat_wal_receiver TO xormon;
    

Attach PostgreSQL

  • As PostgreSQL gives us info about every DB on host machine we suggest that you use the machine hostname as "Host alias" and choose one DB for initial connect. XorMon NG UI ➡ Device ➡ Database ➡PostgreSQL DB ➡ New :
    PostgreSQL monitoring configuration
    In case of error: SCRAM authentication requires libpq version 10 or above
    Either upgrade libpq to version 10+ or change users password authentication to md5 and generate new password

  • Run "Test" for configured Oracle VM host, it must show "OK"

  • Wait about 1 hour, then refresh the web browser cache by Ctrl-F5

Oracle DB monitoring schema
Data is obtained through SQL (SQL*Plus). Oracle client must be installed on XorMon NG server.
Monitoring implementation does not require any Oracle licensed features like Oracle Diagnostics Pack or Oracle Tuning Pack .

Monitoring requires a database user with the following view only permissions to collect monitoring data:
  • create session
  • select rights on specific sys.* tables
Note a bug in Oracle Database 19.11.0.0 and 19.12.0.0

Oracle Instant Client and SQL*Plus installation

Oracle Instant Client and SQL Plus are used for collecting information from an Oracle DB instance.
Oracle DB user is needed with sufficient privileges.
Version compatibility: OIC 19c supports Oracle DB versions 11.2.0.4 and newer.
If you are using previously installed SQL Plus, make sure it is version 12.2 and above.
Oracle Instant Client (OIC) can be deployed from a ZIP file or installed as an RPM package.
Installation steps are performed under root account

  • Linux: libaio prerequisite (skip it in case of XorMon NG Virtual Appliance or Docker deployment)
    • RedHat:
      yum -y install libaio.x86_64
    • Debian:
      apt-get -y install libaio1 
      
  • Linux CentOS8/RHEL8: libnsl.so.1 might not have been in the OS present, install it:
    dnf install libnsl.x86_64
    
  • Installation from ZIP files
    • AIX: note that Oracle Client packages does not work on AIX 6.1. It works on AIX 7.1 and newer.

    • Download ZIP packages from Oracle Download website Linux x86-64, AIX
      OIC basic: instantclient-basic-linux.x64-19.6.0.0.0dbru.zip
      SQL*Plus: instantclient-sqlplus-linux.x64-19.6.0.0.0dbru.zip

      Create /opt/oracle directory and unzip
      # umask 022
      # mkdir /opt/oracle
      # cd /opt/oracle
      # unzip /<download_dir>/instantclient-basic-linux.x64-19.6.0.0.0dbru.zip
      # unzip /<download_dir>/instantclient-sqlplus-linux.x64-19.6.0.0.0dbru.zip
      
      ZIP files will be unpacked into /opt/oracle/instantclient_<version>

    • ldconfig
      # echo "/opt/oracle/instantclient_<version>" > /etc/ld.so.conf.d/oracle-instantclient.conf
      # ldconfig
      
  • RPM installation
    • Download RPM packages from Oracle Download site:Linux x86-64
      OIC basic: oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
      SQL*Plus: oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm

      or directly via wget:
      wget https://download.oracle.com/otn_software/linux/instantclient/19600/oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
      wget https://download.oracle.com/otn_software/linux/instantclient/19600/oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm
      
    • Install on RedHat, Rocky
      yum install oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm       
    • Install on Debian, Ubuntu (install "alien" if it is not there)
      apt-get update
      apt-get -y install alien
      alien --to-deb --scripts  oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
      alien --to-deb --scripts  oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm
      apt-get install -y libaio1  # Oracle client prerequisite
      apt-get install -y ./oracle-instantclient19.6-basic_19.6.0.0.0-2_amd64.deb
      apt-get install -y ./oracle-instantclient19.6-sqlplus_19.6.0.0.0-2_amd64.deb
      apt list --installed| grep orac
        oracle-instantclient19.6-basic/now 19.6.0.0.0-2 amd64 [installed,local]
        oracle-instantclient19.6-sqlplus/now 19.6.0.0.0-2 amd64 [installed,local] 
      ls -l /usr/lib/oracle/19.6/client64/bin/sqlplus
        -rwxr-xr-x 1 root root 24472 Dec 17  2019 /usr/lib/oracle/19.6/client64/bin/sqlplus  

Create DB user

Database user in each Oracle instance must be created, it will be used for monitoring access.
You can choice whatever user name (below examle show "xormon" user, then specify it in XorMon NG Database properties.
  • Standalone / RAC
    $ sqlplus sys"/as sysdba"
      create user xormon_check identified by "<password>" default tablespace USERS temporary tablespace TEMP;
      grant create session to xormon_check;
      grant select on sys.v_$instance_ping to xormon_check;
      grant select on sys.v_$resource_limit to xormon_check;
      grant select on sys.gv_$resource_limit to xormon_check;
      grant select on sys.v_$sysmetric_summary to xormon_check;
      grant select on sys.gv_$sysmetric_summary to xormon_check;
      grant select on sys.v_$sysmetric_history to xormon_check;
      grant select on sys.gv_$sysmetric_history to xormon_check;
      grant select on sys.v_$datafile to xormon_check;
      grant select on sys.gv_$datafile to xormon_check;
      grant select on sys.v_$filestat to xormon_check;
      grant select on sys.gv_$filestat to xormon_check;
      grant select on sys.v_$instance to xormon_check;
      grant select on sys.gv_$instance to xormon_check;
      grant select on sys.v_$database to xormon_check;
      grant select on sys.gv_$database to xormon_check;
      grant select on sys.v_$osstat to xormon_check;
      grant select on sys.gv_$osstat to xormon_check;
      grant select on sys.v_$session to xormon_check;
      grant select on sys.gv_$session to xormon_check;
      grant select on sys.v_$sgainfo to xormon_check;
      grant select on sys.gv_$sgainfo to xormon_check;
      grant select on sys.v_$containers to xormon_check;
      grant select on sys.gv_$containers to xormon_check;
      grant select on sys.v_$pgastat to xormon_check;
      grant select on sys.gv_$pgastat to xormon_check;
      grant select on sys.v_$system_wait_class to xormon_check;
      grant select on sys.gv_$system_wait_class to xormon_check;
      grant select on sys.v_$system_event  to xormon_check;
      grant select on sys.gv_$system_event  to xormon_check;
      grant select on sys.v_$cluster_interconnects  to xormon_check;
      grant select on sys.gv_$cluster_interconnects to xormon_check;
      grant select on sys.v_$sysstat to xormon_check;
      grant select on sys.gv_$sysstat to xormon_check;
      grant select on sys.v_$service_stats to xormon_check;
      grant select on sys.gv_$service_stats to xormon_check;
      grant select on sys.v_$event_histogram to xormon_check;
      grant select on sys.gv_$event_histogram to xormon_check;
      grant select on dba_free_space to xormon_check;
      grant select on dba_temp_free_space to xormon_check;
      grant select on dba_data_files to xormon_check;
      grant select on dba_registry to xormon_check;
      grant select on dba_temp_files to xormon_check;
      grant select on sys.registry$history to xormon_check;
      grant select on sys.REGISTRY$SQLPATCH to xormon_check;
      grant select on sys.v_$recovery_file_dest to xormon_check;
      grant select on sys.v_$log to xormon_check;
      grant select on sys.v_$controlfile to xormon_check;
      grant select on dba_alert_history_detail to xormon_check;
      grant select on sys.v_$logfile to xormon_check;
      exit;
    
  • Multitenant
    $ sqlplus sys"/as sysdba"
      create user xormon_check identified by "<password>" default tablespace USERS temporary tablespace TEMP CONTAINER = ALL;
      grant create session to xormon_check CONTAINER = ALL;
      grant SET CONTAINER to xormon_check CONTAINER = ALL;
      grant select on sys.v_$instance_ping to xormon_check CONTAINER = ALL;
      grant select on sys.v_$resource_limit to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$resource_limit to xormon_check CONTAINER = ALL;
      grant select on sys.v_$sysmetric_summary to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$sysmetric_summary to xormon_check CONTAINER = ALL;
      grant select on sys.v_$sysmetric_history to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$sysmetric_history to xormon_check CONTAINER = ALL;
      grant select on sys.v_$datafile to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$datafile to xormon_check CONTAINER = ALL;
      grant select on sys.v_$filestat to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$filestat to xormon_check CONTAINER = ALL;
      grant select on sys.v_$instance to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$instance to xormon_check CONTAINER = ALL;
      grant select on sys.v_$database to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$database to xormon_check CONTAINER = ALL;
      grant select on sys.v_$osstat to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$osstat to xormon_check CONTAINER = ALL;
      grant select on sys.v_$session to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$session to xormon_check CONTAINER = ALL;
      grant select on sys.v_$sgainfo to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$sgainfo to xormon_check CONTAINER = ALL;
      grant select on sys.v_$containers to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$containers to xormon_check CONTAINER = ALL;
      grant select on sys.v_$pgastat to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$pgastat to xormon_check CONTAINER = ALL;
      grant select on sys.v_$system_wait_class to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$system_wait_class to xormon_check CONTAINER = ALL;
      grant select on sys.v_$system_event  to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$system_event  to xormon_check CONTAINER = ALL;
      grant select on sys.v_$con_sysmetric_summary  to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$con_sysmetric_summary  to xormon_check CONTAINER = ALL;
      grant select on sys.v_$con_sysmetric_history   to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$con_sysmetric_history  to xormon_check CONTAINER = ALL;
      grant select on sys.v_$cluster_interconnects  to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$cluster_interconnects to xormon_check CONTAINER = ALL;
      grant select on sys.v_$sysstat to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$sysstat to xormon_check CONTAINER = ALL;
      grant select on sys.v_$service_stats to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$service_stats to xormon_check CONTAINER = ALL;
      grant select on sys.v_$event_histogram to xormon_check CONTAINER = ALL;
      grant select on sys.gv_$event_histogram to xormon_check CONTAINER = ALL;
      grant select on dba_free_space to xormon_check CONTAINER = ALL;
      grant select on dba_temp_free_space to xormon_check CONTAINER = ALL;
      grant select on dba_data_files to xormon_check CONTAINER = ALL;
      grant select on dba_registry to xormon_check CONTAINER = ALL;
      grant select on dba_temp_files to xormon_check CONTAINER = ALL;
      grant select on sys.registry$history to xormon_check CONTAINER = ALL;
      grant select on sys.REGISTRY$SQLPATCH to xormon_check CONTAINER = ALL;
      grant select on sys.v_$recovery_file_dest to xormon_check CONTAINER = ALL;
      grant select on sys.v_$log to xormon_check CONTAINER = ALL; 
      grant select on sys.v_$controlfile to xormon_check CONTAINER = ALL;
      grant select on dba_alert_history_detail to xormon_check CONTAINER = ALL;
      grant select on sys.v_$logfile to xormon_check CONTAINER = ALL; 
      exit;
    
  • DataGuard: Standalone/RAC
    Enhance user priviledges on primary database. DG instance does not need xormon user.
      grant select on sys.v_$dataguard_config  to xormon_check;
      grant select on sys.v_$ARCHIVE_DEST to xormon_check;
      grant select on sys.v_$archive_dest_status to xormon_check;
    
  • DataGuard: Multitenant/RAC Multitenant
    Enhance user priviledges on primary database. DG instance does not need xormon user.
      grant select on sys.v_$dataguard_config  to xormon_check CONTAINER = ALL;
      grant select on sys.v_$ARCHIVE_DEST to xormon_check CONTAINER = ALL;
      grant select on sys.v_$archive_dest_status to xormon_check CONTAINER = ALL;
    
  • Note that for older databases than 12c you get errors like this, ignore them (you do not get this data from database: PSU,patches info)
    SQL> grant SET CONTAINER to xormon;      
         ORA-00990: missing or invalid privilege
    SQL> grant select on sys.REGISTRY$SQLPATCH to xormon;
         ORA-00942: the table does not exist  
    

Connectivity check

Allow access from the XorMon NG host to Oracle DB host.
Make sure you are using correct port.
Default port for Oracle DB sessions is 1521.

Attach Oracle Databases

  • XorMon NG UI ➡ Device ➡ Database ➡ Oracle DB ➡ New :
    Oracle DB monitoring configuration

    Oracle service: to list Oracle service use this cmd on the server where DB is running: lsnrctl status
    Oracle RAC: Do not use SCAN IP in the configuration, add Virtual IP for each RAC node/instance separately. These instances should use the same listener service
    Make sure that connection test passes without errors or warnings.

  • Number of attached Oracle RAC instances is limited to 1 in the Free edition.
    However you can attach Oracle RAC nodes even as stand-alone instances if you reach the limitation, there is no limit for attaching of Oracle stand-alone DB instances.

  • Run "Test" for configured database, it must show "OK"

  • Wait about 1 hour, then refresh the web browser cache by Ctrl-F5

Data is obtained via ODBC.

Prerequisites

  • Install one of the supported ODBC drivers for SQL Server:
  • Make sure you have installed perl modules: perl-DBI and perl-DBD-ODBC on XorMon NG server
    # rpm -q perl-DBI perl-DBD-ODBC
      perl-DBI-1.627-4.el7.x86_64
      perl-DBD-ODBC-1.50-3.el7.x86_64
    
    Install them if they are missing under root user (Linux RedHat, CentOS):
    # yum install perl-DBD-ODBC
    

Connectivity check

    Allow access from the XorMon NG host to SQL Server host.
    Make sure you are using correct port. Default port for SQL server sessions is 1433.

Create DB user

    Create database user with these privileges on each database on host machine.
    CREATE LOGIN xormon WITH PASSWORD = 'your_passw0rd';
    CREATE USER xormon FOR LOGIN xormon; 
    GRANT CONNECT ANY DATABASE TO xormon;
    USE master;
    GRANT VIEW SERVER STATE TO xormon;
    

Attach Microsoft SQL Server

  • As SQL Server gives us info about every DB on host machine we suggest that you use the machine hostname as "Host alias" and choose one DB for initial connect.
    XorMon NG UI ➡ Device ➡ Database ➡ Microsoft SQL Server ➡ New :
    Microsoft SQL Server monitoring configuration

    Use <servername>\<instancename> in the Host field if you have Named Instances like in this example:
    Microsoft SQL Server monitoring configuration named

  • Run "Test" for configured database, it must show "OK"

  • Wait about 1 hour, then refresh the web browser cache by Ctrl-F5

IBM Db2 monitoring schema

It gets data using Perl module DBD::DB2

Prerequisites

Skip this in case you are on the XorMon NG Virtual Appliance

  • Install Perl DBI and DBD::DB2 modules as per www.ibm.com/support/pages/db2-perl-database-interface-luw
    Get from above page driver package of requested version (like ibm_data_server_driver_package_linuxx64_v11.5.tar.gz) and put it into /tmp
    # under root
    
    umask 022
    cd /tmp
    tar xvfz /tmp/ibm_data_server_driver_package_linuxx64_v11.5.tar.gz
    cd /tmp/dsdriver
    ./installDSDriver
    . ./db2profile
    export DB2_HOME=/tmp/dsdriver 
    export DB2LIB=/tmp/dsdriver/lib
    cpanm DBD::DB2
    
    # copy DB2 libraries to /usr/local/lib
    cp -r /tmp/dsdriver/lib/* /usr/local/lib/
    
    # and index them for usage
    Ldconfig /usr/local/lib
    
    # test Perl DBD::DB2 module (there should be no errors)
    perl -MDBD::DB2 -le 'print $DBD::DB2::VERSION'
    
    rm -rf /tmp/dsdriver /tmp/ibm_data_server_driver_package_linuxx64_v11.5.tar.gz
    

Create DB user

Create a database user with these privileges on each database on the host machine.
  1. Create system user xormon if there isn't one:
    www.ibm.com/docs/en/db2/11.1?topic=unix-creating-group-user-ids-db2-database-installation

  2. Grant privileges to the user in the database.
    GRANT createtab,bindadd,connect ON DATABASE TO USER xormon;
    GRANT EXECUTE ON FUNCTION  SYSPROC.* TO USER xormon;
    GRANT EXECUTE ON PROCEDURE SYSPROC.* TO USER xormon;
    GRANT SELECT ON TABLE SYSIBMADM.DB2_MEMBER TO USER xormon;
    CREATE SCHEMA xormon;
    

Attach IBM Db2 to XorMon NG

  • Add each database through the UI XorMon UI ➡ Device ➡ Database ➡ IBM Db2 ➡ New :
    IBM Db2 monitoring configuration

  • Run "Test" for configured database, it must show "OK"

  • Wait about 1 hour, then refresh the web browser cache by Ctrl-F5