• /
  • EnglishEspañol日本語한국어Português
  • Log inStart now

Oracle Database monitoring integration

Our Oracle Database integration collects key performance metrics on databases, tablespaces, and memory by default. You can customize your configuration to collect even more metrics, giving you detailed characterization of database performance.

Read on to install the integration, and to see what data we collect.

Compatibility and requirements

Our integration is compatible with Oracle Database from versions 11.2 to 23.

Before installing the integration, make sure that you meet the following requirements:

Important

ARM64 architecture is not supported.

Install and activate

To install the Oracle Database integration:

  1. Install the infrastructure agent, and replace the INTEGRATION_FILE_NAME variable with nri-oracledb.

  2. Change directory to the integrations folder:

    bash
    $
    cd /etc/newrelic-infra/integrations.d
  3. Copy the sample configuration file:

    bash
    $
    sudo cp oracledb-config.yml.sample oracledb-config.yml
  4. Edit the oracledb-config.yml file as described in the configuration settings.

Additional notes:

Did this doc help with your installation?

Configuration

Enabling your Oracle DB Server

In the Oracle database, execute the following statements to create a new user and assign user privileges. USERNAME and similar user-specific values must be replaced.

  1. Choose which kind of database you have. For assistance with user maintenance questions, consult the Oracle docs or contact your system or database administrator.

  2. Grant CONNECT privileges to the user:

    GRANT CONNECT TO USERNAME;
  3. Grant SELECT privileges to the user on the following global views:

    • cdb_data_files

    • cdb_pdbs

    • cdb_users

    • gv_$sysmetric

    • gv_$pgastat

    • gv_$instance

    • gv_$filestat

    • gv_$parameter

    • sys.dba_data_files

    • gv_$session

    • gv_$sesstat

    • gv_$statname

    • gv_$rowcache

    • gv_$sga

    • gv_$sysstat

    • v_$database

    • gv_$librarycache

    • gv_$sqlarea

    • gv_$system_event

    • dba_tablespaces

    • gv_$session_wait

    • gv_$rollstat

    • v_$instance

      Execute the following SQL statements together in one script, or individually:

      GRANT SELECT ON cdb_data_files TO USERNAME;
      GRANT SELECT ON cdb_pdbs TO USERNAME;
      GRANT SELECT ON cdb_users TO USERNAME;
      GRANT SELECT ON gv_$sysmetric TO USERNAME;
      GRANT SELECT ON gv_$pgastat TO USERNAME;
      GRANT SELECT ON gv_$instance TO USERNAME;
      GRANT SELECT ON gv_$filestat TO USERNAME;
      GRANT SELECT ON gv_$parameter TO USERNAME;
      GRANT SELECT ON sys.dba_data_files TO USERNAME;
      GRANT SELECT ON DBA_TABLESPACES TO USERNAME;
      GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO USERNAME;
      GRANT SELECT ON gv_$session TO USERNAME;
      GRANT SELECT ON gv_$sesstat TO USERNAME;
      GRANT SELECT ON gv_$statname TO USERNAME;
      GRANT SELECT ON gv_$rowcache TO USERNAME;
      GRANT SELECT ON gv_$sga TO USERNAME;
      GRANT SELECT ON gv_$sysstat TO USERNAME;
      GRANT SELECT ON v_$database TO USERNAME;
      GRANT SELECT ON gv_$librarycache TO USERNAME;
      GRANT SELECT ON gv_$sqlarea TO USERNAME;
      GRANT SELECT ON gv_$system_event TO USERNAME;
      GRANT SELECT ON dba_tablespaces TO USERNAME;
      GRANT SELECT ON gv_$session_wait TO USERNAME;
      GRANT SELECT ON gv_$rollstat TO USERNAME;
      GRANT SELECT ON v_$instance TO USERNAME;

    To collect PDB metrics, grant gv$con_sysmetric privileges by running:

    GRANT SELECT ON gv$con_sysmetric TO USERNAME;

Configure the integration

There are several ways to configure the integration, depending on how it was installed:

  • If enabled via Amazon ECS: see Monitor services running on ECS.
  • If installed on-host: edit the config in the integration's YAML config file, oracledb-config.yml.

An integration's YAML-format configuration is where you can place required login credentials and configure how data is collected. Which options you change depend on your setup and preference.

The configuration file has common settings applicable to all integrations like interval, timeout, inventory_source. To read all about these common settings refer to our Configuration Format document.

Important

If you are still using our Legacy configuration/definition files please refer to this document for help.

Specific settings related to Oracle DB are defined using the env section of the configuration file. These settings control the connection to your Oracle DB instance as well as other security settings and features. The list of valid settings is described in the next section of this document.

Oracle DB Instance Settings

The Oracle DB integration collects both Metrics(M) and Inventory(I) information. In the table, use the Applies To column for the settings available to each collection:

Setting

Description

Default

Applies To

SERVICE_NAME

The service name for the Oracle instance.

N/A

M/I

HOSTNAME

Hostname or IP where Oracle DB is running.

127.0.0.1

M/I

PORT

Port on which Oracle DB is listening.

1521

M/I

USERNAME

Username for accessing the Oracle DB server.

N/A

M/I

PASSWORD

Password for the given user.

N/A

M/I

CONNECTION_STRING

A full connection string such as those found in tnsnames.ora. Takes precedence over hostname, port, and service name.

N/A

M/I

ORACLE_HOME

Path to where ORACLE_HOME is. This field is required.

N/A

M/I

TABLESPACES

A JSON array of tablespaces to collect. If omitted, it collects all tablespaces. If empty, it skips tablespace metrics collection.

[]

M

IS_SYS_DBA

Indicates whether the authenticating user has SysDBA permissions.

false

M

IS_SYS_OPER

Indicates whether the authenticating user has SysOper permissions.

false

M

EXTENDED_METRICS

Indicates whether to collect extended metrics. Check which are extended metrics in the table below

false

M

SKIP_METRICS_GROUPS

Collected metrics are grouped together depending on the query used to obtain the data. These metric groups are listed here and can be skipped from collection by adding the name of the group to SKIP_METRICS_GROUPS in JSON array format. By default no group is skipped. See example below.

[]

M

DISABLE_CONNECTION_POOL

Disable connection pooling. Use only if the integration is getting errors when trying to establish new connections to Oracle DB.

false

M

MAX_OPEN_CONNECTIONS

Maximum number of simultaneous connections opened by the integration.

5

M

SYS_METRICS_SOURCE

Set it to PDB to collect application container metrics. Set it to ALL to collect multitenant container database (CDB) and customer-created pluggable databases (PDB) metrics. If you set it to any other value, you'll get SYS metrics for standalone and multitenant databases with CDB access only.

''

M

CUSTOM_METRICS_QUERY

A SQL query to collect custom metrics. See example below.

N/A

M

CUSTOM_METRICS_CONFIG

YAML configuration with one or more SQL queries to collect custom metrics. See example below.

false

M

METRICS

Set to true to enable metrics-only collection.

false

INVENTORY

Set to true to enable inventory-only collection.

false

The values for these settings can be defined in several ways:

  • Add the value directly to the configuration file. This is the most common way.
  • Replace the values from environment variables using the {{}} notation. This requires infrastructure agent 1.14.0+. For more on this, see more on infrastructure agent passthrough environment variables.
  • Use secrets management to protect sensible information, such as passwords, so that it's not exposed in plain text on the configuration file. For more information, see secrets management.

Labels and custom attributes

You can also decorate your metrics with labels. Labels allow you to add key/value pair attributes to your metrics so that you can query, filter, or group your metrics.

Even though our default sample configuration file includes examples of labels, they're optional. You can remove, modify, or add new ones.

labels:
env: production
role: load_balancer

Example configurations

Find and use data

To find your integration data in New Relic, go to one.newrelic.com > All capabilities > Infrastructure > Third-party services and select one of the Oracle Database integration links.

Oracle Database data is attached to the following event types:

  • OracleDatabaseSample
  • OracleTablespaceSample

For more on how to find and use your data, see Understand integration data.

Metric data

The Oracle Database integration collects the following metric data attributes. Each metric name is prefixed with a category indicator and a period, such as disk. or memory..

Database metrics

These attributes can be found by querying the OracleDatabaseSample event type.

Metric

Description

Extended

db.activeParallelSessions

Active parallel sessions.

db.activeSerialSessions

Active serial sessions.

db.averageActiveSessions

Average active sessions.

db.backgroundCheckpointsPerSecond

Checkpoints per second.

db.backgroundCpuUsagePerSecond

Background CPU usage per second.

db.backgroundTimePerSecond

Background time per second.

db.blockChangesPerSecond

DB block changes per second.

db.blockChangesPerTransaction

DB block changes per transaction.

db.blockChangesPerUserCall

DB block changes per user call.

db.blockGetsPerSecond

DB block gets per second.

db.blockGetsPerTransaction

DB block gets per transaction.

db.blockGetsPerUserCall

DB block gets per user call.

db.branchNodeSplitsPerSecond

Branch node splits per second.

db.branchNodeSplitsPerTransaction

Branch node splits per transaction.

db.consistentReadChangesPerSecond

Consistent read changes per second.

db.consistentReadChangesPerTransaction

Consistent read changes per transaction.

db.consistentReadGetsPerSecond

Consistent read gets per second.

db.consistentReadGetsPerTransaction

Consistent read gets per transaction.

db.cpuTimeRatio Database

CPU time ratio.

db.cpuUsagePerSecond

CPU usage per second.

db.cpuUsagePerTransaction

CPU usage per transaction.

db.crBlocksCreatedPerSecond

CR blocks created per second.

db.crBlocksCreatedPerTransaction

CR blocks created per transaction.

db.crUndoRecordsAppliedPerSecond

CR undo records applied per second.

db.crUndoRecordsAppliedPerTransaction

CR undo records applied per transaction.

db.currentLogons

Current logons count.

db.currentOpenCursors

Current open cursors count.

db.cursorCacheHitsPerAttempts

Cursor cache hit ratio.

db.databaseCpuTimePerSecond

Database time per second.

db.dbwrCheckpointsPerSecond

DBWR checkpoints per second.

db.enqueueDeadlocksPerSecond

Enqueue deadlocks per second.

db.enqueueDeadlocksPerTransaction

Enqueue deadlocks per transaction.

db.enqueueRequestsPerSecond

Enqueue requests per second.

db.enqueueRequestsPerTransaction

Enqueue requests per transaction.

db.enqueueTimeoutsPerSecond

Enqueue timeouts per second.

db.enqueueTimeoutsPerTransaction

Enqueue timeouts per transaction.

db.enqueueWaitsPerSecond

Enqueue waits per second.

db.enqueueWaitsPerTransaction

Enqueue waits per transaction.

db.executionsPerSecond

Executions per second.

db.executionsPerTransaction

Executions per transaction.

db.executionsPerUserCall

Executions per user call.

db.fullIndexScansPerSecond

Full index scans per second.

db.fullIndexScansPerTransaction

Full index scans per transaction.

db.GcCrBlockRecievedPerSecond

GC CR block received per second.

db.GcCrBlockRecievedPerTransaction

GC CR block received per transaction.

db.GcCurrentBlockReceivedPerSecond

GC current block received per second.

db.GcCurrentBlockReceivedPerTransaction

GC current block received per transaction.

db.globalCacheAverageCrGetTime

Global cache average CR get time.

db.globalCacheAverageCurrentGetTime

Global cache average current get time.

db.hardParseCountPerSecond

Hard parse count per second.

db.hardParseCountPerTransaction

Hard parse count per transaction.

db.hostCpuUsagePerSecond

Host CPU usage per second.

db.hostCpuUtilization

Host CPU utilization (percentage).

db.leafNodeSplitsPerSecond

Leaf node splits per second.

db.leafNodeSplitsPerTransaction

Leaf node splits per transaction.

db.libraryCacheHitRatio

Library cache hit ratio.

db.libraryCacheMissRatio

Library cache miss ratio.

db.logicalReadsPerSecond

Logical reads per second.

db.logicalReadsPerTransaction

Logical reads per transaction.

db.logonsPerSecond

Logons per second.

db.logonsPerTransaction

Logons per transaction.

db.longTableScansPerSecond

Long table scans per second.

db.longTableScansPerTransaction

Long table scans per transaction.

db.openCursorsPerSecond

Open cursors per second.

db.openCursorsPerTransaction

Open cursors per transaction.

db.osLoad

Current OS load.

db.parseFailureCountPerSecond

Parse failure count per second.

db.parseFailureCountPerTransaction

Parse failure count per transaction.

db.pgaCacheHitPercentage

PGA cache hit percentage.

db.processLimitPercentage

Process limit percentage.

db.recursiveCallsPerSecond

Recursive calls per second.

db.recursiveCallsPerTransaction

Recursive calls per transaction.

db.redoWritesPerSecond

Redo writes per second.

db.redoWritesPerTransaction

Redo writes per transaction.

db.responseTimePerTransaction

Response time per transaction.

db.rowCacheHitRatio

Row cache hit ratio.

db.rowCacheMissRatio

Row cache miss ratio.

db.rowsPerSort

Rows per sort.

db.sessionCount

Session count.

db.sessionLimitPercentage

Session limit percentage.

db.sharedPoolFreePercentage

Shared pool free percentage.

db.softParseRatio

Soft parse ratio.

db.sortsPerUserCall

Total sorts per user call.

db.sqlServiceResponseTime

SQL service response time.

db.streamsPoolUsagePercentage

Streams pool usage percentage.

db.tableScansPerUserCall

Total table scans per user call.

db.totalIndexScansPerSecond

Total index scans per second.

db.totalIndexScansPerTransaction

Total index scans per transaction.

db.totalParseCountPerSecond

Total parse count per second.

db.totalParseCountPerTransaction

Total parse count per transaction.

db.totalTableScansPerSecond

Total table scans per second.

db.totalTableScansPerTransaction

Total table scans per transaction.

db.TransactionsPerLogon

Transactions per logon.

db.userCallsPerSecond

User calls per second.

db.userCallsPerTransaction

User calls per transaction.

db.userCallsRatio

User calls ratio.

db.userCommitsPercentage

User commits percentage.

db.userCommitsPerSecond

User commits per second.

db.userLimitPercentage

User limit percentage.

db.userRollbacksPercentage

User rollbacks per transaction.

db.userRollbacksPerSecond

User rollbacks per second.

db.userRollbackUndoRecordsAppliedPerSecond

User rollback undo records applied per second.

db.userRollbackUndoRecordsAppliedPerTransaction

User rollback undo records applied per transaction.

db.waitTimeRatio

Database wait time ratio.

db.capturedUserCalls

Captured Used Calls

db.executeWithoutParseRatio

Execute Without Parse Ratio

db.logonsPerSecond

Logons Per Sec

db.physicalReadBytesPerSecond

Physical Read Bytes Per Sec

db.physicalReadIORequestsPerSecond

Pysical Read IO Requests Per Sec

db.physicalReadsPerSecond

Pysical Reads Per Sec

db.physicalWriteBytesPerSecond

Physical Writes Bytes Per Sec

db.physicalWritesPerSecond

Physical Writes Per Sec

disk.blocksRead

Number of block reads.

disk.blocksWritten

Number of block writes.

disk.logicalReadsPerUserCall

Logical reads per user call.

disk.physicalLobsReadsPerSecond

Physical reads direct lobs per second.

disk.physicalLobsWritesPerSecond

Physical writes direct lobs per second.

disk.physicalReadBytesPerSecond

Physical read total bytes per second.

disk.physicalReadIoRequestsPerSecond

Physical read total I/O requests per second.

disk.physicalReadsPerSecond

Physical reads direct per second.

disk.physicalWriteBytesPerSecond

Physical write total bytes per second.

disk.physicalWriteIoRequestsPerSecond

Physical write I/O requests per second.

disk.physicalWritesPerSecond

Physical writes direct per second.

disk.physicalWriteTotalIoRequestsPerSecond

Physical write total I/O requests per second.

disk.reads

Total number of physical reads.

disk.readTimeInMilliseconds

Amount of file read time.

disk.sortPerSecond

Disk sort per second.

disk.sortPerTransaction

Disk sort per transaction.

disk.tempSpaceUsedInBytes

Temp space used.

disk.writes

Total number of physical writes.

disk.writeTimeInMilliseconds

Amount of file write time.

lockedAccounts

Number of accounts whose account_status is not OPEN.

longRunningQueries

Number of long running (> 60s) queries.

memory.bufferCacheHitRatio

Buffer cache hit ratio.

memory.globalCacheBlocksCorrupted

Global cache blocks corrupted.

memory.globalCacheBlocksLost

Global cache blocks lost.

memory.pgaAllocatedInBytes

Current amount of PGA memory allocated by the instance.

memory.pgaFreeableInBytes

Maximum size of a work area executed in automatic mode.

memory.pgaFreeableInBytes

Number of bytes of PGA memory in all processes that could be freed back to the operating system.

memory.pgaInUseInBytes

Indicates how much PGA memory is currently consumed by work areas. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java).

memory.redoAllocationHitRatio

Redo allocation hit ratio.

memory.redoGeneratedBytesPerSecond

Redo generated bytes per second.

memory.redoGeneratedBytesPerTransaction

Redo generated bytes per transaction.

memory.sortsRatio

Memory sorts ratio.

network.ioMegabytesPerSecond

I/O megabytes per second.

network.ioRequestsPerSecond

I/O requests per second.

network.trafficBytePerSecond

Network traffic volume per second.

query.physicalLobsReadsPerTransaction

Physical reads direct lobs per transaction.

query.physicalLobsWritesPerTransaction

Physical writes direct lobs per transaction.

query.physicalReadsPerTransaction

Physical reads direct per transaction.

query.physicalReadsPerTransaction

Physical reads per transaction.

query.physicalWritesPerTransaction

Physical writes direct per transaction.

query.physicalWritesPerTransaction

Physical writes per transaction.

query.transactionsPerSecond

User transaction per second.

redoLog.logFileSwitch

Number of redo log file switch events.

redoLog.logFileSwitchArchivingNeeded

Number of redo log file switch events that need archiving.

redoLog.logFileSwitchCheckpointIncomplete

Number of redo log file switch event checkpoints that are incomplete.

redoLog.waits

Number of redo log waits.

rollbackSegments.gets

Number of rollback segments gets.

rollbackSegments.ratioWait

Ratio of waits for rollback segments.

rollbackSegments.waits

Number of rollback segments waits.

sga.bufferBusyWaits

Number of SGA buffer busy waits.

sga.fixedSizeInBytes

SGA fixed size.

sga.freeBufferInspected

Number of SGA free buffer inspected.

sga.freeBufferWaits

Number of SGA free buffer waits.

sga.hitRatio

Hit ratio for the SGA.

sga.logBufferAllocationRetriesRatio

Retry ratio of allocations for the SGA log buffer.

sga.logBufferRedoAllocationRetries

Redo allocation ratio for the SGA log buffer.

sga.logBufferRedoEntries

Number of Redo entries in the SGA log buffer.

sga.logBufferSpaceWaits

Buffer space waits for the SGA log buffer.

sga.redoBuffersInBytes

SGA Redo buffers, in bytes.

sga.sharedPoolDictCacheMissRatio

Miss ratio for the SGA shared pool dictionary (dict) cache.

sga.sharedPoolLibraryCacheHitRatio

Hit ratio for the SGA shared pool library cache.

sga.sharedPoolLibraryCacheReloadRatio

Reload ratio for the SGA shared pool library cache.

sga.sharedPoolLibraryCacheShareableMemoryPerStatementInBytes

SGA cacheable memory per statement, in bytes.

sga.sharedPoolLibraryCacheShareableMemoryPerUserInBytes

SGA cacheable memory per user, in bytes.

sga.ugaTotalMemoryInBytes

Total memory in the User Global Area (UGA).

sorts.diskInBytes

Sorts disk usage, in bytes.

sorts.memoryInBytes

Sorts memory usage, in bytes.

Tablespace metrics

The Oracle Database integration collects the following tablespace metrics. These attributes can be found by querying the OracleTablespaceSample event type. Please note that tablespace usage metrics are not available for read-only standby databases - read more about this limitation in the Oracle Knowledgebase here.

Metric

Description

Extended

tablespace.isOffline

Boolean for tablespace offline status.

tablespace.offlinePDBDatafiles

The number of PDB datafiles that are offline.

tablespace.offlineCDBDatafiles

The number of CDB datafiles that are offline.

tablespace.pdbDatafilesNonWrite

The number of PDB datafiles in a non-writable state.

tablespace.spaceConsumedInBytes

Consumed amount of tablespace in bytes.

tablespace.spaceReservedInBytes

Total reserved tablespace in bytes.

tablespace.spaceUsedPercentage

Ratio of used to total tablespace.

Inventory data

The Oracle Database integration captures the configuration parameters of the Oracle database. The data is available on the Inventory page, under the config/oracledb source. For more about inventory data, see Understand integration data.

The integration captures data for the following Oracle Database configuration parameters:

Troubleshooting

Troubleshooting tips:

Check the source code

This integration is open source software. That means you can browse its source code and send improvements, or create your own fork and build it.

Copyright © 2024 New Relic Inc.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.