Deploying Oracle Database Solution Templates

The SiteScope Oracle Database Solution facilitates the implementation of best-practice monitoring of Oracle Databases with a minimum of configuration. This solution can be used with Oracle 8i, 9i, and 10g databases.

You use this solution template to deploy a set of monitors that test the health, availability, and performance of an Oracle database. The deployed monitors will check general system statistics, such as cache hit ratios and disk I/O, and include tools that provide diagnostic information about important aspects of the database. An in depth description of the Oracle Database Solution is available in SiteScope_Oracle_Best_Practices.pdf. (Note: This is a password protected document. The password is provided along with the Oracle Database Solution license key from Mercury.)

Usage Guidelines

Use the Oracle Database Solution to monitor statistics from Oracle 8i, 9i, 10g databases. Important system metrics are computed with data retrieved from system tables in the Oracle database. A wide range of Oracle system tables such as V$SYSSTAT, V$LATCH, V$ROLL_STAT, and V$BUFFER_POOL_STATISTICS are consulted to produce these metrics. In this way, the Oracle Database Solution implements the equivalent of many of the system monitoring scripts that come bundled with the Oracle installation.

Before configuring the Oracle Database Solution for deployment, you can consult the documentation for the Database Counter Monitor and the Log File Monitor for information about some of the prerequisites and parameters required by the solution template. For example, you will find more information on installing the Oracle JDBC driver needed to communicate with the database and the format of the log file path name parameter.

Completing the Oracle Database Solution Form

To display the Oracle Database Solution Template Form, click the add a Solution Template link on a group's detail page and click the Oracle Database Solution link.

Complete the items on the Oracle Solution Form as follows. When the required items are complete, click the Submit button.

Database Connection URL:

Enter the connection URL to the database you want to monitor. For example, jdbc:oracle:thin:@192.168.0.50:1521:ORCL.

Database User Name

Enter the user name that SiteScope should use to connect to the database.

Database Password

Enter the password for the user name that SiteScope should use to connect to the database.

Monitor Frequency

The frequency in seconds at which you'd like the deployed monitors to run. It is generally a good idea to enter a conservative frequency (10 minutes or greater) when first deploying the solution because some of the SQL queries invoked by the deployed monitors could impose some overhead on the database. Once the monitors are deployed, you will be able to edit the monitors individually and increase the frequencies on specific monitors, if necessary.

Connection Timeout

Enter a timeout value, in seconds, that the monitor should wait for a database connection.

Note:The sum of the Connection Timeout value and Query Timeout value should always be less than the Update every value for the monitor.

Query Timeout

Enter a timeout value, in seconds, that the monitor should wait for a database query to return results.

Note: The sum of the Connection Timeout value and Query Timeout value should always be less than the Update every value for the monitor.

Server

Choose the server you want to monitor. Click the choose server link to open the server selection screen. Select a server from the drop-down menu or enter an UNC path for the server you want to monitor.

Oracle Alert Log Path

Enter the full path to the Oracle alert log. Consult your database administrator or the Oracle documentation for information on how to access this file.

Oracle Listener Log Path:

Enter the full path to the Oracle listener log. Consult your database administrator or the Oracle documentation for information on how to access this file.

Verifying Monitor Values

Each monitor in the Solution template is now displayed in a table. The monitor type along with monitor name is displayed above the table. The first column shows the monitor's property name. The next column shows the value for that property. Values that contained variables are shaded with green. Values that are not affected by variables show no shading. The final column is for displaying errors that SiteScope has found while verifying the values. Errors are shaded in red. Errors should be corrected before proceeding to the next step, but SiteScope does not require it, however doing so may create monitors that do not function properly.

The following is an example section from the Property and Syntax Check page for a Oracle Database Solution Template.

Property and Syntax Check for Oracle Database Solution

Monitor Type: DatabaseCounterMonitor Monitor Name: Oracle Counters: Library Cache Latch Statistics (Per-Second)

Property	Value	Property or Syntax Errors
driver	oracle.jdbc.driver.OracleDriver
connectTimeout	600
user	system
frequency	900
server	jdbc:oracle:thin:@sunqa1:1521:qa920
Metric 1	library cache/MISSES
Metric 2	library cache/IMMEDIATE_MISSES
Metric id1	22 library cache\|\|\|MISSES
query	SELECT NAME, GETS, MISSES, IMMEDIATE_GETS, IMMEDIATE_MISSES FROM V$LATCH WHERE NAME LIKE 'library cache%'
name	Oracle Counters: Library Cache Latch Statistics (Per-Second)
Metric id2	32 library cache\|\|\|IMMEDIATE_MISSES
queryTimeout	600

Once all the monitor values are acceptable, then select the Create Solution button to create the monitors.

Adding Monitors Based on the Solution Template

As SiteScope creates the monitors, the monitor type and name are displayed along with messages of any errors found. A "success" message is shown if the monitors are created successfully. The process does not run the monitor.

After they have been created, you can select the Return link to return the group detail page to view the status of the monitors.

Note: Errors detected during the creation of monitors using a solution template are independent of the status returned when the individual monitors are run. This means that the monitors may be created successfully but that the configuration settings may be incorrect or that the system being monitored is unavailable.

Note: Solution Templates do not configure any automated alerts for the monitors created. You must create and associate one or more alert definitions to the monitors or monitor groups created by solution templates.

Oracle Solution Template Tools

The Oracle Solution Template deploys several tools that you can use to gather diagnostic information about an Oracle database. These tools are deployed to the same group as the monitors that are deployed by the solution template. They are displayed in much the same way as monitors but they are set as disabled. These tools are identified by the bold text Solution Tool in the Status field of the group detail table. See the section on About Oracle Database Solution Tools for more information.

When the user clicks on one of these Solution Tools, SiteScope will make a custom SQL query to the database via the Database Connection Test tool. The results of the query are found in a table at the bottom of the page. From this page, the tool may be run as many times as necessary by clicking the Connect and Execute Query button. Bear in mind that some tools may incur substantial overhead on the database, so executing them in quick succession may not be a good idea.