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 & 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.

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 particular 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 with respect to the total number of process
Processespercent