Plugin Oracle

Name:	Oracle plugin
Type:	read
Callbacks:	config, init, read, shutdown
Status:	supported
FirstVersion:	4.6
Copyright:	2008–2009 noris network AG, 2012 Florian octo Forster
License:	GPLv2 (with exception)
Manpage:	collectd.conf(5)
See also:	List of Plugins

The Oracle plugin uses the "Oracle call interface" (OCI) to connect to one or more Oracle database systems, execute SQL statements on them and read back the result. Depending on the configuration, the returned values are then converted into collectd "value lists" (the data structure used internally to pass statistics around). This plugin is a generic plugin, i.e. it cannot work without configuration, because there is no reasonable default behavior. Please read the Plugin oracle section of the collectd.conf(5) manual page for an in-depth description of the plugin's configuration.

The configuration syntax of the Oracle, DBI, and PostgreSQL plugins is very similar, because the configuration of those plugins is handled by the same module. Also, we tried to keep the syntax similar to that of the SNMP plugin. So if you use any of those plugins already, most of the following will look familiar.

Synopsis

 <Plugin oracle>
   <Query "out_of_stock">
     Statement "SELECT category, COUNT(*) AS value \
                    FROM products \
                    WHERE in_stock = 0 \
                    GROUP BY category"
     <Result>
       Type "gauge"
       # InstancePrefix "foo"
       InstancesFrom "category"
       ValuesFrom "value"
     </Result>
   </Query>
   <Database "product_information">
     ConnectID "db01"
     Username "oracle"
     Password "secret"
     Query "out_of_stock"
   </Database>
 </Plugin>

Example graphs

IO per Tablespace

Based on the following query:

 <Query "io_per_tablespace">
   Statement "SELECT sum(vf.PHYBLKRD)*8192 AS PHY_BLK_R, \
                     sum(vf.PHYBLKWRT)*8192 AS PHY_BLK_W, \
                     'tablespace' AS i_prefix, \
                     dt.tablespace_name \
                  FROM ((dba_data_files dd JOIN v$filestat vf ON dd.file_id = vf.file# ) \
                        JOIN dba_tablespaces dt ON dd.tablespace_name = dt.tablespace_name) \
                  GROUP BY dt.tablespace_name"
   <Result>
     Type "io_octets"
     InstancesFrom "i_prefix" "TABLESPACE_NAME"
     ValuesFrom "PHY_BLK_R" "PHY_BLK_W"
   </Result>
 </Query>

DB efficiency

Based on the following query:

 <Query "db_efficientcy">
   Statement "SELECT round(sum(decode(METRIC_NAME, 'Database Wait Time Ratio', value)),2) AS DATABASE_WAIT_TIME_RATIO, \
                     round(sum(decode(METRIC_NAME, 'Database CPU Time Ratio', value)),2) AS DATABASE_CPU_TIME_RATIO, \
                     'DB_EFFICIENCY' AS DB_EFFICIENCY \
                  FROM SYS.V_$SYSMETRIC \
                  WHERE METRIC_NAME IN ('Database CPU Time Ratio', 'Database Wait Time Ratio') \
                      AND INTSIZE_CSEC = (SELECT max(INTSIZE_CSEC) FROM SYS.V_$SYSMETRIC)"
   <Result>
     Type "disk_time"
     InstancesFrom "DB_EFFICIENCY"
     ValuesFrom "DATABASE_WAIT_TIME_RATIO" "DATABASE_CPU_TIME_RATIO"
   </Result>
 </Query>

License

Because the GPLv2 (the license of the Oracle plugin itself) and the “Oracle® Technology Network License” (OTN) are not compatible, an exception to the GPL has been added to the licensing terms of the Oracle plugin, allowing users to link against the “Oracle® Call Interface” (OCI).

The exception reads:

 Linking src/oracle.c ("the oracle plugin") statically or dynamically with
 other modules is making a combined work based on the oracle plugin. Thus,
 the terms and conditions of the GNU General Public License cover the whole
 combination.

 In addition, as a special exception, the copyright holders of the oracle
 plugin give you permission to combine the oracle plugin with free software
 programs or libraries that are released under the GNU LGPL and with code
 included in the standard release of the Oracle® Call Interface (OCI) under
 the Oracle® Technology Network (OTN) License (or modified versions of such
 code, with unchanged license). You may copy and distribute such a system
 following the terms of the GNU GPL for the oracle plugin and the licenses of
 the other code concerned.

 Note that people who make modified versions of the oracle plugin are not
 obligated to grant this special exception for their modified versions; it is
 their choice whether to do so. The GNU General Public License gives
 permission to release a modified version without this exception; this
 exception also makes it possible to release a modified version which carries
 forward this exception. However, without this exception the OTN License does
 not allow linking with code licensed under the GNU General Public License.

 Oracle® is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective owners.

Building the Oracle plugin

You need to set or export the ORACLE_HOME environment variable before running the configure script in order for it to find the appropriate headers and shared objects. For example:

 ORACLE_HOME="/oracle/app/product/10.2.0/dbhome_1"
 export ORACLE_HOME
 ./configure ${options}

The end of the configure output should contain:

 Configuration:
   Libraries:
     …
     oracle  . . . . . . . yes
     …
   Modules:
     …
     oracle  . . . . . . . yes

Building with Oracle Instant Client

Oracle provides an "Instant Client" software package which provides all the necessary libraries and headers required by the Oracle plugin to build successfully.

For some reason Oracle chose to store the files in locations that differ from a regular installation of Oracle Client in these packages.

Here is how you get going: Install the following packages:

• oracle-instantclient11.2-basic
• oracle-instantclient11.2-devel

With super user privileges execute the following commands:

 mkdir /usr/lib/oracle/11.2/client64/rdbms
 ln -s /usr/include/oracle/11.2/client64 /usr/lib/oracle/11.2/client64/rdbms/public
 export ORACLE_HOME=/usr/lib/oracle/11.2/client64

Now the plugin should build.

Caveats

Native Language Support

collectd uses strtod and strto[u]ll to parse the strings returned from the database. If the database returns values with a different language setting, parsing may fail. You should therefore set the environment variable NLS_LANG to specify how floating point numbers are returned. For example by setting the appropriate environment variable in a file that is sourced by the init script:

 # /etc/default/collectd
 NLS_LANG=AMERICAN_AMERICA
 export NLS_LANG

Dependencies

• OCI
• libclntsh (An Oracle library with a mystic name. Should be part of OCI.)
• Suitable values for LD_LIBRARY_PATH and ORACLE_HOME have to be provided.