oracle诊断文件

查询诊断文件路径

SQL> col name for a22
SQL> col value for a45
SQL> set line 100
SQL> select name,value from v$diag_info;

NAME                   VALUE
---------------------- ---------------------------------------------
Diag Enabled           TRUE
ADR Base               d:\app\wangw
ADR Home               d:\app\wangw\diag\rdbms\orcl\orcl
Diag Trace             d:\app\wangw\diag\rdbms\orcl\orcl\trace
Diag Alert             d:\app\wangw\diag\rdbms\orcl\orcl\alert
Diag Incident          d:\app\wangw\diag\rdbms\orcl\orcl\incident
Diag Cdump             d:\app\wangw\diag\rdbms\orcl\orcl\cdump
Health Monitor         d:\app\wangw\diag\rdbms\orcl\orcl\hm
Default Trace File     d:\app\wangw\diag\rdbms\orcl\orcl\trace\orcl_
                       ora_7060.trc


NAME                   VALUE
---------------------- ---------------------------------------------
Active Problem Count   0
Active Incident Count  0

已选择11行。

SQL>

• Diag Trace：正文格式的报警文件和前后台进程追踪文件路径
• Diag Alert：XML格式的报警文件路径
• Default Trace File：会话和SQL的追踪文件路径

自动诊断资料库ADR

ADR（Automatic Diagnostic Repositroy）是一个基于文件的资料库，存储了数据库的诊断数据，包括如追踪、卸载的事件、报警日志和健康监督报告等。

通过ADRCI操作诊断文件

• 任意目录下输入adrci，会出现多个home列表，而且查看告警文件时会让选择其中一个home

PS C:\Users\wangw> adrci

ADRCI: Release 11.2.0.1.0 - Production on 星期一 4月 11 13:58:00 2022

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

ADR base = "d:\app\wangw"
adrci> show homes
ADR Homes:
diag\clients\user_system\host_2262417689_76
diag\rdbms\orcl\orcl
diag\tnslsnr\desktop-sdi4l2s\listener
adrci> show alert

Choose the alert log from the following homes to view:

1: diag\clients\user_system\host_2262417689_76
2: diag\rdbms\orcl\orcl
3: diag\tnslsnr\desktop-ss5qjcl\listener
Q: to quit

Please select option: 2
Output the results to file: c:\users\wangw\appdata\local\temp\alert_15020_15972_orcl_1.ado

Please select option:

• 直接进入到查看的home下执行adrci，后续操作不会有选项

PS D:\app\wangw\diag\rdbms\orcl\orcl> adrci

ADRCI: Release 11.2.0.1.0 - Production on 星期一 4月 11 14:02:26 2022

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

ADR base = "d:\app\wangw"
adrci> show homes
ADR Homes:
diag\rdbms\orcl\orcl
adrci>

• adrci常用命令

# 浏览整个报警文件，自动调用系统默认编辑器打开文件 
adrci> show alert

ADR Home = D:\app\wangw\diag\rdbms\orcl\orcl:
*************************************************************************
Output the results to file: c:\users\wangw\appdata\local\temp\alert_15524_16216_orcl_1.ado
adrci>

# 浏览最新的报警内容，后面加数字可以显示需要的行数内容
adrci> show alert -tail
2022-04-10 16:22:01.644000 +08:00
Thread 1 advanced to log sequence 11 (LGWR switch)
  Current log# 2 seq# 11 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO02.LOG
2022-04-10 18:20:37.512000 +08:00
Thread 1 advanced to log sequence 12 (LGWR switch)
  Current log# 3 seq# 12 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO03.LOG
2022-04-10 20:23:27.884000 +08:00
Thread 1 cannot allocate new log, sequence 13
Private strand flush not complete
  Current log# 3 seq# 12 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO03.LOG
2022-04-10 20:23:30.882000 +08:00
Thread 1 advanced to log sequence 13 (LGWR switch)
  Current log# 1 seq# 13 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO01.LOG
2022-04-11 08:05:06.982000 +08:00
Clearing Resource Manager plan via parameter
Timed out trying to start process m000.
2022-04-11 13:01:00.507000 +08:00
Thread 1 cannot allocate new log, sequence 14
Private strand flush not complete
  Current log# 1 seq# 13 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO01.LOG
2022-04-11 13:01:03.564000 +08:00
Thread 1 advanced to log sequence 14 (LGWR switch)
  Current log# 2 seq# 14 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO02.LOG
adrci>

# 动态更新浏览报警内容
adrci> show alert -tail -F
2022-04-10 16:22:01.644000 +08:00
Thread 1 advanced to log sequence 11 (LGWR switch)
  Current log# 2 seq# 11 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO02.LOG
2022-04-10 18:20:37.512000 +08:00
Thread 1 advanced to log sequence 12 (LGWR switch)
  Current log# 3 seq# 12 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO03.LOG
2022-04-10 20:23:27.884000 +08:00
Thread 1 cannot allocate new log, sequence 13
Private strand flush not complete
  Current log# 3 seq# 12 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO03.LOG
2022-04-10 20:23:30.882000 +08:00
Thread 1 advanced to log sequence 13 (LGWR switch)
  Current log# 1 seq# 13 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO01.LOG
2022-04-11 08:05:06.982000 +08:00
Clearing Resource Manager plan via parameter
Timed out trying to start process m000.
2022-04-11 13:01:00.507000 +08:00
Thread 1 cannot allocate new log, sequence 14
Private strand flush not complete
  Current log# 1 seq# 13 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO01.LOG
2022-04-11 13:01:03.564000 +08:00
Thread 1 advanced to log sequence 14 (LGWR switch)
  Current log# 2 seq# 14 mem# 0: D:\APP\WANGW\ORADATA\ORCL\REDO02.LOG

# 查询和搜索关键字段，自动将内容通过默认文本编辑器打开
adrci> show alert -p "message_text like '%ORA-%'"

ADR Home = D:\app\wangw\diag\rdbms\orcl\orcl:
*************************************************************************
Output the results to file: c:\users\wangw\appdata\local\temp\alert_16608_16560_orcl_4.ado
adrci>

# 查看问题和事件
adrci> show problem

ADR Home = d:\app\wangw\diag\rdbms\orcl\orcl:
*************************************************************************
0 rows fetched

adrci> show incident

ADR Home = d:\app\wangw\diag\rdbms\orcl\orcl:
*************************************************************************
0 rows fetched

adrci>