|Callbacks:||config, init, read, shutdown|
|Copyright:||2008–2009 Florian octo Forster|
|License:||GPLv2 (with exception)|
|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.
<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>
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>
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>
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.
(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.
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
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:
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.