Database Monitoring : Installation

Database

IBM Db2
MS SQL
Oracle
PostgreSQL / EDB

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

Create DB user

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 :

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

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 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:
- Microsoft ODBC driver for SQL Server (Linux):
  docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server
- unixODBC + FreeTDS
  unixodbc.org
  freetds.org
```
yum install unixODBC unixODBC-devel freetds
```
  Note: Make sure the driver path is present in "/etc/odbcinst.ini" (Most installations will do this automatically)
```
$ cat /etc/odbcinst.ini

# Driver from the freetds-libs package
# Setup from the unixODBC package
[FreeTDS]
Description     = Free Sybase & MS SQL Driver
Driver          = /usr/lib/libtdsodbc.so
Setup           = /usr/lib/libtdsS.so
```
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

Create DB user

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 :

Use <servername>\<instancename> in the Host field if you have Named Instances like in this example:
Run "Test" for configured database, it must show "OK"
Wait about 1 hour, then refresh the web browser cache by Ctrl-F5

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.

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

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 :
Run "Test" for configured database, it must show "OK"
Wait about 1 hour, then refresh the web browser cache by Ctrl-F5