Commonly Configured OEM Alerts and Their Solutions.

Archiver Hung Alert Log Error

Cause: Possible cause is that the destination LOG_ARCHIVE_DEST_n is full.
Action: Check alert log file and archiver for additional information. 

ORA-01578:ORACLE data block corrupted

Cause: The data block indicated was corrupted, mostly due to
       software errors.
Action: Try to restore the segment containing the block indicated.
        This may involve dropping the segment and recreating it. 

ORA-08103:object no longer exists

Cause: The object has been deleted by another user since the operation began,
       or a prior incomplete recovery restored the database to a point in 
       time during the deletion of the object.
Action: Delete the object if this is the result of an incomplete recovery.

Generic Alert Log Error

1) This metric signifies that the database being monitored has generated errors
   to the ALERT log file since the last sample time.


ORA-600, ORA-7445

1) Internal Program or OS exception contact Oracle Support.

Total Invalid Object Count

1) Recompile or rebuild objects as necessary.

Segments Approaching Maximum Extents Count

1) This metric checks for segments nearing the upper limit of the number of 
   maximum extents.
2) 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.


Segments Not Able to Extend Count

1) This metric checks for segments which cannot allocate an additional extent.

2) Perform one of the following:

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

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

2.3) Relocate segments to another tablespace thus increasing the free space in this tablespace. 



Tablespace Space Used (%)

1) Tablespace does not have available free space.

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

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

2.3) Relocate segments to another tablespace thus increasing the free space in this tablespace. 

2.4) Run Segment advisor on the tablespace.

Redo Log Allocation Hit (%)

1) Redo log buffer is undersized
2) Increase the LOG_BUFFER
3) Or Go for Automatic Memory Management(10g,11g).


Blocking Session Count.

This metric signifies that a database user is blocking at least one other
user from performing an action.


Data Source:

SELECT SUM(num_blocked) 
  FROM (SELECT id1, id2, MAX(DECODE(block, 1, sid, 0)) blocking_sid,
    SUM(DECODE(request, 0, 0, 1)) num_blocked 
    FROM v$lock 
      WHERE block = 1 OR request>0 
      GROUP BY id1, id2) 
  GROUP BY blocking SID ;

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


Lock Limit Usage (%)

1) The DML_LOCK limit is about to be reached in the database.

DataSource: 

SELECT resource_name name, 
  100*DECODE(initial_allocation, ' UNLIMITED', 0, current_utilization / 
  initial_allocation) usage 
FROM v$resource_limit 
WHERE LTRIM(limit_value) 
  != '0' AND LTRIM(initial_allocation) != '0' AND resource_name = 'dml_locks';


Action: Increase the DML_LOCKS instance parameter by 10%.


Session Limit Usage (%)

1) The total session limit is about to be reached in the database,

DataSource:

 SELECT resource_name name, 
  100*DECODE(initial_allocation, ' UNLIMITED', 0, current_utilization) != '0' 
  AND resource_name = 'sessions';

Action:

Increase the sessions instance parameter.

For Dedicated environment SESSIONS=2.73*PROCESSES
For shared Server Environment SESSIONS=1.1*PROCESSES


Library cache lock (%)

1) Blocking situations can occur when two sessions compile the
   same PL/SQL package, or one session is recreating an index
   while another session is trying to execute a SQL statement
   that depends on that index. 

DataSource:

column h_wait format A20 
SELECT s.sid, 
  waiter.p1raw w_p1r, 
  waiter.p2raw w_p2r, 
  holder.event h_wait, 
  holder.p1raw h_p1r, 
  holder.p2raw h_p2r, 
  count(s.sid) users_blocked, 
  sql.hash_value 
FROM 
  v$sql sql, 
  v$session s, 
  x$kgllk l, 
  v$session_wait waiter, 
  v$session_wait holder 
WHERE 
  s.sql_hash_value = sql.hash_value and 
  l.KGLLKADR=waiter.p2raw and 
  s.saddr=l.kgllkuse and 
  waiter.event like 'library cache lock' and 
  holder.sid=s.sid 
GROUP BY 
  s.sid, 
  waiter.p1raw , 
  waiter.p2raw , 
  holder.event , 
  holder.p1raw , 
  holder.p2raw , s
  ql.hash_value ;


Library cache pin (%)

Action:

If there is general widespread waiting then the shared 
pool may need tuning.

If there is a blocking scenario, collect evidence as described
in the following query and contact Oracle support.

DataSource:

column h_wait format A20 
SELECT s.sid, 
  waiter.p1raw w_p1r, 
  holder.event h_wait, 
  holder.p1raw h_p1r, 
  holder.p2raw h_p2r, 
  holder.p3raw h_p2r, 
  count(s.sid) users_blocked, 
  sql.hash_value 
FROM 
  v$sql sql, 
  v$session s, 
  x$kglpn p, 
  v$session_wait waiter, 
  v$session_wait holder 
WHERE 
  s.sql_hash_value = sql.hash_value and 
  p.kglpnhdl=waiter.p1raw and 
  s.saddr=p.kglpnuse and 
  waiter.event like 'library cache pin' and 
  holder.sid=s.sid 
GROUP BY 
  s.sid, 
  waiter.p1raw , 
  holder.event , 
  holder.p1raw , 
  holder.p2raw , 
  holder.p3raw , 
  sql.hash_value ;