Splunk® Supported Add-ons

Splunk Add-on for Oracle Database

Configure Splunk DB Connect v1 inputs for the Splunk Add-on for Oracle Database

To gather inventory and performance data from your Oracle Database Server, the Splunk Add-on for Oracle Database leverages Splunk DB Connect. Follow the instructions that correspond to the version of DB Connect that you have installed. This topic presents the instructions for DB Connect Version 1.1.7.

Note: Not all sources can be collected using DB Connect version 1.1.7, see Source types for the Splunk Add-on for Oracle Database.

Note: If you are using DB Connect version 2.X, see the v2 instructions instead.

Set up the database connection

1. Copy the contents of $SPLUNK_HOME/etc/apps/Splunk_TA_oracle/default/transforms.conf to $SPLUNK_HOME/etc/apps/Splunk_TA_oracle/local/transforms.conf.

2. Follow the directions in the comments for the Perf section: uncomment the DELIMS and FIELDS lines for the [ORACLE_SYS_PERF], [ORACLE_OS_PERF], [ORACLE_LIB_CACHE], and [ORACLE_DBFILEIO_PERF] stanzas.

3. Create a database connection to the Oracle Database Server using either the Splunk DB Connect GUI or the database.conf file as described in the sections below.

Use the Splunk DB Connect GUI

Refer to the "Manage a database connection" in the Splunk DB Connect manual for step by step instructions for using the GUI to set up a new database connection.

The screenshot below shows an example configuration.

DBX-config-example.png

Use database.conf

1. Copy the sample database.conf in $SPLUNK_HOME/etc/apps/Splunk_TA_oracle/default to $SPLUNK_HOME/etc/apps/dbx/local.

Note: Make all changes to database.conf in $SPLUNK_HOME/etc/apps/dbx/local. Splunk Enterprise encrypts all database passwords in local/database.conf on restart, but passwords are not encrypted for default/database.conf files.

2. Change the name of the database connection stanza in your local copy of the file from [<Oracle>] to a unique value. For example, [<OracleDbHostname_Oracle11gR2_orcl>]

3. Edit the values of the fields below to reflect your production environment. 

  database = <name of the database>
  host = <hostname or IP address of the Oracle Database Server>
  username = <domain/user_id of the Oracle database user>
  password = <password of the Oracle database user>
  port =<network port of the Oracle Database Server instance, generally 1521>

3. Leave the following lines unchanged. 

  isolation_level = DATABASE_SETTING
  readonly = 1
  type = oracle

4. Enable the database through the configuration file as follows: 

  disabled= 0

Configure the inputs

To configure the inputs, you can copy the input template provided in the add-on to your local inputs.conf file and enable the inputs that you want to collect there. Alternatively, you can configure the DB Connect inputs manually using the DB Connect GUI.

Use inputs.conf

1. Copy the contents of the dbx1.inputs.conf.template file from $SPLUNK_HOME/etc/apps/Splunk_TA_oracle/default to $SPLUNK_HOME/etc/apps/dbx/local/inputs.conf.

2. Enable the inputs through the configuration file by toggling the disabled attributes in the input stanzas you wish to use. 

  disabled = 0

3. For each input that you want to enable, adjust the stanza name to replace <Oracle> with the name of the database connection stanza configured in database.conf, or the name of the database connection you created via the GUI.

4. If necessary, adjust the index to another index of your preference.

5. If you are using Oracle Database Server 10g, uncomment the 10g version of the [dbmon-dump://<Oracle>/oracle:osPerf] stanza and comment out the 11g/12/c version.

6. Restart Splunk Enterprise services for your changes to take effect.

Use the Splunk DB Connect GUI

The following examples provide the configuration details for each data type, with example screenshots.

Refer to "Configure database input queries" in the Splunk DB Connect manual for step by step instructions for using the GUI to configure your database inputs.

Inventory events with Oracle database information

Parameter Value
Input Type Dump (Always dump the full table/query)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query 
SELECT * FROM V$DATABASE
Sourcetype oracle:database
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format Key-Value Format
Output timestamp True
Interval 2h

Oracle-database-input.png

Inventory events with Oracle instance information

Parameter Value
Input Type Dump (Always dump the full table/query)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query 
SELECT * FROM V$INSTANCE
Sourcetype oracle:instance
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format Key-Value Format
Output timestamp True
Interval 2h

Oracle-instance-input.png

Inventory events with Oracle session information

Parameter Value
Input Type Dump (Always dump the full table/query)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query 
SELECT * FROM V$SESSION
Sourcetype oracle:session
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format Key-Value Format
Output timestamp True
Interval 2h

Oracle-session-input.png

Inventory events with Oracle SGA information

Parameter Value
Input Type Dump (Always dump the full table/query)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query 
SELECT NAME, TOTAL, FREE
FROM (
SELECT 'SGA' NAME,
(SELECT SUM(VALUE) FROM V$SGA) TOTAL,
(SELECT SUM(BYTES) FROM V$SGASTAT WHERE NAME = 'free memory') FREE
FROM DUAL
)
UNION
SELECT NAME, TOTAL, TOTAL - USED FREE
FROM (
SELECT 'PGA' NAME,
(SELECT VALUE FROM V$PGASTAT WHERE NAME = 'aggregate PGA target parameter') TOTAL,
(SELECT VALUE FROM V$PGASTAT WHERE NAME = 'total PGA allocated') USED
FROM DUAL
)
UNION
SELECT NAME, TOTAL, FREE
FROM (
SELECT 'Shared pool' NAME,
(SELECT SUM(BYTES) FROM V$SGASTAT WHERE POOL = 'shared pool' GROUP BY POOL) TOTAL,
(SELECT BYTES FROM V$SGASTAT WHERE NAME = 'free memory' and POOL = 'shared pool') FREE
FROM DUAL
)
UNION
SELECT NAME, TOTAL, FREE
FROM (
SELECT 'Large pool' NAME,
(SELECT SUM(BYTES) FROM V$SGASTAT WHERE POOL = 'large pool' GROUP BY POOL) TOTAL,
(SELECT BYTES FROM V$SGASTAT WHERE POOL = 'large pool' AND NAME = 'free memory') FREE
FROM DUAL
)
Sourcetype oracle:sga
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format Key-Value Format
Output timestamp True
Interval 5m

Oracle-sga-input.png

Inventory events with Oracle tablespace information

Parameter Value
Input Type Dump (Always dump the full table/query)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query 
SELECT * FROM DBA_TABLESPACES
Sourcetype oracle:tablespace
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format Key-Value Format
Output timestamp True
Interval 1h

Oracle-tablespace-input.png

Inventory events with Oracle tablespace capacity information

Parameter Value
Input Type Dump (Always dump the full table/query)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query 
SELECT TABLESPACE_NAME, TOTAL_BYTES, TOTAL_BLOCKS, FREE_BYTES, USED_BYTES
FROM (
SELECT D.TABLESPACE_NAME, TOTAL_BYTES, TOTAL_BLOCKS,
FREE_BYTES, TOTAL_BYTES - NVL(FREE_BYTES, 0) "USED_BYTES"
FROM (SELECT TABLESPACE_NAME,
SUM (BYTES) TOTAL_BYTES,
SUM (BLOCKS) TOTAL_BLOCKS
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
SUM (BYTES) FREE_BYTES
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
-- Temp table space
SELECT D.TABLESPACE_NAME, TOTAL_BYTES, TOTAL_BLOCKS,
NVL(FREE_BYTES, 0) "FREE_BYTES", USED_BYTES
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) TOTAL_BYTES,
SUM(BLOCKS) TOTAL_BLOCKS
FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
SUM(BYTES_USED) USED_BYTES,
SUM(BYTES_FREE) FREE_BYTES
FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
)
Sourcetype oracle:tablespaceMetrics
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format Key-Value Format
Output timestamp True
Interval 15m

Oracle-tablespacemetrics-input.png

Performance events with Oracle library cache information

Parameter Value
Input Type Dump (Always dump the full table/query)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query 
SELECT NAMESPACE, GETS, GETHITS, GETHITRATIO, PINS, PINHITS, PINHITRATIO, RELOADS, INVALIDATIONS, 
DLM_LOCK_REQUESTS, DLM_PIN_REQUESTS, DLM_PIN_RELEASES, DLM_INVALIDATION_REQUESTS, 
DLM_INVALIDATIONS FROM V$LIBRARYCACHE
Sourcetype oracle:libraryCachePerf
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format CSV
Output timestamp False
Interval 1m

Oracle-librarycacheperf-input.png

Performance events with Oracle I/O performance metrics

Parameter Value
Input Type Dump (Always dump the full table/query)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query 
SELECT 'DB_FILE' TYPE, D.NAME FILE_NAME, F.PHYRDS, F.PHYWRTS, F.AVGIOTIM,
F.MINIOTIM, F.MAXIOWTM, F.MAXIORTM
FROM V$FILESTAT F, V$DATAFILE D WHERE F.FILE# = D.FILE#
UNION
SELECT 'TEMP_DB_FILE' TYPE, T.NAME FILE_NAME,
F.PHYRDS, F.PHYWRTS, F.AVGIOTIM, F.MINIOTIM, F.MAXIOWTM, F.MAXIORTM
FROM V$FILESTAT F, V$TEMPFILE T
WHERE F.FILE# = T.FILE#
ORDER BY 1
Sourcetype oracle:dbFileIoPerf
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format CSV
Output timestamp False
Interval 2m

Oracle-dbfiletoperf-input.png

Performance events with Oracle database server host performance metrics

Parameter Value
Input Type Dump (Always dump the full table/query)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query

For 11g/12c: 

SELECT STAT_NAME, VALUE, CUMULATIVE FROM V$OSSTAT
For 10g: 
SELECT STAT_NAME, VALUE, 'YES' AS CUMULATIVE FROM V$OSSTAT
Sourcetype oracle:osPerf
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format CSV
Output timestamp False
Interval 5m

Oracle-osperf-input.png

Performance events with Oracle system performance metrics

Parameter Value
Input Type Tail (Follow based on increasing value)
Database Enter the database connection name you created when you set up the database connection.
Specify SQL Query True
SQL Query 
SELECT BEGIN_TIME, END_TIME, INTSIZE_CSEC,METRIC_NAME, VALUE, METRIC_UNIT FROM V$SYSMETRIC_HISTORY {{WHERE $rising_column$ > ?}}
Rising Column END_TIME
Sourcetype oracle:sysPerf
Splunk index Main, or your preferred index.
Host Field value The host name of the database server you configured when you set up the database connection.
Output Format CSV
Output timestamp False
Timestamp column END_TIME
Timestamp format yyyy.MM.dd HH:mm:ss
Interval 1m

Oracle-sysperf-input.png

Last modified on 19 July, 2016
 

This documentation applies to the following versions of Splunk® Supported Add-ons: released

Acrobat logo Download manual
Acrobat logo Download this page

Was this topic useful?







You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters