Difference between revisions of "ZenPack:Oracle Database (Commercial)"

From Zenoss Wiki
Jump to: navigation, search
Line 438: Line 438:
''Oracle Database Reference'' documentation:
''Oracle Database Reference'' documentation:
* http://docs.oracle.com/cd/E16655_01/server.121/e17615/stats002.htm
* http://docs.oracle.com/cd/B28359_01/server.111/b28320/stats002.htm
* http://docs.oracle.com/cd/B14117_01/server.101/b10755/stats002.htm
== Miscellaneous and Troubleshooting ==
== Miscellaneous and Troubleshooting ==

Revision as of 16:25, 1 July 2014

Zenoss, Inc.
GNU General Public License, Version 2, or later
ZenPack name

Applications Monitored: 

Oracle Database (Commercial) ZenPack

Oracle database monitoring.


The ZenPack Catalog has moved to its new home at https://www.zenoss.com/product/zenpacks as of January 17, 2017. The following information may be out of date, and this page will eventually be removed.


This is an Open Source ZenPack developed by Zenoss, Inc. Enterprise support for this ZenPack is available to commercial customers with an active subscription.


Version 3.0.1- Download
Released on 2014/05/19
Compatible with Zenoss Resource Manager 4.1.x, Zenoss Resource Manager 4.2.x


This ZenPack is an enhanced version of the older ZenPacks.zenoss.DatabaseMonitor 2.5.0, and adds Instances as a component thereby allowing multiple components to be associated with a single server.


  • OS must support Oracle JDBC 12.1+ drivers
  • Oracle must be listening on a target device's TCP port with JDBC support.



The features added by this ZenPack are listed below. They are each detailed further below.

  • Automatic migration from older versions
  • Data Collection done with Oracle JDBC (Java)
  • Multiple instance support via components
  • TableSpace Support

ConnectionString Format

The Connection String is the data format that is used to specify a database. The connection string format >

                      - or -



Discovery and Migration

Instances will be automatically discovered via connection string which contains username, password, port, and instance-name you provide. If starting from a new server without any OracleDB monitoring, simply bind the Oracle template from the gear menu on the bottom left of the server's infrastructure page.

Migration is done by binding the new Oracle template to the existing server and executing "Model Device" from the gear menu on the inventory page. Do not remove the older template until the new template is in place or the migration will lack the required information. Once you have verified that the new data model is correct you should delete the older template in the Advanced area of the GUI.

Performance Monitoring

Host monitoring is separately done by the server monitoring template. This zenpack is applies a component level addition to the host system. The following Oracle related metrics will be collected every 5 minutes by default.

  • logonscumulative: Total number of logons
  • logonscurrent: Total number of current logons
  • messagesreceived: Messages received in last interval
  • messagessent: Total number of messages sent
  • openedcursorscumulative: Total number of opened cursors since instance has started
  • openedcursorscurrent: Total number of current cursors open
  • physicalreads: Physical reads per second
  • physicalwrites: Physical writes per second
  • redoentries: Redo entry number since instance has started
  • redosize: Total amount of redo generated in bytes
  • redowrites: Total number of writes by LGWR to the redo logs
  • sortsdisk: Number of sort operations that required at least one disk write
  • sortsmemory: Number of sort operations that were performed completely in memory
  • sortsrows: Total number of rows sorted
  • usercalls: Number of user calls such as login, parse, fetch, or execute
  • usercommits: Number of user commits; the closest thing to a user transaction rate
  • userIOwaittime: Total wait time (microseconds) belonging to User I/O wait class
  • userrollbacks: Number of times users manually issue the ROLLBACK statement

Instance Support

The new instance support allows multiple instances per server to be monitored. The Context Display area has the following:

  • Graphs
  • Events
  • Details
  • Templates
  • TableSpaces

Instance Graphs

Instance support includes the following graphs:

  • SGA: Total, Free, Used (bytes)
  • Cache Hit Ratio (%)
  • User IO Wait Time (wait/sec)
  • User Operations: Calls, Commits, Rollbacks (ops/sec)
  • Sorts: Disk, Memory, Rows (sorts/sec)
  • Redo Operations: Entries, Writes (ops/sec)
  • Redo Size (bytes)
  • Physical Operations: Reads, Writes (ops/sec)
  • Messages: Sent, Received (messages/sec)
  • Logins
  • Opened Cursors: Current (#), Per Second (opens/sec)

Table Space Support

Oracle TableSpace has been added. The TableSpace grid has links to the parent Instance. The Context Display area has the following:

  • Graphs
  • Events
  • Details
  • TableSpaces

For each instance, there will be a set of TableSpaces as defined on the target system. This usually includes:

  • TEMP
  • UNDO

TableSpace Graphs

Oracle TableSpaces can have dynamically allocated disk space. This can cause some confusion when considering currently allocated space vs reserve allocated space.

TableSpace graphs include the following:

  • Disk Space: The Used and Free space currently allocated
  • Free Disk Space: Both currently allocated and total free space.
  • Status: Either 1 for up or 0 for down.
  • IO Operations
  • Time Per Request

Installed Items

Installing this ZenPack will add the following items to your Zenoss system:

Configuration Properties

  • zOracleConnectionString
  • zOracleUser
  • zOraclePassword
  • zOracleConnectString (for legacy support)
  • zOracleInstance
  • zOraclePort

Modeler Plugin

  • zenoss.ojdbc.Instances

Datasource Types

  • Oracle

Monitoring Templates

  • OracleInstance: sysstat (Oracle)
  • OracleTableSpaces: TableSpaceDU (Oracle)
  • OracleTableSpaces: TableSpaceIO (Oracle)

Installation and Configuration

Installation consists of the following steps which will be covered in depth:

  • Configure Oracle Server
  • Network Configuration
  • Install the plugin
  • Install the base server device
  • Bind the plugin modeler template to server
  • Set the zOracleConnectionStrings property
  • Model the device

Configure Oracle Server

The Oracle server must be configured correctly and granted access in order to allow access to the tables needed for monitoring. These steps are required:

  • Make sure Oracle server is listening on the correct TCP port (usually 1521)

  • Configure the Oracle DB for ZP Access. You must go into the database and allow the zenoss user to have access to specific tables. The simplest way is to use the sqlplus command from the oracle account. Make sure you have ORACLE_HOME and ORACLE_SID set correctly in your oracle user account. The tables needed include:

    • All V$ tables and views
    • SYS.TS$
    • SYS.FILE$
    • dba_data_files
    • dba_free_space
    • dba_tablespaces
    • dba_temp_files

We recommend using the sample script to set this up:

/* Steps:                                                                  
 1: Log into oracle account. Make sure ORACLE_HOME and ORACLE_SID are set:      
 2: ./sqlplus /nolog                                                            
 3: connect /as sysdba                                                          
 4: oracle_env.sh (for Oracle 10.X)                                             
drop user zenoss cascade;                                                       
create user zenoss identified by zenoss;                                        
grant connect,resource to zenoss;                                               
DROP   ROLE SELECT_VDOLLAR_ROLE;                                                
CREATE ROLE SELECT_VDOLLAR_ROLE;                                                
   FOR x IN (SELECT object_name                                                 
             FROM dba_objects                                                   
             WHERE object_type = 'VIEW'                                         
             AND object_name LIKE 'V\_$%' ESCAPE '\' )                          
      EXECUTE IMMEDIATE 'grant select on ' || x.object_name || ' to SELECT_VDOLLAR_ROLE';
   END LOOP;                                                                    
create view VW_X$KCCFN as select * from SYS.X$KCCFN ;                           
create view VW_X$KTFTHC as select * from SYS.X$KTFTHC ;                         
grant select on SYS.TS$ to SELECT_VDOLLAR_ROLE ;                                
grant select on SYS.FILE$ to SELECT_VDOLLAR_ROLE ;                              
grant select on SYS.VW_X$KCCFN to SELECT_VDOLLAR_ROLE ;                         
grant select on SYS.VW_X$KTFTHC to SELECT_VDOLLAR_ROLE ;                        
grant select on dba_data_files  to SELECT_VDOLLAR_ROLE ;                        
grant select on dba_free_space  to SELECT_VDOLLAR_ROLE ;                        
grant select on dba_tablespaces to SELECT_VDOLLAR_ROLE ;                        
grant select on dba_temp_files  to SELECT_VDOLLAR_ROLE ;                        
grant SELECT_VDOLLAR_ROLE to zenoss;

Network Configuration

If your network has strong firewalls you may need to open up ports from your Zenoss installation to the monitored Oracle servers. You only need to open the TCP port the Oracle's JDBC listener uses. This is typically TCP 1521, but will differ with multiple instances which must each have unique TCP ports.

Installing the Plugin

You can install the ZenPack egg via the GUI or manually.

To manually install the ZenPack egg, take the following steps:

sudo su - zenoss                                                           
zenpack --install ZenPacks.zenoss.DatabaseMonitor-*.egg                         
zenoss restart

where ZenPacks.zenoss.DatabaseMonitor-*.egg should match your egg version.

Install the Base Server Device

Install the base server as you normally would. Just select the type of server that Oracle will run on (Linux, Solaris, Windows, etc..). Make sure to set zProperty zCommandCommandTimeout to 120.

Now you are ready to bind the modeler:

Bind the Plugin Modeler Template to Server

  • From the Infrastructure page, select your server.
  • From Modeler Plugins, push zenoss.ojdbc.Instances to the Selected group, Save.
  • Now you are almost ready to model the device.

Enable the TableSpace Templates

Because of potential performance issues, Tablespace templates are disabled by   

default. They must be enabled manually. For instances with less that 12 TableSpaces, enabling this should be safe. Please test your environment in advance to determine if this will affect your system load.

To enable these templates:

  • Navigate to Advanced -> Monitoring Templates
  • Select OracleTableSpace
  • DoubleClick TableSpaceDU and click Enabled
  • Save
  • DoubleClick TableSpaceIO and click Enabled
  • Save

Set the zConnectionStrings Property

  • Select your server from the Infrastructures Tab
  • Click on Configuration Properties
  • Search for zOracle and locate and select zOracleConnectionStrings
    • You may enter all the connection strings for Instances
    • Don't fill in the singular zOracleConnectString
  • Search for zOracleUser: Enter your username for the DB (zenoss by default)
  • Search for zOraclePassword: Enter your password for the DB (zenoss by default)

These settings will allow you to protect your passwords if you set ${here/zOraclePassword} in the connection string as in the following examples:


We STRONGLY recommend that you install the same read-only user in your databases so that you don't have to chase down usernames and passwords for your instances. A read-only DB user will also enhance your security and be less prone to abuse.

Model the Component

  • From the device view, select Model Device from the gear menu.
  • If all goes will Zenoss should model the device.
  • Since the Instances are just components of the server, you should see them hanging off of the device as components.

Migration from Older Versions

When migrating from the older version of DatabaseMonitor, you must:

  • Install the new version of the ZP
  • Restart Zenoss Services like Zenhub
  • Make sure to leave the old version in place during the install process. This will allow the new version to construct the new zConnectionStrings entry.
  • Bind the Plugin Modeler as above
  • Model the Component as above
  • Remove the old monitor Template from the device

Moving Old RRD Data

You will have to manually move the old RRD data. In order to do so you must first install the new Zenpack and identify the new location of the Instance you are monitoring as well as the old. To do so:

  1. Identify the old and new locations of the ZP RRD data:
<zenoss>: find $ZENHOME/perf/Devices -name "sysstat_usercalls.rrd"

Copy down that folder location. The new location will have "oracle_instances" in the path.

  1. Copy the RRD files from the old to the new locations:
<zenoss>: cp -a $ZENHOME/perf/Devices/old/path/ $ZENHOME/.../oracle_instances/new/path/

DataPoints and DataSources

Adding a New DataSource

Adding new datasource is a simple process, however you have an SQL query that conforms to the correct format. In this ZenPack, we require that the SQL query return data in the following two column format:

name1 | value1
name2 | value1
..etc.. | ..etc..

where NAME is the datapoint identifier and VALUE is its numerical value. For example, a valid query to find the number of theads for a particular instance would be:

"select 'thread_count' as name, thread# value from v$instance"

There is one problem though: you need to double up on the dollar sign because the Zenoss TALES interpreter will try to interpret a single-dollar variable as an internal variable. The actual query must look like this:

"select 'thread_count' as name, thread# value from v$$instance"

Note: If your datapoint NAME has any special characters or spaces, they will be stripped out of the name key.

In order to add this query to the OracleInstance datasource, perform the following steps:

  • Go to the Advanced page and select Monitoring Templates
  • Select OracleInstance
  • Hit the + key in the DataSources panel
    • Enter "threads" as Name
    • Select "Oracle" as the Type
    • -Submit-
  • Now open the threads datasource by double-clicking it. Add the following to the form:
    • Name: threads
    • Enabled: on
    • Component: ${here/id}
    • Timeout: 120
    • Cycle Time: 300
    • Severity: [Info|Warning|Critical]
    • ConnectionString: ${here/connectionString}
    • SQL Query: select 'thread_count' as name, thread# value from v$$instance
    • -SAVE-

Now you have the datasource defined you must add a Datapoint to it. This example query only has a single value associated with it. Next we will add this datapoint.

Adding a New DataPoint

We now add the thread_count datapoint to our new threads datasource. You could also add a datapoint from an existing DataSource like sysstat if it collects extra data that is not already setup.

We do the following:

  • Highlight the threads datasource in the DataSources panel.
  • From the Gear Menu, select Add Data Point:
    • Name: thread_count
    • Description: Number of threads
    • RRD Type: GAUGE
    • Read Only: on
    • -SAVE-

Now you can use the datapoint in a graph by referencing the name as threads.thread_count from the pull downs in the Graph section. Alternatively, from the DataSources panel, you can select the datapoint and assign it directly to a graph.

Note: Once you assign this point to a graph, it will be referenced with the dot as threads_thread_count.

Adding New DataPoints to SYSSTAT DataSource

As mentioned before you can add a new datapoint to the existing sysstat datasource by simply adding the correct key identifier to the sysstat datasource as outlined above. To get a current list of these indentifiers, you can consult the Oracle docuentation for Statistics Descriptions in the Oracle Database Reference documentation:

Miscellaneous and Troubleshooting

JDBC Collection Speed and Security Note (/dev/random):

The data collector uses the Oracle JDBC connector to OracleDB. The JDBC connector normally uses the Linux device file /dev/random for its random number generation, primarily for encryption. The lack of system entropy causes this device significant delay and therefore causes data collection to time out and eventually fail.

We have bypassed this problem in java by passing -Djava.security.egd=file:///dev/urandom to the java runtime command. If your number generator is fast enough you can re-enstate the original /dev/random by modifying $ZPHOME/lib/txojdbc.py in the _get_query_command().

Insufficient Processes on Oracle

If you get an ORA-12519 in your data collection, and your DB is setup with fewer than 100 processes you may need to bump up that amount to 150 in sqlplus:

alter system set processes = 150 scope = spfile;


Normal Installation (packaged egg)

  1. Download the appropriate egg file for the version of Zenoss you are running.
  2. Ensure you are logged in as the zenoss user:
    $ sudo su - zenoss
  3. Install the ZenPack:
    $ zenpack --install ZenPacks.zenoss.DatabaseMonitor-*.egg
  4. Restart these services:
    $ zenoss restart


Purplemarker.png New: Don't forget to add yourself to the Zenoss User Map!

blog comments powered by Disqus