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

From Zenoss Wiki
Jump to: navigation, search
(Replace 3.0.7 release with 3.0.8.)
 
(32 intermediate revisions by 3 users not shown)
Line 3: Line 3:
 
|Organization=Zenoss, Inc.
 
|Organization=Zenoss, Inc.
 
|ZenPack name=ZenPacks.zenoss.DatabaseMonitor
 
|ZenPack name=ZenPacks.zenoss.DatabaseMonitor
 +
|Flavor=commercial
 
|Releases={{Release
 
|Releases={{Release
|Version=3.0.0
+
|Version=3.0.8
|Release date=2013/12/19
+
|Tag=3.0.8
|Compatible with=Zenoss Resource Manager 4.1.x, Zenoss Resource Manager 4.2.x
+
|Release date=2016/11/16
|Download URL=https://support.zenoss.com/ics/support/DLRedirect.asp?deptID=16059&fileID=60128
+
|Compatible with=Zenoss Resource Manager 4.2.x, Zenoss Resource Manager 5.0.x, Zenoss Resource Manager 5.1.x, Zenoss Resource Manager 5.x.x
 +
|Download URL=https://zenoss.leapfile.net/
 
}}
 
}}
 
|Devices=
 
|Devices=
Line 17: Line 19:
 
|DataAudits=
 
|DataAudits=
 
}}
 
}}
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.
+
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
== Prerequisites ==                                                          
+
be associated with a single server.
+
 
* OS must support Oracle JDBC 12.1+ drivers                                
+
== Prerequisites ==
 +
 
 +
* OS must support Oracle JDBC 12.1+ drivers
 
* Oracle must be listening on a target device's TCP port with JDBC support.
 
* Oracle must be listening on a target device's TCP port with JDBC support.
  
 
__TOC__
 
__TOC__
  
== Gallery ==                                                                
+
== Gallery ==
  
<gallery widths=150px heights=200px>                                          
+
<gallery widths=150px heights=200px>
databasemonitor_add_connectionstring.png                                      
+
databasemonitor_add_connectionstring.png|Add Connection String
databasemonitor_monitoring_template.png                                        
+
databasemonitor_monitoring_template.png|Monitoring Template
databasemonitor_model_device.png                                              
+
databasemonitor_model_device.png|Model Device
databasemonitor_chr.png                                                        
+
databasemonitor_chr.png|Cache Hit Ratio
databasemonitor_components.png                                                
+
databasemonitor_components.png|Components
databasemonitor_instances.png                                                  
+
databasemonitor_instances.png|Instances
databasemonitor_sga.png                                                        
+
databasemonitor_sga.png|SGA
databasemonitor_messages.png                                                  
+
databasemonitor_messages.png|Messages
databasemonitor_physical_ops.png                                              
+
databasemonitor_physical_ops.png|Physical Operations
databasemonitor_ts.png                                                        
+
databasemonitor_ts.png|TableSpace Details
databasemonitor_ts_diskspace.png                                              
+
databasemonitor_ts_diskspace.png|TableSpace Disk
databasemonitor_ts_io.png                                                      
+
databasemonitor_ts_io.png|TableSpace IO
 
</gallery>
 
</gallery>
  
== Features ==                                                                
+
== Features ==
                                                                               
+
 
 
The features added by this ZenPack are listed below. They are each detailed further below.
 
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  must have the following JDBC Thin driver format:
 
                                                                               
 
<pre>&lt;Username&gt;/&lt;Password&gt;@&lt;fqdn.domain.com&gt;:&lt;port&gt;:&lt;SID&gt;</pre>
 
For example:                                                                   
 
                                                                               
 
<pre>Example: joe/secretpassword@area51.zenoss.com:1521:XE</pre>
 
             
 
=== 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 &quot;Model      Device&quot; 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 ===                                                
+
* Automatic migration from older versions
                                                                               
+
* Data Collection done with Oracle JDBC (Java)
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.
+
* Multiple instance support via components
                                                                               
+
* TableSpace Support
* logonscumulative: Total number of logons                                     
+
 
* logonscurrent: Total number of current logons                                
+
=== ConnectionString Format ===
* messagesreceived: Messages received in last interval                        
+
 
* messagessent: Total number of messages sent                                  
+
The Connection String is the data format that is used to specify a database. The
* openedcursorscumulative: Total number of opened cursors since instance has started
+
connection string format must have one of the following JDBC Thin driver
* openedcursorscurrent: Total number of current cursors open                  
+
formats:
* physicalreads: Physical reads per second                                    
+
 
* physicalwrites: Physical writes per second                                  
+
<pre>&lt;Username&gt;/&lt;Password&gt;@&lt;fqdn.domain.com&gt;:&lt;port&gt;:&lt;SID&gt;
* redoentries: Redo entry number since instance has started                    
+
                      - or -
* redosize: Total amount of redo generated in bytes                            
+
&lt;Username&gt;/&lt;Password&gt;@&lt;fqdn.domain.com&gt;:&lt;port&gt;/&lt;SERVICE_NAME&gt;</pre>
* redowrites: Total number of writes by LGWR to the redo logs                  
+
Example:
* sortsdisk: Number of sort operations that required at least one disk write  
+
 
* sortsmemory: Number of sort operations that were performed completely in memory
+
<pre>Example: joe/secretpassword@area51.zenoss.com:1521:XE
* sortsrows: Total number of rows sorted                                       
+
Example: joe/secretpassword@area51.zenoss.com:1521/service.zenoss.int</pre>
* 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
+
* Usernames and passwords should conform to standard Oracle formats.
* userIOwaittime: Total wait time (microseconds) belonging to User I/O wait class
+
 
* userrollbacks: Number of times users manually issue the ROLLBACK statement  
+
=== Discovery and Migration ===
                                                                               
+
 
=== Instance Support ===                                                      
+
Instances will be automatically discovered via connection string which contains
                                                                               
+
username, password, port, and instance-name you provide. If starting from a new
The new instance support allows multiple instances per server to be monitored. The ''Context Display''     area has the following:
+
server without any OracleDB monitoring, simply bind the Oracle template from the
                                                                               
+
gear menu on the bottom left of the server's infrastructure page.
* Graphs                                                                      
+
 
* Events                                                                      
+
Migration is done by binding the new Oracle template to the existing server and
* Details                                                                      
+
executing &quot;Model Device&quot; from the gear menu on the inventory page. Do
* Templates                                                                    
+
not remove the older template until the new template is in place or the
* TableSpaces                                                                  
+
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
==== Instance Graphs ====                                                      
+
area of the GUI.
                                                                               
+
 
Instance support includes the following graphs:                                
+
=== Metrics ===
                                                                               
+
 
* SGA: Total, Free, Used (bytes)                                              
+
Host monitoring is separately done by the server monitoring template. This
* Cache Hit Ratio (%)                                                          
+
zenpack is applies a component level addition to the host system. The following
* User IO Wait Time (wait/sec)                                                
+
Oracle related metrics will be collected every 5 minutes by default.
* User Operations: Calls, Commits, Rollbacks (ops/sec)                        
+
 
* Sorts: Disk, Memory, Rows (sorts/sec)                                        
+
==== Instance Metrics ====
* Redo Operations: Entries, Writes (ops/sec)                                  
+
 
* Redo Size (bytes)                                                            
+
* sysstat.cache_hit_ratio: Cache Hit Ratio
* Physical Operations: Reads, Writes (ops/sec)                                
+
* sysstat.logonscumulative: Cumulative Logons
* Messages: Sent, Received (messages/sec)                                      
+
* sysstat.logonscurrent: Total number of current logons
* Logins                                                                      
+
* sysstat.messagesreceived: Messages received in last interval
* Opened Cursors: Current (#), Per Second (opens/sec)                          
+
* sysstat.messagessent: Total number of messages sent
                                                                               
+
* sysstat.openedcursorscumulative: Total number of opened cursors since instance has started
=== Table Space Support ===                                                    
+
* sysstat.openedcursorscurrent: Total number of current cursors open
                                                                               
+
* sysstat.physicalreads: Physical reads per second
Oracle TableSpace has been added. The TableSpace grid has links to the parent Instance. The ''Context     Display'' area has the following:
+
* sysstat.physicalwrites: Physical writes per second
                                                                               
+
* sysstat.redoentries: Redo entry number since instance has started
* Graphs                                                                      
+
* sysstat.redosize: Total amount of redo generated in bytes
* Events                                                                      
+
* sysstat.redowrites: Total number of writes by LGWR to the redo logs
* Details                                                                      
+
* sysstat.sessionpgamemory: Session PGA Memory in use
* TableSpaces                                                                  
+
* sysstat.sessionpgamemorymax: Maximum available PGA Memory
                                                                               
+
* sysstat.sga_free: SGA Free Memory
For each instance, there will be a set of TableSpaces as defined on the target system. This usually       includes:
+
* sysstat.sga_total: SGA Total Memory
                                                                               
+
* sysstat.sga_used: SGA Used memory
* SYSAUX                                                                      
+
* sysstat.sortsdisk: Number of sort operations that required at least one disk write
* SYSTEM                                                                      
+
* sysstat.sortsmemory: Number of sort operations that were performed completely in memory
* TEMP                                                                        
+
* sysstat.sortsrows: Number of sort operations that were performed completely in memory
* UNDO                                                                        
+
* sysstat.usercalls: Number of user calls such as login, parse, fetch, or execute
 +
* sysstat.usercommits: Number of user commits; the closest thing to a user transaction rate
 +
* sysstat.userIOwaittime: Total wait time (microseconds) belonging to User I/O wait class
 +
* sysstat.userrollbacks: Number of times users manually issue the ROLLBACK statement
 +
 
 +
==== Tablespace Metrics ====
 +
 
 +
* dbmTBSP.BYTES_FREE: Free disk space
 +
* dbmTBSP.BYTES_USED: Used disk space
 +
* dbmTBSP.TOTAL_SIZE: Total disk Space
 +
 
 +
==== Tablespace File Metrics ====
 +
 
 +
These metrics are based on statistics over tablespace files.
 +
If more than one tablespace file exists, metrics are file averages, and
 +
standard deviations are calculated over file metrics.
 +
 
 +
* dbmTBSPstats.READS: Total number of reads to db
 +
* dbmTBSPstats.READTIME: Read time average over files
 +
* dbmTBSPstats.WRITES: Total number of writes to db
 +
* dbmTBSPstats.WRITETIME: Write time average over files
 +
 
 +
* dbmTBSPstats.READS_STDDEV: Standard Deviation on reads
 +
* dbmTBSPstats.READTIME_STDDEV: Standard Deviation on read-time
 +
* dbmTBSPstats.WRITES_STDDEV: Standard Deviation on writes
 +
* dbmTBSPstats.WRITETIME_STDDEV: Standard Deviation on write-time
 +
 
 +
 
 +
=== 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)
 +
* PGA: 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 at least:
 +
 
 +
* SYSAUX
 +
* SYSTEM
 +
* TEMP
 +
* UNDO
 
* USERS
 
* USERS
  
==== TableSpace Graphs ====                                                    
+
==== TableSpace Graphs ====
                                                                               
+
 
Oracle TableSpaces can have dynamically allocated disk space. This can cause some confusion when           considering currently allocated space vs reserve allocated space.
+
Oracle TableSpaces can have dynamically allocated disk space. This can cause
                                                                               
+
some confusion when considering currently allocated space vs reserve allocated
TableSpace graphs include the following:                                      
+
space.
                                                                               
+
 
* Disk Space: The '''Used''' and '''Free''' space currently allocated          
+
TableSpace graphs include the following:
* Free Disk Space: Both currently allocated and total free space.             
+
 
* Status: Either 1 for up or 0 for down.                                       
+
* Disk Space (bytes): '''Used''', '''Free''', and '''Total''' space allocated
* IO Operations                                                               
+
* IO Operations (ops): Reads, Writes, and their standard deviation across files
* Time Per Request                                                            
+
* Time Per Request (sec): Read-time, write-time, and standard deviation across files
  
 
== Installed Items ==
 
== Installed Items ==
  
Installing this ZenPack will add the following items to your Zenoss system:    
+
Installing this ZenPack will add the following items to your Zenoss system:
                                                                               
+
 
=== Configuration Properties ===                                              
+
=== Configuration Properties ===
                                                                               
+
 
* zOracleConnectionString                                                      
+
* zOracleConnectionString
* zOracleUser                                                                  
+
* zOracleUser
* zOraclePassword                                                              
+
* zOraclePassword
* zOracleConnectString (for legacy support)                                    
+
* zOracleConnectString (for legacy support)
* zOracleInstance                                                              
+
* zOracleInstance
* zOraclePort                                                                  
+
* zOraclePort
                                                                               
+
 
=== Modeler Plugin ===                                                        
+
=== Modeler Plugin ===
                                                                               
+
 
* zenoss.ojdbc.Instances                                                      
+
* zenoss.ojdbc.Instances
                                                                               
+
 
=== Datasource Types ===                                                      
+
=== Datasource Types ===
                                                                               
+
 
* Oracle                                                                      
+
* Oracle
                                                                               
+
 
=== Monitoring Templates ===                                                  
+
=== Monitoring Templates ===
                                                                               
+
 
* OracleInstance: sysstat (Oracle)                                             
+
* OracleInstance: sysstat
* OracleTableSpaces: TableSpaceDU (Oracle)                                     
+
* OracleTableSpaces: dbmTBSP
* OracleTableSpaces: TableSpaceIO (Oracle)                                     
+
 
                                                                             
+
== Installation and Configuration ==
== Installation and Configuration ==                                          
+
 
                                                                               
+
Installation consists of the following steps which will be covered in depth:
Installation consists of the following steps which will be covered in depth:  
+
 
                                                                               
+
 
* Configure Oracle Server
 
* Configure Oracle Server
* Network Configuration                                                
+
* Network Configuration
* Install the plugin                                                          
+
* Install the plugin
* Install the base server device                                              
+
* Install the base server device
* Bind the plugin modeler template to server                                  
+
* Bind the plugin modeler template to server
* Set the zOracleConnectionStrings property                                    
+
* Set the zOracleConnectionStrings property
* Model the device                                                            
+
* Model the device
                                                                               
+
 
=== Configure Oracle Server ===                                                
+
=== 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:
+
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:'''
<ul>                                                                          
+
 
<li><p>Make sure Oracle server is listening on the correct TCP port (usually 1521)</p></li>
+
<ul>
<li><p>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:</p>
+
<li>Make sure Oracle server is listening on the correct TCP port (usually 1521)</li>
<blockquote><ul>                                                              
+
<li><p>Configure the Oracle DB for ZP Access. You must configure the database
<li>All V$ tables and views</li>                                              
+
to create and allow access to specific tables for the Zenoss user. The simplest
<li>SYS.X$KCCFN</li>                                                          
+
way is to use the sqlplus command from the oracle account. Make sure you have
<li>SYS.X$KTFTHC</li>                                                          
+
ORACLE_HOME and ORACLE_SID set correctly in your oracle user account. The
<li>SYS.TS$</li>                                                              
+
'''REQUIRED''' tables are:</p>
<li>SYS.FILE$</li>                                                            
+
 
<li>SYS.VW_X$KCCFN</li>                                                        
+
<blockquote><ul>
<li>SYS.VW_X$KTFTHC</li>                                                      
+
<li>All V$ tables and views</li>
<li>dba_data_files</li>                                                        
+
<li>SYS.X$KCCFN (not directly available to normal user)</li>
<li>dba_free_space</li>                                                        
+
<li>SYS.X$KTFTHC (not directly available to normal user)</li>
<li>dba_tablespaces</li>                                                      
+
<li>SYS.TS$</li>
<li>dba_temp_files</li></ul>                                                  
+
<li>SYS.FILE$</li>
</blockquote></li></ul>                                                        
+
<li>SYS.VW_X$KCCFN</li>
+
<li>SYS.VW_X$KTFTHC</li>
We recommend using the sample script to set this up:                          
+
<li>dba_data_files</li>
                                                                               
+
<li>dba_free_space</li>
<pre>/* Steps:                                                                
+
<li>dba_tablespaces</li>
  1: Log into oracle account. Make sure ORACLE_HOME and ORACLE_SID are set:    
+
<li>dba_temp_files</li></ul>
  2: ./sqlplus /nolog                                                          
+
</blockquote></li></ul>
  3: connect /as sysdba                                                        
+
 
  4: oracle_env.sh (for Oracle 10.X)                                           
+
We recommend using the following script to set this up:
*/                                                                            
+
 
                                                                               
+
<pre>/* -----------------------------------------------------------------------
drop user zenoss cascade;                                                      
+
Steps to create a secure Zenoss user for monitoring:
create user zenoss identified by zenoss;                                      
+
  1: Log into oracle account. Make sure ORACLE_HOME and ORACLE_SID are set:
grant connect,resource to zenoss;                                              
+
  2: oracle_env.sh (for Oracle 10.X)
                                                                               
+
3: ./sqlplus /nolog
DROP  ROLE SELECT_VDOLLAR_ROLE;                                               
+
  4: connect /as sysdba
CREATE ROLE SELECT_VDOLLAR_ROLE;                                               
+
  ----------------------------------------------------------------------- */
                                                                               
+
 
BEGIN                                                                         
+
drop user zenoss cascade;
  FOR x IN (SELECT object_name                                               
+
create user zenoss identified by zenoss;
            FROM dba_objects                                                 
+
grant connect to zenoss;
            WHERE object_type = 'VIEW'                                       
+
grant SELECT ANY DICTIONARY to zenoss;
            AND object_name LIKE 'V\_$%' ESCAPE '\' )                         
+
 
  LOOP                                                                       
+
create view VW_X$KCCFN as select * from SYS.X$KCCFN ;
      EXECUTE IMMEDIATE 'grant select on ' || x.object_name || ' to SELECT_VDOLLAR_ROLE';
+
create view VW_X$KTFTHC as select * from SYS.X$KTFTHC ;
  END LOOP;                                                                   
+
 
END;                                                                           
+
grant select on SYS.TS$ to zenoss ;
/                                                                             
+
grant select on SYS.FILE$ to zenoss ;
                                                                               
+
grant select on SYS.VW_X$KCCFN to zenoss ;
create view VW_X$KCCFN as select * from SYS.X$KCCFN ;                          
+
grant select on SYS.VW_X$KTFTHC to zenoss ;
create view VW_X$KTFTHC as select * from SYS.X$KTFTHC ;                        
+
grant select on dba_data_files  to zenoss ;
                                                                               
+
grant select on dba_free_space  to zenoss ;
grant select on SYS.TS$ to SELECT_VDOLLAR_ROLE ;                              
+
grant select on dba_tablespaces to zenoss ;
grant select on SYS.FILE$ to SELECT_VDOLLAR_ROLE ;                            
+
grant select on dba_temp_files  to zenoss ;
grant select on SYS.VW_X$KCCFN to SELECT_VDOLLAR_ROLE ;                        
+
commit;
grant select on SYS.VW_X$KTFTHC to SELECT_VDOLLAR_ROLE ;                      
+
/* ---------------------------------------------------------------------- */</pre>
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;</pre>                                    
+
 
=== Network Configuration ===
 
=== 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.
+
If your network has strong firewalls you may need to open up ports from your
=== Installing the Plugin ===                                                  
+
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
You can install the ZenPack egg via the GUI or manually.                      
+
differ with multiple instances which must each have unique TCP ports.
                                                                               
+
 
To manually install the ZenPack egg, take the following steps:                
+
=== Installing the Plugin ===
                                                                               
+
 
<pre>sudo su - zenoss                                                          
+
You can install the ZenPack egg via the GUI or manually.
zenpack --install ZenPacks.zenoss.DatabaseMonitor-*.egg                        
+
 
zenoss restart</pre>                                                          
+
To manually install the ZenPack egg, take the following steps:
where ZenPacks.zenoss.DatabaseMonitor-*.egg should match your egg version.    
+
 
                                                                               
+
<pre>sudo su - zenoss
=== Install the Base Server Device ===                                        
+
zenpack --install ZenPacks.zenoss.DatabaseMonitor-*.egg
                                                                               
+
zenoss restart</pre>
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.
+
where ZenPacks.zenoss.DatabaseMonitor-*.egg should match your egg version.
                                                                               
+
 
Now you are ready to bind the modeler:                                        
+
=== Install the Base Server Device ===
                                                                               
+
 
=== Bind the Plugin Modeler Template to Server ===                            
+
Install the base server as you normally would. Just select the type of server
                                                                               
+
that Oracle will run on (Linux, Solaris, Windows, etc..). The host server should
* From the Infrastructure page, select your server.                            
+
have it's base data collection services running as usual in order to monitor its
* From Modeler Plugins, push zenoss.ojdbc.Instances to the Selected group, Save.
+
vitals. Make sure to set zProperty '''zCommandCommandTimeout''' to 120.
* Now you are almost ready to model the device.                                
+
 
+
Now you are ready to bind the modeler:
=== Enable the TableSpace Templates ===                                        
+
 
                                                                               
+
=== Bind the Plugin Modeler Template to Server ===
Because of potential performance issues, Tablespace templates must be enabled manually. F>
+
 
                                                                               
+
* From the Infrastructure page, select your server.
To enable these templates:                                                    
+
* From ''Modeler Plugins'', move zenoss.ojdbc.Instances to the Selected group, Save.
                                                                               
+
* Now you are almost ready to model the device.
* Navigate to Advanced -&gt; Monitoring Templates                              
+
 
* Select OracleTableSpace                                                      
+
=== Enabled TableSpace Templates ===
* DoubleClick TableSpaceDU and click ''Enabled''                              
+
 
* Save                                                                        
+
Tablespace templates are enabled by default. If you have more than 100
* DoubleClick TableSpaceIO and click ''Enabled''                               
+
Tablespaces per instance you may see performance issues that can cause
* Save                                                                         
+
collection to fail. If that is the case, you can disable Tablespace monitoring
                               
+
by disabling those templates.
                                                                               
+
 
=== Set the zConnectionStrings Property ===                                    
+
To disable these templates:
                                                                               
+
 
* Select your server from the ''Infrastructures Tab''                          
+
* Navigate to Advanced -&gt; Monitoring Templates
* Click on ''Configuration Properties''                                        
+
* Select OracleTableSpace
* Search for ''zOracle'' and locate and select ''zOracleConnectionStrings''    
+
* DoubleClick dbmTBSP and uncheck ''Enabled''
** You may enter all the connection strings for Instances                      
+
* Save
** '''Don't''' fill in the singular ''zOracleConnectString''                  
+
 
                                                                               
+
=== 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 zOracleUser: Enter your username for the DB (''zenoss'' by default)
 
* Search for zOraclePassword: Enter your password 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:
 
                                                                               
 
<pre>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}</pre>
 
  
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.
+
These settings will allow you to protect your passwords if you set
                                                                               
+
'''${here/zOraclePassword}''' in the connection string as in the following
=== Model the Component ===                                                    
+
examples:
                                                                               
+
 
* From the device view, select '''Model Device''' from the gear menu.          
+
<pre>JoeNameth/${here/zOraclePassword}@db1.example.com:1521:XE
* If all goes will Zenoss should model the device.                            
+
${here/zOracleUser}/${here/zOraclePassword}@db1.example.com:1521:XE
* Since the Instances are just components of the server, you should see them hanging off of the device as components.
+
${here/zOracleUser}/${here/zOraclePassword}@${here/manageIp}:${here/zOraclePort}:${here/zOracleInstance}
                                                                               
+
${here/zOracleUser}/${here/zOraclePassword}@db1.example.com:1521/db7.zenoss.int</pre>
== Migration from Older Versions ==                                            
+
 
                                                                               
+
We '''STRONGLY''' recommend that you install the same read-only user in your
When migrating from the older version of DatabaseMonitor, you must:            
+
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
* Install the new version of the ZP                                            
+
to abuse.
* 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.
+
=== Model the Component ===
* Bind the Plugin Modeler as above                                            
+
 
* Model the Component as above                                                
+
* From the device view, select '''Model Device''' from the gear menu.
* Remove the old monitor Template from the device                              
+
* 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.
=== Moving Old RRD Data ===
+
 
 +
=== Unlocking Components from Deletion ===
 +
 
 +
This ZenPack automatically locks Instance components so that they are not
 +
removed when modeling fails to collect. This can happen for a variety of
 +
reasons including network failure, system downtime, and Oracle maintenance.
 +
 
 +
You can unlock your Instance components if you wish the modeler to
 +
remove deleted Instances as follows:
 +
 
 +
# Select your Instance
 +
# Select the gear menu
 +
# Select Locking...
 +
# Un-check "Lock from deletion"
 +
 
 +
== Migration from Older Versions ==
 +
 
 +
=== Migrating from 2.5.X ===
 +
 
 +
When migrating from the 2.5.X 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
 +
* There are no Tablespace files for 2.5.X to deal with conversion
 +
 
 +
=== Moving Old RRD Data from 2.5.X ===
  
 
You will have to manually move the old RRD data. In order to do so you must
 
You will have to manually move the old RRD data. In order to do so you must
Line 335: Line 414:
  
 
<pre>&lt;zenoss&gt;: cp -a $ZENHOME/perf/Devices/old/path/ $ZENHOME/.../oracle_instances/new/path/</pre>
 
<pre>&lt;zenoss&gt;: cp -a $ZENHOME/perf/Devices/old/path/ $ZENHOME/.../oracle_instances/new/path/</pre>
 +
 +
=== Moving Old RRD Data from 3.0.0 ===
 +
 +
We changed the Tablespaces datasource name in newer versions. We recommend that
 +
you delete the old files and start fresh.
 +
 +
If you want to migrate tablespace files, you will need to be change them
 +
accordingly. For example, in the containing folder:
 +
 +
<pre>mv TableSpaceDU_bytes_free.rrd  dbmTBSP_BYTES_FREE.rrd
 +
mv TableSpaceDU_bytes_max.rrd  dbmTBSP_MAXBYTES.rrd
 +
mv TableSpaceDU_bytes_used.rrd  dbmTBSP_BYTES_USED.rrd
 +
mv TableSpaceIO_reads.rrd  dbmTBSP_READS.rrd
 +
mv TableSpaceIO_readtime.rrd  dbmTBSP_READTIME.rrd
 +
mv TableSpaceIO_writes.rrd dbmTBSP_WRITES.rrd
 +
mv TableSpaceIO_writetime.rrd dbmTBSP_WRITETIME.rrd</pre>
 +
 
== DataPoints and DataSources ==
 
== DataPoints and DataSources ==
  
Line 366: Line 462:
 
</table>
 
</table>
  
where ''NAME'' is the datapoint identifier and ''VALUE'' is its numerical
+
where ''NAME'' is the datapoint identifier and ''VALUE'' is its numerical value.
value. For example, a valid query to find the number of theads for a particular
+
For example, a valid query to find the number of theads for a particular
 
instance would be:
 
instance would be:
  
 
<pre>&quot;select 'thread_count' as name, thread# value from v$instance&quot;</pre>
 
<pre>&quot;select 'thread_count' as name, thread# value from v$instance&quot;</pre>
There is one problem though: you need to double up on the ''dollar sign''
+
 
 +
There is one caveat: you need to double up on the ''dollar sign''
 
because the Zenoss TALES interpreter will try to interpret a single-dollar
 
because the Zenoss TALES interpreter will try to interpret a single-dollar
variable as an internal variable. The actual query must look like this:
+
variable as an internal variable. The double-dollar expression protects this
 +
literal dollar sign. The actual query must thus look like this:
  
 
<pre>&quot;select 'thread_count' as name, thread# value from v$$instance&quot;</pre>
 
<pre>&quot;select 'thread_count' as name, thread# value from v$$instance&quot;</pre>
Note: If your datapoint ''NAME'' has any special characters or spaces, they will be stripped out of the name key.
+
 
 +
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:
 
In order to add this query to the OracleInstance datasource, perform the following steps:
Line 386: Line 486:
 
** Select &quot;Oracle&quot; as the Type
 
** Select &quot;Oracle&quot; as the Type
 
** '''-Submit-'''
 
** '''-Submit-'''
 
 
* Now open the '''threads''' datasource by double-clicking it. Add the following to the form:
 
* Now open the '''threads''' datasource by double-clicking it. Add the following to the form:
 
** Name: threads
 
** Name: threads
Line 398: Line 497:
 
** '''-SAVE-'''
 
** '''-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.
  
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.
+
Next we will add this datapoint.
  
 
=== Adding a New 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 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:
 
We do the following:
Line 414: Line 517:
 
** Read Only: on
 
** Read Only: on
 
** '''-SAVE-'''
 
** '''-SAVE-'''
 
  
 
Now you can use the datapoint in a graph by referencing the name as
 
Now you can use the datapoint in a graph by referencing the name as
Line 421: Line 523:
 
assign it directly to a graph.
 
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'''.
+
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 ===
 
=== Adding New DataPoints to SYSSTAT DataSource ===
  
 
As mentioned before you can add a new datapoint to the existing '''sysstat'''
 
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 by simply adding the correct '''key''' identifier to the
datasource as outlined above. To get a current list of these indentifiers, you
+
'''sysstat''' datasource as outlined above. To get a current list of these
can consult the Oracle docuentation for ''Statistics Descriptions'' in the
+
identifiers, you can consult the Oracle documentation for ''Statistics
''Oracle Database Reference'' documentation:
+
Descriptions'' in the ''Oracle Database Reference'' documentation:
 +
 
 +
References:
 +
 
 +
* 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 ==
Line 442: Line 551:
  
 
We have bypassed this problem in java by passing
 
We have bypassed this problem in java by passing
'''-Djava.security.egd=file:///dev/urandom''' to the java runtime command. If
+
'''-Djava.security.egd=file:///dev/urandom''' to the Java runtime command. If
your number generator is fast enough you can re-enstate the original
+
your number generator is fast enough you can reinstate the original /dev/random
/dev/random by modifying $ZPHOME/lib/txojdbc.py in the _get_query_command().
+
by modifying $ZPHOME/lib/txojdbc.py in the _get_query_command().
  
 
=== Insufficient Processes on Oracle ===
 
=== 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:
+
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:
  
 
<pre>alter system set processes = 150 scope = spfile;</pre>
 
<pre>alter system set processes = 150 scope = spfile;</pre>
 +
 +
== Zenoss Analytics ==
 +
 +
This ZenPack provides additional support for Zenoss Analytics. Perform the
 +
following steps to install extra reporting resources into Zenoss Analytics
 +
after installing the ZenPack.
 +
 +
* Copy analytics-bundle.zip from $ZENHOME/ZenPacks/ZenPacks.zenoss.DatabaseMonitor*/ZenPacks/zenoss/DatabaseMonitor/analytics/ on your Zenoss server.
 +
* Navigate to Zenoss Analytics in your browser.
 +
* Login as superuser.
 +
* Remove any existing '''Oracle Database ZenPack''' folder.
 +
** Choose ''Repository'' from the ''View'' menu at the top of the page.
 +
** Expand ''Public'' in the list of folders.
 +
** Right-click on '''Oracle Database ZenPack''' folder and choose ''Delete''.
 +
** Confirm deletion by clicking ''OK''.
 +
* Add the new '''Oracle Database ZenPack''' folder.
 +
** Choose ''Server Settings'' from the ''Manage'' menu at the top of the page.
 +
** Choose ''Import'' in the left page.
 +
** Remove checks from all check boxes.
 +
** Click ''Choose File'' to import a data file.
 +
** Choose the analytics-bundle.zip file copied from your Zenoss server.
 +
** Click ''Import''.
 +
 +
You can now navigate back to the '''Oracle Database ZenPack''' folder in the
 +
repository to see the following resources added by the bundle.
 +
 +
=== Domains ===
 +
 +
* OracleDB
 +
 +
=== Ad Hoc Views ===
 +
 +
* Cache Hit Ratio
 +
* Free Disk Space
 +
* Physical Reads and Writes
 +
* Instance Messages
 +
 +
Domains can be used to create ad hoc views using the following steps.
 +
 +
# Choose ''Ad Hoc View'' from the ''Create'' menu.
 +
# Click ''Domains'' at the top of the data chooser dialog.
 +
# Expand ''Public'' then '''Oracle Database ZenPack'''.
 +
# Choose the ''OracleDB Domain'' domain.
 +
 +
== Changes ==
 +
 +
=== 3.0.8 ===
 +
* Fix possible "Permission denied" error after upgrading Zenoss 5 (ZEN-25575)
 +
 +
=== 3.0.7 ===
 +
* Fix bug in GNUMakefile
 +
 +
=== 3.0.6 ===
 +
 +
* Guard modeler against modeler errors (ZEN-24515)
 +
* Correct Analytics Bundle: (ZEN-24317)
 +
* Security fixes: Include full regex for mask_pass (ZEN-24634)
 +
 +
=== 3.0.5 ===
 +
 +
* Re-work Analytics for new metrics introduced in 3.0.4
 +
* Improve TBSP event handling (ZEN-24339)
 +
* Mask connectionString in errors (ZEN-21748)
 +
* Ensure dbmTBSP omits damaged TableSpaces (ZEN-23916)
 +
* Minor Graph Adjustments
 +
 +
=== 3.0.4 ===
 +
 +
* Added multi-file support and datasource for tablespaces
 +
* Added standard deviation for multi-file tablespaces
 +
* Reads and Writes  units changed from count/sec to count
 +
** As a result: Tablespace IO Operations Read/Write data are lost on upgrade
 +
 +
=== 3.0.3 ===
 +
 +
* Added Analytics Support
 +
* Added $ and # to valid ConnectionString passwords
 +
* Updated Thresholds
 +
 +
=== 3.0.2 ===
 +
 +
* Enhanced tablespace scalability to 100's of tablespaces (ZEN-11569)
 +
* Allow for c##name style usernames for RAC (ZEN-12387)
 +
* Enchanced security for the JDBC user
 +
 +
=== 3.0.1 ===
 +
 +
* Added Support for RAC-style service names in connectionStrings
 +
 +
=== 3.0.0 ===
 +
 +
* JDBC connectivity added
 +
* Added support for multiple instances
 +
* Added support for tablespaces
 
{{ZenPackFooter}}
 
{{ZenPackFooter}}

Latest revision as of 22:00, 16 November 2016


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.8- Download
Released on 2016/11/16
Compatible with Zenoss Resource Manager 4.2.x, Zenoss Resource Manager 5.0.x, Zenoss Resource Manager 5.1.x, Zenoss Resource Manager 5.x.x

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

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

Contents

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

ConnectionString Format

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

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

Example:

Example: joe/secretpassword@area51.zenoss.com:1521:XE
Example: joe/secretpassword@area51.zenoss.com:1521/service.zenoss.int
  • Usernames and passwords should conform to standard Oracle formats.

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.

Metrics

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.

Instance Metrics

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

Tablespace Metrics

  • dbmTBSP.BYTES_FREE: Free disk space
  • dbmTBSP.BYTES_USED: Used disk space
  • dbmTBSP.TOTAL_SIZE: Total disk Space

Tablespace File Metrics

These metrics are based on statistics over tablespace files. If more than one tablespace file exists, metrics are file averages, and standard deviations are calculated over file metrics.

  • dbmTBSPstats.READS: Total number of reads to db
  • dbmTBSPstats.READTIME: Read time average over files
  • dbmTBSPstats.WRITES: Total number of writes to db
  • dbmTBSPstats.WRITETIME: Write time average over files
  • dbmTBSPstats.READS_STDDEV: Standard Deviation on reads
  • dbmTBSPstats.READTIME_STDDEV: Standard Deviation on read-time
  • dbmTBSPstats.WRITES_STDDEV: Standard Deviation on writes
  • dbmTBSPstats.WRITETIME_STDDEV: Standard Deviation on write-time


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)
  • PGA: 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 at least:

  • 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 (bytes): Used, Free, and Total space allocated
  • IO Operations (ops): Reads, Writes, and their standard deviation across files
  • Time Per Request (sec): Read-time, write-time, and standard deviation across files

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
  • OracleTableSpaces: dbmTBSP

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 configure the database to create and allow access to specific tables for the Zenoss user. 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 REQUIRED tables are:

    • All V$ tables and views
    • SYS.X$KCCFN (not directly available to normal user)
    • SYS.X$KTFTHC (not directly available to normal user)
    • 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 following script to set this up:

/* -----------------------------------------------------------------------
 Steps to create a secure Zenoss user for monitoring:
 1: Log into oracle account. Make sure ORACLE_HOME and ORACLE_SID are set:
 2: oracle_env.sh (for Oracle 10.X)
 3: ./sqlplus /nolog
 4: connect /as sysdba
 ----------------------------------------------------------------------- */

 drop user zenoss cascade;
 create user zenoss identified by zenoss;
 grant connect to zenoss;
 grant SELECT ANY DICTIONARY to zenoss;

 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 zenoss ;
 grant select on SYS.FILE$ to zenoss ;
 grant select on SYS.VW_X$KCCFN to zenoss ;
 grant select on SYS.VW_X$KTFTHC to zenoss ;
 grant select on dba_data_files  to zenoss ;
 grant select on dba_free_space  to zenoss ;
 grant select on dba_tablespaces to zenoss ;
 grant select on dba_temp_files  to zenoss ;
 commit;
/* ---------------------------------------------------------------------- */

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..). The host server should have it's base data collection services running as usual in order to monitor its vitals. 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, move zenoss.ojdbc.Instances to the Selected group, Save.
  • Now you are almost ready to model the device.

Enabled TableSpace Templates

Tablespace templates are enabled by default. If you have more than 100 Tablespaces per instance you may see performance issues that can cause collection to fail. If that is the case, you can disable Tablespace monitoring by disabling those templates.

To disable these templates:

  • Navigate to Advanced -> Monitoring Templates
  • Select OracleTableSpace
  • DoubleClick dbmTBSP and uncheck 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:

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}
${here/zOracleUser}/${here/zOraclePassword}@db1.example.com:1521/db7.zenoss.int

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.

Unlocking Components from Deletion

This ZenPack automatically locks Instance components so that they are not removed when modeling fails to collect. This can happen for a variety of reasons including network failure, system downtime, and Oracle maintenance.

You can unlock your Instance components if you wish the modeler to remove deleted Instances as follows:

  1. Select your Instance
  2. Select the gear menu
  3. Select Locking...
  4. Un-check "Lock from deletion"

Migration from Older Versions

Migrating from 2.5.X

When migrating from the 2.5.X 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
  • There are no Tablespace files for 2.5.X to deal with conversion

Moving Old RRD Data from 2.5.X

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/

Moving Old RRD Data from 3.0.0

We changed the Tablespaces datasource name in newer versions. We recommend that you delete the old files and start fresh.

If you want to migrate tablespace files, you will need to be change them accordingly. For example, in the containing folder:

mv TableSpaceDU_bytes_free.rrd   dbmTBSP_BYTES_FREE.rrd
mv TableSpaceDU_bytes_max.rrd  dbmTBSP_MAXBYTES.rrd
mv TableSpaceDU_bytes_used.rrd  dbmTBSP_BYTES_USED.rrd
mv TableSpaceIO_reads.rrd   dbmTBSP_READS.rrd
mv TableSpaceIO_readtime.rrd  dbmTBSP_READTIME.rrd
mv TableSpaceIO_writes.rrd dbmTBSP_WRITES.rrd
mv TableSpaceIO_writetime.rrd dbmTBSP_WRITETIME.rrd

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:

NAME | VALUE
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 caveat: 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 double-dollar expression protects this literal dollar sign. The actual query must thus 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 identifiers, you can consult the Oracle documentation for Statistics Descriptions in the Oracle Database Reference documentation:

References:

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 reinstate 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;

Zenoss Analytics

This ZenPack provides additional support for Zenoss Analytics. Perform the following steps to install extra reporting resources into Zenoss Analytics after installing the ZenPack.

  • Copy analytics-bundle.zip from $ZENHOME/ZenPacks/ZenPacks.zenoss.DatabaseMonitor*/ZenPacks/zenoss/DatabaseMonitor/analytics/ on your Zenoss server.
  • Navigate to Zenoss Analytics in your browser.
  • Login as superuser.
  • Remove any existing Oracle Database ZenPack folder.
    • Choose Repository from the View menu at the top of the page.
    • Expand Public in the list of folders.
    • Right-click on Oracle Database ZenPack folder and choose Delete.
    • Confirm deletion by clicking OK.
  • Add the new Oracle Database ZenPack folder.
    • Choose Server Settings from the Manage menu at the top of the page.
    • Choose Import in the left page.
    • Remove checks from all check boxes.
    • Click Choose File to import a data file.
    • Choose the analytics-bundle.zip file copied from your Zenoss server.
    • Click Import.

You can now navigate back to the Oracle Database ZenPack folder in the repository to see the following resources added by the bundle.

Domains

  • OracleDB

Ad Hoc Views

  • Cache Hit Ratio
  • Free Disk Space
  • Physical Reads and Writes
  • Instance Messages

Domains can be used to create ad hoc views using the following steps.

  1. Choose Ad Hoc View from the Create menu.
  2. Click Domains at the top of the data chooser dialog.
  3. Expand Public then Oracle Database ZenPack.
  4. Choose the OracleDB Domain domain.

Changes

3.0.8

  • Fix possible "Permission denied" error after upgrading Zenoss 5 (ZEN-25575)

3.0.7

  • Fix bug in GNUMakefile

3.0.6

  • Guard modeler against modeler errors (ZEN-24515)
  • Correct Analytics Bundle: (ZEN-24317)
  • Security fixes: Include full regex for mask_pass (ZEN-24634)

3.0.5

  • Re-work Analytics for new metrics introduced in 3.0.4
  • Improve TBSP event handling (ZEN-24339)
  • Mask connectionString in errors (ZEN-21748)
  • Ensure dbmTBSP omits damaged TableSpaces (ZEN-23916)
  • Minor Graph Adjustments

3.0.4

  • Added multi-file support and datasource for tablespaces
  • Added standard deviation for multi-file tablespaces
  • Reads and Writes units changed from count/sec to count
    • As a result: Tablespace IO Operations Read/Write data are lost on upgrade

3.0.3

  • Added Analytics Support
  • Added $ and # to valid ConnectionString passwords
  • Updated Thresholds

3.0.2

  • Enhanced tablespace scalability to 100's of tablespaces (ZEN-11569)
  • Allow for c##name style usernames for RAC (ZEN-12387)
  • Enchanced security for the JDBC user

3.0.1

  • Added Support for RAC-style service names in connectionStrings

3.0.0

  • JDBC connectivity added
  • Added support for multiple instances
  • Added support for tablespaces

Installation

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


Discuss

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

blog comments powered by Disqus