oracle11g:通过sql语句读取alert log 信息

在11g中，oracle对数据库的诊断结果进行了一些改进，下面将讨论通过sql语句获取警告日志信息：

在oracle11g以前，如果需要通过sql语句来读取警告日志，一般通过外部表或者使用utl_file管道函数方式。在读取文本信息之后需要解析然后导出自己需要的信息。

从11g开始，oracle已经替你做了这些工作。它提供了一个固态表x$dbgalertext，当你查询的时候oracle从alert目录读取log.xml（包含警告日志中的所有信息），解析之，然后按行返回给用户：

SQL> set pagesize 9999
SQL> select message_text from x$dbgalertext where rownum<=20;

MESSAGE_TEXT
--------------------------------------------------------------------------------

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\app\hongsy\product\11.1.0\db_1\RDBMS
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in client-side pfile D:\APP\HONGSY\ADMIN\ORCL\PFILE\INIT.ORA on machine HH

System parameters with non-default values:
  processes                = 150
  nls_language             = "SIMPLIFIED CHINESE"
  nls_territory            = "CHINA"
  sga_target               = 460M
  control_files            = "D:\APP\HONGSY\ORCL\CONTROL01.CTL"

20 rows selected.

以下是x$dbgalertext的字段信息：

SQL> desc X$DBGALERTEXT
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(4)
 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(16)
 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

这里同样有个固态表X$DBGDIREXT,它包含diag目录下所有文件和目录信息：

SQL> select lpad(' ',lvl,' ')||logical_file file_name
  2  from X$DBGDIREXT
  3  where rownum <=20;

FILE_NAME
--------------------------------------------------------------------------------
asm
clients
 user_hongsy
  host_2986972688_11
   alert
    log.xml
   cdump
   incident
   incpkg
   lck
    am_1096102193_3488045378.lck
    am_1096102262_3454819329.lck
    am_3216668543_3129272988.lck
   metadata
    adr_control.ams
    inc_meter_impt_def.ams
    inc_meter_pk_impts.ams
   stage
   sweep
   trace

20 rows selected.

如果你自己构建了一些监控警告日志的程序，那么从11g开始，这些X$表将会帮助你。这对没有操作系统用户权限时，可以通过数据库权限来查阅。但是如果数据库实例没有运行的话，将不能使用此方式来查看警告日志信息了。