[Tips] Oracle 11g alert文件的变化 -- [复制链接]

shahand

版主

精华贴数: 8
技术积分: 25730
社区积分: 4455
注册时间: 2002-7-31

论坛徽章:: 83

ITPUB北京九华山庄2008年会纪念徽章
日期:2008-01-21 16:50:24

ITPUB北京2009年会纪念徽章
日期:2009-02-09 11:42:45

发表于 2007-7-2 18:48:28 |显示全部楼层

Oracle 11g alert文件的变化(后台报警日志)

刚刚加班写的

[B]
一．首先介绍2个新名词
Automatic Diagnostic Repository (ADR)—[/B]
一个存放数据库诊断日志、跟踪文件的目录，称作ADR base，对应初始化参数DIAGNOSTIC_DEST，如果设置了ORACLE_BASE环境变量，
DIAGNOSTIC_DEST等于ORACLE_BASE，如果没有设置ORACLE_BASE，则等与ORACLE_HOME/log。
[php]
SQL> show parameter DIAGNOSTIC

NAME    TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest    string /home/oracle

......  [/php]

ADRCI Command-Line Utility 命令行工具
使用该工具查看日志，跟踪信息，查看健康报告。

[B]
二．一个比较大的变化：aelrt xml file[/B]
在oracle 11g中，alert文件的信息是以xml的文件格式存在的，另外提供了普通文本格式的alert文件。
这两份log文件的位置分别是V$DIAG_INFO中的Diag Alert 和Diag Trace 对应的目录。
  [php]
1* SELECT * FROM V$DIAG_INFO

INST_ID NAME    VALUE
---------- ------------------------- ------------------------------------------------------------
1 Diag Enabled    TRUE
1 ADR Base    /home/oracle
1 ADR Home    /home/oracle/diag/rdbms/orcl/orcl
1 Diag Trace    /home/oracle/diag/rdbms/orcl/orcl/trace
1 Diag Alert    /home/oracle/diag/rdbms/orcl/orcl/alert
1 Diag Incident    /home/oracle/diag/rdbms/orcl/orcl/incident
1 Diag Cdump    /home/oracle/diag/rdbms/orcl/orcl/cdump
1 Health Monitor    /home/oracle/diag/rdbms/orcl/orcl/hm
1 Default Trace File    /home/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12624.trc
1 Active Problem Count    0
1 Active Incident Count    0
[/php]

这个是xml格式的alert log：
[php]
[oracle@ractest ~]$ cd /home/oracle/diag/rdbms/orcl/orcl/alert
[oracle@ractest alert]$ ls -l
total 148
-rw-r-----  1 oracle dba 139355 Jul  3 08:21 log.xml
[oracle@ractest alert]$ tail log.xml
<txt>QMNC started with pid=20, OS id=8646
</txt>
</msg>
<msg time='2007-07-03T08:21:31.911+08:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:2925:2802784106' type='NOTIFICATION' group='admin_ddl'
level='16' host_id='ractest' host_addr='10.200.15.180'
module='sqlplus@ractest (TNS V1-V3)' pid='8640'>
<txt>Completed: ALTER DATABASE OPEN
</txt>
</msg>
[oracle@ractest alert]$
[/php]

这是文本格式的alert：[php]
[oracle@ractest trace]$ cd /home/oracle/diag/rdbms/orcl/orcl/trace
[oracle@ractest trace]$ ls -l al*
-rw-r-----  1 oracle dba 27988 Jul  3 08:21 alert_orcl.log
[/php]
Alert文件记录了：关键错误（Critical errors (incidents) ），数据库管理操作，包括启动关闭数据库，创建、删除表空间，
物化视图刷新错误等信息，另外我做试验的时候还发现还包括有一般的ddl操作，下面是例子：

[php] SQL> create table test as select * from dba_objects where rownum <50;

Table created.

SQL> insert into test select * from dba_objects where rownum < 10 ;

9 rows created.

SQL> commit
  2  /

Commit complete.

SQL> delete from test where rownum < 30  ;

29 rows deleted.

SQL> commit;

Commit complete.

SQL> drop table test ;

Table dropped.

对应的alert信息：

Sat Jun 30 06:00:02 2007
Sat Jun 30 06:00:02 2007
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is UnwindToSCN (LockdownSCN) is 304868
Sat Jun 30 08:51:24 2007
create table test as select * from dba_objects where rownum <50
Sat Jun 30 08:51:49 2007
drop table test
Sat Jun 30 08:52:03 2007

.......
[/php]

[B]三．查看alert文件的方法：[/B]
1.使用Enterprise Manager，进入Oracle database home page，在Related Links点击Alert Log Contents.
见图：
2.使用vi等文本编辑alert_SID.log （text-only alert log, without the XML tags）
3.使用工具adrci：
[php]
[oracle@ractest ~]$ which adrci
/home/oracle/11g/bin/adrci
[oracle@ractest ~]$ adrci

ADRCI: Release 11.1.0.4.0 - Beta on Tue Jul 3 09:02:14 2007

Copyright (c) 1982, 2006, Oracle.  All rights reserved.

ADR base = "/home/oracle"
adrci>>help show alert
  Usage: SHOW ALERT [-p <predicate_string>] [-tail [num]] [-v]
                  [-file <alert_file_name>]
…………
  Examples:
show alert
show alert -p "message_text like '%incident%'"
show alert -tail 20
adrci>>show alert -tail 10

ADR Home = /home/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
2007-07-03 08:21:31.039000 +08:00
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
Starting background process SMCO
SMCO started with pid=18, OS id=8642
Opening with internal Resource Manager plan
Starting background process FBAR
FBAR started with pid=19, OS id=8644
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=20, OS id=8646
Completed: ALTER DATABASE OPEN

......
[/php]

关于这个工具，我会再单独写一篇介绍它的具体用法。