Monitoring OracleDB

Introduction

Oracle Database is a multi-model database management system produced and marketed by Oracle Corporation.
Monitoring your OracleDB includes:

• Determining relevant metrics according to your business needs. Create custom SQL metrics and apply to the database.
• Track and analyze the monitoring metrics.
• Check the availability of the database.

Prerequisites

• Database and port(1521) can be accessed by the gateway.
• SELECT ANY DICTIONARY privilege to monitor the database. For more information, see Monitoring Privileges.
• A gateway management profile mapped to a resource.
• Create credentials with type Database before assigning a template to a resource. The Port, Connection Time Out, and Is Secure values are not used to create credentials.

Supported version

OracleDB version 12.2.0.2 or higher.

Database discovery and monitoring

The administrator can deploy an agent or gateway to support OracleDB agent-based or agentless monitoring.

Discovery using the gateway

The gateway establishes a connection to the database using the Java Database Connectivity (JDBC) Java API and collects metrics using SQL queries. To monitor an OracleDB, install gateway version 5.0.0 or later.

Use one of the following mechanisms to discover OracleDB servers to add them to your inventory:

• WMI
• SSH
• SNMP

Optionally, add a database server manually to the infrastructure inventory.

To add a resource manually to the inventory:

1. Select Infrastructure.
2. Click the Settings > Resource > Add.
3. From the Add Device window, provide the required details such as Device Type and Management Profile.
4. Click Save.

Prepare the device to start monitoring

To monitor OracleDB:

1. Associate appropriate database credentials to the discovered database.
2. Assign one or more database-specific global monitors or templates on the device.
   Optionally, create custom metrics or monitors using SQL queries and assign them to the database.

To track monitoring of OracleDB database in your cloud environment, see Monitoring Cloud Database using Gateway.

Global templates and metrics

Global templates

Global metrics

Create custom metrics

To create a metric using SQL query for OracleDB monitoring, navigate to Monitoring > Metrics > Create Metric. For example, to create a metric to check the DB instance use:

SELECT INSTANCE_NAME, DATABASE_STATUS FROM V$INSTANCE

For more information, see Manage Metrics.

Assign templates from setup

Assign OracleDB templates to one or more resources for a selected client and modify the configuration parameters while assigning templates. For more information, see Assign Templates from Setup.

Assign templates at the device level

Applying Oracle templates at the device level helps assign one or more templates to a specific resource. You can change the configuration parameter default values while assigning the templates. For more information, see Assign Templates at the Device Level.

Template configuration parameters:

Assign template from device management policies

Device management policies help manage resources. You can assign monitoring templates, knowledge base articles, and custom attributes using device management policies. The device management policy can be applied to one or a set of resources. For more information, see Create Policies.

View resource metrics

The gateway monitors the application using the assigned templates and displays the results in graphical format. To view resource metrics, click the desired database resource name > resource Details > Metrics.

Troubleshooting

Verify the following if the graphs are not plotted correctly:

• The resource is accessible and Telnet is established for the IP address and port.
• Valid credentials are used for the resource.

If you experience OracleDB monitoring issues, follow these troubleshooting steps:

1. Verify server and gateway connectivity using Ping.

ping <IP Address>

Example

ping 12.26.105.173

1. Verify Telnet-to-server listening port connectivity.

telnet <IP Address> <Port>

Example

telnet 172.26.105.173 1433

1. Execute the following commands from GCLI to verify the database connectivity:

gcli

db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <ReadTimeout> <Secure Flag> <Query>

Example

db oracleDB172.26.105.173 root Pass@123 5432 orcl 15000 10000 insecure "SELECT count(*) AS ACTIVE_SESSIONS from v$session where status='ACTIVE’"

From gateway version 5.3.0, use the following format to execute the command along with the result-set:

db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <Read Timeout> <Secure Flag> <ResultSet?: Yes/No> <Query>

Example

db mssql 172.26.105.173 root Pass@123 5432 orcl15000 10000 insecure Yes "SELECT count(*) AS ACTIVE_SESSIONS from v$session where status='ACTIVE'"