{"id":2837,"date":"2018-02-03T14:40:43","date_gmt":"2018-02-03T05:40:43","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2837"},"modified":"2018-02-03T14:42:59","modified_gmt":"2018-02-03T05:42:59","slug":"oracle-alert-log-%eb%a5%bc-%ed%84%b0%eb%af%b8%eb%84%90%ec%9d%b4-%ec%95%84%eb%8b%8c-%ec%98%a4%eb%a0%8c%ec%a7%80-%ed%86%a0%eb%93%9c%ec%97%90%ec%84%9c-%ec%a1%b0%ed%9a%8c%ed%95%98%eb%8a%94-%ec%bf%bc","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2837","title":{"rendered":"Oracle Alert Log \ub97c \ud130\ubbf8\ub110\uc774 \uc544\ub2cc \uc624\ub80c\uc9c0, \ud1a0\ub4dc\uc5d0\uc11c \uc870\ud68c\ud558\ub294 \ucffc\ub9ac (Oracle 11g \uc774\uc0c1)"},"content":{"rendered":"<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true  \">prompt Alert Log Error and Alert Message Total summary (30 Days)\r\nprompt _______________________________________________________\r\nprompt\r\nselect 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    \r\nfrom \"X$DBGALERTEXT\" \r\nWHERE ( upper(MESSAGE_TEXT) LIKE 'ORA-%' )\r\nAND ORIGINATING_TIMESTAMP &gt; SYSDATE - 31\r\ngroup by SUBSTR(TRIM(MESSAGE_TEXT),INSTR(MESSAGE_TEXT,'-',1,1)+1,INSTR(MESSAGE_TEXT,':',1,1)-5)  ; \r\n\r\n<\/pre>\n<pre class=\"lang:plsql decode:true\">\r\nprompt Alert Log Online Backup Message Day Summary (30 Days)\r\nprompt\r\n\r\nselect \r\n substr(to_char(first_time,'yyyy\/mm\/dd'),1,10) \"Day\",ALERT_NUM,\r\n  decode(sum(1),0,0,sum(1)) \"Per Day\" ,\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\",\r\ndecode(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\"\r\nfrom (\r\nselect ORIGINATING_TIMESTAMP first_time , \r\n       SUBSTR(TRIM(MESSAGE_TEXT),INSTR(MESSAGE_TEXT,'-',1,1)+1,INSTR(MESSAGE_TEXT,':',1,1)-5) AS ALERT_NUM \r\nfrom \"X$DBGALERTEXT\" \r\nWHERE ( upper(MESSAGE_TEXT) LIKE '%COMPLETED%END%BACKUP%' )\r\nAND ORIGINATING_TIMESTAMP &gt; SYSDATE - 31\r\n)\r\ngroup by ALERT_NUM,substr(to_char(first_time,'yyyy\/mm\/dd'),1,10)\r\norder by substr(to_char(first_time,'yyyy\/mm\/dd'),1,10) asc ;\r\n\r\n\r\nprompt Alert Log Error Message Detail (30Day)\r\n\r\nselect TO_CHAR(ORIGINATING_TIMESTAMP,'YYYY\/MM\/DD HH24:MI:SS') AS ALERT_DATE , \r\nSUBSTR(TRIM(MESSAGE_TEXT),INSTR(MESSAGE_TEXT,'-',1,1)+1,INSTR(MESSAGE_TEXT,':',1,1)-5) AS ALERT_NUM , MESSAGE_TEXT ALERT_TEXT \r\nfrom \"X$DBGALERTEXT\" \r\nWHERE ( upper(MESSAGE_TEXT) LIKE 'ORA-%' )\r\nand  ORIGINATING_TIMESTAMP &gt; sysdate - 31\r\n\/\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; prompt Alert Log Error and Alert Message Total summary (30 Days) prompt _______________________________________________________ prompt select SUBSTR(TRIM(MESSAGE_TEXT),INSTR(MESSAGE_TEXT,&#8217;-&#8216;,1,1)+1,INSTR(MESSAGE_TEXT,&#8217;:&#8217;,1,1)-5) AS ALERT_NUM , count(1) AS MONTH_SUM_CNT from &#8220;X$DBGALERTEXT&#8221; WHERE ( upper(MESSAGE_TEXT) LIKE &#8216;ORA-%&#8217; ) AND ORIGINATING_TIMESTAMP &gt; SYSDATE &#8211; 31 group by SUBSTR(TRIM(MESSAGE_TEXT),INSTR(MESSAGE_TEXT,&#8217;-&#8216;,1,1)+1,INSTR(MESSAGE_TEXT,&#8217;:&#8217;,1,1)-5) ; prompt Alert Log Online Backup Message Day Summary (30 Days) prompt select substr(to_char(first_time,&#8217;yyyy\/mm\/dd&#8217;),1,10) &#8220;Day&#8221;,ALERT_NUM, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[3],"tags":[53,929],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2837"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2837"}],"version-history":[{"count":4,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2837\/revisions"}],"predecessor-version":[{"id":2841,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2837\/revisions\/2841"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2837"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2837"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2837"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}