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

以往我们为了管理数据库的便利,总是希望能够利用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

*

沪ICP备14014813号-2

沪公网安备 31010802001379号