Set Up Oracle Performance Metrics Script on Linux
This page has instructions for setting up the performance metrics script on Linux for the Oracle app.
The script was developed and tested on:
-
Linux Server 8.4, Python (3.7.10), Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production - Version 19.3.0.0.0
About the Performance Metric Script
The Sumo app for Oracle uses a Python script to query database tables to collect Oracle server performance metrics.
The script connects the database using a database user account, runs a set of example SQL queries, and prints the query results in JSON format to the console. The account credentials and SQL queries are specified in the script configuration file, oracle-perf-monitor.cfg
.
The SQL queries in the “[queries]
” section of the configuration file are provided as examples. You can add, modify, and remove queries, as desired. We recommend having the queries you plan to use reviewed by your DBA.
The database user that you use to run the SQL queries should have permission to access query-specific databases, tables, and views. The table below shows the SQL commands to grant the required permissions for each query.
Query |
SQL Command to Grant Permissions |
---|---|
For Queries 1 through 3 |
|
For Query 4 |
|
For Queries 5 and 6 |
|
Prerequisite
Check Linux version
Check the version of Linux currently running on your machine to ensure compatibility with the script.
$ lsb_release -a
LSB Version: :core-4.1-amd64:core-4.1-noarch
Distributor ID: OracleServer
Description: Oracle Linux Server release 8.4
Release: 8.4
Codename: n/a
If you get the error lsb_release: command not found , use below command to install lsb core and then re-run above command
sudo yum update && sudo yum install redhat-lsb-core
Install Python 3.7.10+ for Linux
In this step, you install Python 3.7.10 for Linux.
-
Install Python using the following command.
yum install python3 -y
ln -s /usr/bin/python3 /usr/bin/python -
To check the Python version:
python3 --version
Python 3.7.10 -
In this step, you install pip if it’s not already installed.
-
To determine whether pip is installed, run this command:
$ pip3 -V
If pip is not installed, you’ll see this message:
The program 'pip' is currently not installed.
-
To install pip, run this command
yum install python3-pip -y
-
To verify the installation, run this command:
$ pip3 -V
You should see message like this:
pip 20.2.2 from /usr/lib/python3.7/site-packages/pip (python 3.7).
-
Install Oracle Instant Client on Linux 64bit OS
-
Install Oracle Instant Client packages
$ dnf install oracle-instantclient-release-el8 -y
$ dnf install oracle-instantclient-basic
$ oracle-instantclient-devel oracle-instantclient-jdbc
$ oracle-instantclient-odbc oracle-instantclient-sqlplus oracle-instantclient-tools -y -
Install the
libaio
andlibaio-dev
packages if they are not already installed. Typically, in standard Oracle Linux Server, thelibaio
andlibaio-dev
are not pre-installed. These packages are required to start the Oracle Instant Client.
$ dnf install -y libaio libaio-devel
-
Test the client
$ sqlplus username/password@//databasehost:1521/sidvalue
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 25 12:42:35 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
-
Run a sample SQL query to test the connection.
SQL> select BANNER from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Set up cx_Oracle on Linux
In this step, you set up cx_Oracle, an open-source Python interface to Oracle.
-
Install cx_Oracle.
pip3 install cx_Oracle==7.3
-
Check the cx_oracle version.
pip3 list | grep cx-Oracle
cx-Oracle (7.3.0)
Set-up and Configure Performance Metrics Script
In this step, you set up the performance metrics script.
Download the script - Clone the git repo
Clone the Sumo Logic Python performance metrics script and configuration files from the git repo inside a folder (for example: oracle_script) using below command:
$ git clone git@github.com:SumoLogic/sumologic-oracle-perf-monitor.git
Once the script is cloned, navigate to oracle_script/sumooracle
Two files oracle-perf-monitor.cfg and oracle-perf-monitor.py should be present.
Configure the script
Make the following updates to the script configuration file (oracle-perf-monitor.cfg
). In the [dbLogin]
section, supply values for each parameter:
If you do not wish to keep the password in the configuration file, keep the oraPassword field blank. You can set the password in the environment variable DB_PASSWORD.
To set environment variable , add variable in your ~/.bash_profile
file in your environment
export DB_PASSWORD = <<DB Password>>
The script first tries to read the password from the config file, if the password is not found in the config file, it searches for an environment variable DB_PASSWORD.
[dbLogin]
-
oraUser= database user id example myuser123
-
oraPassword= user password example mypwd123
-
oraHost=server name example ip-101-25-17-22
-
oraPort=port number example 1521
-
oraInstance=oracle instance SID (SID_NAME) example XE
Test the script
$ python3 oracle-perf-monitor.py
Configure the Sumo Logic Script Source
-
In Sumo Logic, go to Manage Data > Collection > Collection.
-
Find the name of the installed collector to which you'd like to add a Source. Click Add. Then choose Add Source from the pop-up menu.
-
Select Script for the Source type. Collectors using version 19.245-4 and later do not allow Script Sources to run by default. To allow Script Sources you need to set the Collector parameter
enableScriptSource
in user.properties to true and restart the Collector.-
For Name enter any name you like, for instance, Oracle Server Script
-
The Description is optional.
-
For Source Category, enter the desired category. It can be any value you like, for example,
DB/Oracle/DBQueryScript
. -
Fields. Set the following fields:
-
component = database
-
db_system = oracle
-
db_cluster = <Your_Oracle_Cluster_Name>
. Enter Default if you do not have one. -
environment = <Your_Environment_Name>
(for example,Dev
,QA
, orProd
)
-
-
For Frequency, select desired frequency, for instance, 5 minutes
-
For Specify a timeout for your command, select a value that is long enough that long-running queries can complete, for instance, 30 seconds.
-
For Command,
select /usr/bin/python
-
For Script, enter a path to the script, for example,
oracle_script/sumooracle/oracle-perf-monitor.py
-
For Working Directory, enter a directory, for example,
oracle_script/sumooracle
-
Click Save.
-
After a few minutes, your new Source should be propagated down to the Collector and will begin submitting your Oracle log files to the Sumo Logic service.