ADR(Automatic Diagnositc Repository)

ADR(Automatic Diagnositc Repository)

环境：linux、oracle12c
1、ADR(Automatic Diagnositc Repository) 存放数据库诊断的数据的目录结构

ADR Base基础目录 位置有diagnostic_dest参数决定
show parameter diagnostic_dest

---

diagnostic_dest string /u01/app/oracle

如果没有设置diagnostic_dest参数
1)设置ORACLE_BASE环境变量 ，参数diagnostic_dest设置为$ORACL{E}_{B}ASE2)没有设置ORACL{E}_{B}ASE环境变量，参数diagnosti{c}_{d}est设置为$ORACLE_HOME/log

SELECT * FROM v$diag_info; --查看ARR的位置信息
ADR Base ARD基目录的路径
ADR HOME 当前数据库实例的ADR主目录的路径
Diag Trace 文本的预警信息和前后台进程的跟踪文件
Diag Alert XML版本的预警日志信息
Diag Incident 意外事件

2、ADRCI  ADR命令解释器（数据库日志查看）

[oracle@12c ~]$ adrci

ADRCI: Release 12.2.0.1.0 - Production on Sun Jul 26 16:10:25 2020

Copyright © 1982, 2017, Oracle and/or its affiliates. All rights reserved.

ADR base = “/u01/app/oracle”
adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl
diag/rdbms/catalog/catalog
diag/rdbms/yh/yh
adrci> show homes
ADR Homes:
diag/rdbms/orcl/orcl
diag/rdbms/catalog/catalog
diag/rdbms/yh/yh
diag/clients/user_oracle/host_2679879049_107
diag/tnslsnr/12c/listener
adrci> set home diag/rdbms/orcl/orcl #设置查看的库的目录
adrci> help show alert

adrci> show alert -tail 20

adrci> show control #设置日志保留时间SHORTP_POLICY 720h 默认30天 Trace files Core dump file等
LONGP_POLICY 8760 默认365天 Alert log Incident dumps等
set control (SHORTP_POLICY = 1440)
set control (SIZEP_POLICY = 1000000000)

3、在rman中debug调试命令

[oracle@12c ~]$ rman target sys/oracle@orcl catalog vpc1/oracle@catalog debug trace /home/oracle/trace_orcl.log #方法一：直接dubug

RMAN> run{ #方法二：run块中指定dubug
2> debug on;
3> allocate channel ch1 type disk;
4> backup datafile 4;
5> debug off;
6> backup datafile 5;
7> release channel ch1;
8> }相关字典表
SELECT name FROM v$fixe{d}_{t}ableWHEREnameLIK{E}^{\prime }V$RMAN%';V$RMA{N}_{S}TATUSV$RMAN_OUTPUT

4、Data Recovery Advisor （诊断医生，只对单实例数据库有用，不支持RAC）

RMAN> list failure;
RMAN> list failure critical|high|all|closed

RMAN> list failure;

Database Role: PRIMARY

List of Database Failures

Failure ID Priority Status Time Detected Summary

---

2422 HIGH OPEN 2020-07-26 16:51:56 One or more non-system datafiles are missing
RMAN> list failure 2422 detail;

RMAN> advise failure; #生成修复建议
RMAN> advise failure 2422;

RMAN> repair failure preview #查看如何修复
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1221963910.hm
contents of repair script:

restore and recover datafile

restore ( datafile 15 );
recover datafile 15;
sql ‘alter database datafile 15 online’;

RMAN> repair failure noprompt; #直接修复不互动

change failure xxx priority xxx;
只能将故障的优先级从high修改为low或者将low修改成high，不能修改为 critical。相关字典表
SELECT * FROM v$fixe{d}_{t}ableWHEREnameLIK{E}^{\prime }V$IR_%';V$I{R}_{F}AILURE4294952817VIEW655370V$IR_REPAIR 4294952819 VIEW 65537 0V$I{R}_{M}ANUA{L}_{C}HECKLIST4294952821VIEW655370V$IR_FAILURE_SET 4294952823 VIEW 65537 0

5、adr修复坏块实验

1、制作坏块
dd if=/dev/zero of=/u01/app/oracle/oradata/prod/users01.dbf count=2 seek=150 bs=8192 conv=notrunc
2、人为校验
rman > validate database;
Select * from v$database_block_corruption;
3、修复坏块
Recover datafile 6 block 150;
Recover datafile 6 block 151;
recover corruption list;