Oracle配置数据库诊断

环境：RHEL 6.4 + Oracle 11.2.0.4

1. 设置ADR
2. 使用Support Workbench
3. 恢复块介质

1. 设置ADR 1.1 查看v$diag_info

查看v$diag_info诊断库相关信息：

col value for a70 col name for a35 set linesize 140 select * from v$diag_info; SQL> select * from v$diag_info; INST_ID NAME VALUE ---------- ----------------------------------- ---------------------------------------------------------------------- 1 Diag Enabled TRUE 1 ADR Base /opt/app/oracle11 1 ADR Home /opt/app/oracle11/diag/rdbms/vas/vas 1 Diag Trace /opt/app/oracle11/diag/rdbms/vas/vas/trace 1 Diag Alert /opt/app/oracle11/diag/rdbms/vas/vas/alert 1 Diag Incident /opt/app/oracle11/diag/rdbms/vas/vas/incident 1 Diag Cdump /opt/app/oracle11/diag/rdbms/vas/vas/cdump 1 Health Monitor /opt/app/oracle11/diag/rdbms/vas/vas/hm 1 Default Trace File /opt/app/oracle11/diag/rdbms/vas/vas/trace/vas_ora_10952.trc 1 Active Problem Count 2 1 Active Incident Count 17 11 rows selected. 1.2 ADRCI工具

ADR完全基于文件系统，可以使用ADRCI查询ADR的内容，还可以将事件和问题信息打包在可以发送给Oracle Support部门的ZIP压缩文件中。

$ adrci ADRCI: Release 11.2.0.4.0 - Production on Thu Dec 31 10:57:51 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ADR base = "/oradata/app/oracle" adrci> help HELP [topic] Available Topics: CREATE REPORT ECHO EXIT HELP HOST IPS PURGE RUN SET BASE SET BROWSER SET CONTROL SET ECHO SET EDITOR SET HOMES | HOME | HOMEPATH SET TERMOUT SHOW ALERT SHOW BASE SHOW CONTROL SHOW HM_RUN SHOW HOMES | HOME | HOMEPATH SHOW INCDIR SHOW INCIDENT SHOW PROBLEM SHOW REPORT SHOW TRACEFILE SPOOL There are other commands intended to be used directly by Oracle, type "HELP EXTENDED" to see the list adrci> 2. 使用Support Workbench 2.1 手工构造一则ORA-00600错误 SQL> alter user jingyu identified by values ''; alter user jingyu identified by values '' * ERROR at line 1: ORA-00600: internal error code, arguments: [kzsviver:1], [], [], [], [], [], [], [], [], [], [], []

可以在EM中，打包问题 -> 快速打包 -> 创建新程序包 -> 查看内容 -> 查看清单 -> 调度
已成功生成程序包 ORA600kzs_20151231145006 的上载文件。
该上载文件位于 [/oradata/app/oracle/product/11.2.0/dbhome_1/ChinaUnicomDB_shitan/sysman/emd/state/ORA600kzs_20151231145006_COM_1.zip]。
请将其手动发送到 Oracle。
我们来看下这个压缩文件打包了哪些文件：

$ unzip ORA600kzs_20151231145006_COM_1.zip Archive: ORA600kzs_20151231145006_COM_1.zip inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_CONFIGURATION.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_PACKAGE.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_PACKAGE_INCIDENT.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_PACKAGE_FILE.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_PACKAGE_HISTORY.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_FILE_METADATA.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_FILE_COPY_LOG.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION_DEF.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION_PARAMETER_DEF.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION_PARAMETER.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_INCIDENT_TYPE.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_INCIDENT_ACTION_MAP.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/INCIDENT.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/INCCKEY.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/INCIDENT_FILE.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/PROBLEM.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/HM_RUN.dmp inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/EM_USER_ACTIVITY.dmp inflating: diag/rdbms/shitan/shitan/incident/incdir_5097/shitan_ora_27456_i5097.trm inflating: diag/rdbms/shitan/shitan/incident/incdir_5097/shitan_ora_27456_i5097.trc inflating: diag/rdbms/shitan/shitan/trace/shitan_ora_27456.trc inflating: diag/rdbms/shitan/shitan/trace/shitan_ora_27456.trm inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/manifest_1_1.xml inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/manifest_1_1.html inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/manifest_1_1.txt inflating: diag/rdbms/shitan/shitan/alert/log.xml inflating: diag/rdbms/shitan/shitan/trace/alert_shitan.log inflating: diag/rdbms/shitan/shitan/trace/shitan_mmon_18549.trc inflating: diag/rdbms/shitan/shitan/trace/shitan_mmon_18549.trm inflating: diag/rdbms/shitan/shitan/trace/shitan_ora_18600.trc inflating: diag/rdbms/shitan/shitan/trace/shitan_ora_18600.trm inflating: diag/rdbms/shitan/shitan/trace/shitan_lgwr_18541.trc inflating: diag/rdbms/shitan/shitan/trace/shitan_lgwr_18541.trm inflating: diag/rdbms/shitan/shitan/trace/shitan_diag_18531.trc inflating: diag/rdbms/shitan/shitan/trace/shitan_diag_18531.trm inflating: diag/rdbms/shitan/shitan/trace/shitan_dbrm_18533.trc inflating: diag/rdbms/shitan/shitan/trace/shitan_dbrm_18533.trm inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/config.xml inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/crs/crsdiag.log inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/opatch/opatch.log inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/opatch/opatch.xml inflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/metadata.xml inflating: metadata.xml 2.2 使用HM(Health Monitor) col name for a50 select id, name, offline_capable from v$hm_check order by id; 3. 恢复块介质