11g新特性:X$DBGALERTEXT一个很酷的内部视图

来源：互联网发布：淘宝代购太平鸟真假编辑：程序博客网时间：2024/09/21 06:21

http://www.oracledatabase12g.com/archives/11g%E6%96%B0%E7%89%B9%E6%80%A7xdbgalertext%E4%B8%80%E4%B8%AA%E5%BE%88%E9%85%B7%E7%9A%84%E5%86%85%E9%83%A8%E8%A7%86%E5%9B%BE.html

以往我们为了管理数据库的便利，总是希望能够利用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                                          NUMBERSQL> set linesize 100 pagesize 1400SQL> 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:00Generic Internal ErrorORA 600Errors 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:00Generic Internal ErrorORA 600Errors 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:00Generic Internal ErrorORA 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:00Errors 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:00Generic Internal ErrorORA 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:00Generic Internal ErrorORA 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:00Generic Internal ErrorORA 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:00Generic Internal ErrorORA 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:00Generic Internal ErrorORA 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 ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE    11.2.0.3.0      ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL> select * from global_name;GLOBAL_NAME--------------------------------------------------------------------------------www.oracledatabase12g.com & www.askmaclean.comSQL> select count(*) from X$DBGALERTEXT;  COUNT(*)----------     51740SQL> ! echo > /s01/orabase/diag/rdbms/vprod/VPROD1/alert/log.xmlSQL> select count(*) from X$DBGALERTEXT;  COUNT(*)----------     34005SQL> ! 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:00Errors 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:00Errors 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:00Errors 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:00Errors 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:00Errors 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:00Errors 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:00Errors 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:00Errors 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:00Errors 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:00Errors 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:00Errors 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:00Errors 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:00alter database open07-JAN-12 02.05.48.781 AM -05:00ORA-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实现计算告警日志信息度量的方法。