• /
  • Log in

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 versions 11.2 and higher.

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

Install and activate

To install the Oracle Database integration:

  1. Follow the instructions for installing an integration, using the file name nri-oracledb.

  2. Change directory to the integrations folder:

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

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

  5. Restart the infrastructure agent.

Additional notes:

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. If you use Oracle DB 12c or higher, use ALTER SESSION to access the database and manage users and user properties. Do not run this query if your Oracle DB version is lower than 12c.

    ALTER SESSION set "_Oracle_SCRIPT"=true;
  2. Use CREATE USER to add a new user to the database. Replace USER_PASSWORD with the new user's password.

    CREATE USER USERNAME IDENTIFIED BY "USER_PASSWORD";

    Tip

    For assistance with user maintenance questions, consult the Oracle documentation or contact your system or database administrator.

  3. Grant CONNECT privileges to the user.

    GRANT CONNECT TO USERNAME;
  4. 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

    You can 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;

Configure the integration

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

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 fiels is required.

N/A

M/I

TABLESPACES

A JSON array of tablespaces to collect. If omitted, collects all tablespaces up to a maximun of 200 tablespaces.

[]

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

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

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 Infrastructure, go to one.newrelic.com > 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.

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

Amount of file read time.

disk.sortPerSecond

Disk sort per second.

disk.sortPerTransaction

Disk sort per transaction.

disk.tempSpaceUsed

Temp space used.

disk.writes

Total number of physical writes.

disk.writeTime

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

Current amount of PGA memory allocated by the instance.

memory.pgaFreeable

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

memory.pgaInUse

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

Maximum size of a work area executed in automatic mode.

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 per transaction.

query.physicalReadsPerTransaction

Physical reads direct per transaction.

query.physicalWritesPerTransaction

Physical writes per transaction.

query.physicalWritesPerTransaction

Physical writes direct 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.freeBufferWaits

Number of SGA free buffer waits.

sga.freeBufferInspected

Number of SGA free buffer inspected.

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.

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.

For more help

If you need more help, check out these support and learning resources:

Create issueEdit page
Copyright © 2021 New Relic Inc.