Set Up Oracle Performance Metrics Script on Windows
This page has instructions for setting up the performance metrics script on Windows for the Oracle app.
The script was developed and tested on:
-
Microsoft Windows Server 2012 R2 Base, 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. Install Python 2.7.12 for Windows
-
Download and Python 2.7.12 for Windows from:
https://www.python.org/downloads/release/python-2712/ -
Install Python in the
C:\Python27\
folder. -
Add
C:\Python27\
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 2.7.12 and added it to your path, open a new command window and run the following command:
C:\Users\Administrator>python --version
You should see this response:
Python 2.7.12
Step 2. Install Oracle Instant Client for Windows 64bit
-
Download and setup Oracle Instant Client for Win 64bit.
http://www.oracle.com/technetwork/topics/winx64soft-089540.html
Instantclient-basic-windows.x64-12.2.0.1.0.zip
Instantclient-sqlplus-windows.x64-12.2.0.1.0.zip
Instantclient-sdk-windows.x64-12.2.0.1.0.zip -
Unzip the packages into a single directory such as
C:\oracle\instantclient_12_2\
-
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.
-
Download and install the correct Visual Studio Redistributable from Microsoft. Instant Client 12.2 requires the Visual Studio 2013 redistributable.
-
Test the client
sqlplus username/password@//databasehost:1521/sidvalue
C:\Users\Administrator>sqlplus nitin/pande@//10.0.0.197:1521/NITINSID
SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 25 17:13: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
2.6) Run some sample SQL query to test the connection is working
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
SQL>
Step 3. Install cx_Oracle on Windows
For related information, see http://cx-oracle.readthedocs.io/en/l...cle-on-windows.
-
Use Python’s pip package to install cx_Oracle.
python -m pip install cx_Oracle --upgrade
-
Check the cx_Oracle version.
c:\Application\sumo_OracleDB_Scripts>python
Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:24:40) [MSC v.1500 64 bit
AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> print cx_Oracle.version
6.2.1
>>>
Step 4. Set up performance metrics script
In this step you set up the performance metrics script.
-
Download Sumo Python 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
to a folder, for examplec:\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:
c
:\Application\sumo_OracleDB_Scripts>python oracle-perf-monitor.py
Step 5. Configure the 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.
- 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 instance.
C:\Application\sumo_OracleDB_Scripts\oracle-perf-monitor.py
- For Working Directory, enter: for instance
C:\Application\sumo_OracleDB_Scripts
- Click Save.