|
1 2 3 4 5 6 7 8 |
prompt Alert Log Error and Alert Message Total summary (30 Days) prompt _______________________________________________________ prompt select SUBSTR(TRIM(MESSAGE_TEXT),INSTR(MESSAGE_TEXT,'-',1,1)+1,INSTR(MESSAGE_TEXT,':',1,1)-5) AS ALERT_NUM , count(1) AS MONTH_SUM_CNT from "X$DBGALERTEXT" WHERE ( upper(MESSAGE_TEXT) LIKE 'ORA-%' ) AND ORIGINATING_TIMESTAMP > SYSDATE - 31 group by SUBSTR(TRIM(MESSAGE_TEXT),INSTR(MESSAGE_TEXT,'-',1,1)+1,INSTR(MESSAGE_TEXT,':',1,1)-5) ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
prompt Alert Log Online Backup Message Day Summary (30 Days) prompt select substr(to_char(first_time,'yyyy/mm/dd'),1,10) "Day",ALERT_NUM, decode(sum(1),0,0,sum(1)) "Per Day" , decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0))) "00", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0))) "01", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0))) "02", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0))) "03", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0))) "04", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0))) "05", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0))) "06", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0))) "07", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0))) "08", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0))) "09", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0))) "10", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0))) "11", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0))) "12", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0))) "13", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0))) "14", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0))) "15", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0))) "16", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0))) "17", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0))) "18", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0))) "19", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0))) "20", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0))) "21", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0))) "22", decode(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),0,0,sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0))) "23" from ( select ORIGINATING_TIMESTAMP first_time , SUBSTR(TRIM(MESSAGE_TEXT),INSTR(MESSAGE_TEXT,'-',1,1)+1,INSTR(MESSAGE_TEXT,':',1,1)-5) AS ALERT_NUM from "X$DBGALERTEXT" WHERE ( upper(MESSAGE_TEXT) LIKE '%COMPLETED%END%BACKUP%' ) AND ORIGINATING_TIMESTAMP > SYSDATE - 31 ) group by ALERT_NUM,substr(to_char(first_time,'yyyy/mm/dd'),1,10) order by substr(to_char(first_time,'yyyy/mm/dd'),1,10) asc ; prompt Alert Log Error Message Detail (30Day) select TO_CHAR(ORIGINATING_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS') AS ALERT_DATE , SUBSTR(TRIM(MESSAGE_TEXT),INSTR(MESSAGE_TEXT,'-',1,1)+1,INSTR(MESSAGE_TEXT,':',1,1)-5) AS ALERT_NUM , MESSAGE_TEXT ALERT_TEXT from "X$DBGALERTEXT" WHERE ( upper(MESSAGE_TEXT) LIKE 'ORA-%' ) and ORIGINATING_TIMESTAMP > sysdate - 31 / |