转 数据库日志监控

#####以上仅仅针对11G以上的版本

##sample 0

with oneday as
(select /*+ materialize */
*
from v$diag_alert_ext
where ORIGINATING_TIMESTAMP > systimestamp - 1)
select to_char(ORIGINATING_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')|| ' ' || message_text
from oneday
where ORIGINATING_TIMESTAMP > systimestamp - 301/(24 * 60 * 60)
and message_text like '%ORA-%'
and message_text not like '%result of ORA-609%'
and message_text not like '%result of ORA-28%'
and message_text not like '%(ORA-3136)%'
and message_text not like '%ORA-01013%'

 11.2.0.4.0 存在bug Bug 14368995 - Dump in memcpy() under qerfxFetch() from query on fixed view, FIX 在11.2.0.4.6.0 版本上。

workaroud 有隐患，建议打补丁（for linux 11.2.0.4.0,no aix patch in 11.2.0.4.0）或者升级- 11.2.0.4.6 (Apr 2015) Database Patch Set Update (DB PSU) (for aix and linux,)

To solve the issue, use any of below alternatives:

• Upgrade to an Oracle version where the base bug is fixed:
  
  - 12.1.0.1 (Base Release)
  - 11.2.0.4.6 (Apr 2015) Database Patch Set Update (DB PSU)
  - 11.2.0.4 Bundle Patch 13 for Exadata Database (Nov 2014)
  - 11.2.0.4 Patch 15 on Windows Platforms
  
  - OR -
  
  
• Apply interim patch 14368995, if available for your platform and Oracle version.
  
  To check for conflicting patches, please use the MOS Patch Planner Tool
  Please refer to
  Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?
  
  If no patch exists for your version, please contact Oracle Support for a backport request.
  
  - OR - 规避方法：
  
  
• As a work around, please execute any of the below alternatives:
  
  
  • Set the 10235 event to level 2, as in:
    SQL> ALTER SYSTEM SET EVENT="10235 trace name context forever, level 2" SCOPE=SPFILE;
      
    Then restart the instance.
    Since the patch is available, the best solution is to apply the patch. 
    
    

######转

Selects from v$diag_alert_ext Run Slowly with Large Alert Logs (文档 ID 1684140.1)

转到底部

---

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later

SYMPTOMS

• Selects from v$diag_alert_ext take a long time
• Selects read the whole table whatever the where clause:
  
  

  select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V$DIAG_ALERT_EXT WHERE ORIGINATING_TIMESTAMP > systimestamp - INTERVAL '60' minute;
  
  SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL ALLSTATS LAST'));
  
  PLAN_TABLE_OUTPUT
  --------------------------------------------------------------------------------
  SQL_ID 87m9dp1xgwv44, child number 0
  -------------------------------------
  select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V$DIAG_ALERT_EXT WHERE
  ORIGINATING_TIMESTAMP > systimestamp - INTERVAL '60' minute
  
  Plan hash value: 116576056
  
  ---------------------------------------------------------------------------
  | Id | Operation        | Name             | E-Rows |E-Bytes| Cost (%CPU) |
  ---------------------------------------------------------------------------
  |  0 | SELECT STATEMENT |                  |        |       |      1 (100)|
  |* 1 |  FIXED TABLE FULL| X$DIAG_ALERT_EXT |      5 |  5245 |        0 (0)|
  ---------------------------------------------------------------------------
  

  
  
  
  

CHANGES

 none

CAUSE

  The v$diag_alert_ext view is based upon the fixed table x$DIAG_ALERT_EXT which contains information from the XML-based alert log (log.xml). This log can become large and thus selects can take some time.

SOLUTION

The easiest solution is to use an inline view and making sure no merging or predicate pushing is taking place.

With the with clause and a hint a real-life monitoring, query looks like this:

with oneday as (select /*+ materialize */ * from v$diag_alert_ext where ORIGINATING_TIMESTAMP>systimestamp-1)
select to_char(ORIGINATING_TIMESTAMP,’YYYY-MM-DD HH24:MI:SS’) || ‘ ‘ || message_text from oneday
where ORIGINATING_TIMESTAMP>systimestamp-301/(24*60*60) and message_text like ‘%ORA-%’ and message_text not like ‘%result of ORA-609%’ and message_text not like ‘%result of ORA-28%’ and message_text not like ‘%(ORA-3136)%’ and message_text not like ‘%ORA-01013:%’;"