oracle alert column,Oracle 11g alert文件的变化

一．首先介绍2个新名词

Automatic Diagnostic Repository (ADR)—

一个存放数据库诊断日志、跟踪文件的目录，称作ADR base，对应初始化参数DIAGNOSTIC_DEST，

如果设置了ORACLE_BASE环境变量，DIAGNOSTIC_DEST等于ORACLE_BASE，

如果没有设置ORACLE_BASE，则等与ORACLE_HOME/log。

SQL> show parameter diag

NAME                                 TYPE        VALUE

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

diagnostic_dest                      string      /oracle/app/oracle

......

ADRCI Command-Line Utility 命令行工具

使用该工具查看日志，跟踪信息，查看健康报告。

[oracle@ora1 ~]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Sat Jul 10 18:47:33 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/oracle/app/oracle"

adrci> ?

DIA-48415: Syntax error found in string [?

] at column [2]

adrci> help

HELP [topic]

Available Topics:

CREATE REPORT

ECHO

EXIT

HELP

HOST

IPS

PURGE

RUN

SET BASE

SET BROWSER

SET CONTROL

SET ECHO

SET EDITOR

SET HOMES | HOME | HOMEPATH

SET TERMOUT

SHOW ALERT

SHOW BASE

SHOW CONTROL

SHOW HM_RUN

SHOW HOMES | HOME | HOMEPATH

SHOW INCDIR

SHOW INCIDENT

SHOW PROBLEM

SHOW REPORT

SHOW TRACEFILE

SPOOL

There are other commands intended to be used directly by Oracle, type

"HELP EXTENDED" to see the list

二．一个比较大的变化：aelrt xml file

在oracle 11g中，alert文件的信息是以xml的文件格式存在的，另外提供了普通文本格式的alert文件。

这两份log文件的位置分别是V$DIAG_INFO中的Diag Alert 和Diag Trace 对应的目录。

代码:

alert_ora11g.log文件存放：

/oracle/app/oracle/diag/rdbms/ora11g/ora11g/trace/alert_ora11g.log

/oracle/app/oracle/diag/rdbms/ora11g/ora11g/alert/log.xml

SQL> col NAME format a30

SQL> col VALUE format a80

SQL> select * from v$diag_info;   INST_ID NAME                           VALUE

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

1 Diag Enabled                   TRUE

1 ADR Base                       /oracle/app/oracle

1 ADR Home                       /oracle/app/oracle/diag/rdbms/ora11g/ora11g

1 Diag Trace                     /oracle/app/oracle/diag/rdbms/ora11g/ora11g/trace

1 Diag Alert                     /oracle/app/oracle/diag/rdbms/ora11g/ora11g/alert

1 Diag Incident                  /oracle/app/oracle/diag/rdbms/ora11g/ora11g/incident

1 Diag Cdump                     /oracle/app/oracle/diag/rdbms/ora11g/ora11g/cdump

1 Health Monitor                 /oracle/app/oracle/diag/rdbms/ora11g/ora11g/hm

1 Default Trace File             /oracle/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_4991.trc

1 Active Problem Count           0

1 Active Incident Count          0

11 rows selected.

Alert文件记录了：关键错误(Critical errors (incidents) )，数据库管理操作，包括启动关闭数据库，创建、删除表空间，

物化视图刷新错误等信息，另外我做试验的时候还发现还包括有一般的ddl操作，

三．查看alert文件的方法：

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：

SQL> host

[oracle@ora1 pfile]$ which adrci

/oracle/app/oracle/product/11.2.0/dbhome_1/bin/adrci

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

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

ADR base = "/home/oracle"

adrci>>help show alert

Usage: SHOW ALERT [-p ] [-tail [num]] [-v]

[-file ]

…………

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: