以往我们为了管理数据库的便利，总是希望能够利用SQL查询Oracle关键的Alert.log告警日志，在11g以前可以通过创建Alert.log内容的外部表来实现查询日志内容的目的的。 从11g 开始引入了新的ADR(Automatic Diagnostic Repository ADR is a file-based repository for database diagnostic data, such as traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more. It has a unified directory structure across multiple instances and multiple products stored outside of any database. It is,therefore, available for problem diagnosis when the database is down.)自动诊断仓库特性，默认的Alert.log转换为LOG.XML的形式，默认存放在$ADR_HOME/alert&trace目录下，并且为日志条目增加了如Level之类的属性，使得告警日志能够为Oracle Support提供更多有用的信息。
SQL>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> set linesize 100 pagesize 1400 SQL> select originating_timestamp, message_group, problem_key, message_text 2 from X$DBGALERTEXT 3 where message_text like '%ORA-00600%' 4 and rownum < 10; ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_GROUP ---------------------------------------------------------------- PROBLEM_KEY ---------------------------------------------------------------- MESSAGE_TEXT ---------------------------------------------------------------------------------------------------- 10-NOV-11 03.15.52.025 AM -05:00 Generic Internal Error ORA 600 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc (incident=11106): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], [] 10-NOV-11 03.15.54.882 AM -05:00 Generic Internal Error ORA 600 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc (incident=11108): ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], [] 20-NOV-11 12.13.07.918 AM -05:00 Generic Internal Error ORA 600 [4194] Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_smon_4689.trc (incident=16965): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] 20-NOV-11 12.13.11.260 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_smon_4689.trc: ORA-01595: error freeing extent (3) of rollback segment (10)) ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] 20-NOV-11 12.16.56.765 AM -05:00 Generic Internal Error ORA 600 [4194] Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_4935.trc (incident=16997): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] 20-NOV-11 12.17.00.852 AM -05:00 Generic Internal Error ORA 600 [ORA-00600: internal error code, arguments: [4194], [], Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_4935.trc (incident=16998): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] 20-NOV-11 12.20.07.859 AM -05:00 Generic Internal Error ORA 600 [4194] Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_5029.trc (incident=16999): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] 20-NOV-11 12.20.09.654 AM -05:00 Generic Internal Error ORA 600 [ORA-00600: internal error code, arguments: [4194], [], Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_5029.trc (incident=17000): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] 20-NOV-11 12.22.43.632 AM -05:00 Generic Internal Error ORA 600 [4194] Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_m000_5137.trc (incident=17141): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []X$DBGALERTEXT的内容极其丰富，包括message记录发生的日期、发生问题的进程号、trace日志的位置、问题的关键(problem key)，可以为我们分析数据库日志提供极为强大的接口，节约大量的时间。 X$DBGALERTEXT, that allows the log.xml file to be queried like a table. The X$DBGALERTEXT table is effectively indexed by the date the alert was raised so queries against this table based on a date range, which we are doing here, is efficient and performant. In 11g, X$DBGALERTEXT now exists as a table. You can query the table for the specific columns needed. 但是请注意X$DBGALERTEXT的数据实际来源于ADR中的log.xml文件， 若xml被删除或者内容缺失均会影响到X$DBGALERTEXT的信息完整性：
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - ProductionCORE
本文转自maclean_007 51CTO博客，原文链接:
http://blog.51cto.com/maclean/1278227
