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.
Our integration is compatible with Oracle Database versions 11.2 and higher.
Before installing the integration, make sure that you meet the following requirements:
- Linux distro compatible with infrastructure, except for RHEL/CentOS/OEL versions lower than 7.
- Install the infrastructure agent.
- Oracle Instant Client on the agent box.
- Oracle database with
ORACLE_HOMEconfigured to the correct directory for the root user.
- Oracle database user with both CONNECT and SELECT privileges on the required global views.
- Oracle database with a
listener.orafile configured to monitor from a remote connection. By default, Oracle Database only listens to localhost.
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.
If you use Oracle DB 12c or higher, use
ALTER SESSIONto 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;
CREATE USERto 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.
CONNECTprivileges to the user.
GRANT CONNECT TO USERNAME;
SELECTprivileges to the user on the following global views:
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;
To install the Oracle Database integration:
Follow the instructions for installing an integration, using the file name
Change directory to the integrations folder:cd /etc/newrelic-infra/integrations.d
Copy the sample configuration file:sudo cp oracledb-config.yml.sample oracledb-config.yml
oracledb-config.ymlfile as described in the configuration settings.
- Advanced: It's also possible to install the integration from a tarball file. This gives you full control over the installation and configuration process.
- On-host integrations do not automatically update. For best results, regularly update the integration package and the infrastructure agent.
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.
oracledb-config.yml file accepts the following commands:
all_data: collects both inventory and metric data.
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:
oracle_home: path to where
ORACLE_HOMEis. This field is required.
is_sys_oper: boolean value that indicates whether the authenticating user has SysOper permissions. Default:
tablespaces: A JSON array of tablespaces to collect. If omitted, collects all tablespaces. Default: none (all tablespaces). Due to performance reasons, the integration will refuse to collect tablespace data for more than 200 tablespaces. If your database has more than 200 of tablespaces, you must restrict collection to a smaller number by using the
hostname: hostname of the instance to monitor. Default:
port: port number on which Oracle Database is running. Default:
connection_string: a full connection string such as those found in
tnsnames.ora. If this is specified, it takes priority over
extended_metrics: boolean value that indicates whether to collect extended metrics. Default:
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 field controls the environment attribute. Default:
oracledb-config.yml file configuration:
For more about the general structure of on-host integration configuration, see Configuration.
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:
For more on how to find and use your data, see Understand integration 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
These attributes can be found by querying the
OracleDatabaseSample event type.
Active parallel sessions.
Active serial sessions.
Average active sessions.
Checkpoints per second.
Background CPU usage per second.
Background time per second.
DB block changes per second.
DB block changes per transaction.
DB block changes per user call.
DB block gets per second.
DB block gets per transaction.
DB block gets per user call.
Branch node splits per second.
Branch node splits per transaction.
Consistent read changes per second.
Consistent read changes per transaction.
Consistent read gets per second.
Consistent read gets per transaction.
CPU time ratio.
CPU usage per second.
CPU usage per transaction.
CR blocks created per second.
CR blocks created per transaction.
CR undo records applied per second.
CR undo records applied per transaction.
Current logons count.
Current open cursors count.
Cursor cache hit ratio.
Database time per second.
DBWR checkpoints per second.
Enqueue deadlocks per second.
Enqueue deadlocks per transaction
Enqueue requests per second
Enqueue requests per transaction.
Enqueue timeouts per second.
Enqueue timeouts per transaction.
Enqueue waits per second.
Enqueue waits per transaction.
Executions per second.
Executions per transaction.
Executions per user call.
Full index scans per second.
Full index scans per transaction.
GC CR block received per second.
GC CR block received per transaction.
GC current block received per second.
GC current block received per transaction.
Global cache average CR get time.
Global cache average current get time.
Hard parse count per second.
Hard parse count per transaction.
Host CPU usage per second.
Host CPU utilization (percentage).
Leaf node splits per second.
Leaf node splits per transaction.
Library cache hit ratio.
Library cache miss ratio.
Logical reads per second.
Logical reads per transaction.
Logons per second.
Logons per transaction.
Long table scans per second.
Long table scans per transaction.
Open cursors per second.
Open cursors per transaction.
Current OS load.
Parse failure count per second.
Parse failure count per transaction.
PGA cache hit percentage.
Process limit percentage.
Recursive calls per second.
Recursive calls per transaction.
Redo writes per second.
Redo writes per transaction.
Response time per transaction.
Row cache hit ratio.
Row cache miss ratio.
Rows per sort.
Session limit percentage.
Shared pool free percentage.
Soft parse ratio.
Total sorts per user call.
SQL service response time.
Streams pool usage percentage.
Total table scans per user call.
Total index scans per second.
Total index scans per transaction.
Total parse count per second.
Total parse count per transaction.
Total table scans per second.
Total table scans per transaction.
Transactions per logon.
User calls per second.
User calls per transaction.
User calls ratio.
User commits percentage.
User commits per second.
User limit percentage.
User rollbacks per transaction.
User rollbacks per second.
User rollback undo records applied per second.
User rollback undo records applied per transaction.
Database wait time ratio.
Number of block reads.
Number of block writes.
Logical reads per user call.
Physical reads direct lobs per second.
Physical writes direct lobs per second.
Physical read total bytes per second.
Physical read total I/O requests per second.
Physical reads direct per second.
Physical write total bytes per second.
Physical write I/O requests per second.
Physical writes direct per second.
Physical write total I/O requests per second.
Total number of physical reads.
Amount of file read time.
Disk sort per second.
Disk sort per transaction.
Temp space used.
Total number of physical writes.
Amount of file write time.
Number of accounts whose
Number of long running (> 60s) queries.
Buffer cache hit ratio.
Global cache blocks corrupted.
Global cache blocks lost.
Current amount of PGA memory allocated by the instance.
Number of bytes of PGA memory in all processes that could be freed back to the operating system.
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).
Maximum size of a work area executed in automatic mode.
Redo allocation hit ratio.
Redo generated bytes per second.
Redo generated bytes per transaction.
Memory sorts ratio.
I/O megabytes per second.
I/O requests per second.
Network traffic volume per second.
Physical reads direct lobs per transaction.
Physical writes direct lobs per transaction.
Physical reads per transaction.
Physical reads direct per transaction.
Physical writes per transaction.
Physical writes direct per transaction.
User transaction per second.
Number of redo log file switch events.
Number of redo log file switch events that need archiving.
Number of redo log file switch event checkpoints that are incomplete.
Number of redo log waits.
Number of rollback segments gets.
Ratio of waits for rollback segments.
Number of rollback segments waits.
Number of SGA buffer busy waits.
SGA fixed size.
Number of SGA free buffer waits.
Number of SGA free buffer inspected.
Hit ratio for the SGA.
Retry ratio of allocations for the SGA log buffer.
Redo allocation ratio for the SGA log buffer.
Number of Redo entries in the SGA log buffer.
Buffer space waits for the SGA log buffer.
SGA Redo buffers, in bytes.
Miss ratio for the SGA shared pool dictionary (dict) cache.
Hit ratio for the SGA shared pool library cache.
Reload ratio for the SGA shared pool library cache.
SGA cacheable memory per statement, in bytes.
SGA cacheable memory per user, in bytes.
Total memory in the User Global Area (UGA).
Sorts disk usage, in bytes.
Sorts memory usage, in bytes.
The Oracle Database integration collects the following tablespace metrics. These attributes can be found by querying the
OracleTablespaceSample event type.
Boolean for tablespace offline status.
The number of PDB datafiles that are offline.
The number of CDB datafiles that are offline.
The number of PDB datafiles in a non-writable state.
Consumed amount of tablespace in bytes.
Total reserved tablespace in bytes.
Ratio of used to total tablespace.
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:
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.
If you need more help, check out these support and learning resources:
- Browse the Explorers Hub to get help from the community and join in discussions.
- Find answers on our sites and learn how to use our support portal.
- Run New Relic Diagnostics, our troubleshooting tool for Linux, Windows, and macOS.
- Review New Relic's data security and licenses documentation.