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




Source: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/oracle_agent2


Template DB Oracle by Zabbix Agent 2

Overview

For Zabbix version: 5.0 The template is developed for monitoring DBMS Oracle Database single instance via Zabbix Agent2.

This template was tested on:

  • Zabbix, version 5.0
  • Oracle Database, version 12c2, 18c, 19c

Setup

See Zabbix template operation for basic instructions.

  1. Setup and configure zabbix-agent2 compiled with the Oracle monitoring plugin.
  2. Set the {$ORACLE.CONNSTRING} such as <protocol(host:port)> or named session.
  3. Set the user name and password in host macros ({$ORACLE.USER}, {$ORACLE.PASSWORD}, {$ORACLE.SERVICE}) if you want to override parameters from the Zabbix agent configuration file.

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

Zabbix configuration

No specific Zabbix configuration is required.

Macros used

Name Description Default
{$ORACLE.ASM.USED.PCT.MAX.HIGH}

Maximum percentage of used ASM disk group for high trigger expression.

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

Maximum percentage of used ASM disk group for warning trigger expression.

90
{$ORACLE.CONCURRENCY.MAX.WARN}

Maximum percentage of sessions concurrency usage for trigger expression.

80
{$ORACLE.CONNSTRING} tcp://localhost:1521
{$ORACLE.DB.FILE.MAX.WARN}

Maximum percentage of database files for trigger expression.

80
{$ORACLE.DBNAME.MATCHES}

This macro is used in database discovery. It can be overridden on the host or linked template level.

.*
{$ORACLE.DBNAME.NOT_MATCHES}

This macro is used in database discovery. It can be overridden on a host or linked template level.

PDB\$SEED
{$ORACLE.EXPIRE.PASSWORD.MIN.WARN}

Number of days of warning before password expires (for trigger expression).

7
{$ORACLE.PASSWORD}

Oracle user password.

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

Maximum percentage of PGA usage alert treshold (for trigger expression).

90
{$ORACLE.PROCESSES.MAX.WARN}

Maximum percentage of active processes alert treshold (for trigger expression).

80
{$ORACLE.REDO.MIN.WARN}

Minimum number of REDO logs alert treshold (for trigger expression).

3
{$ORACLE.SERVICE}

Oracle Service Name

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

Maximum session lock duration in seconds for count the session as a prolongely locked query.

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

Maximum number of the prolongely locked sessions alert treshold (for trigger expression).

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

Maximum percentage of locked sessions alert treshold (for trigger expression).

20
{$ORACLE.SESSIONS.MAX.WARN}

Maximum percentage of active sessions alert treshold (for trigger expression).

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

Minimum percentage of free shared pool alert treshold (for trigger expression).

5
{$ORACLE.TABLESPACE.NAME.MATCHES}

This macro is used in tablespace discovery. It can be overridden on a host or linked template level.

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

This macro is used in tablespace discovery. It can be overridden on a host or linked template level.

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

Maximum percentage of used tablespace high severity alert treshold (for trigger expression).

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

Maximum percentage of used tablespace warning severity alert treshold (for trigger expression).

80
{$ORACLE.USER}

Oracle username.

zabbix

Template links

There are no template links in this template.

Discovery rules

Name Description Type Key and additional info
Database discovery

Scanning databases in DBMS.

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

Filter:

AND

- A: {#DBNAME} MATCHES_REGEX {$ORACLE.DBNAME.MATCHES}

- B: {#DBNAME} NOT_MATCHES_REGEX {$ORACLE.DBNAME.NOT_MATCHES}

PDB discovery

Scanning PDB in DBMS.

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

Filter:

AND

- A: {#DBNAME} MATCHES_REGEX {$ORACLE.DBNAME.MATCHES}

- B: {#DBNAME} NOT_MATCHES_REGEX {$ORACLE.DBNAME.NOT_MATCHES}

Tablespace discovery

Scanning tablespaces in DBMS.

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

Filter:

AND

- A: {#TABLESPACE} MATCHES_REGEX {$ORACLE.TABLESPACE.NAME.MATCHES}

- B: {#TABLESPACE} NOT_MATCHES_REGEX {$ORACLE.TABLESPACE.NAME.NOT_MATCHES}

Archive log discovery

Log archive destinations.

ZABBIX_PASSIVE oracle.archive.discovery["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
ASM disk groups discovery

ASM disk groups

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

Items collected

Group Name Description Type Key and additional info
Oracle Oracle: Ping

Test the connection to Oracle Database state

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

Preprocessing:

- DISCARD_UNCHANGED_HEARTBEAT: 10m

Oracle Oracle: Version

Oracle Server version.

DEPENDENT oracle.version

Preprocessing:

- JSONPATH: $.version

- DISCARD_UNCHANGED_HEARTBEAT: 1d

Oracle Oracle: Uptime

Oracle instance uptime in seconds.

DEPENDENT oracle.uptime

Preprocessing:

- JSONPATH: $.uptime

Oracle Oracle: Instance status

Status of the instance.

DEPENDENT oracle.instance_status

Preprocessing:

- JSONPATH: $.status

Oracle Oracle: Archiver state

Automatic archiving status.

DEPENDENT oracle.archiver_state

Preprocessing:

- JSONPATH: $..archiver.first()

Oracle Oracle: Instance name

Name of the instance.

DEPENDENT oracle.instance_name

Preprocessing:

- JSONPATH: $.instance

Oracle Oracle: Instance hostname

Name of the host machine.

DEPENDENT oracle.instance_hostname

Preprocessing:

- JSONPATH: $..hostname.first()

Oracle Oracle: Instance role

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

DEPENDENT oracle.instance.role

Preprocessing:

- JSONPATH: $.role

Oracle Oracle: Buffer cache hit ratio

Ratio of buffer cache hits. (LogRead - PhyRead)/LogRead

DEPENDENT oracle.buffer_cache_hit_ratio

Preprocessing:

- JSONPATH: $.['Buffer Cache Hit Ratio']

Oracle Oracle: Cursor cache hit ratio

Ratio of cursor cache hits. CursorCacheHit/SoftParse

DEPENDENT oracle.cursor_cache_hit_ratio

Preprocessing:

- JSONPATH: $.['Cursor Cache Hit Ratio']

Oracle Oracle: Library cache hit ratio

Ratio of library cache hits. Hits/Pins

DEPENDENT oracle.library_cache_hit_ratio

Preprocessing:

- JSONPATH: $.['Library Cache Hit Ratio']

Oracle Oracle: Shared pool free %

Shared pool free memory percent. Free/Total

DEPENDENT oracle.shared_pool_free

Preprocessing:

- JSONPATH: $.['Shared Pool Free %']

Oracle Oracle: Physical reads per second

Reads per second.

DEPENDENT oracle.physical_reads_rate

Preprocessing:

- JSONPATH: $.['Physical Reads Per Sec']

Oracle Oracle: Physical writes per second

Writes per second.

DEPENDENT oracle.physical_writes_rate

Preprocessing:

- JSONPATH: $.['Physical Writes Per Sec']

Oracle Oracle: Physical reads bytes per second

Read bytes per second.

DEPENDENT oracle.physical_read_bytes_rate

Preprocessing:

- JSONPATH: $.['Physical Read Bytes Per Sec']

Oracle Oracle: Physical writes bytes per second

Write bytes per second.

DEPENDENT oracle.physical_write_bytes_rate

Preprocessing:

- JSONPATH: $.['Physical Write Bytes Per Sec']

Oracle Oracle: Enqueue timeouts per second

Enqueue timeouts per second.

DEPENDENT oracle.enqueue_timeouts_rate

Preprocessing:

- JSONPATH: $.['Enqueue Timeouts Per Sec']

Oracle Oracle: GC CR block received per second

GC CR block received per second.

DEPENDENT oracle.gc_cr_block_received_rate

Preprocessing:

- JSONPATH: $.['GC CR Block Received Per Second']

Oracle Oracle: Global cache blocks corrupted

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

DEPENDENT oracle.cache_blocks_corrupt

Preprocessing:

- JSONPATH: $.['Global Cache Blocks Corrupted']

Oracle Oracle: Global cache blocks lost

The number of global cache blocks lost

DEPENDENT oracle.cache_blocks_lost

Preprocessing:

- JSONPATH: $.['Global Cache Blocks Lost']

Oracle Oracle: Logons per second

The number of logon attempts.

DEPENDENT oracle.logons_rate

Preprocessing:

- JSONPATH: $.['Logons Per Sec']

Oracle Oracle: Average active sessions

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

DEPENDENT oracle.active_sessions

Preprocessing:

- JSONPATH: $.['Average Active Sessions']

Oracle Oracle: Active serial sessions

The number of active serial sessions.

DEPENDENT oracle.active_serial_sessions

Preprocessing:

- JSONPATH: $.['Active Serial Sessions']

Oracle Oracle: Active parallel sessions

The number of active parallel sessions.

DEPENDENT oracle.active_parallel_sessions

Preprocessing:

- JSONPATH: $.['Active Parallel Sessions']

Oracle Oracle: Long table scans per second

The number of long table scans per second. A table is considered 'long' if the table is not cached and if its high-water mark is greater than 5 blocks.

DEPENDENT oracle.long_table_scans_rate

Preprocessing:

- JSONPATH: $.['Long Table Scans Per Sec']

Oracle Oracle: SQL service response time

SQL service response time in seconds.

DEPENDENT oracle.service_response_time

Preprocessing:

- JSONPATH: $.['SQL Service Response Time']

- MULTIPLIER: 0.01

Oracle Oracle: User rollbacks per second

The number of times that users manually issue the ROLLBACK statement or an error occurred during a user's transactions.

DEPENDENT oracle.user_rollbacks_rate

Preprocessing:

- JSONPATH: $.['User Rollbacks Per Sec']

Oracle Oracle: Total sorts per user call

Total sorts per user call.

DEPENDENT oracle.sorts_per_user_call

Preprocessing:

- JSONPATH: $.['Total Sorts Per User Call']

Oracle Oracle: Rows per sort

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

DEPENDENT oracle.rows_per_sort

Preprocessing:

- JSONPATH: $.['Rows Per Sort']

Oracle Oracle: Disk sort per second

The number of sorts going to disk per second

DEPENDENT oracle.disk_sorts

Preprocessing:

- JSONPATH: $.['Disk Sort Per Sec']

Oracle Oracle: Memory sorts ratio

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

DEPENDENT oracle.memory_sorts_ratio

Preprocessing:

- JSONPATH: $.['Memory Sorts Ratio']

Oracle Oracle: 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, and so on

DEPENDENT oracle.database_wait_time_ratio

Preprocessing:

- JSONPATH: $.['Database Wait Time Ratio']

Oracle Oracle: Database CPU time ratio

Calculated by dividing the total CPU used by the database by the Oracle time model statistic DB time.

DEPENDENT oracle.database_cpu_time_ratio

Preprocessing:

- JSONPATH: $.['Database CPU Time Ratio']

Oracle Oracle: Temp space used

Temp space used.

DEPENDENT oracle.temp_space_used

Preprocessing:

- JSONPATH: $.['Temp Space Used']

Oracle Oracle: Sessions limit

User and system sessions.

DEPENDENT oracle.session_limit

Preprocessing:

- JSONPATH: $.sessions

Oracle Oracle: Datafiles limit

Max allowable number of datafile.

DEPENDENT oracle.db_files_limit

Preprocessing:

- JSONPATH: $.db_files

Oracle Oracle: Processes limit

Max user processes.

DEPENDENT oracle.processes_limit

Preprocessing:

- JSONPATH: $.processes

Oracle Oracle: Session count

Session count.

DEPENDENT oracle.session_count

Preprocessing:

- JSONPATH: $.total

Oracle Oracle: Active user sessions

The number of active user sessions.

DEPENDENT oracle.session_active_user

Preprocessing:

- JSONPATH: $.active_user

⛔️ON_FAIL: CUSTOM_VALUE -> 0

Oracle Oracle: Active background sessions

The number of active background sessions.

DEPENDENT oracle.session_active_background

Preprocessing:

- JSONPATH: $.active_background

⛔️ON_FAIL: CUSTOM_VALUE -> 0

Oracle Oracle: Inactive user sessions

The number of inactive user sessions.

DEPENDENT oracle.session_inactive_user

Preprocessing:

- JSONPATH: $.inactive_user

⛔️ON_FAIL: CUSTOM_VALUE -> 0

Oracle Oracle: 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 oracle.session_lock_rate

Preprocessing:

- JSONPATH: $.lock_rate

Oracle Oracle: Sessions locked over {$ORACLE.SESSION.LOCK.MAX.TIME}s

Count of the prolongely locked sessions. (You can change maximum session lock duration in seconds for query by {$ORACLE.SESSION.LOCK.MAX.TIME} macro. Default 600 sec)

DEPENDENT oracle.session_long_time_locked

Preprocessing:

- JSONPATH: $.long_time_locked

Oracle Oracle: Sessions concurrency

The percentage of concurrency. Concurrency is a DB behaviour when different transactions request to change the same resource - in case of modifying data transactions sequentially block temporarily the right to change data, the rest of the transactions are waiting for access. In the case when access for resource is locked for a long time, then the concurrency grows (like the transaction queue) and this often has 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 oracle.session_concurrency_rate

Preprocessing:

- JSONPATH: $.concurrency_rate

Oracle Oracle: PGA, Total inuse

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

DEPENDENT oracle.total_pga_used

Preprocessing:

- JSONPATH: $.['total PGA inuse']

Oracle Oracle: PGA, Aggregate target parameter

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

DEPENDENT oracle.pga_target

Preprocessing:

- JSONPATH: $.['aggregate PGA target parameter']

Oracle Oracle: PGA, Total allocated

Current amount of 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 oracle.total_pga_allocated

Preprocessing:

- JSONPATH: $.['total PGA allocated']

Oracle Oracle: PGA, Total freeable

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

DEPENDENT oracle.total_pga_freeable

Preprocessing:

- JSONPATH: $.['total freeable PGA memory']

Oracle Oracle: PGA, Global memory bound

Maximum size of a work area executed in automatic mode.

DEPENDENT oracle.pga_global_bound

Preprocessing:

- JSONPATH: $.['global memory bound']

Oracle Oracle: FRA, Space limit

Maximum amount of disk space (in bytes) that the database can use for the fast recovery area.

DEPENDENT oracle.fra_space_limit

Preprocessing:

- JSONPATH: $.space_limit

Oracle Oracle: FRA, Used space

Amount of disk space (in bytes) used by fast recovery area files created in current and all previous fast recovery areas.

DEPENDENT oracle.fra_space_used

Preprocessing:

- JSONPATH: $.space_used

Oracle Oracle: FRA, Space reclaimable

Total amount of disk space (in bytes) that can be created by deleting obsolete, redundant, and other low priority files from the fast recovery area.

DEPENDENT oracle.fra_space_reclaimable

Preprocessing:

- JSONPATH: $.space_reclaimable

Oracle Oracle: FRA, Number of files

Number of files in the fast recovery area

DEPENDENT oracle.fra_number_of_files

Preprocessing:

- JSONPATH: $.number_of_files

Oracle Oracle: FRA, Used space in % DEPENDENT oracle.fra_usable_pct

Preprocessing:

- JSONPATH: $.usable_pct

Oracle Oracle: FRA, Number of restore points DEPENDENT oracle.fra_restore_point

Preprocessing:

- JSONPATH: $.restore_point

Oracle Oracle: SGA, java pool

Memory is allocated from the java pool.

DEPENDENT oracle.sga_java_pool

Preprocessing:

- JSONPATH: $.java_pool

Oracle Oracle: SGA, large pool

Memory is allocated from the large pool.

DEPENDENT oracle.sga_large_pool

Preprocessing:

- JSONPATH: $.large_pool

Oracle Oracle: SGA, shared pool

Memory is allocated from the shared pool.

DEPENDENT oracle.sga_shared_pool

Preprocessing:

- JSONPATH: $.shared_pool

Oracle Oracle: SGA, log buffer

The number of bytes allocated for the redo log buffer.

DEPENDENT oracle.sga_log_buffer

Preprocessing:

- JSONPATH: $.log_buffer

Oracle Oracle: SGA, fixed

The fixed SGA is an internal housekeeping area.

DEPENDENT oracle.sga_fixed

Preprocessing:

- JSONPATH: $.fixed_sga

Oracle Oracle: SGA, buffer cache

The size of the cache of standard blocks.

DEPENDENT oracle.sga_buffer_cache

Preprocessing:

- JSONPATH: $.buffer_cache

Oracle Oracle: User's expire password

The number of days before zabbix account password expired.

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

Preprocessing:

- JSONPATH: $.exp_passwd_days_before

Oracle Oracle: Redo logs available to switch

Number of available for log switching inactive/unused REDO logs.

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

Preprocessing:

- JSONPATH: $.available

Oracle Oracle: Number of processes ZABBIX_PASSIVE oracle.proc.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Preprocessing:

- JSONPATH: $.proc_num

Oracle Oracle: Datafiles count

Current number of datafile.

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

Preprocessing:

- JSONPATH: $.datafile_num

Oracle Oracle 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 oracle.db_open_mode["{#DBNAME}"]

Preprocessing:

- JSONPATH: $..{#DBNAME}.open_mode.first()

- DISCARD_UNCHANGED_HEARTBEAT: 15m

Oracle Oracle Database '{#DBNAME}': Role

Current role of the database, 1 - 'SNAPSHOT STANDBY', 2 - 'LOGICAL STANDBY', 3 - 'PHYSICAL STANDBY', 4 - 'PRIMARY ', 5 -'FAR SYNC'

DEPENDENT oracle.db_role["{#DBNAME}"]

Preprocessing:

- JSONPATH: $..{#DBNAME}.role.first()

- DISCARD_UNCHANGED_HEARTBEAT: 15m

Oracle Oracle Database '{#DBNAME}': Log mode

Archive log mode, 0 - 'NOARCHIVELOG', 1 - 'ARCHIVELOG', 2 - 'MANUAL'

DEPENDENT oracle.db_log_mode["{#DBNAME}"]

Preprocessing:

- JSONPATH: $..{#DBNAME}.log_mode.first()

- DISCARD_UNCHANGED_HEARTBEAT: 15m

Oracle Oracle Database '{#DBNAME}': Force logging

Indicates whether the database is under force logging mode (YES) or not (NO)

DEPENDENT oracle.db_force_logging["{#DBNAME}"]

Preprocessing:

- JSONPATH: $..{#DBNAME}.force_logging.first()

- DISCARD_UNCHANGED_HEARTBEAT: 15m

Oracle Oracle 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 oracle.pdb_open_mode["{#DBNAME}"]

Preprocessing:

- JSONPATH: $..{#DBNAME}.open_mode.first()

- DISCARD_UNCHANGED_HEARTBEAT: 15m

Oracle Oracle TBS '{#TABLESPACE}': Tablespace allocated, bytes

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

DEPENDENT oracle.tbs_alloc_bytes["{#TABLESPACE}"]

Preprocessing:

- JSONPATH: $..{#TABLESPACE}.used_bytes.first()

Oracle Oracle TBS '{#TABLESPACE}': Tablespace MAX size, bytes

Maximum size of tablespace.

DEPENDENT oracle.tbs_max_bytes["{#TABLESPACE}"]

Preprocessing:

- JSONPATH: $..{#TABLESPACE}.max_bytes.first()

Oracle Oracle TBS '{#TABLESPACE}': Tablespace free, bytes

Free bytes of allocated space.

DEPENDENT oracle.tbs_free_bytes["{#TABLESPACE}"]

Preprocessing:

- JSONPATH: $..{#TABLESPACE}.free_bytes.first()

Oracle Oracle TBS '{#TABLESPACE}': Tablespace usage percent

Allocated bytes/Max bytes*100

DEPENDENT oracle.tbs_used_pct["{#TABLESPACE}"]

Preprocessing:

- JSONPATH: $..{#TABLESPACE}.used_pct.first()

Oracle Oracle TBS '{#TABLESPACE}': Open status

Tablespace status. 1 - 'ONLINE' 2 - 'OFFLINE' 3- 'READ ONLY'

DEPENDENT oracle.tbs_status["{#TABLESPACE}"]

Preprocessing:

- JSONPATH: $..{#TABLESPACE}.status.first()

Oracle Archivelog '{#DEST_NAME}': Error

Displays the error text

DEPENDENT oracle.archivelog_error["{#DEST_NAME}"]

Preprocessing:

- JSONPATH: $..['{#DEST_NAME}'].log_sequence.first()

Oracle Archivelog '{#DEST_NAME}': Last sequence

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

DEPENDENT oracle.archivelog_log_sequence["{#DEST_NAME}"]

Preprocessing:

- JSONPATH: $..['{#DEST_NAME}'].log_sequence.first()

Oracle Archivelog '{#DEST_NAME}': Status

Identifies the current status of the destination: 1 - 'Valid', 2 - 'Dederred',3 - 'Error', 0 - 'Unknown'

DEPENDENT oracle.archivelog_log_status["{#DEST_NAME}"]

Preprocessing:

- JSONPATH: $..['{#DEST_NAME}'].log_sequence.first()

Oracle ASM '{#DG_NAME}': Total size

Total size of ASM disk group.

DEPENDENT oracle.asm_total_size["{#DG_NAME}"]

Preprocessing:

- JSONPATH: $..['{#DG_NAME}'].size_byte.first()

Oracle ASM '{#DG_NAME}': Free size

Free size of ASM disk group.

DEPENDENT oracle.asm_free_size["{#DG_NAME}"]

Preprocessing:

- JSONPATH: $..['{#DG_NAME}'].free_size_byte.first()

Oracle ASM '{#DG_NAME}': Free size

Usage percent of ASM disk group.

DEPENDENT oracle.asm_used_pct["{#DG_NAME}"]

Preprocessing:

- JSONPATH: $..['{#DG_NAME}'].used_percent.first()

Zabbix_raw_items Oracle: Get instance state

The item gets state of the current instance.

ZABBIX_PASSIVE oracle.instance.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: Get system metrics

The item gets system metric values.

ZABBIX_PASSIVE oracle.sys.metrics["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: Get system parameters

Get a set of system parameter values.

ZABBIX_PASSIVE oracle.sys.params["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: 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_PASSIVE oracle.sessions.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}","{$ORACLE.SESSION.LOCK.MAX.TIME}"]
Zabbix_raw_items Oracle: Get PGA stats

Get PGA statistics.

ZABBIX_PASSIVE oracle.pga.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: Get FRA stats

Get FRA statistics.

ZABBIX_PASSIVE oracle.fra.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: Get SGA stats

Get SGA statistics.

ZABBIX_PASSIVE oracle.sga.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: Get tablespaces stats

Get tablespaces stats.

ZABBIX_PASSIVE oracle.ts.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: Get CDB and No-CDB info

Get info about CDB and No-CDB databases on instansce.

ZABBIX_PASSIVE oracle.cdb.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: Get PDB info

Get info about PDB databases on instansce.

ZABBIX_PASSIVE oracle.pdb.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: Get archive log info ZABBIX_PASSIVE oracle.archive.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]
Zabbix_raw_items Oracle: Get ASM stats

Get ASM disk groups stats.

ZABBIX_PASSIVE oracle.diskgroups.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"]

Triggers

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

Connection to Oracle Database is currently unavailable.

{TEMPLATE_NAME:oracle.ping["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"].last()}=0 DISASTER
Oracle: Version has changed (new version value received: {ITEM.VALUE})

Oracle DB version has changed. Ack to close.

{TEMPLATE_NAME:oracle.version.diff()}=1 and {TEMPLATE_NAME:oracle.version.strlen()}>0 INFO

Manual close: YES

Oracle: Failed to fetch info data (or no data for 30m)

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

{TEMPLATE_NAME:oracle.uptime.nodata(30m)}=1 INFO
Oracle: has been restarted (uptime < 10m)

Uptime is less than 10 minutes

{TEMPLATE_NAME:oracle.uptime.last()}<10m INFO

Manual close: YES

Oracle: Instance name has changed (new name received: {ITEM.VALUE})

Oracle DB Instance name has changed. Ack to close.

{TEMPLATE_NAME:oracle.instance_name.diff()}=1 and {TEMPLATE_NAME:oracle.instance_name.strlen()}>0 INFO

Manual close: YES

Oracle: Instance hostname has changed (new hostname received: {ITEM.VALUE})

Oracle DB Instance hostname has changed. Ack to close.

{TEMPLATE_NAME:oracle.instance_hostname.diff()}=1 and {TEMPLATE_NAME:oracle.instance_hostname.strlen()}>0 INFO

Manual close: YES

Oracle: Shared pool free is too low (less {$ORACLE.SHARED.FREE.MIN.WARN}% for 5m)

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

{TEMPLATE_NAME:oracle.shared_pool_free.max(5m)}<{$ORACLE.SHARED.FREE.MIN.WARN} WARNING
Oracle: Too many active sessions (over {$ORACLE.SESSIONS.MAX.WARN}% for 5 min)

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

{TEMPLATE_NAME:oracle.session_count.min(5m)} * 100 / {Template DB Oracle by Zabbix Agent 2:oracle.session_limit.last()} > {$ORACLE.SESSIONS.MAX.WARN} WARNING
Oracle: Too many locked sessions (over {$ORACLE.SESSIONS.LOCK.MAX.WARN}% for 5 min)

Number of locked sessions is over {$ORACLE.SESSIONS.LOCK.MAX.WARN}% of the running sessions.

{TEMPLATE_NAME:oracle.session_lock_rate.min(5m)} > {$ORACLE.SESSIONS.LOCK.MAX.WARN} WARNING
Oracle: Too many sessions locked over {$ORACLE.SESSION.LOCK.MAX.TIME}s (over {$ORACLE.SESSION.LONG.LOCK.MAX.WARN} for 5 min)

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

{TEMPLATE_NAME:oracle.session_long_time_locked.min(5m)} > {$ORACLE.SESSION.LONG.LOCK.MAX.WARN} WARNING
Oracle: Too hight database concurrency (over {$ORACLE.CONCURRENCY.MAX.WARN}% for 5 min)

Concurrency rate is over {$ORACLE.CONCURRENCY.MAX.WARN}%. A high contention value does not indicate the root cause of the problem, but is a signal to search for it. In the case of high competition, an analysis of resource consumption should be carried out, the most "heavy" queries made in the database, possibly - session tracing. All this will help determine the root cause and possible optimization points both in the database configuration and in the logic of building queries of the application itself.

{TEMPLATE_NAME:oracle.session_concurrency_rate.min(5m)} > {$ORACLE.CONCURRENCY.MAX.WARN} WARNING
Oracle: Total PGA inuse is too high (over {$ORACLE.PGA.USE.MAX.WARN}% for 5 min)

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

{TEMPLATE_NAME:oracle.total_pga_used.min(5m)} * 100 / {Template DB Oracle by Zabbix Agent 2:oracle.pga_target.last()} > {$ORACLE.PGA.USE.MAX.WARN} WARNING
Oracle: Zabbix account will expire soon (under {$ORACLE.EXPIRE.PASSWORD.MIN.WARN} days)

Password for zabbix user in the database will expire soon.

{TEMPLATE_NAME:oracle.user.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"].last()} < {$ORACLE.EXPIRE.PASSWORD.MIN.WARN} WARNING
Oracle: Number of REDO logs available for switching is too low (less {$ORACLE.REDO.MIN.WARN} for 5 min)

Number of available for log switching inactive/unused REDOs is low (Database down risk)

{TEMPLATE_NAME:oracle.redolog.info["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"].max(5m)} < {$ORACLE.REDO.MIN.WARN} WARNING
Oracle: Too many active processes (over {$ORACLE.PROCESSES.MAX.WARN}% for 5 min)

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

{TEMPLATE_NAME:oracle.proc.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"].min(5m)} * 100 / {Template DB Oracle by Zabbix Agent 2:oracle.processes_limit.last()} > {$ORACLE.PROCESSES.MAX.WARN} WARNING
Oracle: Too many database files (over {$ORACLE.DB.FILE.MAX.WARN}% for 5 min)

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

{TEMPLATE_NAME:oracle.datafiles.stats["{$ORACLE.CONNSTRING}","{$ORACLE.USER}","{$ORACLE.PASSWORD}","{$ORACLE.SERVICE}"].min(5m)} * 100 / {Template DB Oracle by Zabbix Agent 2:oracle.db_files_limit.last()} > {$ORACLE.DB.FILE.MAX.WARN} WARNING
Oracle Database '{#DBNAME}': Open status in mount mode

The Oracle DB has a MOUNTED state.

{TEMPLATE_NAME:oracle.db_open_mode["{#DBNAME}"].last()}=1 WARNING
Oracle Database '{#DBNAME}': Open status has changed (new value received: {ITEM.VALUE})

Oracle DB open status has changed. Ack to close.

{TEMPLATE_NAME:oracle.db_open_mode["{#DBNAME}"].diff()}=1 INFO

Manual close: YES

Depends on:

- Oracle Database '{#DBNAME}': Open status in mount mode

Oracle Database '{#DBNAME}': Role has changed (new value received: {ITEM.VALUE})

Oracle DB role has changed. Ack to close.

{TEMPLATE_NAME:oracle.db_role["{#DBNAME}"].diff()}=1 INFO

Manual close: YES

Oracle Database '{#DBNAME}': Force logging is deactivated for DB with active Archivelog

Force Logging mode - it is very important metric for Databases in 'ARCHIVELOG'. This feature allows to forcibly write all transactions to the REDO.

{TEMPLATE_NAME:oracle.db_force_logging["{#DBNAME}"].last()} = 0 and {Template DB Oracle by Zabbix Agent 2:oracle.db_log_mode["{#DBNAME}"].last()} = 1 WARNING
Oracle Database '{#DBNAME}': Open status in mount mode

The Oracle DB has a MOUNTED state.

{TEMPLATE_NAME:oracle.pdb_open_mode["{#DBNAME}"].last()}=1 WARNING
Oracle Database '{#DBNAME}': Open status has changed (new value received: {ITEM.VALUE})

Oracle DB open status has changed. Ack to close.

{TEMPLATE_NAME:oracle.pdb_open_mode["{#DBNAME}"].diff()}=1 INFO

Manual close: YES

Oracle TBS '{#TABLESPACE}': Tablespace usage is too high (over {$ORACLE.TBS.USED.PCT.MAX.WARN}% for 5m). {TEMPLATE_NAME:oracle.tbs_used_pct["{#TABLESPACE}"].min(5m)}>{$ORACLE.TBS.USED.PCT.MAX.WARN} WARNING

Depends on:

- Oracle TBS '{#TABLESPACE}': Tablespace Usage is too high (over {$ORACLE.TBS.USED.PCT.MAX.HIGH}% for 5m).

Oracle TBS '{#TABLESPACE}': Tablespace Usage is too high (over {$ORACLE.TBS.USED.PCT.MAX.HIGH}% for 5m). {TEMPLATE_NAME:oracle.tbs_used_pct["{#TABLESPACE}"].min(5m)}>{$ORACLE.TBS.USED.PCT.MAX.HIGH} HIGH
Oracle TBS '{#TABLESPACE}': Tablespase is OFFLINE

The tablespase is in the offline state.

{TEMPLATE_NAME:oracle.tbs_status["{#TABLESPACE}"].last()}=2 WARNING
Oracle TBS '{#TABLESPACE}': Tablespace status has changed (new value received: {ITEM.VALUE})

Oracle tablespace status has changed. Ack to close.

{TEMPLATE_NAME:oracle.tbs_status["{#TABLESPACE}"].diff()}=1 INFO

Manual close: YES

Depends on:

- Oracle TBS '{#TABLESPACE}': Tablespase is OFFLINE

Archivelog '{#DEST_NAME}': Log Archive is not valid

ARL destination not in 3 - Valid or 2 - Deferred.

{TEMPLATE_NAME:oracle.archivelog_log_status["{#DEST_NAME}"].last()}<2 HIGH
ASM '{#DG_NAME}': Disk group usage is too high (over {$ORACLE.ASM.USED.PCT.MAX.WARN}% for 5m)

Usage percent of ASM disk group is over {$ORACLE.ASM.USED.PCT.MAX.WARN}

{TEMPLATE_NAME:oracle.asm_used_pct["{#DG_NAME}"].min(5m)}>{$ORACLE.ASM.USED.PCT.MAX.WARN} WARNING

Depends on:

- ASM '{#DG_NAME}': Disk group usage is too high (over {$ORACLE.ASM.USED.PCT.MAX.HIGH}% for 5m)

ASM '{#DG_NAME}': Disk group usage is too high (over {$ORACLE.ASM.USED.PCT.MAX.HIGH}% for 5m)

Usage percent of ASM disk group is over {$ORACLE.ASM.USED.PCT.MAX.WARN}

{TEMPLATE_NAME:oracle.asm_used_pct["{#DG_NAME}"].min(5m)}>{$ORACLE.ASM.USED.PCT.MAX.HIGH} HIGH

Feedback

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

Add your solution