AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2013-10-28 894
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [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文件，一般直接进入目录，直接查看。
[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.

文章标签：
关系型数据库
lfreeali
目录
相关文章
.29.
|
6月前
|
安全 关系型数据库 MySQL
⑩④【MySQL】什么是视图？怎么用？视图的检查选项? 视图的作用？[VIEW]
⑩④【MySQL】什么是视图？怎么用？视图的检查选项? 视图的作用？[VIEW]
.29.
119 0
梦笔生花伟
|
SQL 数据处理
视图的创建
如何创建和使用视图的功能，更好的方便我们的工作。
梦笔生花伟
106 0
科技小能手
在尝试同步从控制器 的命名上下文 时出现 命名上下文要被删除或没有从指定的服务器上复制
科技小能手
3032 0
lfreeali
|
索引
[20180503]视图提示使用索引.txt
[20180503]视图提示使用索引.txt --//昨天优化sql语句,想提示某个视图里面的表使用索引,有点忘记ZALBB以前讲过的提示写法,看了以前链接, --//自己在写一个例子便于记忆.
lfreeali
960 0
lfreeali
|
SQL Oracle 关系型数据库
[20180328]不要在sys建立用户对象.txt
[20180328]不要在sys建立用户对象.txt --//好几年前遇到的问题,开发安装UTL_DBWS在linux下遇到问题,最后选择winows下安装. --//随着业务增加,无法支撑,需要数据库组迁移到linux下,也就是我当时的安装笔记: --//http://blog.
lfreeali
985 0
科技小先锋
File类的基本创建、删除与获取文件信息
科技小先锋
1139 0
科技小能手
|
SQL 索引
x$ksusecst 内部视图详解
科技小能手
1082 0
技术小甜
手动创建CView视图
技术小甜
1204 0
lfreeali
|
Oracle 关系型数据库
[20170502]11G查询隐含参数视图.txt
[20170502]11G查询隐含参数视图GV$SYSTEM_PARAMETER3.txt --//oracle 存在许多隐含参数,一直以为oracle没有提供正常的视图查询该内容,实际上oracle 11G已经提供这方面的功能,只不过oracle并不公开.
lfreeali
1022 0
lfreeali
|
SQL Perl 关系型数据库
[20160713]改变参数在另外的会话.txt
[20160713]改变参数在另外的会话.txt --DBMS_SYSTEM包包含两个过程SET_BOOL_PARAM_IN_SESSION和SET_INT_PARAM_IN_SESSION，它仅仅支持逻辑值true与false，以及某个数 --值的修改，好像不支持字符串的修改。
lfreeali
866 0