OracleDB monitoring integration

New Relic Infrastructure's OracleDB integration reports data from your Oracle database to New Relic Infrastructure. This document explains how to install and configure the OracleDB integration and describes the data collected.

Access to this feature depends on your subscription level. Requires Infrastructure Pro.

Features

The New Relic OracleDB on-host integration collects key performance metrics on databases, tablespaces, and memory by default. Optionally, the integration can be configured to collect a number of extended metrics, which provide a more finely detailed characterization of database performance for increased visibility and more accurate diagnoses.

Compatibility and requirements

To use the OracleDB integration, ensure your system meets these requirements:

OracleDB users and privileges

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

  1. Use ALTER SESSION to access the database and manage users and user properties.
    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:
    • gv_$sysmetric
    • gv_$pgastat
    • gv_$instance
    • gv_$filestat
    • gv_$parameter
    • sys.dba_data_files
    You can execute the following SQL statements in together one script or individually.
    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;

Install

On-host integrations do not automatically update. For best results, you should occasionally update the integration and update the Infrastructure agent.

To install the OracleDB integration:

  1. Follow the instructions for installing an integration, using the file name nri-oracledb.
  2. Via the command line, change directory to the integrations folder:
    cd /etc/newrelic-infra/integrations.d
  3. Create a copy of the sample configuration file by running:
    sudo cp oracledb-config.yml.sample oracledb-config.yml
  4. Edit the oracledb-config.yml configuration file using the configuration settings.
  5. Restart the infrastructure agent.

Configure

Use the OracleDB integration's oracledb-config.yml configuration file to put required login credentials and configure how data is collected. For an example configuration, see the example config file.

Commands

The oracledb-config.yml file provides one command:

  • all_data: collects both inventory and metric data.

Arguments

The all_data command accepts the following arguments:

  • service_name: OracleDB 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.

  • 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 OracleDB is running. Default: 1521.

  • extended_metrics: Boolean value that indicates whether to collect extended metrics. Default: false.

Labels

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

Example oracledb-config.yml file configuration:

Example configuration
integration_name: com.newrelic.oracledb

instances:
  - name: oracledb
    command: all_data
    arguments: 
        username: oracle_user
        password: oracle_password
        hostname: oracle-host.localnet
        is_sys_dba: true
        service_name: ORCL
    labels: 
        env: staging

Find and use data

To find your integration data in Infrastructure, go to infrastructure.newrelic.com > Integrations > On-host integrations and select one of the OracleDB integration links.

In New Relic Insights, OracleDB data is attached to the following Insights event types:

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

Metric data

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

These attributes can be found by querying the OracleDatabaseSample event types in Insights.

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.

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.

The OracleDB integration collects the following tablespace metrics. These attributes can be found by querying the OracleTablespaceSample event type in Insights.

Metric Description

tablespace.isOffline

Boolean for tablespace offline status. Extended: yes.

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 OracleDB integration captures the configuration parameters of the Oracle database. The data is available on the Infrastructure Inventory page, under the config/oracledb source. For more about inventory data, see Understand integration data.

The integration captures data for the following OracleDB configuration parameters:

Parameters
Metric Description

DBFIPS_140

Enable use of crypographic libraries in FIPS mode, public.

O7_DICTIONARY_ACCESSIBILITY

Version 7 dictionary accessibility support.

active_instance_count

Number of active instances in the cluster database.

adg_account_info_tracking

ADG user account info tracked in standby (LOCAL) or in primary (GLOBAL).

allow_global_dblinks

LDAP lookup for DBLINKS.

allow_group_access_to_sga

Allow read access for SGA to users of Oracle owner group.

approx_for_aggregation

Replace exact_aggregation with approximate_aggregation.

approx_for_count_distinct

Replace count_distinct with approx_count_distinct.

approx_for_percentile

Replace percentile_* with approx_percentile.

aq_tm_processes

Number of AQ time managers to start.

archive_lag_target

Maximum number of seconds of redos the standby could lose.

asm_diskstring

Disk set locations for discovery.

asm_preferred_read_failure_groups

Preferred read failure groups.

audit_file_dest

Directory in which auditing files are to reside.

audit_sys_operations

Enable sys auditing.

audit_syslog_level

Syslog facility and level.

audit_trail

Enable system auditing.

autotask_max_active_pdbs

Setting for autotask maximum maintenance PDBs.

awr_pdb_autoflush_enabled

Enable/Disable AWR automatic PDB flushing.

awr_pdb_max_parallel_slaves

Maximum concurrent AWR PDB MMON slaves per instance.

awr_snapshot_time_offset

Setting for AWR snapshot time offset.

background_core_dump

Core size for background processes.

background_dump_dest

Detached process dump directory.

backup_tape_io_slaves

Backup tape I/O slaves.

bitmap_merge_area_size

Maximum memory allow for bitmap merge.

blank_trimming

Blank trimming semantics parameter.

buffer_pool_keep

Number of database blocks/latches in keep buffer pool.

buffer_pool_recycle

Number of database blocks/latches in recycle buffer pool.

cdb_cluster

If TRUE startup in CDB cluster mode.

cdb_cluster_name

CDB cluster name.

cell_offload_compaction

Cell packet compaction strategy.

cell_offload_decryption

Enable SQL processing offload of encrypted data to cells.

cell_offload_parameters

Additional cell offload parameters.

cell_offload_plan_display

Cell offload explain plan display.

cell_offload_processing

Enable SQL processing offload to cells.

cell_offloadgroup_name

Set the offload group name.

circuits

Max number of circuits.

client_result_cache_lag

Client result cache maximum lag in milliseconds.

client_result_cache_size

Client result cache max size in bytes.

clonedb

Clone database.

clonedb_dir

CloneDB Directory.

cluster_database

If TRUE startup in cluster database mode.

cluster_database_instances

Number of instances to use for sizing cluster DB SGA structures.

cluster_interconnects

Interconnects for RAC use.

commit_logging

Transaction commit log write behaviour.

commit_point_strength

Bias this node has toward not preparing in a two-phase commit.

commit_wait

Transaction commit log wait behaviour.

commit_write

Transaction commit log write behaviour.

common_user_prefix

Enforce restriction on a prefix of a common user, role, or profile .

compatible

Database will be completely compatible with this software version.

connection_brokers

Connection brokers specification.

containers_parallel_degree

Parallel degree for a CONTAINERS() query.

control_file_record_keep_time

Control file record keep time in days.

control_files

Control file names list.

control_management_pack_access

Declares which manageability packs are enabled.

core_dump_dest

Core dump directory.

cpu_count

Number of CPUs for this instance.

create_bitmap_area_size

Size of create bitmap buffer for bitmap index.

create_stored_outlines

Create stored outlines for DML statements.

cursor_bind_capture_destination

Allowed destination for captured bind variables.

cursor_invalidation

Default for DDL cursor invalidation semantics.

cursor_sharing

Cursor sharing mode.

cursor_space_for_time

Use more memory in order to get faster execution.

data_guard_sync_latency

Data guard sync latency.

data_transfer_cache_size

Size of data transfer cache.

db_16k_cache_size

Size of cache for 16K buffers.

db_2k_cache_size

Size of cache for 2K buffers.

db_32k_cache_size

Size of cache for 32K buffers.

db_4k_cache_size

Size of cache for 4K buffers.

db_8k_cache_size

Size of cache for 8K buffers.

db_big_table_cache_percent_target

Big table cache target size in percentage.

db_block_buffers

Number of database blocks cached in memory.

db_block_checking

Header checking and data and index block checking.

db_block_checksum

Store checksum in DB blocks and check during reads.

db_block_size

Size of database block in bytes.

db_cache_advice

Buffer cache sizing advisory.

db_cache_size

Size of default buffer pool for standard block size buffers.

db_create_file_dest

Default database location.

db_create_online_log_dest_1

Online log/controlfile destination #1.

db_create_online_log_dest_2

Online log/controlfile destination #2.

db_create_online_log_dest_3

Online log/controlfile destination #3.

db_create_online_log_dest_4

Online log/controlfile destination #4.

db_create_online_log_dest_5

Online log/controlfile destination #5.

db_domain

Directory part of global database name stored with CREATE DATABASE.

db_file_multiblock_read_count

DB block to be read each I/O.

db_file_name_convert

Datafile name convert patterns and strings for standby/clone db.

db_files

Max allowable number of db files.

db_flash_cache_file

Flash cache file for default block size.

db_flash_cache_size

Flash cache size for db_flash_cache_file.

db_flashback_retention_target

Maximum flashback database log retention time in minutes..

db_index_compression_inheritance

Options for table or tablespace level compression inheritance.

db_keep_cache_size

Size of KEEP buffer pool for standard block size buffers.

db_lost_write_protect

Enable lost write detection.

db_name

Database name specified in CREATE DATABASE.

db_performance_profile

Database performance category.

db_recovery_file_dest

Default database recovery file location.

db_recovery_file_dest_size

Database recovery files size limit.

db_recycle_cache_size

Size of RECYCLE buffer pool for standard block size buffers.

db_securefile

Permit securefile storage during lob creation.

db_ultra_safe

Sets defaults for other parameters that control protection levels.

db_unique_name

Database unique name.

db_unrecoverable_scn_tracking

Track nologging SCN in controlfile.

db_writer_processes

Number of background database writer processes to start.

dbwr_io_slaves

DBWR I/O slaves.

ddl_lock_timeout

Timeout to restrict the time that DDLS wait for DML lock.

default_sharing

Default sharing clause.

deferred_segment_creation

Defer segment creation to first insert.

dg_broker_config_file1

Data guard broker configuration file #1.

dg_broker_config_file2

Data guard broker configuration file #2.

dg_broker_start

Start data guard broker (DMON process).

diagnostic_dest

Diagnostic base directory.

disable_pdb_feature

Disable features.

disk_asynch_io

Use asynch I/O for random access devices.

dispatchers

Specifications of dispatchers.

distributed_lock_timeout

Number of seconds a distributed transaction waits for a lock.

dml_locks

DML locks - one for each table modified in a transaction.

dnfs_batch_size

Max number of dNFS asynch I/O requests queued per session.

dst_upgrade_insert_conv

Enables/Disables internal conversions during DST upgrade.

enable_automatic_maintenance_pdb

Enable/Disable automated maintenance for non-root PDB.

enable_ddl_logging

Enable ddl logging.

enable_dnfs_dispatcher

Enable DNFS dispatcher.

enable_goldengate_replication

Goldengate replication enabled.

enable_pluggable_database

Enable pluggable database.

enabled_PDBs_on_standby

List of enabled PDB patterns.

encrypt_new_tablespaces

Whether to encrypt newly created tablespaces.

event

Debug event control - default null string.

exafusion_enabled

Enable exafusion.

external_keystore_credential_location

External keystore credential location.

fal_client

FAL client.

fal_server

FAL server list.

fast_start_io_target

Upper bound on recovery reads.

fast_start_mttr_target

MTTR target in seconds.

fast_start_parallel_rollback

Max number of parallel recovery slaves that may be used.

file_mapping

Enable file mapping.

fileio_network_adapters

Network adapters for File I/O.

filesystemio_options

IO operations on filesystem files.

fixed_date

Fixed SYSDATE value.

forward_listener

Forward listener.

gcs_server_processes

Number of background gcs server processes to start.

global_names

Enforce that database links have same name as remote database.

global_txn_processes

Number of background global transaction processes to start.

hash_area_size

Size of in-memory hash work area.

heat_map

ILM Heatmap Tracking.

hi_shared_memory_address

SGA starting address (high order 32-bits on 64-bit platforms).

hs_autoregister

Enable automatic server DD updates in HS agent self-registration.

ifile

Include file in init.Ora.

inmemory_adg_enabled

Enable IMC support on ADG.

inmemory_automatic_level

Enable Automatic In-Memory management.

inmemory_clause_default

Default in-memory clause for new tables.

inmemory_expressions_usage

Controls which In-Memory Expressions are populated in-memory.

inmemory_force

Force tables to be in-memory or not.

inmemory_max_populate_servers

Maximum inmemory populate servers.

inmemory_optimized_arithmetic

Controls whether or not DSBs are stored in-memory.

inmemory_prefer_xmem_memcompress

Prefer to store tables with given memcompress levels in xmem.

inmemory_prefer_xmem_priority

Prefer to store tables with given priority levels in xmem.

inmemory_query

Specifies whether in-memory queries are allowed.

inmemory_size

Size in bytes of in-memory area.

inmemory_trickle_repopulate_servers_percent

Inmemory trickle repopulate servers percent.

inmemory_virtual_columns

Controls which user-defined virtual columns are stored in-memory.

inmemory_xmem_size

Size in bytes of in-memory xmem area.

instance_abort_delay_time

Time to delay an internal initiated abort (in seconds).

instance_groups

List of instance group names.

instance_mode

Indicates whether the instance read-only or read-write or read-mostly.

instance_name

Instance name supported by the instance.

instance_number

Instance number.

instance_type

Type of instance to be executed.

instant_restore

Instant repopulation of datafiles.

java_jit_enabled

Java VM JIT enabled.

java_max_sessionspace_size

Max allowed size in bytes of a Java sessionspace.

java_pool_size

Size in bytes of java pool.

java_restrict

Restrict Java VM Access.

java_soft_sessionspace_limit

Warning limit on size in bytes of a Java sessionspace.

job_queue_processes

Maximum number of job queue slave processes.

large_pool_size

Size in bytes of large pool.

ldap_directory_access

RDBMS's LDAP access option.

ldap_directory_sysauth

OID usage parameter.

license_max_sessions

Maximum number of non-system user sessions allowed.

license_max_users

Maximum number of named users that can be created in the database.

license_sessions_warning

Warning level for number of non-system user sessions.

listener_networks

Listener registration networks.

local_listener

Local listener.

lock_name_space

Lock name space used for generating lock names for standby/clone database.

lock_sga

Lock entire SGA in physical memory.

log_archive_config

Log archive config.

log_archive_dest

Archival destination text string.

log_archive_dest_1

Archival destination #1 text string.

log_archive_dest_10

Archival destination #10 text string.

log_archive_dest_11

Archival destination #11 text string.

log_archive_dest_12

Archival destination #12 text string.

log_archive_dest_13

Archival destination #13 text string.

log_archive_dest_14

Archival destination #14 text string.

log_archive_dest_15

Archival destination #15 text string.

log_archive_dest_16

Archival destination #16 text string.

log_archive_dest_17

Archival destination #17 text string.

log_archive_dest_18

Archival destination #18 text string.

log_archive_dest_19

Archival destination #19 text string.

log_archive_dest_2

Archival destination #2 text string.

log_archive_dest_20

Archival destination #20 text string.

log_archive_dest_21

Archival destination #21 text string.

log_archive_dest_22

Archival destination #22 text string.

log_archive_dest_23

Archival destination #23 text string.

log_archive_dest_24

Archival destination #24 text string.

log_archive_dest_25

Archival destination #25 text string.

log_archive_dest_26

Archival destination #26 text string.

log_archive_dest_27

Archival destination #27 text string.

log_archive_dest_28

Archival destination #28 text string.

log_archive_dest_29

Archival destination #29 text string.

log_archive_dest_3

Archival destination #3 text string.

log_archive_dest_30

Archival destination #30 text string.

log_archive_dest_31

Archival destination #31 text string.

log_archive_dest_4

Archival destination #4 text string.

log_archive_dest_5

Archival destination #5 text string.

log_archive_dest_6

Archival destination #6 text string.

log_archive_dest_7

Archival destination #7 text string.

log_archive_dest_8

Archival destination #8 text string.

log_archive_dest_9

Archival destination #9 text string.

log_archive_dest_state_1

Archival destination #1 state text string.

log_archive_dest_state_10

Archival destination #10 state text string.

log_archive_dest_state_11

Archival destination #11 state text string.

log_archive_dest_state_12

Archival destination #12 state text string.

log_archive_dest_state_13

Archival destination #13 state text string.

log_archive_dest_state_14

Archival destination #14 state text string.

log_archive_dest_state_15

Archival destination #15 state text string.

log_archive_dest_state_16

Archival destination #16 state text string.

log_archive_dest_state_17

Archival destination #17 state text string.

log_archive_dest_state_18

Archival destination #18 state text string.

log_archive_dest_state_19

Archival destination #19 state text string.

log_archive_dest_state_2

Archival destination #2 state text string.

log_archive_dest_state_20

Archival destination #20 state text string.

log_archive_dest_state_21

Archival destination #21 state text string.

log_archive_dest_state_22

Archival destination #22 state text string.

log_archive_dest_state_23

Archival destination #23 state text string.

log_archive_dest_state_24

Archival destination #24 state text string.

log_archive_dest_state_25

Archival destination #25 state text string.

log_archive_dest_state_26

Archival destination #26 state text string.

log_archive_dest_state_27

Archival destination #27 state text string.

log_archive_dest_state_28

Archival destination #28 state text string.

log_archive_dest_state_29

Archival destination #29 state text string.

log_archive_dest_state_3

Archival destination #3 state text string.

log_archive_dest_state_30

Archival destination #30 state text string.

log_archive_dest_state_31

Archival destination #31 state text string.

log_archive_dest_state_4

Archival destination #4 state text string.

log_archive_dest_state_5

Archival destination #5 state text string.

log_archive_dest_state_6

Archival destination #6 state text string.

log_archive_dest_state_7

Archival destination #7 state text string.

log_archive_dest_state_8

Archival destination #8 state text string.

log_archive_dest_state_9

Archival destination #9 state text string.

log_archive_duplex_dest

duplex Archival destination text string.

log_archive_format

Archival destination format.

log_archive_max_processes

Maximum number of active ARCH processes.

log_archive_min_succeed_dest

Minimum number of archive destinations that must succeed.

log_archive_start

Start archival process on SGA initialization.

log_archive_trace

Establish archive operation tracing level.

log_buffer

Redo circular buffer size.

log_checkpoint_interval

Number of redo blocks checkpoint threshold.

log_checkpoint_timeout

Maximum time interval between checkpoints in seconds.

log_checkpoints_to_alert

Log checkpoint begin/end to alert file.

log_file_name_convert

Logfile name convert patterns and strings for standby/clone db.

long_module_action

Use longer module and action.

max_datapump_jobs_per_pdb

Maximum number of concurrent data pump jobs per PDB.

max_dispatchers

Max number of dispatchers.

max_dump_file_size

Maximum size (in bytes) of dump file.

max_idle_time

Maximum session idle time in minutes.

max_iops

MAX I/O per second.

max_mbps

MAX MB per second.

max_pdbs

Max number of pdbs allowed in CDB or Application ROOT.

max_shared_servers

Max number of shared servers.

max_string_size

Controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL.

memoptimize_pool_size

Size of cache for imoltp buffers.

memory_max_target

Max size for Memory Target.

memory_target

Target size of Oracle SGA and PGA memory.

multishard_query_data_consistency

Consistency setting for multishard queries.

multishard_query_partial_results

Enable partial results for multishard queries.

nls_calendar

NLS calendar system name.

nls_comp

NLS comparison.

nls_currency

NLS local currency symbol.

nls_date_format

NLS Oracle date format.

nls_date_language

NLS date language name.

nls_dual_currency

Dual currency symbol.

nls_iso_currency

NLS ISO currency territory name.

nls_language

NLS language name.

nls_length_semantics

Create columns using byte or char semantics by default.

nls_nchar_conv_excp

NLS raise an exception instead of allowing implicit conversion.

nls_numeric_characters

NLS numeric characters.

nls_sort

NLS linguistic definition name.

nls_territory

NLS territory name.

nls_time_format

Time format.

nls_time_tz_format

Time with timezone format.

nls_timestamp_format

Time stamp format.

nls_timestamp_tz_format

Timestamp with timezone format.

noncdb_compatible

Non-CDB Compatible.

object_cache_max_size_percent

Percentage of maximum size over optimal of the user session's object cache.

object_cache_optimal_size

Optimal size of the user session's object cache in bytes.

ofs_threads

Number of OFS threads.

olap_page_pool_size

Size of the olap page pool in bytes.

one_step_plugin_for_pdb_with_tde

Facilitate one-step plugin for PDB with TDE encrypted data.

open_cursors

Max number of cursors per session.

open_links

Max number of open links per session.

open_links_per_instance

Max number of open links per instance.

optimizer_adaptive_plans

Controls all types of adaptive plans.

optimizer_adaptive_reporting_only

Use reporting-only mode for adaptive optimizations.

optimizer_adaptive_statistics

Controls all types of adaptive statistics.

optimizer_capture_sql_plan_baselines

Automatic capture of SQL plan baselines for repeatable statements.

optimizer_dynamic_sampling

Optimizer dynamic sampling.

optimizer_features_enable

Optimizer plan compatibility parameter.

optimizer_ignore_hints

Enables the embedded hints to be ignored.

optimizer_ignore_parallel_hints

Enables embedded parallel hints to be ignored.

optimizer_index_caching

Optimizer percent index caching.

optimizer_index_cost_adj

Optimizer index cost adjustment.

optimizer_inmemory_aware

Optimizer in-memory columnar awareness.

optimizer_mode

Optimizer mode.

optimizer_secure_view_merging

Optimizer secure view merging and predicate pushdown/movearound.

optimizer_use_invisible_indexes

Usage of invisible indexes (TRUE or FALSE).

optimizer_use_pending_statistics

Control whether to use optimizer pending statistics.

optimizer_use_sql_plan_baselines

Use of SQL plan baselines for captured sql statements.

os_authent_prefix

Prefix for auto-logon accounts.

os_roles

Retrieve roles from the operating system.

outbound_dblink_protocols

Outbound DBLINK Protocols allowed.

parallel_adaptive_multi_user

Enable adaptive setting of degree for multiple user streams.

parallel_degree_limit

Limit placed on degree of parallelism.

parallel_degree_policy

Policy used to compute the degree of parallelism (MANUAL, LIMITED, AUTO, or ADAPTIVE).

parallel_execution_message_size

Message buffer size for parallel execution.

parallel_force_local

Force single instance execution.

parallel_instance_group

Instance group to use for all parallel operations.

parallel_max_servers

Maximum parallel query servers per instance.

parallel_min_degree

Controls the minimum DOP computed by auto DOP.

parallel_min_percent

Minimum percent of threads required for parallel query.

parallel_min_servers

Minimum parallel query servers per instance.

parallel_min_time_threshold

Threshold above which a plan is a candidate for parallelization (in seconds).

parallel_servers_target

Instance target in terms of number of parallel servers.

parallel_threads_per_cpu

Number of parallel execution threads per CPU.

pdb_file_name_convert

PDB file name convert patterns and strings for create cdb/pdb.

pdb_lockdown

Pluggable database lockdown profile.

pdb_os_credential

Pluggable database OS credential to bind.

pdb_template

PDB template.

permit_92_wrap_format

Allow 9.2 or older wrap format in PL/SQL.

pga_aggregate_limit

Limit of aggregate PGA memory for the instance or PDB.

pga_aggregate_target

Target size for the aggregate PGA memory consumed by the instance.

plscope_settings

Plscope_settings controls the compile time collection, cross reference, and storage of PL/SQL source code identifier and SQL statement data.

plsql_ccflags

PL/SQL ccflags.

plsql_code_type

PL/SQL code-type.

plsql_debug

PL/SQL debug.

plsql_optimize_level

PL/SQL optimize level.

plsql_v2_compatibility

PL/SQL version 2.X compatibility flag.

plsql_warnings

PL/SQL compiler warnings settings.

pre_page_sga

Pre-page sga for process.

private_temp_table_prefix

Private temporary table prefix.

processes

User processes.

processor_group_name

Name of the processor group that this instance should run in.

query_rewrite_enabled

Allow rewrite of queries using materialized views if enabled.

query_rewrite_integrity

Perform rewrite using materialized views with desired integrity.

rdbms_server_dn

RDBMS's distinguished name.

read_only_open_delayed

If TRUE delay opening of read only files until first access.

recovery_parallelism

Number of server processes to use for parallel recovery.

recyclebin

Recyclebin processing.

redo_transport_user

Data guard transport user when using password file.

remote_dependencies_mode

Remote-procedure-call dependencies mode parameter.

remote_listener

Remote listener.

remote_login_passwordfile

Password file usage parameter.

remote_os_authent

Allow non-secure remote clients to use auto-logon accounts.

remote_os_roles

Allow non-secure remote clients to use os roles.

remote_recovery_file_dest

Default remote database recovery file location for refresh/relocate.

replication_dependency_tracking

Tracking dependency for replication parallel propagation.

resource_limit

Master switch for resource limit.

resource_manage_goldengate

Goldengate resource manager enabled.

resource_manager_cpu_allocation

Resource Manager CPU allocation.

resource_manager_plan

Resource mgr top plan.

result_cache_max_result

Maximum result size as percent of cache size.

result_cache_max_size

Maximum amount of memory to be used by the cache.

result_cache_mode

Result cache operator usage mode.

result_cache_remote_expiration

Maximum life time (min) for any result using a remote object.

resumable_timeout

Set resumable timeout.

rollback_segments

Undo segment list.

sec_case_sensitive_logon

Case sensitive password enabled for logon.

sec_max_failed_login_attempts

Maximum number of failed login attempts on a connection.

sec_protocol_error_further_action

TTC protocol error continue action.

sec_protocol_error_trace_action

TTC protocol error action.

sec_return_server_release_banner

Whether the server returns the complete version information.

serial_reuse

Reuse the frame segments.

service_names

Service names supported by the instance.

session_cached_cursors

Number of cursors to cache in a session.

session_max_open_files

Maximum number of open files allowed per session.

sessions

User and system sessions.

sga_max_size

Max total SGA size.

sga_min_size

Minimum, guaranteed size of PDB's SGA.

sga_target

Target size of SGA.

shadow_core_dump

Core size for shadow processes.

shared_memory_address

SGA starting address (low order 32-bits on 64-bit platforms).

shared_pool_reserved_size

Size in bytes of reserved area of shared pool.

shared_pool_size

Size in bytes of shared pool.

shared_server_sessions

Max number of shared server sessions.

shared_servers

Number of shared servers to start up.

shrd_dupl_table_refresh_rate

Duplicated table refresh rate (in seconds).

skip_unusable_indexes

Skip unusable indexes if set to TRUE.

smtp_out_server

Utl_smtp server and port configuration parameter.

sort_area_retained_size

Size of in-memory sort work area retained between fetch calls.

sort_area_size

Size of in-memory sort work area.

spatial_vector_acceleration

Enable spatial vector acceleration.

spfile

Server parameter file.

sql92_security

Require select privilege for searched update/delete.

sql_trace

Enable SQL trace.

sqltune_category

Category qualifier for applying hintsets.

standby_db_preserve_states

Preserve state cross standby role transition.

standby_file_management

If auto, files are created/dropped automatically on standby.

standby_pdb_source_file_dblink

Database link to standby source files.

standby_pdb_source_file_directory

Standby source file directory location.

star_transformation_enabled

Enable the use of star transformation.

statistics_level

Statistics level.

streams_pool_size

Size in bytes of the streams pool.

tape_asynch_io

Use asynch I/O requests for tape devices.

target_pdbs

Parameter is a hint to adjust certain attributes of the CDB.

tde_configuration

Per-PDB configuration for transparent tata encryption.

temp_undo_enabled

Is temporary undo enabled.

thread

Redo thread to mount.

threaded_execution

Threaded Execution Mode.

timed_os_statistics

Internal os statistic gathering interval in seconds.

timed_statistics

Maintain internal timing statistics.

trace_enabled

Enable in memory tracing.

tracefile_identifier

Trace file custom identifier.

transactions

Max. Number of concurrent active transactions.

transactions_per_rollback_segment

Number of active transactions per rollback segment.

undo_management

Instance runs in SMU mode if TRUE, else in RBU mode.

undo_retention

Undo retention in seconds.

undo_tablespace

Use/switch undo tablespace.

unified_audit_sga_queue_size

Size of unified audit SGA queue.

unified_audit_systemlog

Syslog facility and level for unified audit.

uniform_log_timestamp_format

Use uniform timestamp formats vs pre-12.2 formats.

use_dedicated_broker

Use dedicated connection broker.

use_large_pages

Use large pages if available (TRUE, FALSE, or ONLY).

user_dump_dest

User process dump directory.

version

OracleDB version.

xwallet_root

Wallet root instance initialization parameter.

workarea_size_policy

Policy used to size SQL working areas (MANUALAUTO).

Troubleshooting

Troubleshooting tips:

The Oracle library cannot be loaded

If monitoring remotely, install the Oracle Instant Client and follow the instructions on how to add libclntsh.so to the shared library search path.

If monitoring from the box with OracleDB installed, install the Oracle Instant Client and add the path ORACLE_HOME/lib to the ldconfig search path.

ORACLE_HOME is not set correctly

This error will appear in the logs as [ERR] ORA-01284: Error while trying to retrieve text for error.

To avoid this error make sure ORACLE_HOME is set correctly for the agent process. The agent runs as root, so its environment is not the same as the oracle user.

To verify this setting, execute cat /proc/$(pgrep newrelic-infra)/environ to print out the environment variables for the infrastructure process, the output should include ORACLE_HOME if configured correctly.

For more help

Recommendations for learning more: