Queries to view Alert Log content And Alert Location

Quering alert log using SQL query

Oracle 11g开始，提供了X$DBGALERTEXT，可以用来使用sql访问数据库alert日志，在不方便直接访问服务器时，提供了另外的方式访问数据库alert日志。X$DBGALERTEXT的定义如下：

SYS> desc X$DBGALERTEXT
 Name										     Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ADDR											      RAW(8)
 INDX											      NUMBER
 INST_ID										      NUMBER
 ORIGINATING_TIMESTAMP									      TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP									      TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID									      VARCHAR2(64)
 COMPONENT_ID										      VARCHAR2(64)
 HOST_ID										      VARCHAR2(64)
 HOST_ADDRESS										      VARCHAR2(46)
 MESSAGE_TYPE										      NUMBER
 MESSAGE_LEVEL										      NUMBER
 MESSAGE_ID										      VARCHAR2(64)
 MESSAGE_GROUP										      VARCHAR2(64)
 CLIENT_ID										      VARCHAR2(64)
 MODULE_ID										      VARCHAR2(64)
 PROCESS_ID										      VARCHAR2(32)
 THREAD_ID										      VARCHAR2(64)
 USER_ID										      VARCHAR2(64)
 INSTANCE_ID										      VARCHAR2(64)
 DETAILED_LOCATION									      VARCHAR2(160)
 PROBLEM_KEY										      VARCHAR2(64)
 UPSTREAM_COMP_ID									      VARCHAR2(100)
 DOWNSTREAM_COMP_ID									      VARCHAR2(100)
 EXECUTION_CONTEXT_ID									      VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE								      NUMBER
 ERROR_INSTANCE_ID									      NUMBER
 ERROR_INSTANCE_SEQUENCE								      NUMBER
 VERSION										      NUMBER
 MESSAGE_TEXT										      VARCHAR2(2048)
 MESSAGE_ARGUMENTS									      VARCHAR2(128)
 SUPPLEMENTAL_ATTRIBUTES								      VARCHAR2(128)
 SUPPLEMENTAL_DETAILS									      VARCHAR2(128)
 PARTITION										      NUMBER
 RECORD_ID										      NUMBER
 
 

比较常用的SQL如下(可以得到类似直接访问文本格式alert日志样式的结果)：

SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from X$DBGALERTEXT;

Finding Trace File Path using SQL

11g开始，Oracle提供了X$DBGDIREXT接口，可以用来查看diagnostic_dest下的目录和文件。X$DBGDIREXT的定义如下：

 SYS> desc X$DBGDIREXT;
 Name										     Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ADDR											      RAW(8)
 INDX											      NUMBER
 INST_ID										      NUMBER
 PHYSICAL_PATH										      VARCHAR2(444)
 LOGICAL_PATH										      VARCHAR2(444)
 PHYSICAL_FILE										      VARCHAR2(68)
 LOGICAL_FILE										      VARCHAR2(68)
 CREATION_TIME										      TIMESTAMP(3) WITH TIME ZONE
 MODIFY_TIME										      TIMESTAMP(3) WITH TIME ZONE
 LVL											      NUMBER
 TYPE											      NUMBER

一个非常handy的用法：有时候不熟悉Oracle的人，我们需要它帮忙把alert日志取出来的时候，由于11g的alert是放在diagnostic中的，描述日志的路径每次都要费很大的劲，现在我们可以用下面sql来直接获取到alert日志的路径了：

SELECT PHYSICAL_PATH || CHR(47) || PHYSICAL_FILE
  FROM X$DBGDIREXT
 WHERE 1 = 1
   AND PHYSICAL_FILE LIKE 'alert_%'
   AND PHYSICAL_PATH LIKE '%rdbms%'
/