Oracle Database is a multi-model database management system produced and marketed by Oracle Corporation.

It is a database commonly used for running online transaction processing (OLTP), data warehousing (DW) and mixed (OLTP and DW) database workloads.

Prerequisites

  1. Download and install the Oracle Instant Client libraries.

  2. Create a read-only user with access to your Oracle Database server. To create a user, connect to your Oracle database as an administrative (for example, SYSDBA or SYSOPER) and run the following:

==Enable Oracle Script==
ALTER SESSION SET "_ORACLE_SCRIPT"=true;
 
==Create the user==
CREATE USER '<Username>' IDENTIFIED BY '<PASSWORD>';
 
==Grant access to the '<Username>' user==
GRANT CONNECT TO '<Username>';
GRANT SELECT ON GV_$PROCESS TO '<Username>';
GRANT SELECT ON gv_$sysmetric TO '<Username>';
GRANT SELECT ON sys.dba_data_files TO '<Username>';
GRANT SELECT ON sys.dba_tablespaces TO '<Username>';
GRANT SELECT ON sys.dba_tablespace_usage_metrics TO '<Username>';

For Oracle version 11g, do not run the line -ALTER SESSION SET "_ORACLE_SCRIPT"=true; 3. For Oracle version 12c and 19c, log into the root database as an Administrator to create a user and grant permissions:

alter session set container = cdb$root;
CREATE USER '<Username>' IDENTIFIED BY '<Password>' CONTAINER=ALL;
GRANT CREATE SESSION TO '<Username>' CONTAINER=ALL;
Grant select any dictionary to '<Username>' container=all;
GRANT SELECT ON GV_$PROCESS TO '<Username>' CONTAINER=ALL;
GRANT SELECT ON gv_$sysmetric TO '<Username>' CONTAINER=ALL;
  1. For Virtual Machines, install the Linux Agent.

Configuring the credentials

Configure the credentials in the directory /opt/opsramp/agent/conf/app.d/creds.yaml

oracle:
- name: oracle
  user: <username>
  pwd: <Password>
  encoding-type: plain
  labels:
    key1: val1
    key2: val2

Configuring the application

Virtual machine

Configure the application in the directory /opt/opsramp/agent/conf/app/discovery/auto-detection.yaml

- name: oracle
  instance-checks:
    service-check:
      - oracle
    process-check:
      - oracle
    port-check:
      - 1521

Validate

Go to Resources under the Infrastructure tab to check if your resources are onboarded and the metrics are collected.

Supported metrics

OpsRamp MetricMetric Display NameUnit
oracle_buffer_cachehit_ratio

Ratio of buffer cache hits
Buffer Cachehit Ratiofraction
oracle_cursor_cachehit_ratio

Ratio of cursor cache hits
Cursor Cachehit Ratiofraction
oracle_library_cachehit_ratio

Ratio of library cache hits
Library Cachehit Ratiofraction
oracle_shared_pool_free

shared pool free memory %
Shared Pool Freepercent
oracle_physical_reads

Physical reads per second
Physical ReadsReads / second
oracle_physical_writes

Physical writes per second
Physical WritesWrites / second
oracle_enqueue_timeouts

Enqueue timeouts per second
Enqueue TimeoutsTimeouts / second
oracle_gc_cr_block_received

GC CR block received
Gc Cr Block ReceivedBlocks / second
oracle_cache_blocks_corrupt

Corrupt cache blocks
Cache Blocks CorruptBlocks
oracle_cache_blocks_lost

Lost cache blocks
Cache Blocks LostBlocks
oracle_logons

Number of logon attempts
Logons
oracle_active_sessions

Number of active sessions
Active Sessions
oracle_long_table_scans

Number of long table scans per second
Long Table ScansScans / second
oracle_service_response_time

Service response time
Service Response Timeseconds
oracle_user_rollbacks

Number of user rollbacks
User Rollbacksoperations
oracle_sorts_per_user_call

Sorts per user call
Sorts Per User Call
oracle_rows_per_sort

Rows per sort
Rows Per SortRows / Operation
oracle_disk_sorts

Disk sorts per second
Disk Sortsoperations / second
oracle_memory_sorts_ratio

Memory sorts ratio
Memory Sorts Ratiofraction
oracle_database_wait_time_ratio

Database wait time ratio
Database Wait Time Ratiofraction
oracle_session_limit_usage

Session limit usage
Session Limit Usagepercent
oracle_session_count

Session count
Session Count
oracle_process_pga_used_memory

PGA memory used by process
Process Pga Used Memorybytes
oracle_process_pga_allocated_memory

PGA memory allocated by process
Process Pga Allocated Memorybytes
oracle_process_pga_freeable_memory

PGA memory freeable by process
Process Pga Freeable Memorybytes
oracle_process_pga_maximum_memory

PGA maximum memory ever allocated by process
Process Pga Maximum Memorybytes
oracle_temp_space_used

Temp space used
Temp Space Usedbytes
oracle_tablespace_used

Tablespace used
Tablespace Usedbytes
oracle_tablespace_size

Tablespace size
Tablespace Sizebytes
oracle_tablespace_in_use

Tablespace in-use
Tablespace In Usefraction
oracle_tablespace_offline

Tablespace offline
Tablespace Offline
oracle_blocking_lock_queries

Number of blocking locks
Blocking Lock Queries
oracle_cache_invalidations

Number of Cache invalidations on database
Cache Invalidations
oracle_data_filelesize_allocated

Data File Size Allocated for the database.
Data Filelesize Allocatedmegabytes
oracle_library_cache_reloads

Number of Library Cache Reloads by database.
Library Cache Reloads
oracle_users_commit

Number of User commits
Users Commit
oracle_long_running_queries

Number of Long Running Queries
Long Running Queries
oracle_tablescan_blocks

Number of Table scan blocks by database
Tablescan Blocks
oracle_processes

Caluclates the percentage of processes running for the total number of process
Processespercent