Search This Blog

Total Pageviews

Monday, 17 October 2011

Oracle outstanding alerts



Oracle outstanding alerts



set linesize   200
set pagesize  1000
set trimout     on
set trimspool   on
Set Feedback   off
set timing     off
set verify     off


prompt

prompt -- ----------------------------------------------------------------------- ---

prompt --   Outstanding Alert                                                     ---

prompt -- ----------------------------------------------------------------------- ---

prompt


column ct                          format a18              heading  "Creation Time"
column instance_name               format a8               heading  "Instance|Name"
column object_type                 format a14              heading  "Object|Type"
column message_type                format a9               heading  "Message|Type"
column message_level               format 9999             heading  "Mess.|Lev."
column reason                      format a30              heading  "Reason"
column suggested_action            format a75              heading  "Suggested|Action"

Select  
       To_Char(Creation_Time, 'DD-MM-YYYY HH24:MI') ct
     , instance_name          
     , object_type            
     , message_type         
     , message_level       
     , reason             
     , suggested_action  
  From  dba_outstanding_alerts
 Order  By Creation_Time
;


Prompt

           



                  Instance Object         Message   Mess.                                Suggested
Creation Time      Name     Type           Type       Lev. Reason                         Action
------------------ -------- -------------- --------- ----- ------------------------------ ---------------------------------------------------------------------------
30-06-2011 13:20   db    TABLESPACE     Warning       5 Tablespace [DM_CCCPROD1_INDEX] Add space to the tablespace
                                                            is [85 percent] full

13-09-2011 16:45   db    TABLESPACE     Warning       5 Tablespace [DM_CCCPROD1_DOCBAS Add space to the tablespace
                                                           E] is [85 percent] full

17-10-2011 15:51   db    EVENT_CLASS    Warning       5 Metrics "Database Time Spent W Run ADDM to get more performance analysis about your system.
                                                           aiting (%)" is at 33.97673 for
                                                            event class "Network"


===========================

## Alerts database ##
set lines 250 pages 1000
col "Alter_from" for a20
col creation_time for a20
col reason for a120
select 'Alert_outstanding' "Alter_from",to_char(creation_time,'DD/MM/YYYY HH24:MI:SS') creation_time, reason 
from dba_outstanding_alerts
union
select 'History' "Alter_from",to_char(creation_time,'DD/MM/YYYY HH24:MI:SS') creation_time, reason 
from dba_alert_history 
order by 1 desc,creation_time;




## Alert log ##
set linesize 160 pagesize 0
col time for a20
col message_text for a120 head Message
SELECT to_char(originating_timestamp,'DD/MM/YYYY HH24:MI:SS') time, message_text FROM v$diag_alert_ext 
where originating_timestamp>sysdate-8/24
order by RECORD_ID ;




set linesize 200 pagesize 200
col record_id for 9999999 head id
col message_text for a120 head message
select record_id, to_char(originating_timestamp,'dd-mon-yyyy hh24:mi:ss') , message_text from x$dbgalertext 
where originating_timestamp > systimestamp - 15 
and regexp_like(message_text, '(ORA-|error)') 
order by record_id;


set linesize 254
col ORIGINATING_TIMESTAMP format a50
col MESSAGE_TEXT format a170
 
SELECT ORIGINATING_TIMESTAMP, message_text FROM X$DIAG_ALERT_EXT
where 1 = 1
  and originating_timestamp >= sysdate - 30
  and message_text like 'ALTER SYSTEM%'
  and message_text not like 'ALTER SYSTEM ARCHIVE LOG%'
;


set linesize 254
col originating_timestamp format a50
col message_text format a70 wrap
 
select inst_id, originating_timestamp, message_text
    FROM TABLE (
            gv$ (
               CURSOR (
                  SELECT inst_id, originating_timestamp, MESSAGE_TEXT
                    FROM v$diag_alert_ext
                   WHERE  1=1   
				      --and originating_timestamp > (SYSDATE - 10/1440)
					  and originating_timestamp > (SYSDATE - 1)
                         AND (MESSAGE_TEXT LIKE '%ORA-%' 
						 --or MESSAGE_TEXT LIKE '%TNS-%'
						 )
                         AND MESSAGE_TEXT NOT LIKE '%TNS-12502%'
                      )
                )
               )
ORDER BY inst_id, originating_timestamp;




set linesize 200 pagesize 200
col record_id for 9999999 head id
col message_text for a120 head message
select record_id, to_char(originating_timestamp,'dd-mon-yyyy hh24:mi:ss') , message_text from x$dbgalertext 
where originating_timestamp > systimestamp - 15 
and regexp_like(message_text, '(ORA-|error)') 
order by record_id;


No comments:

Oracle DBA

anuj blog Archive