环境: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