Oracle配置数据库诊断

环境：RHEL 6.4 + Oracle 11.2.0.41. 设置ADR
2. 使用Support Workbench
3. 恢复块介质

1. 设置ADR

1.1 查看v$diag_info

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

col value for a70col name for a35set linesize 140select * from v$diag_info;SQL> select * from v$diag_info; INST_ID NAMEVALUE---------- ----------------------------------- ---------------------------------------------------------------------- 1 Diag EnabledTRUE 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 Count2 1 Active Incident Count 1711 rows selected.

1.2 ADRCI工具

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

$ adrciADRCI: Release 11.2.0.4.0 - Production on Thu Dec 31 10:57:51 2015Copyright （c） 1982, 2011, Oracle and/or its affiliates.All rights reserved.ADR base = "/oradata/app/oracle"adrci> help HELP [topic] Available Topics:CREATE REPORTECHOEXITHELPHOSTIPSPURGERUNSET BASESET BROWSERSET CONTROLSET ECHOSET EDITORSET HOMES | HOME | HOMEPATHSET TERMOUTSHOW ALERTSHOW BASESHOW CONTROLSHOW HM_RUNSHOW HOMES | HOME | HOMEPATHSHOW INCDIRSHOW INCIDENTSHOW PROBLEMSHOW REPORTSHOW TRACEFILESPOOL There are other commands intended to be used directly by Oracle, type "HELP EXTENDED" to see the listadrci>

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.zipinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_CONFIGURATION.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_PACKAGE.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_PACKAGE_INCIDENT.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_PACKAGE_FILE.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_PACKAGE_HISTORY.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_FILE_METADATA.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/IPS_FILE_COPY_LOG.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION_DEF.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION_PARAMETER_DEF.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_ACTION_PARAMETER.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_INCIDENT_TYPE.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/DDE_USER_INCIDENT_ACTION_MAP.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/INCIDENT.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/INCCKEY.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/INCIDENT_FILE.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/PROBLEM.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/HM_RUN.dmpinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/export/EM_USER_ACTIVITY.dmpinflating: diag/rdbms/shitan/shitan/incident/incdir_5097/shitan_ora_27456_i5097.trminflating: diag/rdbms/shitan/shitan/incident/incdir_5097/shitan_ora_27456_i5097.trcinflating: diag/rdbms/shitan/shitan/trace/shitan_ora_27456.trcinflating: diag/rdbms/shitan/shitan/trace/shitan_ora_27456.trminflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/manifest_1_1.xmlinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/manifest_1_1.htmlinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/manifest_1_1.txtinflating: diag/rdbms/shitan/shitan/alert/log.xmlinflating: diag/rdbms/shitan/shitan/trace/alert_shitan.loginflating: diag/rdbms/shitan/shitan/trace/shitan_mmon_18549.trcinflating: diag/rdbms/shitan/shitan/trace/shitan_mmon_18549.trminflating: diag/rdbms/shitan/shitan/trace/shitan_ora_18600.trcinflating: diag/rdbms/shitan/shitan/trace/shitan_ora_18600.trminflating: diag/rdbms/shitan/shitan/trace/shitan_lgwr_18541.trcinflating: diag/rdbms/shitan/shitan/trace/shitan_lgwr_18541.trminflating: diag/rdbms/shitan/shitan/trace/shitan_diag_18531.trcinflating: diag/rdbms/shitan/shitan/trace/shitan_diag_18531.trminflating: diag/rdbms/shitan/shitan/trace/shitan_dbrm_18533.trcinflating: diag/rdbms/shitan/shitan/trace/shitan_dbrm_18533.trminflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/config.xmlinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/crs/crsdiag.loginflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/opatch/opatch.loginflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/opatch/opatch.xmlinflating: diag/rdbms/shitan/shitan/incpkg/pkg_1/seq_1/metadata.xmlinflating: metadata.xml

2.2 使用HM（Health Monitor）

col name for a50select id, name, offline_capable from v$hm_check order by id;

3. 恢复块介质

在RMAN备份命令，ANALYZE命令，dbv操作系统命令以及尝试访问受损块的SQL查询，都会找到坏块后填充v$database_block_corruption视图。SQL> select * from v$database_block_corruption;如果Oracle检测到受损块，它将在EM主页和警报日志中注册ORA-01578错误.错误消息包含坏块的绝对文件编号和块编号。例如：

ORA-01578: ORACLE data block corrupted （file # 5, block # 403）ORA-01110: data file 5: "/oradata/data/SHITAN/datafile/o1_mf_dbs_d_ji_c7q2vg1x_.dbf"

通常，引起损坏的原因是操作系统或磁盘硬件故障，如存在故障的I/O硬件或固件、操作系统缓存问题、内存或分页问题或磁盘修复实用程序引发的错误。

-- 恢复5号数据文件的第403个块RMAN> recover datafile 5 block 403;-- 恢复v$database_block_corruption视图中记录的所有坏块RMAN> recover corruption list;

DRA使用相关可参考：

《DRA（Data Recovery Advisor）的使用》

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址