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:
-
Ubuntu OS (16.04.3 LTS), Python (2.7.12), Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
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 and 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 | SQL> grant select on sys.v_$tablespace to <username>; |
For Query 4 | SQL> grant select on sys.v_$session to <username>; |
For Queries 5 and 6 | SQL> grant select on sys.v_$session_wait to <username>; |
Step 1. Check Linux version
Check the version of Linux currently running on your machine to ensure compatibility with the script.
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.3 LTS
Release: 16.04
Codename: xenial
Step 2. Install Python 2.7.12 for Linux
In this step you install Python 2.7.12 for Linux.
-
Install Python using the following command.
sudo apt install python
-
To check the Python version:
python --version
Python 2.7.12 -
In this step, you install pip if it’s not already installed.
- To determine whether pip is installed, run this command:
$ pip -V
If pip is not installed, you’ll see this message:
The program 'pip' is currently not installed.
- To install pip, run this command
sudo apt install python-pip
- To verify the installation, run this command:
$ pip -V
You should see this message:
pip 8.1.1 from /usr/lib/python2.7/dist-packages (python 2.7)
Step 3. Install Oracle Instant Client on Linux 64bit OS
-
Download Oracle Instant Client for Linux from:
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html -
Copy these files into your system, for example to the
~/Application/oracle
folder:
Oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
Oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
Oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm -
Install alien package. (Alien is a program that converts between the rpm, dpkg, stampede slp, and slackware tgz file formats. If you want to use a package from another distribution than the one you have installed on your system, you can use alien to convert it to your preferred package format and install it.)
sudo apt-get install alien
-
Install downloaded Oracle Instant Client RPM packages.
sudo alien -i oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
sudo alien -i oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
sudo alien -i oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm -
Install the
libaio1
andlibaio-dev
packages if they are not already installed. Typically, in standard Ubuntu, thelibaio1
andlibaio-dev
are not pre-installed. These packages are required to start Oracle Instant Client.
sudo apt-get install libaio1 libaio-dev
-
If Instant Client is the only Oracle software installed on your system, update the runtime link path, for example:
sudo sh -c "echo /usr/lib/oracle/12.2/client64/lib > \
/etc/ld.so.conf.d/oracle-instantclient.conf"
sudo ldconfig
Alternatively, set theLD_LIBRARY_PATH
environment variable before running applications. For example:
Export LD_LIBRARY_PATH=/opt/oracle/instantclient_12_2:$LD_LIBRARY_PATH
The variable can optionally be added to configuration files such as~/.bash_profile
and to application configuration files such as/etc/sysconfig/httpd.
-
Test the client
sqlplus64 username/password@//databasehost:1521/sidvalue
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 20 08:39:20 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options -
Run a sample SQL query to test the connection.
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Step 4. Set up cx_Oracle on Linux
In this step, you set up cx_Oracle, an open source Python interface to Oracle.
-
Install cx_Oracle.
sudo pip install cx_Oracle
-
Check the cx_oracle version.
$ pip list | grep cx-Oracle
cx-Oracle (6.2.1)
Step 5. Set up performance metrics script
In this step you set up the performance metrics script.
-
Download the Sumo Python performance metrics script and configuration files:
oracle-perf-monitor.cfg
(https://s3.amazonaws.com/script-collection/oracle/r1.0.0/oracle-perf-monitor.cfg)
oracle-perf-monitor.py
(https://s3.amazonaws.com/script-collection/oracle/r1.0.0/oracle-perf-monitor.py) -
Copy
oracle-perf-monitor.py
into a folder, for example,/home/ubuntu/Application/sumo_OracleDB_Scripts
. -
Make the following updates to the script configuration file (
oracle-perf-monitor.cfg
). In the[dbLogin]
section, supply values for each parameter:
[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.
$ python oracle-perf-monitor.py
Step 6. Configure the Script Source
In this step, you configure a script source for the performance script in Sumo.
-
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
- 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:
/home/ubuntu/Application/sumo_OracleDB_Scripts/oracle-perf-monitor.py
- For Working Directory, enter a dirctory, for example,
/home/ubuntu/Application/sumo_OracleDB_Scripts
- Click Save.