以往我们为了管理数据库的便利，总是希望能够利用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提供更多有用的信息。
得益于XML格式的日志，可以更方便地将数据库告警日志内容转换为行列数。 所以从11g开始 ， 我们甚至于不需要去手动创建外部表了，Oracle 直接提供了X$DBGALERTEXT 这一内部视图来方便我们访问Alert文本：
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 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.askmac.cn & www.askmac.cn SQL> select count(*) from X$DBGALERTEXT; COUNT(*) ---------- 51740 SQL> ! echo > /s01/orabase/diag/rdbms/vprod/VPROD1/alert/log.xml SQL> select count(*) from X$DBGALERTEXT; COUNT(*) ---------- 34005 SQL> ! echo > /s01/orabase/diag/rdbms/vprod/VPROD1/alert/log_1.xml SQL> select count(*) from X$DBGALERTEXT; COUNT(*) ---------- 0
可以看到X$DBGALERTEXT的数据来源于 log.xml及其归档， 在11g中 当log.xml的大小超过10MB时，Oracle会将其内容归档到如log_$N.xml这样的归档文件中，并清空当前的log.xml的内容。
通过X$DBGALERTEXT内部视图接口， 我们可以实现一些原本想都不敢想的日志分析目的, 例如我要找出最近30天内出现过的Internal Errors：
SQL> select originating_timestamp, message_text 2 from X$DBGALERTEXT 3 where originating_timestamp > sysdate - 30 4 and message_group = 'Generic Internal Error'; ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_TEXT -------------------------------------------------------------------------------- 08-DEC-11 03.12.10.998 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc (incident=27673): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] 08-DEC-11 03.12.13.105 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc (incident=27674): ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_TEXT -------------------------------------------------------------------------------- ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] 08-DEC-11 03.12.14.107 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc (incident=27675): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_TEXT -------------------------------------------------------------------------------- 08-DEC-11 03.12.16.017 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc (incident=27676): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] 08-DEC-11 03.40.25.562 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_mmon_30374.trc (incident=29076): ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_TEXT -------------------------------------------------------------------------------- ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] 08-DEC-11 03.40.26.989 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_mmon_30374.trc (incident=29077): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_TEXT -------------------------------------------------------------------------------- 08-DEC-11 03.40.29.020 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30561.trc (incident=29244): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] 08-DEC-11 03.40.31.595 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30561.trc (incident=29245): ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_TEXT -------------------------------------------------------------------------------- ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] 08-DEC-11 03.41.17.338 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30640.trc (incident=29260): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_TEXT -------------------------------------------------------------------------------- 08-DEC-11 03.41.18.668 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30640.trc (incident=29261): ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [ ], [], [], [] 10-DEC-11 01.45.51.556 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ckpt_1026.trc (incident=33838): ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_TEXT -------------------------------------------------------------------------------- ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], [] 10-DEC-11 01.45.52.304 AM -05:00 Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ckpt_1026.trc (incident=33839): ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
或者我要审计最近2天内管理员执行过的DDL语句:
SQL> select originating_timestamp, message_text 2 from X$DBGALERTEXT 3 where originating_timestamp > sysdate - 2 4 and message_group = 'admin_ddl'; ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- MESSAGE_TEXT -------------------------------------------------------------------------------- 07-JAN-12 02.05.48.770 AM -05:00 alter database open 07-JAN-12 02.05.48.781 AM -05:00 ORA-1531 signalled during: alter database open...
Metalink文档How to – Monitor Non Critical 11g Database Alert Log Errors Using a SQL UDM [ID 961682.1] 介绍了一种利用X$DBGALERTEXT实现计算告警日志信息度量的方法。
Oracle Freelist上对于X$DBGALERTEXT在近期也有精彩的讨论，值得移步一看。
Comment