The Archive Area Used (%) metric returns the percentage of space used in the archived redo log destination. If the space used is more than the threshold value given in the threshold arguments, then a warning or critical alert is generated.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each Archive Area Destination object.

If warning or critical threshold values are currently set for any Archive Area Destination object, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each Archive Area Destination object, use the Edit Thresholds page.

Data Source

The formula used for each of the three archive area scenarios is as follows:

• Quota is set: Regardless of whether the destination is using ASM or a conventional filesystem location, if the QUOTA_SIZE attribute is specified in the associated LOG_ARCHIVE_DEST_n parameter (meaning there is a quota specified for the destination), the percentage is calculated using the following formula:

  Archive Area Used (%) = (QUOTA_USED/QUOTA_SIZE) * 100

• No quota is set:

  • Archive area on regular filesystem: Free and used space in the archive area is determined by running the UNIX df -k command against the filesystem on which the archive area resides.

  • Archive area on ASM diskgroup: The space used in the archive area is calculated by first determining the total diskgroup size (minus space required for redundancy management) and dividing that by the diskgroup redundancy factor (1 for External, 2 for Normal, 3 for High) to arrive at the "Total Safely Usable" space. Then, the "Safely Usable Free" space is determined, which is the free space that can be safely utilized taking mirroring and redundancy needs into account. The SQL used to determine these values is as follows:

    select (((NVL(dg.total_mb,0) -            
    NVL(dg.required_mirror_free_mb,0))*1024)/decode(dg.type,'EXTERN',1,'NORMAL',2,'HIGH',3,1)) 
    TotalSafelyUsable,NVL(dg.usable_file_mb,0)*1024 SafelyUsableFree from V$ASM_DISKGROUP_STAT dg 
    where state in ('CONNECTED', 'MOUNTED') and name='$diskGroup'";

    Using the values from this query, the Archive Area Used (%) is calculated as follows:

    Archive Area Used (%) = [(TotalSafelyUsable – SafelyUsableFree)/TotalSafelyUsable] * 100

User Action

Verify that the database archived redo log destination parameters are configured properly. For more information, see Specifying Archive Destinations in Oracle Database Administrator’s Guide.

This metric returns the total space used (in KB) on the device containing the archived redo log destination directory.

Data Source

The formula used for each of the three archive area scenarios is as follows:

• Quota is set: Same underlying methodology as described above for the Archive Area Used (%) metric, but using the following formula:

  Archive Area Used (KB) = QUOTA_USED

• No quota is set:

  • Archive area on regular filesystem: Same underlying methodology as described above for the Archive Area Used (%) metric.

  • Archive area on ASM diskgroup: Same underlying methodology as described above for the Archive Area Used (%) metric, but using the following formula (referencing the values from the SQL query in the Archive Area Used (%) metric):

    Archive Area Used (KB) = TotalSafelyUsable – SafelyUsableFree

User Action

Verify that the database archived redo log destination parameters are configured properly. For more information, see Specifying Archive Destinations in Oracle Database Administrator’s Guide.

This metric returns the free space (in KB) on the device containing the archived redo log destination directory.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each Archive Area Destination object.

If warning or critical threshold values are currently set for any Archive Area Destination object, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each Archive Area Destination object, use the Edit Thresholds page.

Data Source

The formula used for each of the three archive area scenarios is as follows:

• Quota is set: Same underlying methodology as described above for the Archive Area Used (%) metric, but using the following formula:

  Free Archive Area (KB) = QUOTA_SIZE - QUOTA_USED

• No quota is set:

  • Archive area on regular filesystem: Same underlying methodology as described above for the Archive Area Used (%) metric.

  • Archive area on ASM diskgroup: Same underlying methodology as described above for the Archive Area Used (%) metric, but using the following formula (referencing the values from the SQL query in the Archive Area Used (%) metric):

    Free Archive Area (KB) = SafelyUsableFree

User Action

Verify that the database archived redo log destination parameters are configured properly. For more information, see Specifying Archive Destinations in Oracle Database Administrator’s Guide.

This metric returns the total space (in KB) on the device containing the archived redo log destination directory.

Data Source

The formula used for each of the three archive area scenarios is as follows:

• Quota is set: Same underlying methodology as described above for the Archive Area Used (%) metric, but using the following formula:

  Total Archive Area (KB) = QUOTA_SIZE

• No quota is set:

  • Archive area on regular filesystem: Same underlying methodology as described above for the Archive Area Used (%) metric.

  • Archive area on ASM diskgroup: Same underlying methodology as described above for the Archive Area Used (%) metric, but using the following formula (referencing the values from the SQL query in the Archive Area Used (%) metric):

    Total Archive Area (KB) = TotalSafelyUsable

User Action

Verify that the database archived redo log destination parameters are configured properly. For more information, see Specifying Archive Destinations in Oracle Database Administrator’s Guide.

This metric generates a critical alert on the primary database if the fast-start failover (FSFO) configuration is in an unobserved condition, indicating that FSFO is not currently possible.

User Action

If the Data Guard configuration was configured in Enterprise Manager to use the automatic Observer restart feature, the alert will clear after a new observer process is restarted. Otherwise, determine the cause of the unobserved condition, and restart the Observer process if necessary.

This metric displays (in seconds) how far the standby is behind the primary.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Data Source

The data source for this metric is the following command:

v$dataguard_stats('apply lag')

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric shows the approximate number of seconds required to failover to this standby database. This accounts for the startup time, if necessary, plus the remaining time required to apply all the available redo on the standby. If a bounce is not required, it is only the remaining apply time.

Data Source

The data source for this metric is the following command:

v$dataguard_stats ('estimated startup time','apply finish time','standby has been open')

Displays the Redo Apply Rate in KB/second on this standby.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

The approximate number of seconds of redo not yet available on this standby database. This may be because the redo has not yet been shipped or there may be a gap.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Data Source

The data source for this metric is the following command:

v$dataguard_stats('transport lag')

Transport Lag metrics are computed based on data that is periodically received from the primary database. An unchanging value in this column across multiple queries indicates that the standby database is not receiving data from the primary database.

Data Source

DATUM_TIME in v$dataguard_stats

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Use the Data Guard Status metric to check the status of each database in the Data Guard configuration.

By default, a critical and warning threshold value was set for this metric column. Alerts will be generated when threshold values are reached. You can edit the value for a threshold as required.

Data Source

1. Check the Edit Properties General page for the primary and standby databases for detailed information.

2. Examine the database alert logs and the Data Guard broker logs for additional information.

This metric monitors how many instances are in an open state.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

The Oracle Server job queue is a database table that stores information about local jobs such as the PL/SQL call to execute for a job such as when to run a job. Database replication is also managed by using the Oracle job queue mechanism using jobs to push deferred transactions to remote master sites, to purge applied transactions from the deferred transaction queue, or to refresh snapshot refresh groups.

A job can be broken in two ways:

Oracle has failed to successfully execute the job after sixteen attempts. The job has been explicitly marked as broken by using the procedure DBMS_ JOB.BROKEN.

This metric checks for broken DBMS jobs. A critical alert is generated if the number of broken jobs exceeds the value specified by the threshold argument.

Data Source

The data source for this metric is the following command:

SELECT COUNT(*) 
  FROM dba_jobs 
  WHERE broken < > 'N' 

User Action

Check the ALERT log and trace files for error information. Correct the problem that is preventing the job from running. Force immediate re-execution of the job by calling DBMS_SCHEDULER.RUN.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

The Oracle Server job queue is a database table that stores information about local jobs such as the PL/SQL call to execute for a job such as when to run a job. Database replication is also managed by using the Oracle job queue mechanism using jobs to push deferred transactions to remote master sites, to purge applied transactions from the deferred transaction queue or to refresh snapshot refresh groups.

If a job returns an error while Oracle is attempting to execute it, the job fails. Oracle repeatedly tries to execute the job doubling the interval of each attempt. If the job fails sixteen times, Oracle automatically marks the job as broken and no longer tries to execute it.

This metric checks for failed DBMS jobs. An alert is generated if the number of failed job exceeds the value specified by the threshold argument.

Data Source

The data source for this metric is the following command:

SELECT COUNT(*) 
  FROM dba_jobs 
  WHERE NVL(failures, 0) < >  0" 

User Action

Check the ALERT log and trace files for error information. Correct the problem that is preventing the job from running.

This metric represents the active sessions using CPU.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This database-level metric represents the active sessions waiting for I/O. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This database-level metric represents all the waits that are neither idle nor user I/O. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric represents the average database CPU across instances as a percentage.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric represents the percentage of CPU being used across hosts.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric reports the sum of the current CPU load for all cluster database hosts.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric represents the total CPU count across all the cluster database hosts.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric represents the percentage of time spent waiting, database-wide, for resources or objects during this sample period.

This test checks the percentage time spent waiting, database-wide, for resources or objects during this sample period. If the % Wait Time is greater than or equal to the threshold values specified by the threshold arguments, and the number of occurrences exceeds the value specified in the Number of Occurrences parameter, then a warning or critical alert is generated.

Data Source

The data source for this metric is the following formula where:

• DeltaTotalWait: Difference of 'sum of time waited for all wait events in v$system_event' between sample end and start.

• DeltaCpuTime: Difference of 'select value from v$sysstat where name='CPU used by this session' between sample end and start.

DeltaTotalWait / (DeltaTotalWait + DeltaCpuTime)

User Action

Investigate further into which specific wait events are responsible for the bulk of the wait time. Individual wait events may identify unique problems within the database. Diagnosis will be tailored where appropriate through drilldowns specific to individual wait events.

This metric raises an alert, which helps the Oracle Database Vault security analyst to monitor violation attempts on the Database Vault database. This user can select the command rules to be affected by the alert and filter these command rules based on the different types of attempts by using error codes.

This metric raises an alert, which helps the Oracle Database Vault security analyst to monitor violation attempts on the Database Vault database. This user can select the realms to be affected by the alert and filter these realms based on the different types of attempts by using error codes.

This metric tracks and raises an alert if users misconfigure realms.

This metric tracks and raises an alert if users misconfigure command rules.

This metric raises an alert on any change to any Database Vault policy, such as policies for realms and command rules.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Oracle uses deferred transactions to propagate data-level changes asynchronously among master sites in an advanced replication system as well as from an updatable snapshot to its master table.

This metric checks for the number of deferred transactions. An alert is generated if the number of deferred transactions exceeds the value specified by the threshold argument.

Data Source

The data source for this metric is the following command:

SELECT count(*) 
  FROM sys.deftran 

User Action

When the advanced replication facility pushes a deferred transaction to a remote site, it uses a distributed transaction to ensure that the transaction has been properly committed at the remote site before the transaction is removed for the queue at the local site. If transactions are not being pushed to a given remote site, verify that the destination for the transaction was correctly specified. If you specify a destination database when calling DBMS_DEFER_SYS.SCHEDULE_EXECUTION using the DBLINK parameter, or DBMS_DEFER_SYS.EXECUTE using the DESTINATION parameter, make sure the full database link is provided.

Wrong view destinations can lead to erroneous deferred transaction behavior. Verify that the DEFCALLEST and DEFTRANDEST views are the definitions from the CATREPC.SQL and not those from CATDEFER.SQL.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Oracle uses deferred transactions to propagate data-level changes asynchronously among master sites in an advanced replication system as well as from an updatable snapshot to its master table. If a transaction is not successfully propagated to the remote site, Oracle rolls back the transaction, logs the transaction in the SYS.DEFERROR view in the remote destination database.

This metric checks for the number of transactions in SYS.DEFERROR view and raises an alert if it exceeds the value specified by the threshold argument.

Data Source

The data source for this metric is the following command:

SELECT count(*) 
  FROM sys.deferror 

User Action

An error in applying a deferred transaction may result from a database problem, such as a lack of available space in the table to be updated, or may be the result of an unresolved insert, update, or delete conflict. The SYS.DEFERROR view provides the ID of the transaction that could not be applied. Use this ID to locate the queued calls associated with the transaction. These calls are stored in the SYS.DEFCALL view. You can use the procedures in the DBMS_DEFER_QUERY package to determine the arguments to the procedures listed in the SYS.DEFCALL view.

This metric tracks and raises an alert when a defined number of Exadata module version errors occur.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric checks for the number of failed logins on the target database. This check is performed every ten minutes and returns the number of failed logins for that ten-minute interval. This metric will only work for databases where the audit_trail initialization parameter is set to DB or XML and the session is being audited.

If the failed login count crosses the values specified in the threshold arguments, then a warning or critical alert is generated. Because it is important to know every time a significant number of failed logins occurs on a system, this metric will generate a new alert for any ten-minute interval where the thresholds are crossed. You can manually clear these alerts. They will not automatically clear after the next collection.

Data Source

The database stores login information in different views based on the audit_trail setting. The database views used are:

• DB or DB_EXTENDED: DBA_AUDIT_SESSION

• XML (10g Release 2 only): DBA_COMMON_AUDIT_TRAIL

Formerly referred to as flash recovery area, this metric returns an optional disk location that you can use to store recovery-related files such as control file and online redo log copies, archived redo log files, flashback logs, and RMAN backups.

Oracle Database and RMAN manage the files in the fast recovery area automatically. You can specify the disk quota, which is the maximum size of the fast recovery area.

Data Source

The data source for this metric is the following command:

SELECT value 
  FROM v$parameter 
  WHERE name='db_recovery_file_dest';

User Action

No user action is required.

This metric returns the Fast Recovery Area Size.

Data Source

The data source for this metric is the following command:

SELECT value 
  INTO 1_fast_recovery_size
  FROM v$parameter 
  WHERE name='db_recovery_file_dest_size';

User Action

No user action is required.

This metric returns whether or not flashback logging is enabled - YES, NO, or RESTORE POINT ONLY. For the RESTORE POINT ONLY option, flashback is ON but you can only flashback to guaranteed restore points.

Data Source

The data source for this metric is the following command:

SELECT flashback_on 
  FROM v$database;

User Action

No user action is required.

This metric returns the log mode of the database - ARCHIVELOG or NOARCHIVELOG.

Data Source

The data source for this metric is the following command:

SELECT log_mode 
  FROM v$database;

User Action

No user action is required.

This metric represents the percentage of space non-reclaimable (spaced used minus space reclaimable) in the fast recovery area.

Data Source

The data source for this metric is one of the following commands:

Non-reclaimable = space used - space reclaimable

Space Used:
  SELECT SUM(PERCENT_SPACE_USED 
     FROM v$fast_recovery_area_usage;

Space Reclaimable:
  SELECT SUM(PERCENT_SPACE_RECLAIMABLE) 
     FROM v$fast_recovery_area_usage;

User Action

No user action is required.

This metric returns the oldest point-in-time to which you can flashback your database.

Data Source

The data source for this metric is the following command:

SELECT to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS') 
  FROM v$flashback_database_log;

User Action

No user action is required.

This metric represents the percentage of space reclaimable in the fast recovery area.

Data Source

The data source for this metric is the following command:

Space Reclaimable:
  SELECT SUM(PERCENT_SPACE_RECLAIMABLE) 
     FROM v$fast_recovery_area_usage;

User Action

No user action is required.

This metric represents the percentage of space usable in the fast recovery area. The space usable is composed of the space that is free in addition to the space that is reclaimable.

Data Source

The data source for this metric is the following command:

SELECT (CASE WHEN PERCENT_USED > 100 THEN 0 ELSE (100-PERCENT_USED) END) 
PERCENT_FREE
   FROM (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) 
PERCENT_USED
   FROM V$FAST_RECOVERY_AREA_USAGE);

User Action

No user action is required.

This metric collects the total number of text indexes that have crossed the fragmentation percentage threshold specified by the user.

This metric collects the number of text indexes that have crossed the critical fragmentation percentage threshold specified by the user.

Data Source

The fragmentation percentage for each index or index partition is derived by computing the data from DBA_IND_PARTITIONS, CTXSYS.CTX_INDEX_PARTITIONS, and its relevant text index metadata tables. The list of text indexes and the critical percentage threshold against which their fragmentation is to be evaluated are specified by the user as part of the "Evaluate and Fix Text Index Fragmentation" job.

User Action

A metric threshold could be set to generate incidents on the number of text indexes that have crossed the critical fragmentation threshold specified in "Evaluate and Fix Text Index Fragmentation" job. The scheduled DBMS job would automatically attempt to fix such text indexes (if they were specified in the fix list) by rebuilding them (using shadow creation). In addition, the incident also enables the user to fix the fragmented text indexes from the Enterprise Manager console.

This metric collects the total number of text indexes that have crossed the warning fragmentation percentage threshold, but not the critical percentage threshold, specified by the user.

Data Source

The fragmentation percentage for each index or index partition is derived by computing the data from DBA_IND_PARTITIONS, CTXSYS.CTX_INDEX_PARTITIONS, and its relevant text index metadata tables. The list of text indexes and the warning percentage threshold against which their fragmentation is to be evaluated are specified by the user as part of the "Evaluate and Fix Text Index Fragmentation" job.

User Action

A metric threshold could be set to generate incidents on the number of text indexes that have crossed the warning fragmentation threshold, but not the critical threshold, specified in "Evaluate and Fix Text Index Fragmentation" job. The scheduled DBMS job would automatically attempt to fix such text indexes (if they were specified in the fix list) by optimizing them. In addition, the incident also enables the user to fix the fragmented text indexes from the Enterprise Manager console.

This metric represents the total number of invalid objects in the database.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Data Source

The data is derived from the SYS.OBJ$ and SYS.USER$ tables.

User Action

The “Recompile Invalid Objects” corrective action could be setup against the incident to automatically attempt to recompile the invalid objects in the database. Some objects might need specific corrective steps to be performed manually before re-compilation.

This metric represents the total number of invalid objects per schema.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Multiple Thresholds

Different warning and critical threshold values could be set for each Invalid Object Owner (schema) object.

If warning or critical threshold values are currently set for any Invalid Object Owner object, those thresholds could be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each Invalid Object Owner object, use the Edit Thresholds page.

Data Source

The data is derived from the SYS.OBJ$ and SYS.USER$ tables.

User Action

The “Recompile Invalid Objects” corrective action could be setup against the incident to automatically attempt to recompile the invalid objects in a schema. Some objects might need specific corrective steps to be performed manually before recompilation.

This metric provides the average age (in seconds) of the messages in the buffered queue for all nonsystem queues in the database.

This metric gives the age (in seconds) of the first message in the buffered queue for all non-system queues in the database.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each unique combination of Schema Name and Queue Name objects.

If warning or critical threshold values are currently set for any unique combination of Schema Name and Queue Name objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of Schema Name and Queue Name objects, use the Edit Thresholds page.

Data Source

This metric is calculated by finding the age of the first message in all the subscribers of the queue and then the oldest amongst all is taken.

The following views and tables are used for the calculation:

1. <SCHEMA>.AQ$<QUEUE_TABLE>

2. v$buffered_queues

User Action

When using buffered queues for storing and propagating messages, monitor this metric to get the age of first message in the queue.

This metric gives the messages processed percentage per minute per buffered queue in the last collection interval of the metric.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each unique combination of Schema Name and Queue Name objects.

If warning or critical threshold values are currently set for any unique combination of Schema Name and Queue Name objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of Schema Name and Queue Name objects, use the Edit Thresholds page.

Data Source

This is calculated as the percent of total number of messages processed per minute and total number of messages received per minute in the last collection interval per buffered queue.

User Action

When using queues for storing/propagating messages, monitor this metric to get the messages processed percent (or throughput) per minute in the last collection interval for the queue.

This metric gives the messages processed percentage per minute in the last interval per buffered queue in the last collection interval of the metric.

This metric displays the current number of overflow messages spilled to disk from the buffered queue.

This metric gives the total number of messages processed per minute per buffered queue in the last collection interval of the metric.

This metric gives the total number of messages received or enqueued into the buffered queue per minute in the last collection interval of the metric.

This metric display's the average age of messages in the buffered queue per queue in seconds.

This metric displays the age of the first message in the buffered queue per queue per subscriber in seconds.

This metric gives the total number of messages processed per minute per buffered queue subscriber in the last collection interval of the metric.

This metric gives the messages processed percentage for the buffered queue per subscriber. Messages processed percent is calculated as the percent of the total number messages processed or dequeued to the total number of messages received or enqueued.

This metric gives the total number of messages processed per minute per buffered queue subscriber in the last collection interval of the metric.

This metric gives the total number of messages received or enqueued into the queue per subscriber per minute in the last collection interval of the metric.

This metric displays the average age of messages in the persistent queue per queue in seconds.

This metric gives the age (in seconds) of the first message in the persistent queue for all non-system queues in the database.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each unique combination of Schema Name and Queue Name objects.

If warning or critical threshold values are currently set for any unique combination of Schema Name and Queue Name objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of Schema Name and Queue Name objects, use the Edit Thresholds page.

Data Source

This metric is calculated by finding the age of the first message in all the subscribers of the queue and then the oldest amongst all is taken.

The following views/tables are used for the calculation:

1. <SCHEMA>.AQ$_<QUEUE_TABLE>_S

2. <SCHEMA>.AQ$_<QUEUE_TABLE>_I

3. <SCHEMA>.AQ$<QUEUE_TABLE>

User Action

When using persistent queues for storing and propagating messages, monitor this metric to get the age of first message in the queue.

This metric gives the messages processed percentage for the persistent queue. Messages processed percent is calculated as the percent of the total number messages processed or dequeued to the total number of messages received or enqueued.

This metric gives the messages processed percentage per minute per persistent queue in the last collection interval of the metric.

This metric gives the total number of messages processed per minute per persistent queue in the last collection interval of the metric.

This metric gives the total number of messages received or enqueued into the queue per minute in the last collection interval of the metric.

This metric display's the average age of messages in the persistent queue per queue in seconds.

This metric gives the age (in seconds) of the first message in the persistent queue per subscriber for all non-system queues in the database.

This metric gives the messages processed percentage per minute per persistent queue subscriber in the last collection interval of the metric.

This metric gives the messages processed percentage for the persistent queue per subscriber. Messages processed percent is calculated as the percent of the total number messages processed or dequeued to the total number of messages received or enqueued.

This metric gives the messages processed percentage per minute per persistent queue subscriber in the last collection interval of the metric.

This metric gives the total number of messages received or enqueued into the queue per subscriber per minute in the last collection interval of the metric.

This metric monitors the expiry of the Monitoring User account password, and raises an alert when the password is not updated in Oracle Enterprise Manager's target configuration.

This metric monitors the potential expiry of the Monitoring User account.

This metric checks whether the Monitoring User account has monitoring privileges on par with the DBSNMP or SYSDBA user accounts. This is useful when using a non-DBSNMP user account.

The collection is disabled by default.

This metric tracks the negative PSM duration and raises an alert when it exceeds its threshold.

Performance Satisfaction Metric (PSM) is a normalized numeric value that indicates how well a particular Performance Objective is being met, and which enables Oracle Database QoS Management to compare the performance of the system for widely differing Performance Objectives.

This metric represents the count of corrupt data blocks.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Metric Summary 9iR2 or higher Evaluated and Collected every 15 minutes Operator > Warning Threshold - 0 Critical Threshold - Not Defined Number of corrupt data blocks is %value%.

Data Source

The data source for this metric is the following command:

SELECT count(unique(file#)) 
  FROM v$database_block_corruption;

User Action

Perform a database recovery.

This metric represents the count of missing media files.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Metric Summary 8i or higher Evaluated and Collected every 15 minutes Operator > Warning Threshold - 0 Critical Threshold - Not Defined Number of missing media files is %value%.

Data Source

This metric is calculated with the following command:

SELECT count(file#) 
  FROM v$datafile_header 
  WHERE recover ='YES' OR error is not null;

User Action

You should perform a database recovery.

This metric represents the recovery area free space as a percentage. The Critical Threshold is set for < 3% and the Warning Threshold is set for < 15%. You cannot customize these thresholds. An alert is returned the first time the alert occurs, and the alert is not cleared until the available space rises above 15%.

User Action

View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.

This metric represents the recovery area used space as a percentage. The Critical threshold is set for < 97% and the Warning threshold is set for < 85% and these can be customized. Any changes will directly be applied on the database.

User Action

View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.

This metric displays the status of the SCN health.

This metric displays the maximum SCN jump in one second over the previous 24 hours.

Oracle uses the Automatic Segment Advisor job to detect segment issues regularly within maintenance windows. It determines whether the segments have unused space that can be released. The Number of recommendations is the number of segments that have Reclaimable Space. The recommendations come from all runs of the automatic segment advisor job and any user-scheduled segment advisor jobs.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric represents the session suspended by a data object limitation.

User Action

View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric represents the session suspended by a quota limitation.

User Action

View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric represents the session suspended by a rollback segment limitation.

User Action

View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric represents the session suspended by a tablespace limitation.

User Action

View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.

This database-level metric represents snapshots too old because of the rollback segment limit. This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

User Action

View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.

This database-level metric represents snapshots too old because of the tablespace limit. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

User Action

View the latest Automatic Database Diagnostic Monitor (ADDM) report. For a more detailed analysis, run ADDM from the Advisor Central link on the Database Home page.

This metric display's the size of buffered queue, which is the total number of Mega bytes allocated for all messages and metadata.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each unique combination of Schema Name and Queue Name objects.

If warning or critical threshold values are currently set for any unique combination of Schema Name and Queue Name objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of Schema Name and Queue Name objects, use the Edit Thresholds page.

Data Source

The source of this metric is the INSTANCE_NAME column from GV$INSTANCE view.

User Action

When using queues for storing or propagating messages, monitor this metric to get the instance in which the buffered queue is available.

This metric gives the space usage percentage of buffered queue with respect to streams pool size per buffered queue.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each unique combination of Schema Name and Queue Name objects.

If warning or critical threshold values are currently set for any unique combination of Schema Name and Queue Name objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of Schema Name and Queue Name objects, use the Edit Thresholds page.

Data Source

The source of this metric is the QUEUE_SIZE AND CURRENT_SIZE columns from GV$BUFFERED_QUEUES and GV$SGA_DYNAMIC_COMPONENTS views.

User Action

When using buffered queues for storing or propagating messages, monitor this metric to get the space usage percentage of buffered queue with respect to the allocated streams pool size.

This metric usually indicates that transactions are staying longer in memory.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each Apply Name object.

If warning or critical threshold values are currently set for any Apply Name object, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each Apply Name object, use the Edit Thresholds page.

Data Source

The source for this metric is the target database in the gv$buffered_queues and gv$buffered_subscribers tables.

User Action

Either increase Streams Pool size and /or increase Apply Parallelism to speed up Apply processing.

This metric shows the percentage of messages in a wait state.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each unique combination of Apply Name and Messages Delivery Mode objects.

If warning or critical threshold values are currently set for any unique combination of Apply Name and Messages Delivery Mode objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of Apply Name and Messages Delivery Mode objects, use the Edit Thresholds page.

Data Source

The data source for this metric is Target Database and Apply Queue.

User Action

No user action is required.

The reader server for an apply process dequeues messages from the queue. The reader server computes dependencies between LCRs and assembles messages into transactions. The reader server then returns the assembled transactions to the coordinator, which assigns them to idle apply servers.

This metric shows the rate at which message are getting spilled (per second) by the reader server for the apply process since the last time the apply process was started.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each Apply Name object.

If warning or critical threshold values are currently set for any Apply Name object, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each Apply Name object, use the Edit Thresholds page.

Data Source

For this metric, the data source is Target database, gv$streams_apply_reader view.

User Action

No user action is required.

Queue spill indicates the messages are staying in memory longer. It can also indicate that the Propagation or Apply Process is slow to consume the enqueued messages.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each Capture Name object. If warning or critical threshold values are currently set for any Capture Name object, those thresholds can be viewed on the Metric Detail page for this metric. To specify or change warning or critical threshold values for each Capture Name object, use the Edit Thresholds page.

Data Source

The source of this metric is the target database in the gv$buffered_queues table view.

User Action

Increase Streams Pool Size to avoid queue spills.

High Latency indicates that the components are slow.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each unique combination of Streams Process Name and Streams Process Type objects.

If warning or critical threshold values are currently set for any unique combination of Streams Process Name and Streams Process Type objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of Streams Process Name and Streams Process Type objects, use the Edit Thresholds page.

Data Source

The data source for this metric is the target database in the gv$streams_capture, gv$propagation_sender, and gv$streams_apply_server views.

User Action

Identify and correct the least performing component in the streams configuration.

This metric collects information about throughput for each capture, propagation and apply component in the database.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each unique combination of Streams Process Name and Streams Process Type objects.

If warning or critical threshold values are currently set for any unique combination of Streams Process Name and Streams Process Type objects, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each unique combination of Streams Process Name and Streams Process Type objects, use the Edit Thresholds page.

Data Source

Not available.

User Action

The required actions are specific to your site.

This metric shows the number of apply processes that have encountered errors at the local database.

Data Source

The information in this metric is in the DBA_APPLY data dictionary view.

User Action

If an apply process has encountered errors, then correct the conditions that caused the errors.

This metric shows the number of capture processes that have encountered errors at the local database.

Data Source

The information in this metric is in the DBA_CAPTURE data dictionary view.

User Action

If a capture process has encountered errors, then correct the conditions that caused the errors.

This metric shows the number of apply processes at the local database.

Data Source

The information in this metric is in the DBA_APPLY data dictionary view.

User Action

Use this metric to determine the total number of apply processes at the local database.

This metric shows the number of capture processes at the local database.

Data Source

The information in this metric is in the DBA_CAPTURE data dictionary view.

User Action

Use this metric to determine the total number of capture processes at the local database.

This metric shows the number of propagations at the local database.

Data Source

The information in this metric is in the DBA_PROPAGATION data dictionary view.

User Action

Use this metric to determine the total number of propagations at the local database.

This metric shows the number of propagations that have encountered errors at the local database.

Data Source

The information in this metric is in the DBA_PROPAGATION data dictionary view.

User Action

If a propagation has encountered errors, then correct the conditions that caused the errors.

This metric collects the number of messages in Ready state for each Propagation process.

Data Source

The source of the data for this metric is the target database in the source and destination queues.

User Action

No user action is required.

This metric represents the number of resumable sessions currently suspended in the database.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

Data Source

This metric is calculated with the following command:

SELECT count(*) 
  FROM v$resumable 
  WHERE status = 'SUSPENDED' and enabled = 'YES' 

User Action

Query the v$resumable view to see what the correctable errors are that are causing the suspension. The method to correct each error depends on the nature of the error.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

The allocated space of a tablespace is the sum of the current size of its data files. A portion of this allocated space is used to store data while some may be free space. If segments are added to a tablespace, or if existing segments grow, they will use the allocated free space. The allocated free space is only available to segments within the tablespace. If, over time, the segments within a tablespace are not using this free space, the allocated free space is not being used.

This metric calculates the space allocated for each tablespace. It is not intended to generate alerts. Rather it should be used in conjunction with the Allocated Space Used (MB) metric to produce a historical view of the amount of space being used and unused by each tablespace.

Data Source

Tablespace Allocated Space (MB) is calculated by looping though the tablespaces data files and totalling the size of the data files.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

The allocated space of a tablespace is the sum of the current size of its data files. Some of this allocated space is used to store data, and some of it may be free space. If segments are added to a tablespace, or if existing segments grow, they will use the allocated free space. The allocated free space is only available to segments within the tablespace. If, over time, the segments within a tablespace are not using this free space, then the allocated free space is being wasted.

This metric calculates the space used for each tablespace. It is not intended to generate alerts. Rather it should be used in conjunction with the Tablespace Allocated Space (MB) metric to produce a historical view of the amount of space being used and unused by each tablespace.

Data Source

Tablespace Used Space (MB) is derived from Tablespace Allocated Space (MB) Tablespace Allocated Free Space (MB) where:

Tablespace Allocated Space (MB) is calculated by looping through the tablespaces data files and totaling the size of the data files.

Tablespace Allocated Free Space (MB) is calculated by looping through the tablespaces data files and totaling the size of the free space in each data file.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

As segments within a tablespace grow, the available free space decreases. If there is no longer any available free space, meaning data files reached their maximum size or there is no more disk space, then the creation of new segments or the extension of existing segments will fail.

This metric checks for the total available free space in each tablespace. This metric is intended for larger tablespaces, where the Available Space Used (%) metric is less meaningful. If the available free space falls below the size specified in the threshold arguments, then a warning or critical alert is generated.

If the version of the monitored database target is Oracle Database 10g Release 1 or later and the tablespace uses Local Extent Management, then the Oracle Database Server evaluates this metric internally every 10 minutes. Alternatively, if the version of the monitored Database target is Oracle 9i or earlier, or the tablespace uses Dictionary Extent Management, then the Oracle Management Agent tests the value of this metric every 30 minutes.

Data Source

The source of the data for this metric is MaximumSize Total Used Space where:

• TotalUsedSpace: Total used space in MB of tablespace.

• MaximumSize: Maximum size (in MB) of the tablespace. The maximum size is determined by looping through the tablespaces data files, as well as additional free space on the disk that would be available for the tablespace should a data file autoextend.

User Action

Perform one of the following:

• Increase the size of the tablespace by: Enabling automatic extension for one of its existing data files, manually resizing one of its existing data files, or adding a new data file.

• If the tablespace is suffering from tablespace free space fragmentation problems, consider reorganizing the entire tablespace.

• Relocate segments to another tablespace, thereby increasing the free space in this tablespace.

• Run the Segment Advisor on the tablespace.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

As segments within a tablespace grow, the available free space decreases. If there is no longer any available free space, meaning data files have reached their maximum size or there is no more disk space, then the creation of new segments or the extension of existing segments will fail.

This metric checks the Available Space Used (%) for each tablespace. If the percentage of used space is greater than the values specified in the threshold arguments, then a warning or critical alert is generated.

If the version of the monitored database target is Oracle Database 10g Release 1 or later and the tablespace uses Local Extent Management, then the Oracle Database Server evaluates this metric internally every 10 minutes. Alternatively, if the version of the monitored Database target is Oracle 9i or earlier, or the tablespace uses Dictionary Extent Management, then the Oracle Management Agent tests the value of this metric every 30 minutes.

Data Source

This metric is calculated with the following command where:

• TotalUsedSpace: total used space in MB of tablespace.

• MaximumSize: Maximum size (in MB) of the tablespace. The maximum size is determined by looping through the tablespaces data files, as well as additional free space on the disk that would be available for the tablespace should a data file autoextend.

(TotalUsedSpace / MaximumSize) * 100

For additional information about the data source, refer to the fullTbsp.pl Perl script located in the sysman/admin/scripts directory.

User Action

Perform one of the following:

• Increase the size of the tablespace by: Enabling automatic extension for one of its existing data files, manually resizing one of its existing data files, or adding a new data file.

• If the tablespace is suffering from tablespace free space fragmentation problems, consider reorganizing the entire tablespace.

• Relocate segments to another tablespace, thus increasing the free space in this tablespace.

• Run the Segment Advisor on the tablespace.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

As segments within a tablespace grow, the available free space decreases. If there is no longer any available free space, meaning data files have reached their maximum size or there is no more disk space, then the creation of new segments or the extension of existing segments will fail.

This metric checks for the total available free space in each tablespace. This metric is intended for larger tablespaces, where the Available Space Used (%) metric is less meaningful. If the available free space falls below the size specified in the threshold arguments, then a warning or critical alert is generated.

If the version of the monitored database target is Oracle Database 10g Release 1 or later and the tablespace uses Local Extent Management, then the Oracle Database Server evaluates this metric internally every 10 minutes. Alternatively, if the version of the monitored Database target is Oracle 9i or earlier, or the tablespace uses Dictionary Extent Management, then the Oracle Management Agent tests the value of this metric every 30 minutes.

Data Source

The source of the data for this metric is MaximumSize Total Used Space where:

• TotalUsedSpace: Total used space in MB of tablespace.

• MaximumSize: Maximum size (in MB) of the tablespace. The maximum size is determined by looping through the tablespaces data files, as well as additional free space on the disk that would be available for the tablespace should a data file autoextend.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

As segments within a tablespace grow, the available free space decreases. If there is no longer any available free space, meaning data files have reached their maximum size or there is no more disk space, then the creation of new segments or the extension of existing segments will fail.

This metric checks the Available Space Used (%) for each tablespace. If the percentage of used space is greater than the values specified in the threshold arguments, then a warning or critical alert is generated.

If the version of the monitored database target is Oracle Database 10g Release 1 or later and the tablespace uses Local Extent Management, then the Oracle Database Server evaluates this metric internally every 10 minutes. Alternatively, if the version of the monitored Database target is Oracle 9i or earlier, or the tablespace uses Dictionary Extent Management, then the Oracle Management Agent tests the value of this metric every 30 minutes.

Data Source

The source of the data for this metric is (TotalUsedSpace / MaximumSize) * 100 where:

• TotalUsedSpace: Total used space in MB of tablespace.

• MaximumSize: Maximum size (in MB) of the tablespace. The maximum size is determined by looping through the tablespaces data files, as well as additional free space on the disk that would be available for the tablespace should a data file autoextend.

User Action

Perform one of the following:

• Increase the size of the tablespace by: Enabling automatic extension for one of its existing data files, manually resizing one of its existing data files, or adding a new data file.

• If the tablespace is suffering from tablespace free space fragmentation problems, consider reorganizing the entire tablespace.

• Relocate segments to another tablespace, thereby increasing the free space in this tablespace.

• Run the Segment Advisor on the tablespace.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric checks for segments nearing the upper limit of the number of maximum extents. If the number of segments is greater than the values specified in the threshold arguments, a warning or critical alert is generated.

Data Source

The source of the data for this metric is the number of segments for which the maximum number of extents minus the number of existing extents is less than 2.

For additional information about the data source, refer to the problemTbsp.pl Perl script located in the sysman/admin/scripts directory.

User Action

If possible, increase the value of the segments MAXEXTENTS storage parameter. Otherwise, rebuild the segment with a larger extent size ensuring the extents within a segment are the same size by using a locally managed tablespace. For a dictionary managed tablespace, specify STORAGE parameters where NEXT=INITIAL and PCTINCREASE = 0.

This is a database-level metric. For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric checks for segments that cannot allocate an additional extent. If the number of segments is greater than the values specified in the threshold arguments, a warning or critical alert is generated.

Data Source

After checking for the largest chunk free space in the tablespace, this is the number of segments that cannot allocate an additional extent.

For additional information about the data source, refer to the problemTbsp.pl Perl script located in the sysman/admin/scripts directory.

User Action

Perform one of the following:

• Increase the size of the tablespace by enabling automatic extension for one of its existing data files, manually resizing one of its existing data files, or adding a new data file.

• If the tablespace is suffering from tablespace free space fragmentation problems, consider reorganizing the entire tablespace.

• Relocate segments to another tablespace, thereby increasing the free space in this tablespace.

The absolute file number of the temporary file, used to join with other database tables and views to retrieve additional information.

This metric displays the total number of database objects in a schema.

This metric displays the total number of tables in a schema.

This metric represents the total unusable index count in the database.

Data Source

The data is derived from the dba_indexes, dba_ind_partitions, and dba_ind_subpartitions views.

User Action

The “Rebuild Unusable Indexes” corrective action could be setup against the incident to automatically attempt to rebuild the unusable indexes in the database. This lets the user to specify various rebuild options and the schemas in which the indexes should be rebuilt. In addition, the incident also enables the user to rebuild the unusable indexes from the Enterprise Manager console.

This metric represents the total number of unusable indexes per schema.

Multiple Thresholds

Different warning and critical threshold values could be set for each Unusable Index Owner (schema) object.

If warning or critical threshold values are currently set for any Unusable Index Owner object, those thresholds could be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each Unusable Index Owner object, use the Edit Thresholds page.

Data Source

The data is derived from the dba_indexes, dba_ind_partitions, and dba_ind_subpartitions views.

User Action

The “Rebuild Unusable Indexes” corrective action could be setup against the incident to automatically attempt to rebuild the unusable indexes in each Unusable Index Owner (schema) object. This lets the user to specify various rebuild options that should be used for the operation. In addition, the incident also enables the user to rebuild the unusable indexes from the Enterprise Manager console.

For cluster databases, this metric is monitored at the cluster database target level and not by member instances.

This metric signifies that a database user is blocking at least one other user from performing an action, such as updating a table. An alert is generated if the number of consecutive blocking occurrences reaches the specified value. The sessions being blocked can come from different instances.

Data Source

This metric is calculated using the following command:

SELECT blocking_sid, num_blocked 
  FROM ( SELECT blocking_sid, SUM(num_blocked) num_blocked 
  FROM ( SELECT l.id1, l.id2, 
                MAX(DECODE(l.block, 1, i.instance_name||'-'||l.sid, 
                2, i.instance_name||'-'||l.sid, 0 )) blocking_sid, 
                SUM(DECODE(l.request, 0, 0, 1 )) num_blocked 
          FROM gv$lock l, gv$instance i 
          WHERE ( l.block!= 0 OR l.request > 0 ) AND 
                  l.inst_id = i.inst_id 
                  GROUP BY l.id1, l.id2) 
  GROUP BY blocking_sid 
  ORDER BY num_blocked DESC) 
  WHERE num_blocked != 0 

User Action

Either have the user who is blocking other users rollback the transaction, or wait until the blocking transaction has been committed.

This metric represents the maximum time wasted in any given lock chain, not for the total time wasted by everyone in any lock chain.

Multiple Thresholds

For this metric you can set different warning and critical threshold values for each User Lock Type object.

If warning or critical threshold values are currently set for any User Lock Type object, those thresholds can be viewed on the Metric Detail page for this metric.

To specify or change warning or critical threshold values for each User Lock Type object, use the Edit Thresholds page.

Data Source

The data for the metric is retrieved from database view gv$session.

User Action

You can set the threshold for warning alert or critical alert for maximum Blocked DB Time (seconds). When maximum time wasted in any given lock chain reaches the threshold, Enterprise Manager will issue the alert.

This metric represents the maximum length of any lock chain, not for the total number of people stuck in lock chains.