Oracle設定資料庫診斷

Oracle設定資料庫診斷

環境：RHEL 6.4 + Oracle 11.2.0.4

1. 設定ADR
2. 使用Support Workbench
3. 恢複塊介質

1. 設定ADR1.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 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               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 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 listadrci> 

2. 使用Support Workbench2.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 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)的使用》