Oracle DB Monitoring

Skip Prerequisites, Web and LPAR2RRD tabs in case of configuring Virtual Appliance, Docker or a Container

HW sizing


Consider usage of our brand new full-stack infrastructure monitoring tool XorMon Next Generation as LPAR2RRD replacement.
It brings a new level of infrastructure monitoring by relying on a modern technology stack.
In particular, reporting, exporting, alerting and presentation capabilities are unique on the market.

Follow installation procedure for your operating system platform
Oracle DB monitoring schema
LPAR2RRD obtains data through SQL (SQL*Plus). Oracle client must be installed on LPAR2RRD server.
Monitoring implementation does not require any Oracle licensed features like Oracle Diagnostics Pack or Oracle Tuning Pack .

LPAR2RRD 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 this step if you are using XoruX Virtual Appliance
    # yum -y install libaio.x86_64
  • 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 (Linux and AIX)
    • 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 (Linux)
      # echo "/opt/oracle/instantclient_<version>" > /etc/ld.so.conf.d/oracle-instantclient.conf
      # ldconfig
      
  • RPM installation - Linux
    • 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  
  • AIX: missing iocp
    You may get the following err when running sqlplus on AIX.
    # cd /opt/oracle/instantclient_<version>
    # export LIBPATH=`pwd`
    # ./sqlplus
      exec(): 0509-036 Cannot load program sqlplus because of the following errors:
      rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
          from module /opt/oracle/instantclient_19_5/libttsh19.so(), but a runtime definition
          of the symbol was not found.
      rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced
          from module /opt/oracle/instantclient_19_5/libttsh19.so(), but a runtime definition
          of the symbol was not found.
      rtld: 0712-002 fatal error: exiting.
    # lsdev | grep iocp
      iocp0      Defined         I/O Completion Ports
    
    Configure iocp without AIX reboot:
    # smitty iocp
      Select: Change / Show Characteristics of I/O Completion Ports ➡ se to available ➡ confirm ➡ it prints error that reboot is necessary, ignore it for now
    # lsdev | grep iocp
      iocp0      Defined         I/O Completion Ports
    # lsattr -El iocp0
      autoconfig defined STATE to be configured at system restart True
    
    Enable iocp0 now and at restart:
    # mkdev -l iocp0
      iocp0 Available
    # chdev -l iocp0 -a autoconfig='available' -P
      iocp0 changed
    # lsdev | grep iocp
      iocp0      Available         I/O Completion Ports
    

Create DB user for LPAR2RRD

Database user in each Oracle instance must be created, it will be used for LPAR2RRD access.
You can choice whatever user name (below examle show "lpar2rrd" user, then specify it in LPAR2RRD Database properties.
  • Standalone / RAC
    $ sqlplus sys"/as sysdba"
      create user lpar2rrd_check identified by "<password>" default tablespace USERS temporary tablespace TEMP;
      grant create session to lpar2rrd_check;
      grant select on sys.v_$instance_ping to lpar2rrd_check;
      grant select on sys.v_$resource_limit to lpar2rrd_check;
      grant select on sys.gv_$resource_limit to lpar2rrd_check;
      grant select on sys.v_$sysmetric_summary to lpar2rrd_check;
      grant select on sys.gv_$sysmetric_summary to lpar2rrd_check;
      grant select on sys.v_$sysmetric_history to lpar2rrd_check;
      grant select on sys.gv_$sysmetric_history to lpar2rrd_check;
      grant select on sys.v_$datafile to lpar2rrd_check;
      grant select on sys.gv_$datafile to lpar2rrd_check;
      grant select on sys.v_$filestat to lpar2rrd_check;
      grant select on sys.gv_$filestat to lpar2rrd_check;
      grant select on sys.v_$instance to lpar2rrd_check;
      grant select on sys.gv_$instance to lpar2rrd_check;
      grant select on sys.v_$database to lpar2rrd_check;
      grant select on sys.gv_$database to lpar2rrd_check;
      grant select on sys.v_$osstat to lpar2rrd_check;
      grant select on sys.gv_$osstat to lpar2rrd_check;
      grant select on sys.v_$session to lpar2rrd_check;
      grant select on sys.gv_$session to lpar2rrd_check;
      grant select on sys.v_$sgainfo to lpar2rrd_check;
      grant select on sys.gv_$sgainfo to lpar2rrd_check;
      grant select on sys.v_$containers to lpar2rrd_check;
      grant select on sys.gv_$containers to lpar2rrd_check;
      grant select on sys.v_$pgastat to lpar2rrd_check;
      grant select on sys.gv_$pgastat to lpar2rrd_check;
      grant select on sys.v_$system_wait_class to lpar2rrd_check;
      grant select on sys.gv_$system_wait_class to lpar2rrd_check;
      grant select on sys.v_$system_event  to lpar2rrd_check;
      grant select on sys.gv_$system_event  to lpar2rrd_check;
      grant select on sys.v_$cluster_interconnects  to lpar2rrd_check;
      grant select on sys.gv_$cluster_interconnects to lpar2rrd_check;
      grant select on sys.v_$sysstat to lpar2rrd_check;
      grant select on sys.gv_$sysstat to lpar2rrd_check;
      grant select on sys.v_$service_stats to lpar2rrd_check;
      grant select on sys.gv_$service_stats to lpar2rrd_check;
      grant select on sys.v_$event_histogram to lpar2rrd_check;
      grant select on sys.gv_$event_histogram to lpar2rrd_check;
      grant select on dba_free_space to lpar2rrd_check;
      grant select on dba_temp_free_space to lpar2rrd_check;
      grant select on dba_data_files to lpar2rrd_check;
      grant select on dba_registry to lpar2rrd_check;
      grant select on dba_temp_files to lpar2rrd_check;
      grant select on sys.registry$history to lpar2rrd_check;
      grant select on sys.REGISTRY$SQLPATCH to lpar2rrd_check;
      grant select on sys.v_$recovery_file_dest to lpar2rrd_check;
      grant select on sys.v_$log to lpar2rrd_check;
      grant select on sys.v_$controlfile to lpar2rrd_check;
      grant select on dba_alert_history_detail to lpar2rrd_check;
      grant select on sys.v_$logfile to lpar2rrd_check;
      exit;
    
  • Multitenant
    $ sqlplus sys"/as sysdba"
      create user lpar2rrd_check identified by "<password>" default tablespace USERS temporary tablespace TEMP CONTAINER = ALL;
      grant create session to lpar2rrd_check CONTAINER = ALL;
      grant SET CONTAINER to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$instance_ping to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$resource_limit to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$resource_limit to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$sysmetric_summary to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$sysmetric_summary to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$sysmetric_history to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$sysmetric_history to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$datafile to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$datafile to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$filestat to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$filestat to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$instance to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$instance to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$database to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$database to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$osstat to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$osstat to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$session to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$session to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$sgainfo to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$sgainfo to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$containers to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$containers to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$pgastat to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$pgastat to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$system_wait_class to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$system_wait_class to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$system_event  to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$system_event  to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$con_sysmetric_summary  to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$con_sysmetric_summary  to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$con_sysmetric_history   to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$con_sysmetric_history  to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$cluster_interconnects  to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$cluster_interconnects to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$sysstat to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$sysstat to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$service_stats to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$service_stats to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$event_histogram to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.gv_$event_histogram to lpar2rrd_check CONTAINER = ALL;
      grant select on dba_free_space to lpar2rrd_check CONTAINER = ALL;
      grant select on dba_temp_free_space to lpar2rrd_check CONTAINER = ALL;
      grant select on dba_data_files to lpar2rrd_check CONTAINER = ALL;
      grant select on dba_registry to lpar2rrd_check CONTAINER = ALL;
      grant select on dba_temp_files to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.registry$history to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.REGISTRY$SQLPATCH to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$recovery_file_dest to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$log to lpar2rrd_check CONTAINER = ALL; 
      grant select on sys.v_$controlfile to lpar2rrd_check CONTAINER = ALL;
      grant select on dba_alert_history_detail to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$logfile to lpar2rrd_check CONTAINER = ALL; 
      exit;
    
  • DataGuard: Standalone/RAC
    Note: DG is not supported yet (as per v7.00), let us know if you want to test it.
    Enhance user priviledges on primary database. DG instance does not need lpar2rrd user.
      grant select on sys.v_$dataguard_config  to lpar2rrd_check;
      grant select on sys.v_$ARCHIVE_DEST to lpar2rrd_check;
      grant select on sys.v_$archive_dest_status to lpar2rrd_check;
    
  • DataGuard: Multitenant/RAC Multitenant
    Note: DG is not supported yet (as per v7.00), let us know if you want to test it.
    Enhance user priviledges on primary database. DG instance does not need lpar2rrd user.
      grant select on sys.v_$dataguard_config  to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$ARCHIVE_DEST to lpar2rrd_check CONTAINER = ALL;
      grant select on sys.v_$archive_dest_status to lpar2rrd_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 lpar2rrd;      
     ORA-00990: missing or invalid privilege
SQL> grant select on sys.REGISTRY$SQLPATCH to lpar2rrd;
     ORA-00942: the table does not exist  

Connectivity check

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

Check connectivity:
$ perl /home/lpar2rrd/lpar2rrd/bin/conntest.pl 192.168.1.1 1521
  Connection to "192.168.1.1" on port "1521" is ok

Attach Oracle Databases

  • Oracle environment
    Set Oracle environment variables in etc/lpar2rrd.cfg, modify existing setup
    # Oracle DB client setup
    # Adjust ORACLE_HOME & ORACLE_BASE as necessary, "instantclient_X" is only example
    # Do not use ORACLE_HOME variable as part of ORACLE_HOME, use always full path!
      ORACLE_BASE=/opt/oracle
      ORACLE_HOME=/opt/oracle/instantclient_XY
    
    Note ORACLE_HOME must be directory where sqlplus binary is, like in this example when it is in /usr/lib/oracle/19.6/client64/bin/sqlplus
    ORACLE_BASE=/usr/lib/oracle
    ORACLE_HOME=/usr/lib/oracle/19.6/client64/bin
    
    There can be a problem when sqlplus is in "bin" subdir as such ORACLE_HOME do not have to acceptable for Oracle client.
    Either use ORACLE_HOME without "bin" and copy/symlink sqlplus to ORACLE_HOME or upgrade to LPAR2RRD 7.04+ (release in Dec 2020) where it is fixed (you do not need to put "bin" into ORACLE_HOME)

  • LPAR2RRD UI ➡ Settings icon ➡ Oracle DB ➡ New :
    Oracle DB 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.

  • Make sure cron jobs for Oracle DB are in place (upgrade script might do it for you automatically)
    Skip this step if you install the Virtual Appliance - it is already taken care of.
    $ crontab -l | grep "load_oracledb.sh"
    $
    
    Add following lines to crontab if necessary
    $ crontab -e
    
    # Oracle DB support
    0,5,10,15,20,25,30,35,40,45,50,55 * * * *  /home/lpar2rrd/lpar2rrd/load_oracledb.sh > /home/lpar2rrd/lpar2rrd/load_oracledb.out 2>&1
    
  • Wait 30 minutes and then go to the web UI: http://<your web server>/lpar2rrd/
    Use Ctrl-F5 to refresh the web browser cache.


Install LPAR2RRD server (all under lpar2rrd user)

  • Download the latest LPAR2RRD server
    Upgrade your already running LPAR2RRD instance.

  • Install it:
    # su - lpar2rrd
    $ tar xvf lpar2rrd-7.XX.tar
    $ cd lpar2rrd-7.XX
    $ ./install.sh
    $ cd /home/lpar2rrd/lpar2rrd
    
  • Make sure all Perl modules are in place
    cd /home/lpar2rrd/lpar2rrd
    . etc/lpar2rrd.cfg; $PERL bin/perl_modules_check.pl
    
    If there is missing "LWP::Protocol::https" then check this docu to fix it

  • Enable Apache authorisation
    su - lpar2rrd
    umask 022
    cd /home/lpar2rrd/lpar2rrd
    cp html/.htaccess www
    cp html/.htaccess lpar2rrd-cgi
    
  • Schedule to run it from lpar2rrd crontab (it might already exist there)
    $ crontab -l | grep load.sh
    $
    
    Add if it does not exist as above
    $ crontab -e
    
    # LPAR2RRD UI
    0,30 * * * * /home/lpar2rrd/lpar2rrd/load.sh > /home/lpar2rrd/lpar2rrd/load.out 2>&1 
    
    Assure there is just one such entry in crontab.

  • You might need to add lpar2rrd user into /etc/cron.allow (Linux) or /var/adm/cron/cron.allow (AIX) if 'crontab -e' command fails
    Allow it for lpar2rrd user as root user.
    # echo "lpar2rrd" >> /etc/cron.allow
    
  • Assure you have a cron jobs for Oracle DB in place (upgrade script might do it automatically) Skip it on the Virtual Appliance, it is already there.
    $ crontab -l | grep "load_oracledb.sh"
    $
    
    Add it if it does not exist like above
    $ crontab -e
    
    # Oracle DB support
    0,5,10,15,20,25,30,35,40,45,50,55 * * * *  /home/lpar2rrd/lpar2rrd/load_oracledb.sh > /home/lpar2rrd/lpar2rrd/load_oracledb.out 2>&1
    
  • Initial start from cmd line:
    $ cd /home/lpar2rrd/lpar2rrd
    $ ./load.sh
    
  • Go to the web UI: http://<your web server>/lpar2rrd/
    Use Ctrl-F5 to refresh the web browser cache.

Troubleshooting

  • If you have any problems with the UI then check:
    (note that the path to Apache logs might be different, search apache logs in /var)
    tail /var/log/httpd/error_log             # Apache error log
    tail /var/log/httpd/access_log            # Apache access log
    tail /var/tmp/lpar2rrd-realt-error.log    # STOR2RRD CGI-BIN log
    tail /var/tmp/systemd-private*/tmp/lpar2rrd-realt-error.log # STOR2RRD CGI-BIN log when Linux has enabled private temp
    
  • Test of CGI-BIN setup
    umask 022
    cd /home/lpar2rrd/lpar2rrd/
    cp bin/test-healthcheck-cgi.sh lpar2rrd-cgi/
    
    go to the web browser: http://<your web server>/lpar2rrd/test.html
    You should see your Apache, LPAR2RRD, and Operating System variables, if not, then check Apache logs for connected errors