• /
  • ログイン

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:

Oracle Database users and privileges

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

    ヒント

    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;

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:

Configure the integration

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.

For an example configuration, see the example config file.

重要

With secrets management, you can configure on-host integrations with New Relic infrastructure's agent to use sensitive data (such as passwords) without having to write them as plain text into the integration's configuration file. For more information, see Secrets management.

Commands

The oracledb-config.yml file accepts the following commands:

  • all_data: collects both inventory and metric data.

Arguments

The all_data command accepts the following arguments:

  • service_name: Oracle Database service name of the instance (or cluster of instances) to monitor. This field is required.
  • username: username of a user created with the required permissions. This field is required.
  • password: password of a user created with the required permissions. This field is required.
  • is_sys_dba: boolean value that indicates whether the authenticating user has SysDBA permissions. Default: false.
  • oracle_home: path to where ORACLE_HOME is. This field is required.
  • is_sys_oper: boolean value that indicates whether the authenticating user has SysOper permissions. Default: false.
  • hostname: hostname of the instance to monitor. Default: 127.0.0.1.
  • port: port number on which Oracle Database is running. Default: 1521.
  • connection_string: a full connection string such as those found in tnsnames.ora. If this is specified, it takes priority over host, port, and service_name.
  • extended_metrics: boolean value that indicates whether to collect extended metrics. Default: false.
  • custom_metrics_query: a custom SQL query to run against the configured instance. Each row of the query is added as a new metric set on OracleCustomSample. Each non-null column in the row is added as an attribute on that metric set.
  • custom_metrics_config: a path to a YAML file that contains a list of queries, along with custom sample names and metric type overrides. See example below for details.

Labels

The labels field controls the environment attribute. Default: production.

Example configuration

Example oracledb-config.yml file configuration:

For more about the general structure of on-host integration configuration, see Configuration.

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

db.activeParallelSessions

Active parallel sessions.

db.activeSerialSessions

Active serial sessions.

db.averageActiveSessions

Average active sessions. Extended: yes.

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. Extended: yes.

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. Extended: yes.

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

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). Extended: yes.

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. Extended: yes.

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. Extended: yes.

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. Extended: yes.

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. Extended: yes.

disk.blocksWritten

Number of block writes. Extended: yes.

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. Extended: yes.

disk.physicalReadIoRequestsPerSecond

Physical read total I/O requests per second. Extended: yes.

disk.physicalReadsPerSecond

Physical reads direct per second. Extended: yes.

disk.physicalWriteBytesPerSecond

Physical write total bytes per second.

disk.physicalWriteIoRequestsPerSecond

Physical write I/O requests per second.

disk.physicalWritesPerSecond

Physical writes direct per second. Extended: yes.

disk.physicalWriteTotalIoRequestsPerSecond

Physical write total I/O requests per second. Extended: yes.

disk.reads

Total number of physical reads. Extended: yes.

disk.readTime

Amount of file read time. Extended: yes.

disk.sortPerSecond

Disk sort per second.

disk.sortPerTransaction

Disk sort per transaction.

disk.tempSpaceUsed

Temp space used.

disk.writes

Total number of physical writes. Extended: yes.

disk.writeTime

Amount of file write time. Extended: yes.

lockedAccounts

Number of accounts whose account_status is not OPEN.

longRunningQueries

Number of long running (> 60s) queries.

memory.bufferCacheHitRatio

Buffer cache hit ratio. Extended: yes.

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. Extended: yes.

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. Extended: yes.

network.trafficBytePerSecond

Network traffic volume per second. Extended: yes.

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. Extended: yes.

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

tablespace.isOffline

Boolean for tablespace offline status. Extended: yes.

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. Extended: yes.

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 © 2020 New Relic Inc.