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)的使用》
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Oracle】資料庫hang 診斷Oracle資料庫
- 診斷Oracle資料庫Hanging問題Oracle資料庫
- Oracle___診斷案例__資料庫的exp故障Oracle資料庫
- mysql資料庫效能診斷MySql資料庫
- dbms_addm執行oracle資料庫診斷Oracle資料庫
- Oracle 11g資料庫緩慢診斷案例Oracle資料庫
- 資料庫診斷一例資料庫
- Oracle ADDM --dbms_addm執行oracle資料庫診斷Oracle資料庫
- 資料庫異常智慧分析與診斷資料庫
- ODX 診斷資料庫轉換工具 — DDC資料庫
- Part II 診斷和優化資料庫效能優化資料庫
- 大語言模型與資料庫故障診斷模型資料庫
- MySQL資料庫診斷：InnoDB關機問題MySql資料庫
- 使用awr來診斷資料庫效能問題資料庫
- 利用hanganalyz/systemstate dump診斷資料庫hang資料庫
- 自管理的資料庫:自動效能診斷資料庫
- 使用SQL_TRACE進行資料庫診斷SQL資料庫
- oracle 8i資料庫ORA-01092的診斷Oracle資料庫
- 2 Day DBA-管理方案物件-監控和優化資料庫-效能自我診斷：自動資料庫診斷監控物件優化資料庫
- ORACLE診斷案例Oracle
- Oracle故障診斷Oracle
- ORACLE診斷事件Oracle事件
- 如何診斷oracle資料庫執行緩慢或hang住的問題Oracle資料庫
- 使用SQL_TRACE進行資料庫診斷(轉)SQL資料庫
- 使用SQL_TRACE進行資料庫診斷(1)SQL資料庫
- 使用SQL_TRACE進行資料庫診斷(2)SQL資料庫
- OCP課程50：管理II之診斷資料庫資料庫
- 使用SQL_TRACE進行資料庫診斷（zt）SQL資料庫
- 某物流系統資料庫故障診斷案例分析資料庫
- 【AWR】資料庫診斷工具AWR使用全程記錄資料庫
- MySQL使用event等待事件進行資料庫效能診斷MySql事件資料庫
- 轉載：使用SQL_TRACE進行資料庫診斷SQL資料庫
- 使用SQL_TRACE進行資料庫診斷(轉載)SQL資料庫
- oracle 效能診斷工具Oracle
- Oracle診斷事件列表Oracle事件
- ORACLE診斷事件(zt)Oracle事件
- Oracle10g資料庫自動診斷監視工具(ADDM)使用指南Oracle資料庫
- 配置tnsping跟蹤來診斷Oracle Net連線Oracle