Oracle Coherence Monitor User Guide

Oracle Coherence Monitor

User Guide

Database Configuration
This section describes how to replace the default HSQLDB database with another database that is accessible via JDBC or ODBC. While the HSQLDB database may be sufficient for evaluation and testing, it is not recommended for use in production deployments.

NOTE: DB2 database page size must be a minimum of 8k.

You configure the database using property values in conjunction with property filters (which ensure that properties are only used to configure the appropriate applications). For details about properties and property filters, see OCM Properties.

The OC Monitor requires a database that provides tables for the following information:

Alert Thresholds
Each OC Monitor instance requires a database table that contains alert thresholds and enable/disable flags. The values in this table are used by the alert engine at runtime. If this table is not available, alerts will not function.
Historical Data
If you use the OC Monitor Historian, it requires database tables in which to write data.

When using HSQLDB, the database capacity and space requirements described below should be applied to RAM for the system hosting HSQLDB because its tables are held in memory, not on disk.

The default Coherence monitoring configuration uses two database connections, one for alert threshold information (ALERTDEFS), and one for historical data storage via the Historian (RTVHISTORY). The default alert threshold table name is ALERTDEFS. The default history table names are OCM_CLUSTERSTATS, OCM_CACHETOTALS, OCM_STORAGETOTALS, OCM_CACHESERVICETOTALS, OCM_INVOCATIONSERVICETOTALS, OCM_NODETOTALS and OCM_NODESTATS. Substitutions are used for the database connection names and table names which are defined in the rtview.properties file, located in the ocmon/projects/mysample/ directory.

Important Note: The default Historian configuration saves metrics only for the above named tables.

JDBC
JDBC driver file for selected database.

Example: ojdbc14.jar

The location of the JDBC driver must be available on the JAVA classpath. The path to the driver can be added using the sl.rtview.cp property in the rtview.properties file.

JDBC access parameters such as user name, password, driver class and driver URL.

Example:

User Name: kermit
Password: frog

Driver: oracle.jdbc.driver.OracleDriver
URL: jdbc:oracle:thin:@dbhost:1521:rtv

collector.sl.rtview.sql.sqldbsqldb=ALERTDEFS kermit frog jdbc:oracle:thin:@myhost:1521:rtv oracle.jdbc.driver.OracleDriver - false false
collector.sl.rtview.sql.sqldbsqldb=RTVHISTORY kermit frog jdbc:oracle:thin:@myhost:1521:rtv oracle.jdbc.driver.OracleDriver - false false

Verify the following entries are appropriate for your environment:

collector.sl.rtview.sql.sqldb= ALERTDEFS …

collector.sl.rtview.sql.sqldb=RTVHISTORY …

NOTE: Examples are provided in the database.properties file, located in the rtvapm/common/dbconfig directory

ODBC
In order for RTView to communicate with your database using an ODBC-JDBC bridge, you must have an ODBC driver for your database. Most databases that run on Microsoft Windows come standard with an ODBC driver. You must also register your database with ODBC before accessing it from RTView. The name specified for the ODBC data source name during the ODBC driver setup must be RTVHISTORY for the history table connection and ALERTDEFS for the alert parameters table connection.

Database Capacity and Purging
The OC Monitor Historian writes average values to the database every 5 minutes by default.

At the end of each interval, the last 5 minutes of real time data in the Data Server for the Cluster Stats, CacheTotals, Storage Totals, Cache Service Totals, Invocation Service Totals, NodeStats and Node Totals is averaged and written to the Historian database. One CacheTotals row is written for each cache and tier combination, and one NodeStats row is written for each cluster node. Each column value is an average for the most recent compaction interval.

Database capacity for the CACHETOTALS and NODESTATS tables can be estimated with the following formula:

Bytes/day written to historian tables = ((A * 420) + (B * 154)) * 288
Where:
A = the number of cache/tier combinations.
(one cache with both front and back tiers counts as 2 for database capacity estimates)
B = the number of cluster nodes.

Table purging, archiving, displacement or additional compaction is not provided in the default Historian configuration. Optionally, you can provide procedures or scripts to keep the database within capacity limits. If licensed, the Advanced Historian features for table displacement can be used.

Defining Database Connections
sql template files are provided to assist you in creating the correct tables in the database of your choice. The sql templates for the alert tables are in the rtvapm\common\dbconfig directory. The sql templates for the history tables are in the rtvapm\ocmon\dbconfig directory.

The database connections (referred to as SQL connections) used by the OC Monitor are defined in the rtview.properties file, located in the project directory (mysample):

#
# Define the ALERTDEFS DB
#
# local ocmon DB
collector.sl.rtview.sql.sqldb=ALERTDEFS sa - jdbc:hsqldb:hsql://localhost:9103/alertdefs org.hsqldb.jdbcDriver - false true

##########################
# HISTORIAN PROPERTIES
#
# Define the RTVHISTORY DB
#
# Note that hsqldb can have poor performance with big amounts of data.
# We suggest to set an alternate database in production environments.
# To specify your database:
# - run the builder in a separate directory
# - select Tools/Options/SQL; enter your database options
# - select Save/No. The file OPTIONS.ini will be created
# - find the line containing your database options and copy it here:
#
collector.sl.rtview.sql.sqldb=RTVHISTORY sa - jdbc:hsqldb:hsql://localhost:9103/rtvhistory org.hsqldb.jdbcDriver - false true
#
historian.sl.rtview.historian.driver=org.hsqldb.jdbcDriver
historian.sl.rtview.historian.url=jdbc:hsqldb:hsql://localhost:9103/rtvhistory
historian.sl.rtview.historian.username=sa
historian.sl.rtview.historian.password=

Modifying Database Connections
If you replace HSQLDB with another database, the database connections in the rtview.properties file must be modified for the database. Examples are provided in the database.properties file, located in the rtvapm/common/dbconfig directory. Use the appropriate commented out properties in the database.properties file to replace the default HSQLDB entries in the rtview.properties file.

Also, keep the same propfilter value (for example, maincollector, collector, historian) in the rtview.properties file when you modify database connection definitions.

Encrypting Passwords
If you need to provide encrypted passwords (rather than expose server password names in a clear text file), do the following. If not, skip this step.

To provide encrypted passwords, use the “encode_string” utility:

In an initialized command window, execute the following script where mypassword is your plain text password (this password will be encrypted).

encode_string sql mypassword

You then receive an encrypted password for copying and pasting into the password field. For example:

encrypted value: 013430135501346013310134901353013450134801334

Defining Database Tables
sql template files are provided to assist you in creating the correct tables in the database of your choice. The sql templates for the alert tables are in the rtvapm\common\dbconfig directory. The sql templates for the history tables are in the rtvapm\ocmon\dbconfig directory. The standard SQL syntax is provided for each database, but requirements can vary depending on database configuration. Consult with your database administrator, or contact SL Technical Support, if you require assistance.

To define the database tables using the provided template files, perform the following steps.

1. In the dbconfig directory, open the createtables_<tabletype>_<database>.sql file, where <database> corresponds to your type of database (for example, create_ocmon_history_tables_mysql.sql).

Make the following edits as they apply to your database:

myinstance: Replace with the database instance where you wish to create the table.
mydbuser: Replace with the database user whose instance the tables will be created under.

2. Use the edited .sql file to create the database tables. The effective method for doing this will vary depending on your database and how that database is configured. Some possible mechanisms are:

Interactive SQL Tool - Some database applications provide an interface where you can directly type SQL commands. Copy/paste the contents of the appropriate createtables_<tabletype>_<database>.sql file into this tool.
Import Interface - Some database applications allow you to specify a .sql file containing SQL commands. You can use the createtables_<tabletype>_<database>.sql file for this purpose. In some cases it may be necessary to save the statements in that file into individual files.
Third Party Application - Third party tools are available for connecting to a variety of databases (RazorSQL, SQLMaestro, Toad, etc). If your database does not have either of the two above capabilities, a third party tool can be used to enter SQL commands or import .sql files.

3. Configure the OC Monitor to use the database tables you just created by editing the table names in your rtview.properties file, located in the ocmon\projects\mysample directory. You can use the default table names or use your own table names. The following is an example from a sample rtview.properties file:

#
# Default OCM Cache History Tables
#
sl.rtview.sub=$OCMCLUSTERSTATS_TABLE:OCM_CLUSTER TATS
sl.rtview.sub=$OCMCACHETOTALS_TABLE:OCM_CACHETOTALS
sl.rtview.sub=$OCMSTORAGETOTALS_TABLE:OCM_STORAGETOTALS
sl.rtview.sub=$OCMCACHESERVICETOTALS_TABLE:OCM_CACHESERVICETOTALS
sl.rtview.sub=$OCMINVOCATIONSERVICETOTALS_TABLE:OCM_INVOCATIONSERVICETOTALS
sl.rtview.sub=$OCMNODETOTALS_TABLE:OCM_NODETOTALS
sl.rtview.sub=$OCMNODESTATS_TABLE:OCM_NODESTATS

4. Save the file.

RTView contains components licensed under the Apache License Version 2.0.

JMS, JMX and Java are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. They are mentioned in this document for identification purposes only.

SL, SL-GMS, GMS, RTView, SL Corporation, and the SL logo are trademarks or registered trademarks of Sherrill-Lubinski Corporation in the United States and other countries. Copyright © 1998-2013 Sherrill-Lubinski Corporation. All Rights Reserved.

	To provide encrypted passwords, use the “encode_string” utility:

	In an initialized command window, execute the following script where *mypassword* is your plain text password (this password will be encrypted).
		encode_string sql mypassword
	You then receive an encrypted password for copying and pasting into the password field. For example:
		encrypted value: 013430135501346013310134901353013450134801334