Database Monitor

Version 3.1.4

Version 3.1.3

General:

Events:

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

Instances will be automatically discovered via a connection string that contains the 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.

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

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

Instance support includes the following graphs:

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

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

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:

Note: zOracleLinuxUser is the user on the Linux server under which the Oracle process is run.

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

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:

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

If your network has strong firewalls you may need to open up ports from your Virtana 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.

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 its 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:

Tablespace templates are enabled by default.

To disable these templates:

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.

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:

When migrating from the 2.5.X version of DatabaseMonitor, you must:

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:

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

Adding a 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, with the "|" character in between:

where NAME is the datapoint identifier and VALUE is its numerical value. For example, a valid query to find the number of threads 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 Virtana 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:

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.

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:

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.

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:

Ensure that:

Ensure that zOracleConnectionString Matches the Device IP.

Check whether manual connecting and querying works:

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().

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;

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

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

Domains can be used to create Ad Hoc views using the following steps.

3.1.4

3.1.3

3.1.2

3.1.1

3.1.0

3.0.12

3.0.11

3.0.10

3.0.9

3.0.8

3.0.7

3.0.6

3.0.5

3.0.4

3.0.3

3.0.2

3.0.1

3.0.0