[20131027]11G的内部视图X$DBGALERTEXT.txt

链接:
http://www.askmaclean.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

以前我们看alert*.log文件，一般直接进入目录，直接查看。当然也可以通过外部表来访问alert*.log。
而11G开始引入了新的ADR自动诊断仓库特性，默认的Alert.log转换为LOG.XML的形式，默认存放在$ADR_HOME/alert&trace目录下，并且
为日志条目增加了如Level之类的属性，使得告警日志能够为Oracle Support提供更多有用的信息。

从11g开始，我们可以通过内部视图X$DBGALERTEXT访问alert文本。做一个跟踪看看。

@ver

SYS@test> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SYS@test> host cat spid.sql

select spid from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat where rownum=1));

SYS@test> @spid

SPID

------

8688

--打开另外的回话执行
strace -p 8688

--回到回话执行：

select originating_timestamp, message_group, problem_key, message_text

  from X$DBGALERTEXT

 where message_text like '%ORA-%';

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_3.xml", {st_mode=S_IFREG|0640, st_size=10485820, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_3.xml", {st_mode=S_IFREG|0640, st_size=10485820, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_3.xml", {st_mode=S_IFREG|0640, st_size=10485820, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_2.xml", {st_mode=S_IFREG|0640, st_size=10485761, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_2.xml", {st_mode=S_IFREG|0640, st_size=10485761, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_2.xml", {st_mode=S_IFREG|0640, st_size=10485761, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/..", {st_mode=S_IFDIR|0750, st_size=4096, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_1.xml", {st_mode=S_IFREG|0640, st_size=10485861, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_1.xml", {st_mode=S_IFREG|0640, st_size=10485861, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_1.xml", {st_mode=S_IFREG|0640, st_size=10485861, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_7.xml", {st_mode=S_IFREG|0640, st_size=10486190, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_7.xml", {st_mode=S_IFREG|0640, st_size=10486190, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_7.xml", {st_mode=S_IFREG|0640, st_size=10486190, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log.xml", {st_mode=S_IFREG|0640, st_size=469237, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log.xml", {st_mode=S_IFREG|0640, st_size=469237, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log.xml", {st_mode=S_IFREG|0640, st_size=469237, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_5.xml", {st_mode=S_IFREG|0640, st_size=10486018, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_5.xml", {st_mode=S_IFREG|0640, st_size=10486018, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_5.xml", {st_mode=S_IFREG|0640, st_size=10486018, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/.", {st_mode=S_IFDIR|0750, st_size=4096, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_6.xml", {st_mode=S_IFREG|0640, st_size=10485807, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_6.xml", {st_mode=S_IFREG|0640, st_size=10485807, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_6.xml", {st_mode=S_IFREG|0640, st_size=10485807, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_4.xml", {st_mode=S_IFREG|0640, st_size=10485869, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_4.xml", {st_mode=S_IFREG|0640, st_size=10485869, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_4.xml", {st_mode=S_IFREG|0640, st_size=10485869, ...}) = 0

lstat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_8.xml", {st_mode=S_IFREG|0640, st_size=10485769, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_8.xml", {st_mode=S_IFREG|0640, st_size=10485769, ...}) = 0

stat("/u01/app/oracle11g/diag/rdbms/test/test/alert/log_8.xml", {st_mode=S_IFREG|0640, st_size=10485769, ...}) = 0

-- 可以发现访问的文件是/u01/app/oracle11g/diag/rdbms/test/test/alert/log_*.xml

# ls -l /u01/app/oracle11g/diag/rdbms/test/test/alert/log*
-rw-r-----  1 oracle11g oinstall 10485861 2012-09-15 17:29:55 /u01/app/oracle11g/diag/rdbms/test/test/alert/log_1.xml
-rw-r-----  1 oracle11g oinstall 10485761 2012-12-14 10:29:30 /u01/app/oracle11g/diag/rdbms/test/test/alert/log_2.xml
-rw-r-----  1 oracle11g oinstall 10485820 2013-07-06 09:28:37 /u01/app/oracle11g/diag/rdbms/test/test/alert/log_3.xml
-rw-r-----  1 oracle11g oinstall 10485869 2013-08-02 10:36:46 /u01/app/oracle11g/diag/rdbms/test/test/alert/log_4.xml
-rw-r-----  1 oracle11g oinstall 10486018 2013-08-02 10:46:47 /u01/app/oracle11g/diag/rdbms/test/test/alert/log_5.xml
-rw-r-----  1 oracle11g oinstall 10485807 2013-08-02 10:56:48 /u01/app/oracle11g/diag/rdbms/test/test/alert/log_6.xml
-rw-r-----  1 oracle11g oinstall 10486190 2013-08-02 11:57:09 /u01/app/oracle11g/diag/rdbms/test/test/alert/log_7.xml
-rw-r-----  1 oracle11g oinstall 10485769 2013-10-11 17:55:25 /u01/app/oracle11g/diag/rdbms/test/test/alert/log_8.xml
-rw-r-----  1 oracle11g oinstall   469237 2013-10-28 08:33:35 /u01/app/oracle11g/diag/rdbms/test/test/alert/log.xml

--可以发现log.xml大于10M后会发生会将其内容归档到如log_$N.xml这样的归档文件中，并清空当前的log.xml的内容。

-- 视图X$DBGALERTEXT提供了非常丰富的信息，这种查询方法比单纯查看alert*.log方便。例子：

SYS@test> column ORIGINATING_TIMESTAMP format a30

SYS@test> column MESSAGE_GROUP format a20

SYS@test> column PROBLEM_KEY format a20

SYS@test> column MESSAGE_TEXT format a100

select originating_timestamp, message_group, problem_key, message_text

  from X$DBGALERTEXT

 where message_text like '%ORA-00600%';

ORIGINATING_TIMESTAMP          MESSAGE_GROUP        PROBLEM_KEY          MESSAGE_TEXT

------------------------------ -------------------- -------------------- ---------------------------------------------------------------------------------

.....

2013-08-02 16:09:12.484        Generic Internal Err ORA 600 [kdBlkCheckE Errors in file /u01/app/oracle11g/diag/rdbms/test/test/trace/test_w000_23236.trc  (incident=1246313)

                               or                   rror]                :

                                                                         ORA-00600: internal error code, arguments: [kdBlkCheckError], [3], [248], [38508], [], [], [], [], [

                                                                         ], [], [], []

2013-08-02 16:18:51.510        Generic Internal Err ORA 600 [kdBlkCheckE Errors in file /u01/app/oracle11g/diag/rdbms/test/test/trace/test_smon_23656.trc  (incident=1247466)

                               or                   rror]                :

                                                                         ORA-00600: internal error code, arguments: [kdBlkCheckError], [3], [248], [38508], [], [], [], [], [

                                                                         ], [], [], []

2013-08-02 16:18:52.981                                                  Errors in file /u01/app/oracle11g/diag/rdbms/test/test/trace/test_smon_23656.trc:

                                                                         ORA-00607: Internal error occurred while making a change to a data block

                                                                         ORA-00600: internal error code, arguments: [kdBlkCheckError], [3], [248], [38508], [], [], [], [], [

                                                                         ], [], [], []

2013-08-02 16:18:59.409        Generic Internal Err ORA 600 [kdBlkCheckE Errors in file /u01/app/oracle11g/diag/rdbms/test/test/trace/test_w000_23940.trc  (incident=1247602)

                               or                   rror]                :

                                                                         ORA-00600: internal error code, arguments: [kdBlkCheckError], [3], [248], [38508], [], [], [], [], [

                                                                         ], [], [], []

2013-08-02 16:30:32.316        Generic Internal Err ORA 600 [kdBlkCheckE Errors in file /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_24647_192_168_101_6.trc  (inci

                               or                   rror]                dent=1247578):

                                                                         ORA-00600: 内部错误代码, 参数: [kdBlkCheckError], [3], [248], [38508], [], [], [], [], [], [], [], [

                                                                         ]

38862 rows selected.