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设置为$ORACLE_BASE
2)没有设置ORACLE_BASE环境变量，参数diagnostic_dest设置为$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 (c) 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$fixed_table
WHERE name LIKE 'V$RMAN%';
V$RMAN_STATUS
V$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$fixed_table
WHERE name LIKE 'V$IR_%';
V$IR_FAILURE 4294952817 VIEW 65537 0
V$IR_REPAIR 4294952819 VIEW 65537 0
V$IR_MANUAL_CHECKLIST 4294952821 VIEW 65537 0
V$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;