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>
