[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.