Forgot your password?    
+ Reply to Thread
Results 1 to 2 of 2

Thread: Commonly Configured OEM Monitoring Alerts on Production

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    Commonly Configured OEM Monitoring Alerts on Production


  2. #2
    Oracle Administrator
    Join Date
    Dec 2011
    Posts
    92

    Possible Solutions To Alerts

    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 ;

+ Reply to Thread

Similar Threads

  1. Quick Reference for commonly used Unix / Linux user level Commands
    By Hemant in forum OS for Oracle Administration
    Replies: 0
    Last Post: 06-26-2012, 01:07 AM
  2. Test/Development Database Refresh From Production Procedure
    By devesh_ocp in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 04-25-2012, 10:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics