Skip to main content
Sumo Logic

Set Up Oracle Performance Metrics Script on Windows

  1. Last updated
  2. Save as PDF

This page has instructions for setting up the performance metrics script on Windows OS for the Oracle app. 

The script was developed and tested on:

  • Windows Server 2019 standard, Python (3.7.10), Oracle Database 19c (19.3) for Microsoft Windows x64 (64-bit)

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

SQL> grant select on sys.v_$tablespace to <username>;

SQL> grant select on sys.dba_free_space to <username>;

SQL> grant select on sys.v_$datafile to <username>;

SQL> grant select on v_$sysstat to <username>;

For Query 4

SQL> grant select on sys.v_$session to <username>;

SQL> grant select on sys.v_$process to <username>;

For Queries 5 and 6

SQL> grant select on sys.v_$session_wait to <username>;

SQL> grant select on sys.dba_jobs to <username>;

Prerequisite

Download and install Python for Windows from:
https://www.python.org/downloads/

Add the python installation folder to your Path system  variable.
Control Panel > Systems > Advanced System Settings > Advanced Tab > Environment Variable > System Variables > Path.

To confirm that you have successfully installed Python and added it to your path, open a new command window and run the following command,
C:\Users\Administrator>python3 --version
You should see this response:
Python 3.7.10

  1. Download and setup Oracle Instant Client for Win 64bit.
    http://www.oracle.com/technetwork/topics/winx64soft-089540.html
    instantclient-sdk-windows.x64-21.3.0.0.0.zip
    instantclient-basic-windows.x64-21.3.0.0.0.zip
    instantclient-sqlplus-windows.x64-21.3.0.0.0.zip

  2. Unzip the packages into a single directory for example C:\oracle\instantclient_21_3\.

  3. Add this directory to the PATH environment variable. If you have multiple versions of Oracle libraries installed, make sure the new directory occurs first in the path.

  4. Download and install the correct Visual Studio Redistributable from Microsoft. Instant Client 21.3 requires the Visual Studio 2017 redistributable.

  5. Test the client
    sqlplus username/password@//databasehost:1521/sidvalue
    C:\Users\Administrator>sqlplus user/password//IP_ORACLE_SERVER:1521/remote_service_name

    SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 25 17:03:16 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

  6. Run some sample SQL queries to test the connection,
    SQL> select BANNER from v$version;
    BANNER
    -----------------------------------------------------------------
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

  1. Use Python’s pip package to install cx_Oracle.
    python3 -m pip install cx_Oracle==7.3

  2. Check the cx_Oracle version.
    C:\Users\Administrator>python3
    Python 3.7.10 (v2.7.18:8d21aa21f2, Apr 20 2020, 13:25:05) [MSC v.1500 64 bit (AMD64)] on win32
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import cx_Oracle
    >>> print cx_Oracle.version
    7.3.0
    >>>

Set-up and Configure Performance Metrics Script

In this step, you set up the performance metrics script.

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 .

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 wish to keep the password in the configuration file, keep the oraPassword field blank. You can keep the password in the environment variable DB_PASSWORD.

To set environment variable , add variable using below command

setx DB_PASSWORD <<DB Password>>

The script first tries to read password from the config file, if the password is not found in 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

oracle_script/sumooracle>python3 oracle-perf-monitor.py

Configure the Sumo Logic Script Source

  1. In Sumo Logic, go to Manage Data > Collection > Collection.

  2. 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.

  3. 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 sec.

    • For Command, select Windows Script.

    • For Script, select Type a path to the script to execute, then enter: for instanceoracle_script/sumooracle\oracle-perf-monitor.py.

    • For Working Directory, enter: for instance oracle_script/sumooracle

    • Click Save.