Oracle配置数据库诊断

环境:RHEL 6.4 + Oracle 11.2.0.4

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

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. 恢复块介质

在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使用相关可参考:

Reference

  • OCP 认证考试指南 (1Z0-053)[M]. 清华大学出版社, 2010.

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Linux 内存管理

    Linux内存清理:绝大多数情况下都不需要此操作,因为cache的内存在需要的时候是可以自动释放的~

    Alfred Zhao
  • Troubleshooting:重新安装Vertica建库后无法启动

    故障现象:Vertica集群安装成功,但是创建数据库后一直无法up. 具体报错输出如下:

    Alfred Zhao
  • ASM磁盘组扩容流程

    环境:RHEL 6.5 + GI 11.2.0.4 + Oracle 11.2.0.4

    Alfred Zhao
  • 国际电联2018年世界电信展在德班举行

    2018年9月10日,由国际电信联盟(ITU)主办的“2018年世界电信展”在南非德班国际会议中心开幕。中国邮电器材集团有限公司组织相关企业以国家展团形式参展。...

    VRPinea
  • 人工智能太弱?能够瞬间克隆你的虚拟人来了

    “如果我的克隆人与我的孩子进行交互活动,并与他们进行对话,这将是怎样的情景呢?” 在无法兼顾生意和家庭的艰难抉择中,尼克希尔·贾恩(Nikhil Jain)向自...

    企鹅号小编
  • Canvas圆形时钟

    今天带来一个圆形时钟,用JQ编写的Canvas 代码。Canvas 的神奇之处就在于,可以自行绘制图片不需要外部引入,当你深入了解这项技术的时候,你也会忍不住赞...

    我不是费圆
  • CentOS 7下KVM安装部署

    Kernel-based Virtual Machine的简称,是一个开源的系统虚拟化模块,自Linux 2.6.20之后集成在Linux的各个主要发行版本中。...

    拓荒者
  • 圣诞节快乐

    首先祝大家圣诞节快乐!本公众号运营有一段时间了.这段时间里,根据大家的反馈,公众号不断的更新,不断的完善功能,现在已经不是最初的璞玉了.感谢关注本公众号的每一位...

    小闫同学啊
  • 圣诞节!教你用Python画棵圣诞树

    作者 | 糖甜甜甜,985高校经管研二,擅长用 Python、R、tableau 等工具结合统计学和机器学习模型做数据分析。

    AI科技大本营
  • 前端路由原理解析和实现

    路由的概念来源于服务端,在服务端中路由描述的是 URL 与处理函数之间的映射关系。

    ConardLi

扫码关注云+社区

领取腾讯云代金券