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

From Zenoss Wiki
Jump to: navigation, search
Line 145: Line 145:
 
* IO Operations                                                                 
 
* IO Operations                                                                 
 
* Time Per Request                                                               
 
* 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 and Configuration ==                                             
 
                                                                                  
 
                                                                                  

Revision as of 20:50, 19 December 2013


Note: This ZenPack is available in commercial versions of Zenoss. Click here to request more information about this commercial ZenPack. Click here to see all commercial ZenPacks.

Organization
Zenoss, Inc.
ZenPack name
ZenPacks.zenoss.DatabaseMonitor


Applications Monitored: 



Oracle Database (Commercial) ZenPack

Oracle database monitoring.

Warning

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.

Support

This ZenPack is included with commercial versions of Zenoss and enterprise support for this ZenPack is provided to Zenoss customers with an active subscription.

Releases

Version 3.0.0- Download
Released on 2013/12/19
Compatible with Zenoss Core 4.1.1, 4.2.3, 4.2.4

Background

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.

Prerequisites

  • Supported Zenoss version: 4.1.1, 4.2.3, 4.2.4
  • ZenPack dependencies: None
  • Supported versions of Oracle: 10.X, 11.X, 12.X
  • OS must support Oracle JDBC 12.1.0.1 drivers
  • Oracle must be running and listening on a target device's TCP port.

Gallery

Features

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

Data Formats

The Connection String is the data format that is used to specify a database. The connection string format must have the following JDBC Thin driver format:

<Username>/<Password>@<fqdn.domain.com>:<port>:<SID>

For example:

Example: joe/secretpassword@area51.zenoss.com:1521:XE

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:

  • SYSAUX
  • SYSTEM
  • TEMP
  • UNDO
  • USERS

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
  • 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.X$KCCFN
    • SYS.X$KTFTHC
    • SYS.TS$
    • SYS.FILE$
    • SYS.VW_X$KCCFN
    • SYS.VW_X$KTFTHC
    • 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;                                                
                                                                                
BEGIN                                                                           
   FOR x IN (SELECT object_name                                                 
             FROM dba_objects                                                   
             WHERE object_type = 'VIEW'                                         
             AND object_name LIKE 'V\_$%' ESCAPE '\' )                          
   LOOP                                                                         
      EXECUTE IMMEDIATE 'grant select on ' || x.object_name || ' to SELECT_VDOLLAR_ROLE';
   END LOOP;                                                                    
END;                                                                            
/                                                                               
                                                                                
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;

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.

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.

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:

JoeNameth/${here/zOraclePassword}@db1.example.com:1521:XE                  
${here/zOracleUser}/${here/zOraclePassword}@db1.example.com:1521:XE             
${here/zOracleUser}/${here/zOraclePassword}@${here/manageIp}:${here/zOraclePort}:${here/zOracleInstance}

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&quot

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

2. Copy the RRD files from the old to the new locations:

<zenoss>: cp -a $ZENHOME/perf/Devices/old/path/ $ZENHOME/.../oracle_instances/new/path/

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;