Oracle配置資料庫診斷

luashin發表於2016-03-29
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診斷庫相關資訊：
SQL> col value for a70
SQL> col name for a35
SQL> set linesize 140
SQL> 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)
SQL> col name for a50
SQL> 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使用相關可參考：
《DRA(Data Recovery Advisor)的使用》

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9034054/viewspace-2071946/，如需轉載，請註明出處，否則將追究法律責任。

相關文章