col owner format a20
col OBJECT_NAME format a20
col OBJECT_TYPE format a30
col ADVISOR_NAME format a50
set line 300
select OWNER, OBJECT_NAME, OBJECT_TYPE, OBJECT_TYPE from dba_outstanding_alerts;
To check threshold
vi dba_thresholds.sql
SET LINESIZE 200
COLUMN tablespace_name FORMAT A30
COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15
SELECT object_name AS tablespace_name,
metrics_name,
warning_operator,
warning_value,
critical_operator,
critical_value
FROM dba_thresholds
WHERE object_type = '&object_type_TABLESPACE'
ORDER BY object_name;
vi dba_tablespace_threholds
COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15
SELECT tablespace_name,
contents,
extent_management,
threshold_type,
metrics_name,
warning_operator,
warning_value,
critical_operator,
critical_value
FROM dba_tablespace_thresholds
ORDER BY tablespace_name;
To set all tablespace threshold
vi set_all_tablespace_threhold.sql
SET VERIFY OFF
DECLARE
g_warning_value VARCHAR2(4) := '&1';
g_warning_operator VARCHAR2(4) := DBMS_SERVER_ALERT.OPERATOR_GE;
g_critical_value VARCHAR2(4) := '&2';
g_critical_operator VARCHAR2(4) := DBMS_SERVER_ALERT.OPERATOR_GE;
PROCEDURE set_threshold(p_ts_name IN VARCHAR2) AS
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => g_warning_operator,
warning_value => g_warning_value,
critical_operator => g_critical_operator,
critical_value => g_critical_value,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => p_ts_name);
END;
BEGIN
IF g_warning_value = 'NULL' THEN
g_warning_value := NULL;
g_warning_operator := NULL;
END IF;
IF g_critical_value = 'NULL' THEN
g_critical_value := NULL;
g_critical_operator := NULL;
END IF;
FOR cur_ts IN (SELECT tablespace_name
FROM dba_tablespace_thresholds
WHERE warning_operator != 'DO NOT CHECK'
AND extent_management = 'LOCAL')
LOOP
set_threshold(cur_ts.tablespace_name);
END LOOP;
END;
/
SET VERIFY ON
SQL> @/media/sf_scripts_agap/dba_tablespace_threshold
TABLESPACE_NAME CONTENTS EXTENT_MAN THRESHOL METRICS_NAME WARNING_OPER WARNING_VALUE CRITICAL_OPE CRITICAL_VALUE
------------------------------ --------- ---------- -------- ------------------------------ ------------ ------------------------------ ------------ ---------------
AGAP PERMANENT LOCAL EXPLICIT Tablespace Space Usage GE 50 GE 75
SYSAUX PERMANENT LOCAL EXPLICIT Tablespace Space Usage GE 50 GE 75
SYSTEM PERMANENT LOCAL EXPLICIT Tablespace Space Usage GE 50 GE 75
TEMP1 TEMPORARY LOCAL EXPLICIT Tablespace Space Usage DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO NOT CHECK 0
UNDOTBS1 UNDO LOCAL EXPLICIT Tablespace Space Usage DO NOT CHECK <SYSTEM-GENERATED THRESHOLD> DO NOT CHECK 0
USERS PERMANENT LOCAL EXPLICIT Tablespace Space Usage GE 50 GE 75
6 rows selected.
To set specified tablespace threshold
vi set_specified_tablespace_threhold.sql
SET VERIFY OFF
DECLARE
g_warning_value VARCHAR2(4) := '&1';
g_warning_operator VARCHAR2(4) := DBMS_SERVER_ALERT.OPERATOR_GE;
g_critical_value VARCHAR2(4) := '&2';
g_critical_operator VARCHAR2(4) := DBMS_SERVER_ALERT.OPERATOR_GE;
PROCEDURE set_threshold(p_ts_name IN VARCHAR2) AS
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => g_warning_operator,
warning_value => g_warning_value,
critical_operator => g_critical_operator,
critical_value => g_critical_value,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => p_ts_name);
END;
BEGIN
IF g_warning_value = 'NULL' THEN
g_warning_value := NULL;
g_warning_operator := NULL;
END IF;
IF g_critical_value = 'NULL' THEN
g_critical_value := NULL;
g_critical_operator := NULL;
END IF;
FOR cur_ts IN (SELECT tablespace_name
FROM dba_tablespace_thresholds
WHERE warning_operator != 'DO NOT CHECK'
AND extent_management = 'LOCAL' and tablespace_name='&tablespace_name')
LOOP
set_threshold(cur_ts.tablespace_name);
END LOOP;
END;
/
SET VERIFY ON
Sending notification after an event has remained open for a specified period -link
metrics_id |
The internal name of the metrics.
|
warning_operator |
The operator for the comparing the actual value with the warning threshold.
|
warning_value |
The warning threshold value.
|
critical_operator |
The operator for the comparing the actual value with the critical threshold.
|
critical_value |
The critical threshold value.
|
observation_period |
The period at which the metrics values are computed and verified against the threshold setting.
|
consecutive_occurrences |
The number of observation periods the metrics value should violate the threshold value before the alert is issued.
|
instance_name |
The name of the instance for which the threshold is set. This is
NULL for database-wide alerts. |
object_type |
Either
OBJECT_TYPE_SYSTEM or OBJECT_TYPE_SERVICE. |
object_name |
The name of the object.
|
to set threshold
dbms_server_alert.set_threshold(
metrics_id IN NUMBER,
warning_operator IN NUMBER,
warning_value IN VARCHAR2,
critical_operator IN NUMBER,
critical_value IN VARCHAR2,
observation_period IN NUMBER,
consecutive_occurrences IN NUMBER,
instance_name IN VARCHAR2,
object_type IN NUMBER,
object_name IN VARCHAR2);
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value => '10',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value => '20',
observation_period => 1,
consecutive_occurrences => 3,
instance_name => 'ZMYDB',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'CUST'
);
END;
/
| Metrics Name (Internal) | Metrics Name (External) | Units |
|---|---|---|
SQL_SRV_RESPONSE_TIME |
Service Response (for each execution)
|
Seconds
|
BUFFER_CACHE_HIT |
Buffer Cache Hit (%)
|
% of cache accesses
|
LIBRARY_CACHE_HIT |
Library Cache Hit (%)
|
% of cache accesses
|
LIBRARY_CACHE_MISS |
Library Cache Miss (%)
|
% of cache accesses
|
MEMORY_SORTS_PCT |
Sorts in Memory (%)
|
% of sorts
|
REDO_ALLOCATION_HIT |
Redo Log Allocation Hit
|
% of redo allocations
|
TRANSACTION_RATE |
Number of Transactions (for each second)
|
Transactions for each Second
|
PHYSICAL_READS_SEC |
Physical Reads (for each second)
|
Reads for each Second
|
PHYSICAL_READS_TXN |
Physical Reads (for each transaction)
|
Reads for each Transaction
|
PHYSICAL_WRITES_SEC |
Physical Writes (for each second)
|
Writes for each Second
|
PHYSICAL_WRITES_TXN |
Physical Writes (for each transaction)
|
Writes for each Transaction
|
PHYSICAL__READS_DIR_SEC |
Direct Physical Reads (for each second)
|
Reads for each Second
|
PHYSICAL_READS_DIR_TXN |
Direct Physical Reads (for each transaction)
|
Reads for each Transaction
|
PHYSICAL_WRITES_DIR_SEC |
Direct Physical Writes (for each second)
|
Writes for each Second
|
PHYSICAL_WRITES_DIR_TXN |
Direct Physical Writes (for each transaction)
|
Writes for each Transaction
|
PHYSICAL_READS_LOB_SEC |
Direct LOB Physical Reads (for each second)
|
Reads for each Second
|
PHYSICAL_READS_LOB_TXN |
Direct LOB Physical Reads (for each transaction)
|
Reads for each Transaction
|
PHYSICAL_WRITES_LOB_SEC |
Direct LOB Physical Writes (for each second)
|
Writes for each Second
|
PHYSICAL_WRITES_LOB_TXN |
Direct LOB Physical Writes (for each transaction)
|
Writes for each Transaction
|
REDO_GENERATED_SEC |
Redo Generated (for each second)
|
Redo Bytes for each Second
|
REDO_GENERATED_TXN |
Redo Generated (for each transaction)
|
Redo Bytes for each Transaction
|
DATABASE_WAIT_TIME |
Database Wait Time (%)
|
% of all database time
|
DATABASE_CPU_TIME |
Database CPU Time (%)
|
% of all database time
|
LOGONS_SEC |
Cumulative Logons (for each second)
|
Logons for each Second
|
LOGONS_TXN |
Cumulative Logons (for each transaction)
|
Logons for each Transaction
|
LOGONS_CURRENT |
Current Number of Logons
|
Number of Logons
|
OPEN_CURSORS_SEC |
Cumulative Open Cursors (for each second)
|
Cursors for each Second
|
OPEN_CURSORS_TXN |
Cumulative Open Cursors (for each transaction)
|
Cursors for each Transaction
|
OPEN_CURSORS_CURRENT |
Current Number of Cursors
|
Number of Cursors
|
USER_COMMITS_SEC |
User Commits (for each second)
|
Commits for each Second
|
USER_COMMITS_TXN |
User Commits (for each transaction)
|
Commits for each Transaction
|
USER_ROLLBACKS_SEC |
User Rollbacks (for each second)
|
Rollbacks for each Second
|
USER_ROLLBACKS_TXN |
User Rollbacks (for each transaction)
|
Rollbacks for each Transaction
|
USER_CALLS_SEC |
User Calls (for each second)
|
Calls for each Second
|
USER_CALLS_TXN |
User Calls (for each transaction)
|
Calls for each Transaction
|
RECURSIVE_CALLS_SEC |
Recursive Calls (for each second)
|
Calls for each Second
|
RECURSIVE_CALLS_TXN |
Recursive Calls (for each transaction)
|
Calls for each Transaction
|
SESS_LOGICAL_READS_SEC |
Session Logical Reads (for each second)
|
Reads for each Second
|
SESS_LOGICAL_READS_TXN |
Session Logical Reads (for each transaction)
|
Reads for each Transaction
|
DBWR_CKPT_SEC |
DBWR Checkpoints (for each second)
|
Checkpoints for each Second
|
LOG_SWITCH_SEC |
Background Checkpoints (for each second)
|
Checkpoints for each Second
|
REDO_WRITES_SEC |
Redo Writes (for each second)
|
Writes for each Second
|
REDO_WRITES_TXN |
Redo Writes (for each transaction)
|
Writes for each Transaction
|
LONG_TABLE_SCANS_SEC |
Scans on Long Tables (for each second)
|
Scans for each Second
|
LONG_TABLE_SCANS_TXN |
Scans on Long Tables (for each transaction)
|
Scans for each Transaction
|
TOTAL_TABLE_SCANS_SEC |
Total Table Scans (for each second)
|
Scans for each Second
|
TOTAL_TABLE_SCANS_TXN |
Total Table Scans (for each transaction)
|
Scans for each Transaction
|
FULL_INDEX_SCANS_SEC |
Fast Full Index Scans (for each second)
|
Scans for each Second
|
FULL_INDEXE_SCANS_TXN |
Fast Full Index Scans (for each transaction)
|
Scans for each Transaction
|
TOTAL_INDEX_SCANS_SEC |
Total Index Scans (for each second)
|
Scans for each Second
|
TOTAL_INDEX_SCANS_TXN |
Total Index Scans (for each transaction)
|
Scans for each Transaction
|
TOTAL_PARSES_SEC |
Total Parses (for each second)
|
Parses for each Second
|
TOTAL_PARSES_TXN |
Total Parses(for each transaction)
|
Parses for each Transaction
|
HARD_PARSES_SEC |
Hard Parses(for each second)
|
Parses for each Second
|
HARD_PARSES_TXN |
Hard Parses(for each transaction)
|
Parses for each Transaction
|
PARSE_FAILURES_SEC |
Parse Failures (for each second)
|
Parses for each Second
|
PARSE_FAILURES_TXN |
Parse Failures (for each transaction)
|
Parses for each Transaction
|
DISK_SORT_SEC |
Sorts to Disk (for each second)
|
Sorts for each Second
|
DISK_SORT_TXN |
Sorts to Disk (for each transaction)
|
Sorts for each Transaction
|
ROWS_PER_SORT |
Rows Processed for each Sort
|
Rows for each Sort
|
EXECUTE_WITHOUT_PARSE |
Executes Performed Without Parsing
|
% of all executes
|
SOFT_PARSE_PCT |
Soft Parse (%)
|
% of all parses
|
CURSOR_CACHE_HIT |
Cursor Cache Hit (%)
|
% of soft parses
|
USER_CALLS_PCT |
User Calls (%)
|
% of all calls
|
TXN_COMMITTED_PCT |
Transactions Committed (%)
|
% of all transactions
|
NETWORK_BYTES_SEC |
Network Bytes, for each second
|
Bytes for each Second
|
RESPONSE_TXN |
Response (for each transaction)
|
Seconds for each Transaction
|
DATA_DICT_HIT |
Data Dictionary Hit (%)
|
% of dictionary accesses
|
DATA_DICT_MISS |
Data Dictionary Miss (%)
|
% of dictionary accesses
|
SHARED_POOL_FREE_PCT |
Shared Pool Free(%)
|
% of shared pool
|
AVERAGE_FILE_READ_TIME |
Average File Read Time
|
Microseconds
|
AVERAGE_FILE_WRITE_TIME |
Average File Write Time
|
Microseconds
|
DISK_IO |
Disk I/O
|
Milliseconds
|
PROCESS_LIMIT_PCT |
Process Limit Usage (%)
|
% of maximum value
|
SESSION_LIMIT_PCT |
Session Limit Usage (%)
|
% of maximum value
|
USER_LIMIT_PCT |
User Limit Usage (%)
|
% of maximum value
|
AVG_USERS_WAITING |
Average Number of Users Waiting on a Class of Wait Events
|
Count of sessions
|
DB_TIME_WAITING |
Percent of Database Time Spent Waiting on a Class of Wait Events
|
% of Database Time
|
APPL_DESGN_WAIT_SCT |
Application Design Wait (by session count)
|
Count of sessions
|
APPL_DESGN_WAIT_TIME |
Application Design Wait (by time)
|
Microseconds
|
PHYS_DESGN_WAIT_SCT |
Physical Design Wait (by session count)
|
Count of sessions
|
PHYS_DESGN_WAIT_TIME |
Physical Design Wait (by time)
|
Microseconds
|
CONTENTION_WAIT_SCT |
Internal Contention Wait (by session count)
|
Count of sessions
|
CONTENTION_WAIT_TIME |
Internal Contention Wait (by time)
|
Microseconds
|
PSERVICE_WAIT_SCT |
Process Service Wait (by session count)
|
Count of sessions
|
PSERVICE_WAIT_TIME |
Process Service Wait (by time)
|
Microseconds
|
NETWORK_MSG_WAIT_SCT |
Network Message Wait (by session count)
|
Count of sessions
|
NETWORK_MSG_WAIT_TIME |
Network Message Wait (by time)
|
Microseconds
|
DISK_IO_WAIT_SCT |
Disk I/O Wait (by session count)
|
Count of sessions
|
OS_SERVICE_WAIT_SCT |
Operating System Service Wait (by session count)
|
Count of sessions
|
OS_SERVICE_WAIT_TIME |
Operating System Service Wait (by time)
|
Microseconds
|
DBR_IO_LIMIT_WAIT_SCT |
Resource Mgr I/O Limit Wait (by session count)
|
Count of sessions
|
DBR_IO_LIMIT_WAIT_TIME |
Resource Mgr I/O Limit Wait (by time)
|
Microseconds
|
DBR_CPU_LIMIT_WAIT_SCT |
Resource Mgr CPU Limit Wait (by session count)
|
Count of sessions
|
DBR_CPU_LIMIT_WAIT_TIME |
Resource Mgr CPU Limit Wait (by time)
|
Microseconds
|
DBR_USR_LIMIT_WAIT_SCT |
Resource Mgr User Limit Wait (by session count)
|
Count of sessions
|
DBR_USR_LIMIT_WAIT_TIME |
Resource Mgr User Limit Wait (by time)
|
Microseconds
|
OS_SCHED_CPU_WAIT_SCT |
Operating System Scheduler CPU Wait (by session count)
|
Count of sessions
|
OS_SCHED_CPU__WAIT_TIME |
Operating System Scheduler CPU Wait (by time)
|
Microseconds
|
CLUSTER_MSG_WAIT_SCT |
Cluster Messaging Wait (by session count)
|
Count of sessions
|
CLUSTER_MSG_WAIT_TIME |
Cluster Messaging Wait (by time)
|
Microseconds
|
OTHER_WAIT_SCT |
Other Waits (by session count)
|
Count of sessions
|
OTHER_WAIT_TIME |
Other Waits (by time)
|
Microseconds
|
ENQUEUE_TIMEOUTS_SEC |
Enqueue Timeouts (for each second)
|
Timeouts for each Second
|
ENQUEUE_TIMEOUTS_TXN |
Enqueue Timeouts (for each transaction)
|
Timeouts for each Transaction
|
ENQUEUE_WAITS_SEC |
Enqueue Waits (for each second)
|
Waits for each Second
|
ENQUEUE_WAITS_TXN |
Enqueue Waits (for each transaction)
|
Waits for each Transaction
|
ENQUEUE_DEADLOCKS_SEC |
Enqueue Deadlocks (for each second)
|
Deadlocks for each Second
|
ENQUEUE_DEADLOCKS_TXN |
Enqueue Deadlocks (for each transaction)
|
Deadlocks for each Transaction
|
ENQUEUE_REQUESTS_SEC |
Enqueue Requests (for each second)
|
Requests for each Second
|
ENQUEUE_REQUESTS_TXN |
Enqueue Requests (for each transaction)
|
Requests for each Transaction
|
DB_BLKGETS_SEC |
DB Block Gets (for each second)
|
Gets for each Second
|
DB_BLKGETS_TXN |
DB Block Gets (for each transaction)
|
Gets for each Transaction
|
CONSISTENT_GETS_SEC |
Consistent Gets (for each second)
|
Gets for each Second
|
CONSISTENT_GETS_TXN |
Consistent Gets (for each transaction)
|
Gets for each Transaction
|
DB_BLKCHANGES_SEC |
DB Block Changes (for each second)
|
Changes for each Second
|
DB_BLKCHANGES_TXN |
DB Block Changes (for each transaction)
|
Changes for each Transaction
|
CONSISTENT_CHANGES_SEC |
Consistent Changes (for each second)
|
Changes for each Second
|
CONSISTENT_CHANGES_TXN |
Consistent Changes (for each transaction)
|
Changes for each Transaction
|
SESSION_CPU_SEC |
Database CPU (for each second)
|
Microseconds for each Second
|
SESSION_CPU_TXN |
Database CPU (for each transaction)
|
Microseconds for each Transaction
|
CR_BLOCKS_CREATED_SEC |
CR Blocks Created (for each second)
|
Blocks for each Second
|
CR_BLOCKS_CREATED_TXN |
CR Blocks Created (for each transaction)
|
Blocks for each Transaction
|
CR_RECORDS_APPLIED_SEC |
CR Undo Records Applied (for each second)
|
Records for each Second
|
CR_RECORDS_APPLIED_TXN |
CR Undo Records Applied (for each transaction)
|
Records for each Transaction
|
RB_RECORDS_APPLIED_SEC |
Rollback Undo Records Applied (for each second)
|
Records for each Second
|
RB_RECORDS_APPLIED_TXN |
Rollback Undo Records Applied(for each transaction)
|
Records for each Transaction
|
LEAF_NODE_SPLITS_SEC |
Leaf Node Splits (for each second)
|
Splits for each Second
|
LEAF_NODE_SPLITS_TXN |
Leaf Node Splits (for each transaction)
|
Splits for each Transaction
|
BRANCH_NODE_SPLITS_SEC |
Branch Node Splits (for each second)
|
Splits for each Second
|
BRANCH_NODE_SPLITS_TXN |
Branch Node Splits (for each transaction)
|
Splits for each Transaction
|
GC_BLOCKS_CORRUPT |
Global Cache Blocks Corrupt
|
Blocks
|
GC_BLOCKS_LOST |
Global Cache Blocks Lost
|
Blocks
|
GC_AVG_CR_GET_TIME |
Global Cache CR Request
|
Milliseconds
|
GC_AVG_CUR_GET_TIME |
Global Cache Current Request
|
Milliseconds
|
PX_DOWNGRADED_SEC |
Downgraded Parallel Operations (for each second)
|
Operations for each Second
|
PX_DOWNGRADED_25_SEC |
Downgraded to 25% and more (for each second)
|
Operations for each Second
|
PX_DOWNGRADED_50_SEC |
Downgraded to 50% and more (for each second)
|
Operations for each Second
|
PX_DOWNGRADED_75_SEC |
Downgraded to 75% and more (for each second)
|
Operations for each Second
|
PX_DOWNGRADED_SER_SEC |
Downgraded to serial (for each second)
|
Operations for each Second
|
BLOCKED_USERS |
Number of Users blocked by some Session
|
Number of Users
|
PGA_CACHE_HIT |
PGA Cache Hit (%)
|
% bytes processed in PGA
|
ELAPSED_TIME_PER_CALL |
Elapsed time for each user call for each service
|
Microseconds for each call
|
CPU_TIME_PER_CALL |
CPU time for each user call for each service
|
Microseconds for each call
|
TABLESPACE_PCT_FULL |
Tablespace space usage
|
% full
|
col OBJECT_TYPE format a20
col reason format a50
col SUGGESTED_ACTION format a50
col ADVISOR_NAME format a30
col OBJECT_NAME format a20
select
object_type,
object_name,
reason,
suggested_action,
time_suggested,
resolution,
advisor_name,
metric_value,
message_type,
message_group,
message_level
from
dba_alert_history
where
-- creation_time <= sysdate-1 and
resolution = 'cleared'
order by
creation_time desc;
object_type,
object_name,
reason,
suggested_action,
time_suggested,
resolution,
advisor_name,
metric_value,
message_type,
message_group,
message_level
from
dba_alert_history
where
-- creation_time <= sysdate-1 and
resolution = 'cleared'
order by
creation_time desc;
