ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20131027]11G的内部视图X$DBGALERTEXT.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-10-28 08:59:24 0 删除 编辑
[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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-775126/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2456
  • 访问量
    6259827