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

From Zenoss Wiki
Jump to: navigation, search
Line 4: Line 4:
 
|ZenPack name=ZenPacks.zenoss.DatabaseMonitor
 
|ZenPack name=ZenPacks.zenoss.DatabaseMonitor
 
|Releases={{Release
 
|Releases={{Release
|Version=3.0.1
+
|Version=3.0.2
|Release date=2014/05/19
+
|Release date=2014/07/16
 
|Compatible with=Zenoss Resource Manager 4.1.x, Zenoss Resource Manager 4.2.x
 
|Compatible with=Zenoss Resource Manager 4.1.x, Zenoss Resource Manager 4.2.x
 
|Download URL=https://support.zenoss.com/ics/support/DLRedirect.asp?fileID=80213
 
|Download URL=https://support.zenoss.com/ics/support/DLRedirect.asp?fileID=80213
Line 17: Line 17:
 
|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
 +
be associated with a single server.
 
                                                                                  
 
                                                                                  
 
== Prerequisites ==
 
== Prerequisites ==
Line 54: Line 56:
 
=== ConnectionString Format ===
 
=== ConnectionString Format ===
  
The Connection String is the data format that is used to specify a database. The connection string 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:
  
 
<pre>&lt;Username&gt;/&lt;Password&gt;@&lt;fqdn.domain.com&gt;:&lt;port&gt;:&lt;SID&gt;
 
<pre>&lt;Username&gt;/&lt;Password&gt;@&lt;fqdn.domain.com&gt;:&lt;port&gt;:&lt;SID&gt;
 
                       - or -
 
                       - or -
&lt;Username&gt;/&lt;Password&gt;@&lt;fqdn.domain.com&gt;:&lt;port&gt;/&lt;SERVICE_ID&gt;</pre>
+
&lt;Username&gt;/&lt;Password&gt;@&lt;fqdn.domain.com&gt;:&lt;port&gt;/&lt;SERVICE_NAME&gt;</pre>
Examples:
+
Example:
  
<pre>joe/secretpassword@area51.zenoss.com:1521:XE
+
<pre>Example: joe/secretpassword@area51.zenoss.com:1521:XE
joe/secretpassword@area51.zenoss.com:1521/service.zenoss.int</pre>
+
Example: joe/secretpassword@area51.zenoss.com:1521/service.zenoss.int</pre>
             
+
=== Discovery and Migration ===
=== 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 ===                                                
+
Instances will be automatically discovered via connection string which contains
                                                                               
+
username, password, port, and instance-name you provide. If starting from a new
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.
+
server without any OracleDB monitoring, simply bind the Oracle template from the
                                                                               
+
gear menu on the bottom left of the server's infrastructure page.
* logonscumulative: Total number of logons                                     
+
 
* logonscurrent: Total number of current logons                                
+
Migration is done by binding the new Oracle template to the existing server and
* messagesreceived: Messages received in last interval                        
+
executing &quot;Model Device&quot; from the gear menu on the inventory page. Do
* messagessent: Total number of messages sent                                  
+
not remove the older template until the new template is in place or the
* openedcursorscumulative: Total number of opened cursors since instance has started
+
migration will lack the required information. Once you have verified that the
* openedcursorscurrent: Total number of current cursors open                  
+
new data model is correct you should delete the older template in the Advanced
* physicalreads: Physical reads per second                                    
+
area of the GUI.
* physicalwrites: Physical writes per second                                  
+
 
* redoentries: Redo entry number since instance has started                    
+
=== Metrics ===
* redosize: Total amount of redo generated in bytes                            
+
 
* redowrites: Total number of writes by LGWR to the redo logs                  
+
Host monitoring is separately done by the server monitoring template. This
* sortsdisk: Number of sort operations that required at least one disk write  
+
zenpack is applies a component level addition to the host system. The following
* sortsmemory: Number of sort operations that were performed completely in memory
+
Oracle related metrics will be collected every 5 minutes by default.
* sortsrows: Total number of rows sorted                                       
+
 
* usercalls: Number of user calls such as login, parse, fetch, or execute      
+
==== Instance Metrics ====
* 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
+
* sysstat.cache_hit_ratio: Cache Hit Ratio
* userrollbacks: Number of times users manually issue the ROLLBACK statement  
+
* sysstat.logonscumulative: Cumulative Logons
                                                                               
+
* sysstat.logonscurrent: Total number of current logons
=== Instance Support ===                                                      
+
* sysstat.messagesreceived: Messages received in last interval
                                                                               
+
* sysstat.messagessent: Total number of messages sent
The new instance support allows multiple instances per server to be monitored. The ''Context Display''     area has the following:
+
* sysstat.openedcursorscumulative: Total number of opened cursors since instance has started
                                                                               
+
* sysstat.openedcursorscurrent: Total number of current cursors open
* Graphs                                                                      
+
* sysstat.physicalreads: Physical reads per second
* Events                                                                      
+
* sysstat.physicalwrites: Physical writes per second
* Details                                                                      
+
* sysstat.redoentries: Redo entry number since instance has started
* Templates                                                                    
+
* sysstat.redosize: Total amount of redo generated in bytes
* TableSpaces                                                                  
+
* sysstat.redowrites: Total number of writes by LGWR to the redo logs
                                                                               
+
* sysstat.sessionpgamemory: Session PGA Memory in use
==== Instance Graphs ====                                                      
+
* sysstat.sessionpgamemorymax: Maximum available PGA Memory
                                                                               
+
* sysstat.sga_free: SGA Free Memory
Instance support includes the following graphs:                                
+
* sysstat.sga_total: SGA Total Memory
                                                                               
+
* sysstat.sga_used: SGA Used memory
* SGA: Total, Free, Used (bytes)                                              
+
* sysstat.sortsdisk: Number of sort operations that required at least one disk write
* Cache Hit Ratio (%)                                                          
+
* sysstat.sortsmemory: Number of sort operations that were performed completely in memory
* User IO Wait Time (wait/sec)                                                
+
* sysstat.sortsrows: Number of sort operations that were performed completely in memory
* User Operations: Calls, Commits, Rollbacks (ops/sec)                        
+
* sysstat.usercalls: Number of user calls such as login, parse, fetch, or execute
* Sorts: Disk, Memory, Rows (sorts/sec)                                        
+
* sysstat.usercommits: Number of user commits; the closest thing to a user transaction rate
* Redo Operations: Entries, Writes (ops/sec)                                  
+
* sysstat.userIOwaittime: Total wait time (microseconds) belonging to User I/O wait class
* Redo Size (bytes)                                                            
+
* sysstat.userrollbacks: Number of times users manually issue the ROLLBACK statement
* Physical Operations: Reads, Writes (ops/sec)                                
+
 
* Messages: Sent, Received (messages/sec)                                      
+
==== Tablespace Metrics ====
* Logins                                                                      
+
 
* Opened Cursors: Current (#), Per Second (opens/sec)                          
+
* dbmTBSP.BYTES_FREE: Free disk space
                                                                               
+
* dbmTBSP.BYTES_USED: Used disk space
=== Table Space Support ===                                                    
+
* dbmTBSP.READS: Total number of reads to db
                                                                               
+
* dbmTBSP.READTIME: Averate time per read access
Oracle TableSpace has been added. The TableSpace grid has links to the parent Instance. The ''Context     Display'' area has the following:
+
* dbmTBSP.TOTAL_SIZE: Total disk Space
                                                                               
+
* dbmTBSP.WRITES: Total number of writes to db
* Graphs                                                                      
+
* dbmTBSP.WRITETIME: Averate time per write access
* Events                                                                      
+
 
* Details                                                                      
+
=== Instance Support ===
* TableSpaces                                                                  
+
 
                                                                               
+
The new instance support allows multiple instances per server to be monitored.
For each instance, there will be a set of TableSpaces as defined on the target system. This usually       includes:
+
The ''Context Display'' area has the following:
                                                                               
+
 
* SYSAUX                                                                      
+
* Graphs
* SYSTEM                                                                      
+
* Events
* TEMP                                                                        
+
* Details
* UNDO                                                                        
+
* 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:
 +
 
 +
* 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: The '''Used''', '''Free''', and '''Total''' space currently allocated.
* IO Operations                                                                
+
* IO Operations
* Time Per Request                                                            
+
* Time Per Request
  
 
== 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>                                                                          
+
<ul>
<li><p>Make sure Oracle server is listening on the correct TCP port (usually 1521)</p></li>
+
<li>Make sure Oracle server is listening on the correct TCP port (usually 1521)</li>
<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><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>
<blockquote><ul>                                                              
+
<blockquote><ul>
<li>All V$ tables and views</li>                                              
+
<li>All V$ tables and views</li>
<li>SYS.X$KCCFN</li>                                                          
+
<li>SYS.X$KCCFN (not directly available to normal user)</li>
<li>SYS.X$KTFTHC</li>                                                          
+
<li>SYS.X$KTFTHC (not directly available to normal user)</li>
<li>SYS.TS$</li>                                                              
+
<li>SYS.TS$</li>
<li>SYS.FILE$</li>                                                            
+
<li>SYS.FILE$</li>
<li>SYS.VW_X$KCCFN</li>                                                        
+
<li>SYS.VW_X$KCCFN</li>
<li>SYS.VW_X$KTFTHC</li>                                                      
+
<li>SYS.VW_X$KTFTHC</li>
<li>dba_data_files</li>                                                        
+
<li>dba_data_files</li>
<li>dba_free_space</li>                                                        
+
<li>dba_free_space</li>
<li>dba_tablespaces</li>                                                      
+
<li>dba_tablespaces</li>
<li>dba_temp_files</li></ul>                                                  
+
<li>dba_temp_files</li></ul>
</blockquote></li></ul>                                                        
+
</blockquote></li></ul>
+
 
We recommend using the sample script to set this up:                          
+
We recommend using the sample script to set this up:
                                                                               
+
 
<pre>/* Steps:                                                                
+
<pre>/* -----------------------------------------------------------------------
  1: Log into oracle account. Make sure ORACLE_HOME and ORACLE_SID are set:    
+
Steps to create a secure Zenoss user for monitoring:
  2: ./sqlplus /nolog                                                          
+
  1: Log into oracle account. Make sure ORACLE_HOME and ORACLE_SID are set:
  3: connect /as sysdba                                                        
+
  2: oracle_env.sh (for Oracle 10.X)
4: oracle_env.sh (for Oracle 10.X)                                           
+
3: ./sqlplus /nolog
*/                                                                            
+
  4: connect /as sysdba
                                                                               
+
*/
drop user zenoss cascade;                                                      
+
 
create user zenoss identified by zenoss;                                      
+
drop user zenoss cascade;
grant connect,resource to zenoss;                                              
+
create user zenoss identified by zenoss;
                                                                               
+
grant connect,resource to zenoss;
DROP  ROLE SELECT_VDOLLAR_ROLE;                                              
+
 
CREATE ROLE SELECT_VDOLLAR_ROLE;                                              
+
DROP  ROLE SELECT_VDOLLAR_ROLE;
                                                                               
+
CREATE ROLE SELECT_VDOLLAR_ROLE;
BEGIN                                                                          
+
 
   FOR x IN (SELECT object_name                                                
+
BEGIN
             FROM dba_objects                                                  
+
   FOR x IN (SELECT object_name
             WHERE object_type = 'VIEW'                                        
+
             FROM dba_objects
             AND object_name LIKE 'V\_$%' ESCAPE '\' )                        
+
             WHERE object_type = 'VIEW'
   LOOP                                                                        
+
             AND object_name LIKE 'V\_$%' ESCAPE '\' )
 +
   LOOP
 
       EXECUTE IMMEDIATE 'grant select on ' || x.object_name || ' to SELECT_VDOLLAR_ROLE';
 
       EXECUTE IMMEDIATE 'grant select on ' || x.object_name || ' to SELECT_VDOLLAR_ROLE';
   END LOOP;                                                                  
+
   END LOOP;
END;                                                                          
+
END;
/                                                                              
+
/
                                                                               
+
 
create view VW_X$KCCFN as select * from SYS.X$KCCFN ;                          
+
create view VW_X$KCCFN as select * from SYS.X$KCCFN ;
create view VW_X$KTFTHC as select * from SYS.X$KTFTHC ;                        
+
create view VW_X$KTFTHC as select * from SYS.X$KTFTHC ;
                                                                               
+
 
grant select on SYS.TS$ to SELECT_VDOLLAR_ROLE ;                              
+
grant select on SYS.TS$ to SELECT_VDOLLAR_ROLE ;
grant select on SYS.FILE$ 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$KCCFN to SELECT_VDOLLAR_ROLE ;
grant select on SYS.VW_X$KTFTHC 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_data_files  to SELECT_VDOLLAR_ROLE ;
grant select on dba_free_space  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_tablespaces to SELECT_VDOLLAR_ROLE ;
grant select on dba_temp_files  to SELECT_VDOLLAR_ROLE ;                      
+
grant select on dba_temp_files  to SELECT_VDOLLAR_ROLE ;
                                                                               
+
grant SELECT_VDOLLAR_ROLE to zenoss;
grant SELECT_VDOLLAR_ROLE to zenoss;</pre>                                       
+
/* ---------------------------------------------------------------------- */</pre>
 +
Here is a more ''secure'' script uses no ''resource'' rights:
 +
 
 +
<pre>/* -----------------------------------------------------------------------
 +
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; 
 +
/* ---------------------------------------------------------------------- */</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:                 
+
                                                                               
+
<pre>sudo su - zenoss                                                         
+
zenpack --install ZenPacks.zenoss.DatabaseMonitor-*.egg                       
+
zenoss restart</pre>                                                           
+
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  
+
=== Installing the Plugin ===
default. They must be enabled manually. For instances with less that 12       
+
 
TableSpaces, enabling this should be safe. Please test your environment in     
+
You can install the ZenPack egg via the GUI or manually.
advance to determine if this will affect your system load.
+
 
                                                                               
+
To manually install the ZenPack egg, take the following steps:
To enable these templates:                                                    
+
 
                                                                               
+
<pre>sudo su - zenoss
* Navigate to Advanced -&gt; Monitoring Templates                              
+
zenpack --install ZenPacks.zenoss.DatabaseMonitor-*.egg
* Select OracleTableSpace                                                      
+
zenoss restart</pre>
* DoubleClick TableSpaceDU and click ''Enabled''                              
+
where ZenPacks.zenoss.DatabaseMonitor-*.egg should match your egg version.
* Save                                                                        
+
 
* DoubleClick TableSpaceIO and click ''Enabled''                               
+
=== Install the Base Server Device ===
* Save                                                                         
+
 
                               
+
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
=== Set the zConnectionStrings Property ===                                    
+
have it's base data collection services running as usual in order to monitor its
                                                                               
+
vitals. Make sure to set zProperty '''zCommandCommandTimeout''' to 120.
* Select your server from the ''Infrastructures Tab''                          
+
 
* Click on ''Configuration Properties''                                        
+
Now you are ready to bind the modeler:
* Search for ''zOracle'' and locate and select ''zOracleConnectionStrings''    
+
 
** You may enter all the connection strings for Instances                      
+
=== Bind the Plugin Modeler Template to Server ===
** '''Don't''' fill in the singular ''zOracleConnectString''                  
+
 
                                                                               
+
* 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 -&gt; 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 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:
+
These settings will allow you to protect your passwords if you set
                                                                               
+
'''${here/zOraclePassword}''' in the connection string as in the following
<pre>JoeNameth/${here/zOraclePassword}@db1.example.com:1521:XE                
+
examples:
${here/zOracleUser}/${here/zOraclePassword}@db1.example.com:1521:XE            
+
 
 +
<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}
 
${here/zOracleUser}/${here/zOraclePassword}@${here/manageIp}:${here/zOraclePort}:${here/zOracleInstance}
 
${here/zOracleUser}/${here/zOraclePassword}@db1.example.com:1521/db7.zenoss.int</pre>
 
${here/zOracleUser}/${here/zOraclePassword}@db1.example.com:1521/db7.zenoss.int</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.
+
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
=== Model the Component ===                                                    
+
instances. A read-only DB user will also enhance your security and be less prone
                                                                               
+
to abuse.
* From the device view, select '''Model Device''' from the gear menu.          
+
 
* If all goes will Zenoss should model the device.                            
+
=== Model the Component ===
* Since the Instances are just components of the server, you should see them hanging off of the device as components.
+
 
                                                                               
+
* From the device view, select '''Model Device''' from the gear menu.
== Migration from Older Versions ==                                            
+
* 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.
When migrating from the older version of DatabaseMonitor, you must:            
+
 
                                                                               
+
== Migration from Older Versions ==
* Install the new version of the ZP                                            
+
 
* Restart Zenoss Services like Zenhub                                          
+
When migrating from the older version of DatabaseMonitor, you must:
* 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                                            
+
* Install the new version of the ZP
* Model the Component as above                                                
+
* Restart Zenoss Services like Zenhub
* Remove the old monitor Template from the device                              
+
* 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 ===
 
=== Moving Old RRD Data ===
  
Line 337: Line 408:
  
 
<pre>&lt;zenoss&gt;: find $ZENHOME/perf/Devices -name &quot;sysstat_usercalls.rrd&quot;</pre>
 
<pre>&lt;zenoss&gt;: find $ZENHOME/perf/Devices -name &quot;sysstat_usercalls.rrd&quot;</pre>
Copy down that folder location. The new location will have &quot;oracle_instances&quot; in the path.
+
 
 +
Copy down that folder location. The new location will have
 +
&quot;oracle_instances&quot; in the path.
  
 
# Copy the RRD files from the old to the new locations:
 
# Copy the RRD files from the old to the new locations:
  
 
<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>
 +
 
== DataPoints and DataSources ==
 
== DataPoints and DataSources ==
  
Line 350: Line 424:
 
query return data in the following two column format:
 
query return data in the following two column format:
  
<table>
+
{|
<tr class="header">
+
!width="13%"|NAME
<th align="left">NAME</th>
+
!width="6%"||
<th align="left">|</th>
+
!width="12%"|VALUE
<th align="left">VALUE</th>
+
|-
</tr>
+
|name1
<tr class="odd">
+
||
<td align="left">name1</td>
+
|value1
<td align="left">|</td>
+
|-
<td align="left">value1</td>
+
|name2
</tr>
+
||
<tr class="even">
+
|value1
<td align="left">name2</td>
+
|-
<td align="left">|</td>
+
|..etc..
<td align="left">value1</td>
+
||
</tr>
+
|..etc..
<tr class="odd">
+
|}
<td align="left">..etc..</td>
+
<td align="left">|</td>
+
<td align="left">..etc..</td>
+
</tr>
+
</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 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 double-dollar expression protects this literal dollar sign. The actual query must thus look like this:
because the Zenoss TALES interpreter will try to interpret a single-dollar
+
variable as an internal variable. The actual query must 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 393: Line 462:
 
** 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 405: Line 473:
 
** '''-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 421: Line 493:
 
** 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 428: Line 499:
 
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/E16655_01/server.121/e17615/stats002.htm
Line 453: Line 527:
  
 
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>
 
{{ZenPackFooter}}
 
{{ZenPackFooter}}

Revision as of 14:52, 16 July 2014

Organization
Zenoss, Inc.
License
GNU General Public License, Version 2, or later
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 is an Open Source ZenPack developed by Zenoss, Inc. Enterprise support for this ZenPack is available to commercial customers with an active subscription.

Releases

Version 3.0.2- Download
Released on 2014/07/16
Compatible with Zenoss Resource Manager 4.1.x, Zenoss Resource Manager 4.2.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.

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

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.READS: Total number of reads to db
  • dbmTBSP.READTIME: Averate time per read access
  • dbmTBSP.TOTAL_SIZE: Total disk Space
  • dbmTBSP.WRITES: Total number of writes to db
  • dbmTBSP.WRITETIME: Averate time per write access

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:

  • 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, Free, and Total space currently allocated.
  • 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
  • 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 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 (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 sample 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,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;
/* ---------------------------------------------------------------------- */

Here is a more secure script uses no resource rights:

/* ----------------------------------------------------------------------- 
 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.

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:

NAME width="6%" 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 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 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;

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