Oracle configuration database diagnostics

Oracle configuration database diagnostics

Environment: RHEL 6.4 + Oracle 11.2.0.4

1. Set ADR
2. Use Support Workbench
3. Restore Block Media

1. Set ADR1.1 to view v $ diag_info

Viewv$diag_infoDiagnostic database information:

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 Tool

The ADR is fully based on the file system. You can use ADRCI to query the content of the ADR. You can also package the event and problem information in a ZIP file that can be sent to the Oracle Support Department.

$ 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. Using Support Workbench2.1 to manually construct a ORA-00600 Error

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], [], [], [], [], [],[], [], [], [], [], []

You can choose EM> quick packaging> Create a new package> View content> View list> schedule in EM.
The Upload file of the package ORA600kzs_20151231145006 has been generated successfully.
The uploaded file is located in [/oradata/app/oracle/product/11.2.0/dbhome_1/ChinaUnicomDB_shitan/sysman/emd/state/ORA600kzs_20151231145006_COM_1.zip].
Send it to Oracle manually.
Let's take a look at which files are packed in the compressed file:

$ 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 Use HM (Health Monitor)

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

3. Restore Block Media

In the RMAN backup command, the ANALYZE command, the dbv operating system command, and the SQL query attempt to access the damaged block will find the bad block and fill in the v $ database_block_corruption view.

SQL> select * from v$database_block_corruption;

If Oracle detects a damaged block, it registers a ORA-01578 error in the EM homepage and alarm log. The error message contains the absolute file number and block number of the Bad block. For example:

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'

Generally, the cause of the damage is a hardware fault in the operating system or disk, in the case of faulty I/O hardware or firmware, operating system cache problems, memory or paging problems, or disk repair utility errors.

-- Restore the 403rd RMAN> recover datafile 5 block 403 of the data file No. 5; -- restore all bad blocks recorded in the v $ database_block_partition uption view RMAN> recover upload uption list;

For more information about DRA usage, see:

• Use of DRA (Data Recovery Advisor)