Oracle Database Monitoring Extension

Use Case

The Oracle Database is an object-relational database management system.

The Oracle Database monitoring extension captures performance metrics from Oracle databases
(version 10g and above) and displays them in AppDynamics. The extension comes with a standard set of queries
that gather metrics based on the following categories.

• Activity: Throughput over the last minute, such as transactions, SQL executes, I/O reads and writes, average active sessions, etc.
• Resource Utilization: What database resources are currently in use (sessions, open cursors, shared pool, etc).
• Efficiency: Ratios and response times as indicators of the instance's efficiency.

This extension also allows you to query your Oracle Database and view those results on the AppDynamics Metric browser
and hence allowing you to build dashboards and set Health Rules based on the output of the extension.

Prerequisites

Before the extension is installed, the prerequisites mentioned here need to be met. Please do not proceed with the extension installation if the specified prerequisites are not met.

This is very essential in order to establish a connection with the Oracle DB to get the metrics.
The extension needs to be able to connect to Oracle DB in order to collect and send metrics.
To do this, you will have to either establish a remote connection in between the extension and the product,
or have an agent on the same machine running the product in order for the extension to collect and send the metrics.

The Oracle DB extension needs an Oracle user account on every Oracle instance that is to be monitored. You might use an existing account with appropriate rights; however, a dedicated account will be a better solution in terms of security and manageability.
- Example script for account creation (run this with a DBA user):

            CREATE USER appdynamics IDENTIFIED BY oracle;
            GRANT CREATE SESSION TO appdynamics;
            GRANT SELECT ANY DICTIONARY TO appdynamics;

Installation

1. To build from source, clone this repository and run 'mvn clean install'. This will produce a OracleDBMonitor-VERSION.zip in the target directory.
2. Unzip the file OracleDBMonitor-[version].zip into <MACHINE_AGENT_HOME>/monitors/.
3. In the newly created directory "OracleDBMonitor", edit the config.yml configuring the parameters (See Configuration section below).
4. Download the oracle JDBC jar file and place it in the <MACHINE_AGENT_HOME>/monitors/OracleDBMonitor directory.
5. Edit the monitor.xml and provide the name of the jar file in the classpath. For eg.

    <classpath>oracle-monitoring-extension.jar;ojdbc6.jar</classpath>

6. Restart the machineagent
7. If you're monitoring multiple Oracle DB instances, follow the above steps for every Oracle instance that you want to monitor.

Note: Please place the extension in the "monitors" directory of your Machine Agent installation
directory. Do not place the extension in the "extensions" directory of your Machine Agent installation directory.

Configuration

Note : Please make sure to not use tab (\t) while editing yaml files. You may want to validate the yaml file using a yaml validator

1. Configure the Oracle DB parameters by editing the config.yml file in <MACHINE_AGENT_HOME>/monitors/OracleDBMonitor/.

   Here is a sample config.yml file

   #Refer the documentation to get details on how to configure metric prefix - https://community.appdynamics.com/t5/Knowledge-Base/How-do-I-troubleshoot-missing-custom-metrics-or-extensions/ta-p/28695
   #This will create this metric in all the tiers, under this path.
   metricPrefix: "Custom Metrics|OracleDB|"
   #This will create it in specific Tier. Replace <ComponentID> with TierID
   #metricPrefix: "Server|Component:<ComponentID>|Custom Metrics|OracleDB|"
   
   
   dbServers:
       - displayName: "OracleDB"
   #      connectionUrl: "jdbc:oracle:thin:username/password@HostForDatabase:PortForDatabase:databaseName"
         connectionUrl: "jdbc:oracle:thin:@HostForDatabase:PortForDatabase:databaseName"
   
         driver: "oracle.jdbc.OracleDriver"
   
         connectionProperties:
            - user: "system"
            - password: "oracle"
   
         #Needs to be used in conjunction with `encryptionKey`. Please read the extension documentation to generate encrypted password
   #      encryptedPassword: "9XdTa7+McBwP2g2xSpyNsg=="
   
         #Needs to be used in conjunction with `encryptedPassword`. Please read the extension documentation to generate encrypted password
   #      encryptionKey: "myKey"
   
         # Replaces characters in metric name with the specified characters.
         # "replace" takes any regular expression
         # "replaceWith" takes the string to replace the matched characters
   
         metricCharacterReplacer:
           - replace: "%"
             replaceWith: ""
           - replace: ","
             replaceWith: "-"
   
   
         queries:
           - displayName: "Query1 Sessions "
             queryStmt: "SELECT 'Sessions' , count(*) NumberOfSessions from v$session "
             columns:
               - name: "'Sessions'"
                 type: "metricPathName"
   
               - name: "NumberOfSessions"
                 type: "metricValue"
   
           - displayName: "Query2 Percent of Max Sessions"
             queryStmt: "SELECT 'Percent of max sessions' as sessionNumber, a.cnt / b.cpus * 100 AS Value FROM ( SELECT COUNT(*) cnt FROM v$session ) a, ( SELECT value AS cpus FROM v$parameter WHERE name='sessions') b"
             columns:
               - name: "sessionNumber"
                 type: "metricPathName"
   
               - name: "Value"
                 type: "metricValue"
   
           - displayName: "Query3 Percent of Max Open Cursors"
             queryStmt: "SELECT 'Percent of Max open cursors' as cursors, a.crs / b.max_crs * 100 as Value FROM ( SELECT MAX(a.value) AS crs from v$sesstat a, v$statname b where a.statistic# = b.statistic# AND b.name = 'opened cursors current' ) a, ( select value AS max_crs FROM v$parameter WHERE name='open_cursors' ) b"
             columns:
               - name: "cursors"
                 type: "metricPathName"
   
               - name: "Value"
                 type: "metricValue"
   
           - displayName: "Query4 Active User Sessions"
             queryStmt: "SELECT 'Active User Sessions' as ActiveUserSessions, COUNT(*) Count FROM v$session WHERE status='ACTIVE' AND username IS NOT NULL"
             columns:
               - name: "ActiveUserSessions"
                 type: "metricPathName"
   
               - name: "Count"
                 type: "metricValue"
   
           - displayName: "Query5 Avg Active Sessions Per Logical CPU"
             queryStmt: "SELECT 'Average Active Sessions per logical CPU' as AvgActive, a.value / b.cpus AS Value FROM (SELECT value FROM v$sysmetric WHERE group_id = 2 AND metric_name = 'Average Active Sessions') a, (SELECT value AS cpus FROM v$parameter WHERE name='cpu_count') b"
             columns:
               - name: "AvgActive"
                 type: "metricPathName"
   
               - name: "Value"
                 type: "metricValue"
   
           - displayName: "Query6 System Metrics"
             queryStmt: "SELECT metric_name, Value FROM v$sysmetric WHERE group_id = 2 AND metric_name IN ('Average Active Sessions', 'Current OS Load', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'DB Block Changes Per Sec', 'DB Block Changes Per Txn', 'DB Block Gets Per Sec', 'DB Block Gets Per Txn', 'Executions Per Sec', 'Executions Per Txn', 'I/O Megabytes per Second', 'Logical Reads Per Sec', 'Physical Reads Per Sec', 'Memory Sorts Ratio', 'Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec', 'Shared Pool Free %', 'Execute Without Parse Ratio', 'Soft Parse Ratio', 'Temp Space Used', 'Total PGA Allocated', 'Response Time Per Txn', 'SQL Service Response Time') ORDER BY metric_name"
             columns:
               - name: "metric_name"
                 type: "metricPathName"
   
               - name: "Value"
                 type: "metricValue"
   
           - displayName: "Query7 Wait Class BreakDown Metrics"
             queryStmt: "SELECT 'Wait Class Breakdown|'||wait_class as waitingMetric, ROUND(aas, 2) as Value FROM(SELECT n.wait_class, m.time_waited/m.INTSIZE_CSEC AAS FROM v$waitclassmetric m, v$system_wait_class n WHERE m.wait_class_id=n.wait_class_id AND n.wait_class != 'Idle' UNION ALL SELECT 'CPU', value/100 AAS FROM v$sysmetric WHERE metric_name = 'CPU Usage Per Sec' AND group_id = 2)"
             columns:
               - name: "waitingMetric"
                 type: "metricPathName"
   
               - name: "Value"
                 type: "metricValue"
   
           - displayName: "Query8 Table Space Percent Free"
             queryStmt: "select df.tablespace_name as tableName, round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) Value from (select tablespace_name, round(sum(bytes) / 1048576) totalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name"
             columns:
               - name: "tableName"
                 type: "metricPathName"
   
               - name: "Value"
                 type: "metricValue"
   
   
   numberOfThreads: 5
   
   #Run it as a scheduled task instead of running every minute.
   #If you want to run this every minute, comment this out
   # taskSchedule:
   #   numberOfThreads: 1
   #   taskDelaySeconds: 10
   
   

2. Configure the path to the config.yml file by editing the in the monitor.xml file in the <MACHINE_AGENT_HOME>/monitors/OracleDBMonitor/ directory. Below is the sample

   <task-arguments>
       <!-- config file-->
       <argument name="config-file" is-required="true" default-value="monitors/OracleDBMonitor/config.yml" />
        ....
   </task-arguments>
   

   Note: You will need to provide your own JDBC driver for the database you want to connect to. Put the driver JAR file in the same directory and add it to the classpath element in the
   monitor.xml file.!

Example

<java-task>
    <!-- Use regular classpath foo.jar;bar.jar -->
    <!-- append JDBC driver jar -->
    <classpath>oracledb-monitoring-extension.jar;Jar-File-For_Your-DB.jar</classpath>
    <impl-class>com.appdynamics.extensions.oracledb.OracleDBMonitor</impl-class>
</java-task>

4. Restart the Machine Agent.

How to Connect to your Database with the extension

Lets take a look at some sample connection information:

dbServers:
dbServers:
    - displayName: "OracleDB1"
      connectionUrl: "jdbc:oracle:thin:system/oracle@192.168.57.106:1521:orcl12c"
      driver: "oracle.jdbc.OracleDriver"
      
#      connectionProperties:
#         - user: "system"
#         - password: "oracle"

In order to connect to any database, you will have to provide a connectionUrl.
In the example above we see that the extension is connected to the database (orcl12c)(listed in the config) using the connectionUrl.
In this case we are also providing the username, password and the databaseName in the same connectionUrl
and therefore the "connectionProperties" and the fields under it, "user" and "password", are commented out.
You have to make sure that if you are not sending any connectionProperties to create a
connection, then you should comment the whole thing out just like in the example.

Lets take a look at another way you can connect to the database.
In this case we do need to provide properties such as a username and a password and
therefore we uncomment those lines and update them with valid information.

dbServers:
    - displayName: "OracleDB1"
      connectionUrl: "jdbc:oracle:thin:@192.168.57.106:1521:orcl12c"
      driver: "oracle.jdbc.OracleDriver"

      connectionProperties:
         - user: "system"
         - password: "oracle"

In this case we do add the Database Name as the last part of the connectionUrl (orcl12c) but all other properties like the username and password are provided as connectionProperties.
You will have to confirm how your database takes in the login information and based on that provide the information in your config.yaml in order to successfully establish a connection.

Explanation of the type of queries that are supported with this extension

Only queries that start with SELECT are allowed! Your query should only return one row at a time.

It is suggested that you only return one row at a time because if it returns a full table with enormous amount of data, it may overwhelm the system and it may take a very long time to fetch that data.

The extension does support getting values from multiple columns at once but it can only pull the metrics from the latest value from the row returned.

The name of the metric displayed on the Metric Browser will be the "name" value that you specify in the config.yml for that metric.
Looking at the following sample query :

 queries:
   - displayName: "Active Events"
     queryStmt: "Select NODE_NAME, EVENT_CODE, EVENT_ID, EVENT_POSTED_COUNT from Active_events"
     columns:
       - name: "NODE_NAME"
         type: "metricPathName"

       - name: "EVENT_ID"
         type: "metricPathName"

       - name: "EVENT_CODE"
         type: "metricValue"

       - name: "EVENT_POSTED_COUNT"
         type: "metricValue"

1. queries : You can add multiple queries under this field.
   1. displayName : The name you would like to give to the metrics produced by this query.
   2. queryStmt : This will be your SQL Query that will be used to query the database.
   3. columns: Under this field you will have to list all the columns that you are trying to get values from.
      1. name : The name of the column you would like to see on the metric browser.
      2. type : This value will define if the value returned from the column will be used for the name of the metric or if it is going to be the value of the metric.
         1. metricPathName : If you select this, this value will be added to the metric path for the metric.
         2. metricValue : If you select this, then the value returned will become your metric value that will correspond to the name you specified above.

For the query listed above, there will be two metrics returned as we have two columns of type "metricValue".
The metric path for them will be :

1. Custom Metrics|SQL|Instance1|Active Events|NODE_NAME|EVENT_ID|EVENT_CODE
2. Custom Metrics|SQL|Instance1|Active Events|NODE_NAME|EVENT_ID|EVENT_POSTED_COUNT

Lets look at another query.

           - displayName: "Node Status"
             queryStmt: "Select NODE_NAME, NODE_STATE from NODE_STATES"
             columns:
               - name: "NODE_NAME"
                 type: "metricPathName"
   
               - name: "NODE_STATE"
                 type: "metricValue"
                 properties:
                   convert:
                     "INITIALIZING" : 0
                     "UP" : 1
                     "DOWN" : 2
                     "READY" : 3
                     "UNSAFE" : 4
                     "SHUTDOWN" : 5
                     "RECOVERING" : 6

Lets say if your query returns a text value, but you would still like to see it in the metric browser.
In order to make that happen, you could use the "convert" property and assign each value a number.
The extension will automatically convert the text value to the corresponding number.

NOTE: In order to use this feature, please make sure that the value that is being returned is EXACTLY the same as you have listed in the config.yaml, otherwise the extension will throw an error.

Oracle Licensing

The metrics in the supplied code are retrieved from

-   v$session
-   v$sesstat
-   v$sysmetric
-   v$system_wait_class
-   v$waitclassmetric

all of which are, to the author's knowledge, not subject to additional
licensing of the Oracle Diagnostics Pack. See Oracle's "Options and
Packs" documentation:
https://docs.oracle.com/cd/B28359_01/license.111/b28287/options.htm#DBLIC138

If you plan on extending this code using data dictionary views of the
Diagnostics Pack (e.g., DBA_HIST_% views), you might want to make use
of the argument "ash_licensed" in monitor.xml to easily en-/disable
usage of such code.

Metrics

Here is a summary of the collected metrics. Complete documentation of Oracle's website.

AppDynamics displays metric values as integers. Some metrics are therefore scaled up by a factor of 100 for a better display of low values (e.g. between 0 and 2).

Credentials Encryption

Please visit this page to get detailed instructions on password encryption. The steps in this document will guide you through the whole process.

Extensions Workbench

Workbench is an inbuilt feature provided with each extension in order to assist you to fine tune the extension setup before you actually deploy it on the controller. Please review the following document for how to use the Extensions WorkBench

Troubleshooting

1. Please follow the steps listed in the extensions troubleshooting document in order to troubleshoot your issue. These are a set of common issues that customers might have faced during the installation of the extension.
2. If you are seeing java.lang.NoClassDefFoundError: org/ietf/jgss/GSSException please add the jgss.jar in the <MA_HOME>/monitorsLibs and restart Machine agent. You can download the jgss jar from here

Contributing

Always feel free to fork and contribute any changes directly via GitHub.

Version

Note: While extensions are maintained and supported by customers under the open-source licensing model, they interact with agents and Controllers that are subject to AppDynamics’ maintenance and support policy. Some extensions have been tested with AppDynamics 4.5.13+ artifacts, but you are strongly recommended against using versions that are no longer supported.