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设置为
O
R
A
C
L
E
B
A
S
E
2
)
没有设置
O
R
A
C
L
E
B
A
S
E
环境变量,参数
d
i
a
g
n
o
s
t
i
c
d
e
s
t
设置为
ORACLE_BASE 2)没有设置ORACLE_BASE环境变量,参数diagnostic_dest设置为
ORACLEBASE2)没有设置ORACLEBASE环境变量,参数diagnosticdest设置为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
f
i
x
e
d
t
a
b
l
e
W
H
E
R
E
n
a
m
e
L
I
K
E
′
V
fixed_tableWHERE name LIKE 'V
fixedtableWHEREnameLIKE′VRMAN%';V
R
M
A
N
S
T
A
T
U
S
V
RMAN_STATUSV
RMANSTATUSVRMAN_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
f
i
x
e
d
t
a
b
l
e
W
H
E
R
E
n
a
m
e
L
I
K
E
′
V
fixed_tableWHERE name LIKE 'V
fixedtableWHEREnameLIKE′VIR_%';V
I
R
F
A
I
L
U
R
E
4294952817
V
I
E
W
655370
V
IR_FAILURE 4294952817 VIEW 65537 0V
IRFAILURE4294952817VIEW655370VIR_REPAIR 4294952819 VIEW 65537 0V
I
R
M
A
N
U
A
L
C
H
E
C
K
L
I
S
T
4294952821
V
I
E
W
655370
V
IR_MANUAL_CHECKLIST 4294952821 VIEW 65537 0V
IRMANUALCHECKLIST4294952821VIEW655370VIR_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;