oracle diag日志,Oracle 11g 中日志的查看 | 学步园

最新推荐文章于 2026-04-24 05:33:59 发布

转载最新推荐文章于 2026-04-24 05:33:59 发布 · 1.4k 阅读

标签

Oracle 11g的Automatic Diagnostic Repository (ADR)是存储数据库诊断日志和跟踪文件的目录。本文介绍了如何查看和管理Oracle 11g的ADR日志，包括XML格式的alert文件和文本格式的alert_rac1.log，以及使用adrci工具进行操作的方法。

一．首先介绍2个新名词

Automatic Diagnostic Repository (ADR)

一个存放数据库诊断日志、跟踪文件的目录，称作ADR base，对应初始化参数DIAGNOSTIC_DEST，如果设置了ORACLE_BASE环境变量，DIAGNOSTIC_DEST等于ORACLE_BASE，如果没有设置RACLE_BASE，则等与ORACLE_HOME/log。

SQL> show parameter DIAGNOSTIC

NAME TYPE VALUE

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

diagnostic_dest string /oracle/oracle

二．aelrt xml file

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

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

sql>select name ,value from v$diag_info

NAME VALUE

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

Diag Enabled TRUE

ADR Base /oracle/oracle

ADR Home /oracle/oracle/diag/rdbms/rac/rac1

Diag Trace /oracle/oracle/diag/rdbms/rac/rac1/trace

Diag Alert /oracle/oracle/diag/rdbms/rac/rac1/alert

Diag Incident /oracle/oracle/diag/rdbms/rac/rac1/incident

Diag Cdump /oracle/oracle/diag/rdbms/rac/rac1/cdump

Health Monitor /oracle/oracle/diag/rdbms/rac/rac1/hm

Default Trace File /oracle/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_11028.trc

Active Problem Count 0

Active Incident Count 0

这个是xml格式的alert log：

cd/oracle/oracle/diag/rdbms/rac/rac1/alert

[oracle@rac1 alert]$ ls

log.xml

文本格式的alert：

cd /oracle/oracle/diag/rdbms/rac/rac1/trace/

[oracle@rac1 trace]$ ls alert_rac1.log

alert_rac1.log

[oracle@rac1 trace]$ tail alert_rac1.log

Tue Nov 13 08:12:13 2007

Completed: ALTER DATABASE OPEN

Tue Nov 13 08:16:31 2007

Starting background process CJQ0

Tue Nov 13 08:16:31 2007

CJQ0 started with pid=31, OS id=2336

Tue Nov 13 14:00:55 2007

Thread 1 advanced to log sequence 32

Current log# 2 seq# 32 mem# 0: +DATA/rac/onlinelog/group_2.257.638334671

Current log# 2 seq# 32 mem# 1: +FLASH/rac/onlinelog/group_2.258.638333877

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

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

sql>create tablespace test datafile '+DATA/rac/datafile/test.dbf' size 100m extent management local;

[oracle@rac2 trace]$ tail -f alert_rac2.log

Mon Nov 12 03:57:44 2007

CJQ0 started with pid=29, OS id=6576

Mon Nov 12 04:12:38 2007

Stopping background process CJQ0

Mon Nov 12 04:13:00 2007

Starting background process CJQ0

Mon Nov 12 04:13:01 2007

CJQ0 started with pid=31, OS id=18429

Mon Nov 12 04:17:58 2007

create tablespace test datafile '+DATA/rac/datafile/test.dbf' size 100m extent management local

三．查看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：

四的使用adrci

[oracle@rac2 trace]$ which adrci

~/product/11.1.0.6.0/db/bin/adrci

[oracle@rac2 trace]$ adrci

ADRCI: Release 11.1.0.6.0 - Beta on Mon Nov 12 04:34:47 2007

ADR base = "/oracle/oracle"

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

使用方法：

1.client trace file

adrci> show base -product client

adrci> set base

adrci> show tracefile

adrci> show trace

2 server trace file

adrci

adrci> show tracefile

adrci> show trace

3. 告警日志alert的trace file

adrci

help show alert

Usage: SHOW ALERT [-p ] [-term]

[ [-tail [num] [-f]] | [-file ] ]

Examples:

show alert

show alert -p "message_text like '%incident%'"

show alert -tail 20

adrci>>show alert -tail 10