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

Collector TypeTemplate Name
GatewayG2-OracleDB-Oracle Library Stats
G2-OracleDB-Oracle Library Stats - v2.0
G2-OracleDB-Oracle Memory
G2-OracleDB-Oracle Memory - v2.0
G2-OracleDB-Performance
G2-OracleDB-Performance - v2.0
G2-OracleDB-Sessions
G2-OracleDB-Sessions - v2.0
G2-OracleDB-System Stats
G2-OracleDB-System Stats - v2.0
G2-OracleDB-System Summary
G2-OracleDB-System Summary - v2.0
G2-OracleDB-Table Space
G2-OracleDB-Table Space - v2.0
G2-OracleDB-ASM Availability
G2-OracleDB-Database locking
G2-OracleDB-Database backups
G2-OracleDB-Database scheduled jobs
G2-OracleDB-Database Space Usage

Global metrics

MetricMetric Display NameUnitDescription
oracle.active.sessionsActive SessionsCountMonitors the active sessions.
oracle.asm.archive.pctOracle ASM Archive pctPercentage(%)Oracle query for ASM ARCHIVE diskgroup.
oracle.asm.data.pctOracle ASM Data pctPercentage(%)Oracle query for ASM DATA diskgroup.
oracle.blocking.lock.queriesOracle Blocking Lock QueriesCountValidates the number of block lock queries on a specific database.
oracle.blocking.sessionOracle Blocking SessionCountTo monitor blocked valid sessions.
oracle.BufferCacheHitRatio.pctOracle BufferCacheHitRatio pctPercentage(%)To monitor buffer cache hit ratio value in percentage.
oracle.cache.blocks.lostCache Blocks LostCountThe number of global cache blocks lost over the user-defined observation period.
oracle.cache.invalidationsOracle Cache InvalidationsCountValidates the number of cache invalidations on a specific database.
oracle.check.db.aliveCheck Db AliveMonitors database instance connectivity.
oracle.cpu.usage.per.secCPU Usage Per SecPer Second(psec)The CPU usage per second by the database processes measured in hundredths of a second.
oracle.cpu.usage.per.txnCPU Usage Per TxnPercentage(%)The amount of CPU usage per transaction for a specific task or session.
oracle.database.cpu.time.ratioDatabase CPU Time RatioPercentage(%)The Database CPU Time Ratio is calculated by dividing the amount of used CPU in the database by the amount of total database time. Total database time is the time spent by the database on user-level calls .
oracle.database.wait.time.ratioDatabase Wait Time RatioPercentage(%)Monitors the database wait time ratio.
oracle.db.instance.uptimeDB Instance UptimeDaysThe db instance uptime since start up.
oracle.disk.sort.ratio.pctOracle Disk Sort Ratio pctPercentage(%)To monitor disk sort ratio value in percentage.
oracle.duplicated.filename.countOracle Duplicated Filename CountCountTo check the existence of duplicated db filenames in different ASM paths.
oracle.executions.per.secExecutions Per SecPer Second(psec)The average transactions per second
oracle.executions.per.txnExecutions Per TxnThe average amount of time per execution
oracle.failed.logon.countFailed Logon CountCountMonitors the failed logon attempts of the current user.
oracle.inactive.sessionsInactive SessionsCountMonitors the inactive sessions.
oracle.invalid.objectsInvalid ObjectsCountChecks for invalid objects in a database.
oracle.io.wait.timeIO Wait TimeMilli Seconds(ms)The total time taken to complete the I/O operations keeping the application in a blocked state.
oracle.java.pool.freeJava Pool FreeMega Bytes(MB)Monitors the free java pool memory in MB.
oracle.large.pool.freeLarge Pool FreeMega Bytes(MB)Monitors the free large pool memory in MB.
oracle.library.cache.getsOracle Library Cache GetsCountValidates the number of library cache gets by the database.
oracle.library.cachereloadsLibrary Cache ReloadsValidates the number of library cache reloads by the database.
oracle.librarycachehitratio.pctOracle Library Cache Hit Ratio pctPercentage(%)To monitor the library cache hit ratio value in percentage.
oracle.logons.per.secLogons Per SecCountThe number of logons per second during the sample period.
oracle.long.running.queriesOracle Long Running QueriesCountValidates the number of long running queries on a specific database.
oracle.maxdbfilepct.usageOracle Max DB File pct usagePercentage(%)To monitor the percentage of open oracle files.
oracle.maxopencursor.username.countOracle Max Open Cursor Username CountCountTo monitor maximum open cursor value for each db username.
oracle.process.countProcess CountMonitor the current processes running in the database.
oracle.procsused.pctOracle Processes Used pctPercentage(%)To monitor db process usage in percentage.
oracle.rac.tablespace.space.usedOracle RAC Tablespace Space Used Top 20 Greaterthan 85Percentage(%)To monitor top(20) RAC tablespace space usage where percentage > 85%.
oracle.redo.log.space.requestsRedo Log Space RequestsReq per SecThe number of requests per second by Oracle waiting for disk space allocation for the redo log entries.
oracle.redolog.switch.countOracle Redolog Switch CountCountTo monitor the number of redolog switch.
oracle.sequence.pctusedOracle Sequence Pct UsedPercentage(%)To monitor the sequence usage in percentage.
oracle.session.cpu_usedSession Cpu UsedSeconds(s)Monitors the session cpu usage in seconds.
oracle_asm_acfs_statusOracle ASM ACFS StatusMonitors the Oracle ASM ACFS status.
oracle_asm_diskgroup_statusOracle ASM DiskGroup StatusMonitors the Oracle ASM disk group status.
oracle_asm_disk_statusOracle ASM Disk StatusMonitors the Oracle ASM disk status.
oracle_asm_failure_groups_statusOracle ASM Failure Groups StatusMonitors the Oracle ASM failure groups status.
oracle_asm_diskgroup_utilizationOracle ASM Disk Group Space Utilizationpercentage(%)Monitors the Oracle ASM disk group space utilization.
oracle_database_tablespace_usage_without_autoextendOracle Database Tablespace Usage Without AutoExtendpercentage(%)Monitors the Oracle database tablespace usage without autoextend.
oracle_database_tablespace_usage_with_autoextendOracle Database Tablespace Usage With AutoExtendpercentage(%)Oracle Database tablespace usage with autoextend.
oracle_database_required_mirror_freespaceOracle Database Required Mirror Free Spacemegabytes(MB)Monitors Oracle Database including different redundancy requirements (Safely usable data usage).
oracle_database_safelyusable_diskspaceOracle Database Safely Usable Disk Spacemegabytes(MB)Monitors the Oracle Database including safely usable disk space on different redundancy (Safely usable data usage).
oracle_blocking_session_countOracle Blocking Session CountcountMonitors the Oracle blocking session count.
oracle_database_lockedusers_statusOracle Database Locked Users StatusMonitors the Oracle database locked users status.
oracle_database_backup_fullOracle Database Full Backup DaysAgoDaysMonitors the Oracle database full backup days ago.
oracle_database_backup_incrementalOracle Database Incremental Backup DaysAgoDaysMonitors the Oracle database incremental backup days ago.
oracle_database_backup_rmanOracle Database RMAN Backup DaysAgoDaysMonitors the Oracle database RMAN backup days ago.
oracle_database_backup_usermanagedOracle Database User Managed Backup DaysAgoDaysMonitors the Oracle database user managed backup days ago.
oracle_database_scheduler_jobs_statusOracle Database Scheduler Jobs StatusMonitors the Oracle database scheduler jobs status.
oracle_database_dbmsjobs_failure_countOracle Database DBMS Jobs Failure CountCountMonitors the Oracle database DBMS jobs failure count.
oracle_database_autotask_job_statusOracle Database AutoTask Job StatusMonitors the Oracle database auto task job status.

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:

Configuration ParameterDescription
Connection Time OutThe maximum time to connect to the database. The driver waits for the specified time before timing out due to connection failure. The default time is 15000 milliseconds(ms).
Service Transport TypeTo configure the database at a secure end-point. The default type is In-secure. The connection is Secure when the data sent between the client and server is encrypted.
Service PortThe port number where the database is running. The connection is made to the specified port if you specify the port and the database name. The default port is 1521.
DB Instance NameThe name of the database to connect to. The default name is system.
  • If you have multiple database names with different ports, use the following syntax: `DBname1:Port1, DBname2:Port2,..` (Comma-separated).
    Important! Create separate credential sets on the resource if databases consist of different users. For example, if DBname 1 has credential set A and DBname 2 has credential set B, then create separate credentials sets on the resource.
  • If you have multiple database names with the same port, use the following syntax: `DBname1, DBname2,..` (Comma-separated).
Application TypeThe application type value to identify the adapter. For example, ORACLE_DB.
Warning: Do not change the default application type value.

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.

OracleDB

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