Oracle

Oracle

Oracle Database is a multi-model database management system produced and marketed by Oracle Corporation. It is a database commonly used for running online transaction processing (OLTP), data warehousing (DW) and mixed (OLTP & DW) database workloads.

Available solutions




This template is for Zabbix version: 7.4

Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/oracle_agent2?at=release/7.4

Oracle by Zabbix agent 2

Overview

The template is developed to monitor a single DBMS Oracle Database instance with Zabbix agent 2.

Supported versions

Oracle Database 12c2 and newer.

Important! This integration queries the V$ACTIVE_SESSION_HISTORY dynamic performance view which is part of the Oracle Diagnostics Pack. Please make sure that you have the licence required for using this management pack.

Requirements

Zabbix version: 7.4 and higher.

Tested versions

This template has been tested on:

  • Oracle Database 12c2, 18c, 19c, 21c, 23c

Configuration

Zabbix should be configured according to the instructions in the Templates out of the box section.

Setup

  1. Setup and configure Zabbix agent 2 compiled with the Oracle monitoring plugin. See the setup instructions for Oracle Database plugin.

  2. Set the {$ORACLE.CONNSTRING} macro value using either <protocol(host:port)> or named session.

  3. If you want to override parameters from Zabbix agent configuration file, set the user name, password and service name in host macros ({$ORACLE.USER}, {$ORACLE.PASSWORD}, and {$ORACLE.SERVICE}).

    User can contain sysdba, sysoper, sysasm privileges. It must be used with as as a separator e.g user as sysdba, privilege can be upper or lowercase, and must be at the end of username string.

Test availability: zabbix_get -s oracle-host -k oracle.ping["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Macros used

Name Description Default
{$ORACLE.USER}

Oracle username.
{$ORACLE.PASSWORD}

Oracle user's password.
{$ORACLE.CONNSTRING}

Oracle URI or a session name.
{$ORACLE.SERVICE}

Oracle Service Name.

 ORA
{$ORACLE.DBNAME.MATCHES}

Used in database discovery. It can be overridden on the host or linked template level.

 .*
{$ORACLE.DBNAME.NOT_MATCHES}

Used in database discovery. It can be overridden on the host or linked template level.

 PDB\$SEED
{$ORACLE.TABLESPACE.CONTAINER.MATCHES}

Used in tablespace discovery. It can be overridden on the host or linked template level.

 .*
{$ORACLE.TABLESPACE.CONTAINER.NOT_MATCHES}

Used in tablespace discovery. It can be overridden on the host or linked template level.

 CHANGE_IF_NEEDED
{$ORACLE.TABLESPACE.NAME.MATCHES}

Used in tablespace discovery. It can be overridden on the host or linked template level.

 .*
{$ORACLE.TABLESPACE.NAME.NOT_MATCHES}

Used in tablespace discovery. It can be overridden on the host or linked template level.

 CHANGE_IF_NEEDED
{$ORACLE.TBS.USED.PCT.FROM.MAX.WARN}

Warning severity alert threshold for the maximum percentage of tablespace usage from maximum tablespace size (used bytes/max bytes) for the Warning trigger expression.

 90
{$ORACLE.TBS.USED.PCT.FROM.MAX.HIGH}

High severity alert threshold for the maximum percentage of tablespace usage (used bytes/max bytes) for the High trigger expression.

 95
{$ORACLE.TBS.USED.PCT.MAX.WARN}

Warning severity alert threshold for the maximum percentage of tablespace usage (used bytes/allocated bytes) for the Warning trigger expression.

 90
{$ORACLE.TBS.USED.PCT.MAX.HIGH}

High severity alert threshold for the maximum percentage of tablespace usage (used bytes/allocated bytes) for the High trigger expression.

 95
{$ORACLE.TBS.UTIL.PCT.MAX.WARN}

Warning severity alert threshold for the maximum percentage of tablespace utilization (allocated bytes/max bytes) for the High trigger expression.

 80
{$ORACLE.TBS.UTIL.PCT.MAX.HIGH}

High severity alert threshold for the maximum percentage of tablespace utilization (allocated bytes/max bytes) for the High trigger expression.

 90
{$ORACLE.PROCESSES.MAX.WARN}

Alert threshold for the maximum percentage of active processes for the Warning trigger expression.

 80
{$ORACLE.SESSIONS.MAX.WARN}

Alert threshold for the maximum percentage of active sessions for the Warning trigger expression.

 80
{$ORACLE.DB.FILE.MAX.WARN}

The maximum percentage of used database files for the Warning trigger expression.

 80
{$ORACLE.PGA.USE.MAX.WARN}

Alert threshold for the maximum percentage of the Program Global Area (PGA) usage for the Warning trigger expression.

 90
{$ORACLE.SESSIONS.LOCK.MAX.WARN}

Alert threshold for the maximum percentage of locked sessions for the Warning trigger expression.

 20
{$ORACLE.SESSION.LOCK.MAX.TIME}

The maximum duration of the session lock in seconds to count the session as a prolongedly locked query.

 600
{$ORACLE.SESSION.LONG.LOCK.MAX.WARN}

Alert threshold for the maximum number of the prolongedly locked sessions for the Warning trigger expression.

 3
{$ORACLE.CONCURRENCY.MAX.WARN}

The maximum percentage of session concurrency for the Warning trigger expression.

 80
{$ORACLE.REDO.MIN.WARN}

Alert threshold for the minimum number of redo logs for the Warning trigger expression.

 3
{$ORACLE.SHARED.FREE.MIN.WARN}

Alert threshold for the minimum percentage of free shared pool for the Warning trigger expression.

 5
{$ORACLE.EXPIRE.PASSWORD.MIN.WARN}

The number of days before the password expires for the Warning trigger expression.

 7
{$ORACLE.ASM.USED.PCT.MAX.WARN}

The maximum percentage of used space in the Automatic Storage Management (ASM) disk group for the Warning trigger expression.

 90
{$ORACLE.ASM.USED.PCT.MAX.HIGH}

The maximum percentage of used space in the Automatic Storage Management (ASM) disk group for the High trigger expression.

 95

Items

Name Description Type Key and additional info
Ping

Test the connection to Oracle Database state.

 Zabbix agent oracle.ping["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Preprocessing

  • Discard unchanged with heartbeat: 10m
Get instance state

Gets the state of the current instance.

 Zabbix agent oracle.instance.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Version

The Oracle Server version.

 Zabbix agent oracle.version["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Preprocessing

  • Discard unchanged with heartbeat: 12h
Uptime

The Oracle instance uptime expressed in seconds.

 Dependent item oracle.uptime

Preprocessing

  • JSON Path: $.uptime
Instance status

The status of the instance.

 Dependent item oracle.instance_status

Preprocessing

  • JSON Path: $.status
Archiver state

The status of automatic archiving.

 Dependent item oracle.archiver_state

Preprocessing

  • JSON Path: $..archiver.first()
Instance name

The name of the instance.

 Dependent item oracle.instance_name

Preprocessing

  • JSON Path: $.instance
Instance hostname

The name of the host machine.

 Dependent item oracle.instance_hostname

Preprocessing

  • JSON Path: $..hostname.first()
Instance role

Indicates whether the instance is an active instance or an inactive secondary instance.

 Dependent item oracle.instance.role

Preprocessing

  • JSON Path: $.role
Get system metrics

Gets the values of the system metrics.

 Zabbix agent oracle.sys.metrics["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Buffer cache hit ratio

The ratio of buffer cache hits ((LogRead - PhyRead)/LogRead).

 Dependent item oracle.buffer_cache_hit_ratio

Preprocessing

  • JSON Path: $.['Buffer Cache Hit Ratio']
Cursor cache hit ratio

The ratio of cursor cache hits (CursorCacheHit/SoftParse).

 Dependent item oracle.cursor_cache_hit_ratio

Preprocessing

  • JSON Path: $.['Cursor Cache Hit Ratio']
Library cache hit ratio

The ratio of library cache hits (Hits/Pins).

 Dependent item oracle.library_cache_hit_ratio

Preprocessing

  • JSON Path: $.['Library Cache Hit Ratio']
Shared pool free %

Free memory of a shared pool expressed in %.

 Dependent item oracle.shared_pool_free

Preprocessing

  • JSON Path: $.['Shared Pool Free %']
Physical reads per second

Reads per second.

 Dependent item oracle.physical_reads_rate

Preprocessing

  • JSON Path: $.['Physical Reads Per Sec']
Physical writes per second

Writes per second.

 Dependent item oracle.physical_writes_rate

Preprocessing

  • JSON Path: $.['Physical Writes Per Sec']
Physical reads bytes per second

Read bytes per second.

 Dependent item oracle.physical_read_bytes_rate

Preprocessing

  • JSON Path: $.['Physical Read Bytes Per Sec']
Physical writes bytes per second

Write bytes per second.

 Dependent item oracle.physical_write_bytes_rate

Preprocessing

  • JSON Path: $.['Physical Write Bytes Per Sec']
Enqueue timeouts per second

Enqueue timeouts per second.

 Dependent item oracle.enqueue_timeouts_rate

Preprocessing

  • JSON Path: $.['Enqueue Timeouts Per Sec']
GC CR block received per second

The global cache (GC) and the consistent read (CR) block received per second.

 Dependent item oracle.gc_cr_block_received_rate

Preprocessing

  • JSON Path: $.['GC CR Block Received Per Second']
Global cache blocks corrupted

The number of blocks that encountered corruption or checksum failure during the interconnect.

 Dependent item oracle.cache_blocks_corrupt

Preprocessing

  • JSON Path: $.['Global Cache Blocks Corrupted']
Global cache blocks lost

The number of lost global cache blocks.

 Dependent item oracle.cache_blocks_lost

Preprocessing

  • JSON Path: $.['Global Cache Blocks Lost']
Logons per second

The number of logon attempts.

 Dependent item oracle.logons_rate

Preprocessing

  • JSON Path: $.['Logons Per Sec']
Average active sessions

The average number of active sessions at a point in time that are either working or waiting.

 Dependent item oracle.active_sessions

Preprocessing

  • JSON Path: $.['Average Active Sessions']
Active serial sessions

The number of active serial sessions.

 Dependent item oracle.active_serial_sessions

Preprocessing

  • JSON Path: $.['Active Serial Sessions']
Active parallel sessions

The number of active parallel sessions.

 Dependent item oracle.active_parallel_sessions

Preprocessing

  • JSON Path: $.['Active Parallel Sessions']
Long table scans per second

The number of long table scans per second. A table is considered long if it is not cached and if its high water mark is greater than five blocks.

 Dependent item oracle.long_table_scans_rate

Preprocessing

  • JSON Path: $.['Long Table Scans Per Sec']
SQL service response time

The Structured Query Language (SQL) service response time expressed in seconds.

 Dependent item oracle.service_response_time

Preprocessing

  • JSON Path: $.['SQL Service Response Time']

  • Custom multiplier: 0.01
User rollbacks per second

The number of times that users manually issued the ROLLBACK statement or an error occurred during the users' transactions.

 Dependent item oracle.user_rollbacks_rate

Preprocessing

  • JSON Path: $.['User Rollbacks Per Sec']
Total sorts per user call

The total sorts per user call.

 Dependent item oracle.sorts_per_user_call

Preprocessing

  • JSON Path: $.['Total Sorts Per User Call']
Rows per sort

The average number of rows per sort for all types of sorts performed.

 Dependent item oracle.rows_per_sort

Preprocessing

  • JSON Path: $.['Rows Per Sort']
Disk sort per second

The number of sorts going to disk per second.

 Dependent item oracle.disk_sorts

Preprocessing

  • JSON Path: $.['Disk Sort Per Sec']
Memory sorts ratio

The percentage of sorts (from ORDER BY clauses or index building) that are done to disk vs. in-memory.

 Dependent item oracle.memory_sorts_ratio

Preprocessing

  • JSON Path: $.['Memory Sorts Ratio']
Database wait time ratio

Wait time - the time that the server process spends waiting for available shared resources to be released by other server processes such as latches, locks, data buffers, etc.

 Dependent item oracle.database_wait_time_ratio

Preprocessing

  • JSON Path: $.['Database Wait Time Ratio']
Database CPU time ratio

The ratio calculated by dividing the total CPU (used by the database) by the Oracle time model statistic DB time.

 Dependent item oracle.database_cpu_time_ratio

Preprocessing

  • JSON Path: $.['Database CPU Time Ratio']
Temp space used

Used temporary space.

 Dependent item oracle.temp_space_used

Preprocessing

  • JSON Path: $.['Temp Space Used']
Get system parameters

Get a set of system parameter values.

 Zabbix agent oracle.sys.params["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Sessions limit

The user and system sessions.

 Dependent item oracle.session_limit

Preprocessing

  • JSON Path: $.sessions
Datafiles limit

The maximum allowable number of datafiles.

 Dependent item oracle.db_files_limit

Preprocessing

  • JSON Path: $.db_files
Processes limit

The maximum number of user processes.

 Dependent item oracle.processes_limit

Preprocessing

  • JSON Path: $.processes
Get sessions stats

Get sessions statistics. {$ORACLE.SESSION.LOCK.MAX.TIME} -- maximum seconds in the current wait condition for counting long time locked sessions. Default: 600 seconds.

 Zabbix agent oracle.sessions.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}","{$ORACLE.SESSION.LOCK.MAX.TIME}"]
Session count

The session count.

 Dependent item oracle.session_count

Preprocessing

  • JSON Path: $.total
Active user sessions

The number of active user sessions.

 Dependent item oracle.session_active_user

Preprocessing

  • JSON Path: $.active_user

    ⛔️Custom on fail: Set value to: 0
Active background sessions

The number of active background sessions.

 Dependent item oracle.session_active_background

Preprocessing

  • JSON Path: $.active_background

    ⛔️Custom on fail: Set value to: 0
Inactive user sessions

The number of inactive user sessions.

 Dependent item oracle.session_inactive_user

Preprocessing

  • JSON Path: $.inactive_user

    ⛔️Custom on fail: Set value to: 0
Sessions lock rate

The percentage of locked sessions. Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource - either user objects, such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows.

 Dependent item oracle.session_lock_rate

Preprocessing

  • JSON Path: $.lock_rate
Sessions locked over {$ORACLE.SESSION.LOCK.MAX.TIME}s

The count of the prolongedly locked sessions. (You can change the duration of the maximum session lock in seconds for a query using the {$ORACLE.SESSION.LOCK.MAX.TIME} macro. Default = 600 s).

 Dependent item oracle.session_long_time_locked

Preprocessing

  • JSON Path: $.long_time_locked
Sessions concurrency

The percentage of concurrency. Concurrency is a database behavior when different transactions request to change the same resource. In the case of modifying data transactions, it sequentially temporarily blocks the right to change the data, and the rest of the transactions wait for access. When the access to a resource is locked for a long time, the concurrency grows (like the transaction queue), often leaving an extremely negative impact on performance. A high contention value does not indicate the root cause of the problem, but is a signal to search for it.

 Dependent item oracle.session_concurrency_rate

Preprocessing

  • JSON Path: $.concurrency_rate
Get PGA stats

Get PGA statistics.

 Zabbix agent oracle.pga.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
PGA, Total inuse

The amount of Program Global Area (PGA) memory 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).

 Dependent item oracle.total_pga_used

Preprocessing

  • JSON Path: $.['total PGA inuse']
PGA, Aggregate target parameter

The current value of the PGA_AGGREGATE_TARGET initialization parameter. If this parameter is not set, then its value is "0" and automatic management of the PGA memory is disabled.

 Dependent item oracle.pga_target

Preprocessing

  • JSON Path: $.['aggregate PGA target parameter']
PGA, Total allocated

The current amount of the PGA memory allocated by the instance. The Oracle Database attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value.

 Dependent item oracle.total_pga_allocated

Preprocessing

  • JSON Path: $.['total PGA allocated']
PGA, Total freeable

The number of bytes of the PGA memory in all processes that could be freed back to the OS.

 Dependent item oracle.total_pga_freeable

Preprocessing

  • JSON Path: $.['total freeable PGA memory']
PGA, Global memory bound

The maximum size of a work area executed in automatic mode.

 Dependent item oracle.pga_global_bound

Preprocessing

  • JSON Path: $.['global memory bound']
Get FRA stats

Get FRA statistics.

 Zabbix agent oracle.fra.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
FRA, Space limit

The maximum amount of disk space (in bytes) that the database can use for the Fast Recovery Area (FRA).

 Dependent item oracle.fra_space_limit

Preprocessing

  • JSON Path: $.space_limit
FRA, Used space

The amount of disk space (in bytes) used by FRA files created in the current and all the previous FRAs.

 Dependent item oracle.fra_space_used

Preprocessing

  • JSON Path: $.space_used
FRA, Space reclaimable

The total amount of disk space (in bytes) that can be created by deleting obsolete, redundant, and other low-priority files from the FRA.

 Dependent item oracle.fra_space_reclaimable

Preprocessing

  • JSON Path: $.space_reclaimable
FRA, Number of files

The number of files in the FRA.

 Dependent item oracle.fra_number_of_files

Preprocessing

  • JSON Path: $.number_of_files
FRA, Usable space in %

Percentage of space usable in the FRA.

 Dependent item oracle.fra_usable_pct

Preprocessing

  • JSON Path: $.usable_pct
FRA, Number of restore points

Number of restore points in the FRA.

 Dependent item oracle.fra_restore_point

Preprocessing

  • JSON Path: $.restore_point
Get SGA stats

Get SGA statistics.

 Zabbix agent oracle.sga.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
SGA, java pool

The memory is allocated from the Java pool.

 Dependent item oracle.sga_java_pool

Preprocessing

  • JSON Path: $.java_pool
SGA, large pool

The memory is allocated from a large pool.

 Dependent item oracle.sga_large_pool

Preprocessing

  • JSON Path: $.large_pool
SGA, shared pool

The memory is allocated from a shared pool.

 Dependent item oracle.sga_shared_pool

Preprocessing

  • JSON Path: $.shared_pool
SGA, log buffer

The number of bytes allocated for the redo log buffer.

 Dependent item oracle.sga_log_buffer

Preprocessing

  • JSON Path: $.log_buffer
SGA, fixed

The fixed System Global Area (SGA) is an internal housekeeping area.

 Dependent item oracle.sga_fixed

Preprocessing

  • JSON Path: $.fixed_sga
SGA, buffer cache

The size of standard block cache.

 Dependent item oracle.sga_buffer_cache

Preprocessing

  • JSON Path: $.buffer_cache
User's expire password

The number of days before the Zabbix account password expires.

 Zabbix agent oracle.user.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Preprocessing

  • JSON Path: $.exp_passwd_days_before
Redo logs available to switch

The number of inactive/unused redo logs available for log switching.

 Zabbix agent oracle.redolog.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Preprocessing

  • JSON Path: $.available
Number of processes

The current number of user processes.

 Zabbix agent oracle.proc.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Preprocessing

  • JSON Path: $.proc_num
Datafiles count

The current number of datafiles.

 Zabbix agent oracle.datafiles.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Preprocessing

  • JSON Path: $.datafile_num

Triggers

Name Description Expression Severity Dependencies and additional info
Oracle: Connection to database is unavailable

Connection to Oracle Database is currently unavailable.

 last(/Oracle by Zabbix agent 2/oracle.ping["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"])=0 Disaster
Oracle: Version has changed

The Oracle Database version has changed. Acknowledge to close the problem manually.

 last(/Oracle by Zabbix agent 2/oracle.version["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"],#1)<>last(/Oracle by Zabbix agent 2/oracle.version["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"],#2) and length(last(/Oracle by Zabbix agent 2/oracle.version["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]))>0 Info Manual close: Yes
Oracle: Failed to fetch info data

Zabbix has not received any data for the items for the last 5 minutes. The database might be unavailable for connecting.

 nodata(/Oracle by Zabbix agent 2/oracle.uptime,30m)=1 Info
Oracle: Host has been restarted

Uptime is less than 10 minutes.

 last(/Oracle by Zabbix agent 2/oracle.uptime)<10m Info Manual close: Yes
Oracle: Instance name has changed

An Oracle Database instance name has changed. Acknowledge to close the problem manually.

 last(/Oracle by Zabbix agent 2/oracle.instance_name,#1)<>last(/Oracle by Zabbix agent 2/oracle.instance_name,#2) and length(last(/Oracle by Zabbix agent 2/oracle.instance_name))>0 Info Manual close: Yes
Oracle: Instance hostname has changed

An Oracle Database instance hostname has changed. Acknowledge to close the problem manually.

 last(/Oracle by Zabbix agent 2/oracle.instance_hostname,#1)<>last(/Oracle by Zabbix agent 2/oracle.instance_hostname,#2) and length(last(/Oracle by Zabbix agent 2/oracle.instance_hostname))>0 Info Manual close: Yes
Oracle: Shared pool free is too low

The free memory percent of the shared pool has been less than {$ORACLE.SHARED.FREE.MIN.WARN}% for the last 5 minutes.

 max(/Oracle by Zabbix agent 2/oracle.shared_pool_free,5m)<{$ORACLE.SHARED.FREE.MIN.WARN} Warning
Oracle: Too many active sessions

Active sessions are using more than {$ORACLE.SESSIONS.MAX.WARN}% of the available sessions.

 min(/Oracle by Zabbix agent 2/oracle.session_count,5m) * 100 / last(/Oracle by Zabbix agent 2/oracle.session_limit) > {$ORACLE.SESSIONS.MAX.WARN} Warning
Oracle: Too many locked sessions

The number of locked sessions exceeds {$ORACLE.SESSIONS.LOCK.MAX.WARN}% of the running sessions.

 min(/Oracle by Zabbix agent 2/oracle.session_lock_rate,5m) > {$ORACLE.SESSIONS.LOCK.MAX.WARN} Warning
Oracle: Too many sessions locked

The number of locked sessions exceeding {$ORACLE.SESSION.LOCK.MAX.TIME} seconds is too high. Long-term locks can negatively affect the database performance. Therefore, if they are detected, you should first find the most difficult queries from the database point of view and then analyze possible resource leaks.

 min(/Oracle by Zabbix agent 2/oracle.session_long_time_locked,5m) > {$ORACLE.SESSION.LONG.LOCK.MAX.WARN} Warning
Oracle: Too high database concurrency

The concurrency rate exceeds {$ORACLE.CONCURRENCY.MAX.WARN}%. A high contention value does not indicate the root cause of the problem, but is a signal to review resource consumption (determine the "heaviest" queries in the database, trace sessions, etc.) This will help find the root cause and possible optimization points both in database configuration and the logic of building queries.

 min(/Oracle by Zabbix agent 2/oracle.session_concurrency_rate,5m) > {$ORACLE.CONCURRENCY.MAX.WARN} Warning
Oracle: Total PGA inuse is too high

The total PGA in use is more than {$ORACLE.PGA.USE.MAX.WARN}% of PGA_AGGREGATE_TARGET.

 min(/Oracle by Zabbix agent 2/oracle.total_pga_used,5m) * 100 / last(/Oracle by Zabbix agent 2/oracle.pga_target) > {$ORACLE.PGA.USE.MAX.WARN} Warning
Oracle: Zabbix account will expire soon

The password for the Zabbix user in the database expires soon.

 last(/Oracle by Zabbix agent 2/oracle.user.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]) < {$ORACLE.EXPIRE.PASSWORD.MIN.WARN} Warning
Oracle: Number of REDO logs available for switching is too low

The number of inactive/unused redos available for log switching is low (risk of database downtime).

 max(/Oracle by Zabbix agent 2/oracle.redolog.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"],5m) < {$ORACLE.REDO.MIN.WARN} Warning
Oracle: Too many active processes

Active processes are using more than {$ORACLE.PROCESSES.MAX.WARN}% of the available number of processes.

 min(/Oracle by Zabbix agent 2/oracle.proc.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"],5m) * 100 / last(/Oracle by Zabbix agent 2/oracle.processes_limit) > {$ORACLE.PROCESSES.MAX.WARN} Warning
Oracle: Too many database files

The number of datafiles is higher than {$ORACLE.DB.FILE.MAX.WARN}% of the available datafile limit.

 min(/Oracle by Zabbix agent 2/oracle.datafiles.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"],5m) * 100 / last(/Oracle by Zabbix agent 2/oracle.db_files_limit) > {$ORACLE.DB.FILE.MAX.WARN} Warning

LLD rule Database discovery

Name Description Type Key and additional info
Database discovery

Scanning databases in the database management system (DBMS).

 Zabbix agent oracle.db.discovery["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Item prototypes for Database discovery

Name Description Type Key and additional info
Database '{#DBNAME}': Get CDB and No-CDB info

Gets the information about the CDB and non-CDB database on an instance.

 Zabbix agent oracle.cdb.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}","{#DBNAME}"]
Database '{#DBNAME}': Open status

1 - MOUNTED;

2 - READ WRITE;

3 - READ ONLY;

4 - READ ONLY WITH APPLY (a physical standby database is open in real-time query mode).

 Dependent item oracle.db_open_mode["{#DBNAME}"]

Preprocessing

  • JSON Path: $..{#DBNAME}.open_mode.first()

  • Discard unchanged with heartbeat: 15m
Database '{#DBNAME}': Role

The current role of the database where:

1 - SNAPSHOT STANDBY;

2 - LOGICAL STANDBY;

3 - PHYSICAL STANDBY;

4 - PRIMARY;

5 - FAR SYNC.

 Dependent item oracle.db_role["{#DBNAME}"]

Preprocessing

  • JSON Path: $..{#DBNAME}.role.first()

  • Discard unchanged with heartbeat: 15m
Database '{#DBNAME}': Log mode

The archive log mode where:

0 - NOARCHIVELOG;

1 - ARCHIVELOG;

2 - MANUAL.

 Dependent item oracle.db_log_mode["{#DBNAME}"]

Preprocessing

  • JSON Path: $..{#DBNAME}.log_mode.first()

  • Discard unchanged with heartbeat: 15m
Database '{#DBNAME}': Force logging

Indicates whether the database is under force logging mode (YES/NO).

 Dependent item oracle.db_force_logging["{#DBNAME}"]

Preprocessing

  • JSON Path: $..{#DBNAME}.force_logging.first()

  • Discard unchanged with heartbeat: 15m

Trigger prototypes for Database discovery

Name Description Expression Severity Dependencies and additional info
Oracle: Database '{#DBNAME}': Open status in mount mode

The Oracle Database is in a mounted state.

 last(/Oracle by Zabbix agent 2/oracle.db_open_mode["{#DBNAME}"])=1 Warning
Oracle: Database '{#DBNAME}': Open status has changed

The Oracle Database open status has changed. Acknowledge to close the problem manually.

 last(/Oracle by Zabbix agent 2/oracle.db_open_mode["{#DBNAME}"],#1)<>last(/Oracle by Zabbix agent 2/oracle.db_open_mode["{#DBNAME}"],#2) Info Manual close: Yes
Depends on:
  • Oracle: Database '{#DBNAME}': Open status in mount mode
Oracle: Database '{#DBNAME}': Role has changed

The Oracle Database role has changed. Acknowledge to close the problem manually.

 last(/Oracle by Zabbix agent 2/oracle.db_role["{#DBNAME}"],#1)<>last(/Oracle by Zabbix agent 2/oracle.db_role["{#DBNAME}"],#2) Info Manual close: Yes
Oracle: Database '{#DBNAME}': Force logging is deactivated for DB with active Archivelog

Force logging mode is a very important metric for databases in ARCHIVELOG. This feature allows to forcibly write all the transactions to the redo log.

 last(/Oracle by Zabbix agent 2/oracle.db_force_logging["{#DBNAME}"]) = 0 and last(/Oracle by Zabbix agent 2/oracle.db_log_mode["{#DBNAME}"]) = 1 Warning

LLD rule PDB discovery

Name Description Type Key and additional info
PDB discovery

Scanning a pluggable database (PDB) in DBMS.

 Zabbix agent oracle.pdb.discovery["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Item prototypes for PDB discovery

Name Description Type Key and additional info
Database '{#DBNAME}': Get PDB info

Gets the information about the PDB database on an instance.

 Zabbix agent oracle.pdb.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}","{#CON_ID}"]
Database '{#DBNAME}': Open status

1 - MOUNTED;

2 - READ WRITE;

3 - READ ONLY;

4 - READ ONLY WITH APPLY (a physical standby database is open in real-time query mode).

 Dependent item oracle.pdb_open_mode["{#CON_ID}"]

Preprocessing

  • JSON Path: $..{#DBNAME}.open_mode.first()

  • Discard unchanged with heartbeat: 15m

Trigger prototypes for PDB discovery

Name Description Expression Severity Dependencies and additional info
Oracle: Database '{#DBNAME}': Open status in mount mode

The Oracle Database is in a mounted state.

 last(/Oracle by Zabbix agent 2/oracle.pdb_open_mode["{#CON_ID}"])=1 Warning
Oracle: Database '{#DBNAME}': Open status has changed

The Oracle Database open status has changed. Acknowledge to close the problem manually.

 last(/Oracle by Zabbix agent 2/oracle.pdb_open_mode["{#CON_ID}"],#1)<>last(/Oracle by Zabbix agent 2/oracle.pdb_open_mode["{#CON_ID}"],#2) Info Manual close: Yes

LLD rule Tablespace discovery

Name Description Type Key and additional info
Tablespace discovery

Scanning tablespaces in DBMS.

 Zabbix agent oracle.ts.discovery["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Item prototypes for Tablespace discovery

Name Description Type Key and additional info
'{#CON_NAME}' TBS '{#TABLESPACE}': Get tablespaces stats

Gets the statistics of the tablespace.

 Zabbix agent oracle.ts.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}","{#TABLESPACE}","{#CONTENTS}","{#CON_ID}"]
'{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace allocated, bytes

Currently allocated bytes for the tablespace (sum of the current size of datafiles).

 Dependent item oracle.tbs_alloc_bytes["{#CON_ID}","{#TABLESPACE}"]

Preprocessing

  • JSON Path: $..['{#TABLESPACE}'].file_bytes.first()
'{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace MAX size, bytes

The maximum size of the tablespace.

 Dependent item oracle.tbs_max_bytes["{#CON_ID}","{#TABLESPACE}"]

Preprocessing

  • JSON Path: $..['{#TABLESPACE}'].max_bytes.first()
'{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace used, bytes

Currently used bytes for the tablespace (current size of datafiles minus the free space).

 Dependent item oracle.tbs_used_bytes["{#CON_ID}","{#TABLESPACE}"]

Preprocessing

  • JSON Path: $..['{#TABLESPACE}'].used_bytes.first()
'{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace free, bytes

Free bytes of the allocated space.

 Dependent item oracle.tbs_free_bytes["{#CON_ID}","{#TABLESPACE}"]

Preprocessing

  • JSON Path: $..['{#TABLESPACE}'].free_bytes.first()
'{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace usage, percent

Used bytes/allocated bytes*100.

 Dependent item oracle.tbs_used_file_pct["{#CON_ID}","{#TABLESPACE}"]

Preprocessing

  • JSON Path: $..['{#TABLESPACE}'].used_file_pct.first()
'{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace allocated, percent

Allocated bytes/max bytes*100.

 Dependent item oracle.tbs_used_pct["{#CON_ID}","{#TABLESPACE}"]

Preprocessing

  • JSON Path: $..['{#TABLESPACE}'].used_pct_max.first()
'{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace usage from MAX, percent

Used bytes/max bytes*100.

 Dependent item oracle.tbs_used_from_max_pct["{#CON_ID}","{#TABLESPACE}"]

Preprocessing

  • JSON Path: $..['{#TABLESPACE}'].used_from_max_pct.first()
'{#CON_NAME}' TBS '{#TABLESPACE}': Open status

The tablespace status where:

1 - ONLINE;

2 - OFFLINE;

3 - READ ONLY.

 Dependent item oracle.tbs_status["{#CON_ID}","{#TABLESPACE}"]

Preprocessing

  • JSON Path: $..['{#TABLESPACE}'].status.first()

Trigger prototypes for Tablespace discovery

Name Description Expression Severity Dependencies and additional info
Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace usage is too high

The usage of the tablespace {#TABLESPACE} exceeds {$ORACLE.TBS.USED.PCT.MAX.WARN}%

 min(/Oracle by Zabbix agent 2/oracle.tbs_used_file_pct["{#CON_ID}","{#TABLESPACE}"],5m)>{$ORACLE.TBS.USED.PCT.MAX.WARN} Warning Depends on:
  • Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace usage is too high
Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace usage is too high

The usage of the tablespace {#TABLESPACE} exceeds {$ORACLE.TBS.USED.PCT.MAX.HIGH}%

 min(/Oracle by Zabbix agent 2/oracle.tbs_used_file_pct["{#CON_ID}","{#TABLESPACE}"],5m)>{$ORACLE.TBS.USED.PCT.MAX.HIGH} High
Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace utilization is too high

The utilization of the tablespace {#TABLESPACE} exceeds {$ORACLE.TBS.UTIL.PCT.MAX.WARN}%

 min(/Oracle by Zabbix agent 2/oracle.tbs_used_pct["{#CON_ID}","{#TABLESPACE}"],5m)>{$ORACLE.TBS.UTIL.PCT.MAX.WARN} Warning Depends on:
  • Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace utilization is too high
Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace utilization is too high

The utilization of the tablespace {#TABLESPACE} exceeds {$ORACLE.TBS.UTIL.PCT.MAX.HIGH}%

 min(/Oracle by Zabbix agent 2/oracle.tbs_used_pct["{#CON_ID}","{#TABLESPACE}"],5m)>{$ORACLE.TBS.UTIL.PCT.MAX.HIGH} High
Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace usage from MAX is too high

The usage of the tablespace {#TABLESPACE} from MAX exceeds {$ORACLE.TBS.USED.PCT.FROM.MAX.WARN}%

 min(/Oracle by Zabbix agent 2/oracle.tbs_used_from_max_pct["{#CON_ID}","{#TABLESPACE}"],5m)>{$ORACLE.TBS.USED.PCT.FROM.MAX.WARN} Warning Depends on:
  • Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace utilization from MAX is too high
Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace utilization from MAX is too high

The usage of the tablespace {#TABLESPACE} from MAX exceeds {$ORACLE.TBS.USED.PCT.FROM.MAX.HIGH}%

 min(/Oracle by Zabbix agent 2/oracle.tbs_used_from_max_pct["{#CON_ID}","{#TABLESPACE}"],5m)>{$ORACLE.TBS.USED.PCT.FROM.MAX.HIGH} High
Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace is OFFLINE

The tablespace is in the offline state.

 last(/Oracle by Zabbix agent 2/oracle.tbs_status["{#CON_ID}","{#TABLESPACE}"])=2 Warning
Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace status has changed

Oracle tablespace status has changed. Acknowledge to close the problem manually.

 last(/Oracle by Zabbix agent 2/oracle.tbs_status["{#CON_ID}","{#TABLESPACE}"],#1)<>last(/Oracle by Zabbix agent 2/oracle.tbs_status["{#CON_ID}","{#TABLESPACE}"],#2) Info Manual close: Yes
Depends on:
  • Oracle: '{#CON_NAME}' TBS '{#TABLESPACE}': Tablespace is OFFLINE

LLD rule Archive log discovery

Name Description Type Key and additional info
Archive log discovery

Destinations of the log archive.

 Zabbix agent oracle.archive.discovery["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Item prototypes for Archive log discovery

Name Description Type Key and additional info
Archivelog '{#DEST_NAME}': Get archive log info

Gets the archive log statistics.

 Zabbix agent oracle.archive.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}","{#DEST_NAME}"]
Archivelog '{#DEST_NAME}': Error

Displays the error message.

 Dependent item oracle.archivelog_error["{#DEST_NAME}"]

Preprocessing

  • JSON Path: $..['{#DEST_NAME}'].error.first()

  • Discard unchanged with heartbeat: 1h
Archivelog '{#DEST_NAME}': Last sequence

Identifies the sequence number of the last archived redo log to be archived.

 Dependent item oracle.archivelog_log_sequence["{#DEST_NAME}"]

Preprocessing

  • JSON Path: $..['{#DEST_NAME}'].log_sequence.first()
Archivelog '{#DEST_NAME}': Status

Identifies the current status of the destination where:

1 - VALID;

2 - DEFERRED;

3 - ERROR;

0 - UNKNOWN.

 Dependent item oracle.archivelog_log_status["{#DEST_NAME}"]

Preprocessing

  • JSON Path: $..['{#DEST_NAME}'].status.first()

  • Discard unchanged with heartbeat: 1h

Trigger prototypes for Archive log discovery

Name Description Expression Severity Dependencies and additional info
Oracle: Archivelog '{#DEST_NAME}': Log Archive is not valid

The trigger will launch if the archive log destination is not in one of these states:
2 - DEFERRED;
3 - VALID.

 last(/Oracle by Zabbix agent 2/oracle.archivelog_log_status["{#DEST_NAME}"])<2 High

LLD rule ASM disk groups discovery

Name Description Type Key and additional info
ASM disk groups discovery

The ASM disk groups.

 Zabbix agent oracle.diskgroups.discovery["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Item prototypes for ASM disk groups discovery

Name Description Type Key and additional info
ASM '{#DGNAME}': Get ASM stats

Gets the ASM disk group statistics.

 Zabbix agent oracle.diskgroups.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}","{#DGNAME}"]
ASM '{#DGNAME}': Total size

The total size of the ASM disk group.

 Dependent item oracle.asm_total_size["{#DGNAME}"]

Preprocessing

  • JSON Path: $..['{#DGNAME}'].total_bytes.first()
ASM '{#DGNAME}': Free size

The free size of the ASM disk group.

 Dependent item oracle.asm_free_size["{#DGNAME}"]

Preprocessing

  • JSON Path: $..['{#DGNAME}'].free_bytes.first()
ASM '{#DGNAME}': Used size, percent

Usage of the ASM disk group expressed in %.

 Dependent item oracle.asm_used_pct["{#DGNAME}"]

Preprocessing

  • JSON Path: $..['{#DGNAME}'].used_pct.first()

Trigger prototypes for ASM disk groups discovery

Name Description Expression Severity Dependencies and additional info
Oracle: ASM '{#DGNAME}': Disk group usage is too high

The usage of the ASM disk group expressed in % exceeds {$ORACLE.ASM.USED.PCT.MAX.WARN}.

 min(/Oracle by Zabbix agent 2/oracle.asm_used_pct["{#DGNAME}"],5m)>{$ORACLE.ASM.USED.PCT.MAX.WARN} Warning Depends on:
  • Oracle: ASM '{#DGNAME}': Disk group usage is too high
Oracle: ASM '{#DGNAME}': Disk group usage is too high

The usage of the ASM disk group expressed in % exceeds {$ORACLE.ASM.USED.PCT.MAX.HIGH}.

 min(/Oracle by Zabbix agent 2/oracle.asm_used_pct["{#DGNAME}"],5m)>{$ORACLE.ASM.USED.PCT.MAX.HIGH} High

Feedback

Please report any issues with the template at https://support.zabbix.com

You can also provide feedback, discuss the template, or ask for help at ZABBIX forums

Articles and documentation

+ Propose new article

No se encuentra la integración que necesitas?

Request custom integration

Zabbix integration team will develop custom integration based on your requirements and Zabbix best practices.

Request

Propose your integration

Have you already developed high quality integration and want to submit to Zabbix integration repository?

Propose