[20190306]11g health monitor.txt

[20190306]11g health monitor.txt --//前几天做删除tab$的恢复,看链接https://blog.csdn.net/Enmotech/article/details/87834503,我的测试 --//链接:http://blog.itpub.net/267265/viewspace-2637010/=>[20190225]删除tab$记录的恢复5.txt. --//理论讲不会遇到对方的数据字典不一致的问题. --//blog里面提到hcheck脚本,不知道是什么,不过11g提供health monitor,里面也提供数据字典的检查. --//对这方面知识不是很了解,学习看看: 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.了解有那些检查项目: --//主要的视图集中v$hm开头的视图. SYS@book> select * from v$hm_check where internal_check='N';  ID NAME                           NAME_NLS                      CLSID CLS_NAME        FLAGS I O DESCRIPTION --- ------------------------------ ----------------------------- ----- --------------- ----- - - ----------------------------------------   2 DB Structure Integrity Check   DB Structure Integrity Check      2 PERSISTENT_DATA  4098 N Y Checks integrity of all database files  25 CF Block Integrity Check       CF Block Integrity Check          2 PERSISTENT_DATA  4098 N Y Checks integrity of a control file block   3 Data Block Integrity Check     Data Block Integrity Check        2 PERSISTENT_DATA  4098 N Y Checks integrity of a data file block   4 Redo Integrity Check           Redo Integrity Check              2 PERSISTENT_DATA  4098 N Y Checks integrity of redo log content  10 Transaction Integrity Check    Transaction Integrity Check       2 PERSISTENT_DATA  4096 N N Checks a transaction for corruptions  11 Undo Segment Integrity Check   Undo Segment Integrity Check      2 PERSISTENT_DATA  4096 N N Checks integrity of an undo segment  24 Dictionary Integrity Check     Dictionary Integrity Check        2 PERSISTENT_DATA  4096 N N Checks dictionary integrity  27 ASM Allocation Check           ASM Allocation Check              3 ASM              8194 N Y Diagnose allocation failure 8 rows selected. 3.执行时相关参数: CHECK_NAME                   PARAMETER_NAME    TYPE             DEFAULT_VALUE    DESCRIPTION ---------------------------- ----------------- ---------------- ---------------- ---------------------------------------- ASM Allocation Check         ASM_DISK_GRP_NAME DBKH_PARAM_TEXT                   ASM group name CF Block Integrity Check     CF_BL_NUM         DBKH_PARAM_UB4                    Control file block number Data Block Integrity Check   BLC_DF_NUM        DBKH_PARAM_UB4                    File number Data Block Integrity Check   BLC_BL_NUM        DBKH_PARAM_UB4                    Block number Dictionary Integrity Check   CHECK_MASK        DBKH_PARAM_TEXT  ALL              Check mask Dictionary Integrity Check   TABLE_NAME        DBKH_PARAM_TEXT  ALL_CORE_TABLES  Table name Redo Integrity Check         SCN_TEXT          DBKH_PARAM_TEXT  0                SCN of the latest good redo (if known) Transaction Integrity Check  TXN_ID            DBKH_PARAM_TEXT                   Transaction ID Undo Segment Integrity Check USN_NUMBER        DBKH_PARAM_TEXT                   Undo segment number 9 rows selected. 4.手工测试数据字典: --//拿我恢复的测试测试看看. SYS@book> exec dbms_hm.run_check('Dictionary Integrity Check','test_dict_check'); BEGIN dbms_hm.run_check('Dictionary Integrity Check','test_dict_check'); END; * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01427: single-row subquery returns more than one row ORA-06512: at "SYS.DBMS_HM", line 191 ORA-06512: at line 1 --//报错,跟踪发现:执行如下报错: /* Formatted on 2019/3/6 10:17:25 (QP5 v5.252.13127.32867) */ SELECT 52, ROWID, 'ind$.obj#'   FROM IND$  WHERE obj# < 0 UNION ALL SELECT 57, ROWID, 'ind$.type#'   FROM IND$  WHERE type# NOT BETWEEN 1 AND 9 UNION ALL SELECT 58, ROWID, 'ind$.pctfree$'   FROM IND$  WHERE pctfree$ NOT BETWEEN 0 AND 99 UNION ALL SELECT 59, ROWID, 'ind$.analyzetime <= SYSDATE'   FROM IND$  WHERE analyzetime > SYSDATE UNION ALL SELECT 51, ROWID, 'ind$.obj# pk'   FROM IND$  WHERE obj# IS NULL UNION ALL SELECT 51, ROWID, 'ind$.obj# pk'   FROM IND$  WHERE 1 > (  SELECT obj#                 FROM IND$             GROUP BY obj#               HAVING COUNT (*) > 1) UNION ALL SELECT 53, ROWID, 'ind$.dataobj# range'   FROM IND$  WHERE 1 > (  SELECT dataobj#                 FROM IND$             GROUP BY dataobj#               HAVING COUNT (*) > 1) UNION ALL SELECT 54, ROWID, 'ind$.ts# fk'   FROM IND$  WHERE (ts#) IN (SELECT ts#                    FROM IND$                   WHERE     (ts#) NOT IN (SELECT ts# FROM ts$)                         AND ts# != 2147483647) UNION ALL SELECT 55, ROWID, 'ind$.ts,file,block fk'   FROM IND$  WHERE (ts#, file#, block#) IN (SELECT ts#, file#, block#                                   FROM IND$                                  WHERE     (ts#, file#, block#) NOT IN (SELECT ts#                                                                               ,file#                                                                               ,block#                                                                           FROM seg$)                                        AND file# != 0                                        AND block# != 0) UNION ALL SELECT 56, ROWID, 'ind$.obj# fk_obj$'   FROM IND$  WHERE (obj#) IN (SELECT obj#                     FROM IND$                    WHERE (obj#) NOT IN (SELECT obj# FROM obj$)); --//执行如下返回多行:  SELECT dataobj#  FROM IND$ GROUP BY dataobj#   HAVING COUNT (*) > 1; SYS@book> SELECT dataobj#  FROM IND$ GROUP BY dataobj#   HAVING COUNT (*) > 1;   DATAOBJ# ----------      87367      87374      87372      87378 SELECT *   FROM dba_objects  WHERE data_object_id IN (  SELECT /*+ unnest */ dataobj#                               FROM IND$ where  dataobj# is not null                           GROUP BY dataobj#                             HAVING COUNT (*) > 1); OWNER  OBJECT_NAME             SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME ------ ----------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------ SYS    SYS_C0011093                            87367          87367 INDEX               2015-11-24 09:11:19 2015-11-24 09:11:19 2015-11-24:09:11:19 VALID   N Y N          4 SYS    SYS_IOT_TOP_87371                       87372          87372 INDEX               2015-11-24 09:11:19 2015-11-24 09:11:19 2015-11-24:09:11:19 VALID   N Y N          4 SYS    SYS_IOT_TOP_87373                       87374          87374 INDEX               2015-11-24 09:11:19 2015-11-24 09:11:19 2015-11-24:09:11:19 VALID   N Y N          4 SYS    SYS_IOT_TOP_87376                       87378          87378 INDEX               2015-11-24 09:11:19 2015-11-24 09:11:19 2015-11-24:09:11:19 VALID   N Y N          4 OE     PROD_SUPPLIER_IX                        88151          87378 INDEX               2015-11-24 09:11:54 2015-11-24 09:11:54 2015-11-24:09:11:54 VALID   N N N          4 OE     WHS_LOCATION_IX                         88160          87367 INDEX               2015-11-24 09:11:54 2015-11-24 09:11:54 2015-11-24:09:11:54 VALID   N N N          4 OE     CUST_ACCOUNT_MANAGER_IX                 88164          87374 INDEX               2015-11-24 09:11:54 2015-11-24 09:11:54 2015-11-24:09:11:54 VALID   N N N          4 OE     ORD_CUSTOMER_IX                         88166          87372 INDEX               2015-11-24 09:11:54 2015-11-24 09:11:54 2015-11-24:09:11:54 VALID   N N N          4 8 rows selected. --//很明显oracle Dictionary Integrity Check存在bug问题,OE这些索引是通过oracle自带的例子,通过传输表空间安装上来的. --//数据段号与系统表空间的这些索引段号重合了. 5.查看报表: SYS@book> select dbms_hm.get_run_report('test_dict_check') from dual; DBMS_HM.GET_RUN_REPORT('TEST_DICT_CHECK') ----------------------------------------------------------------------------------- Basic Run Information  Run Name                     : test_dict_check  Run Id                       : 93224  Check Name                   : Dictionary Integrity Check  Mode                         : MANUAL  Status                       : ERROR-NOT COMPLETED  Start Time                   : 2019-03-06 10:13:08.197565 +08:00  End Time                     : 2019-03-06 10:13:08.686826 +08:00  Error Encountered            : 604  Source Incident Id           : 0  Number of Incidents Created  : 0 Input Paramters for the Run  TABLE_NAME=ALL_CORE_TABLES  CHECK_MASK=ALL Run Findings And Recommendations  Finding  Finding Name  : Dictionary Inconsistency  Finding ID    : 93225  Type          : FAILURE  Status        : OPEN  Priority      : CRITICAL  Message       : SQL dictionary health check: file$ pk 42 on object FILE$                failed  Message       : Damaged rowid is AAAAARAABAAAADpAAG - description: No further                damage description available --//没有执行完成. SYS@book> @ rowid AAAAARAABAAAADpAAG     OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------         17          1        233          6   0x4000E9           1,233                alter system dump datafile 1 block 233 ; SYS@book> select * from dba_objects where object_id=17; OWNER  OBJECT_NAME SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME ------ ----------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------- SYS    FILE$                          17             17 TABLE               2013-08-24 11:37:35 2013-08-24 11:37:35 2013-08-24:11:37:35 VALID   N N N          1 SYS@book> column SPARE2 noprint SYS@book> column SPARE3 noprint SYS@book> column SPARE4 noprint SYS@book> select rowid,file$.* from file$; ROWID                   FILE#    STATUS$     BLOCKS        TS#   RELFILE#  MAXEXTEND        INC   CRSCNWRP   CRSCNBAS OWNERINSTANCE     SPARE1 ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- ---------- AAAAARAABAAAADpAAA          1          2      64000          0          1    4194302       1280          0          7                  4194306 AAAAARAABAAAADpAAB          2          2      51200          1          2    4194302       1280          0       1834                  8388610 AAAAARAABAAAADpAAC          3          2       3200          2          3    4194302        640          0     923328                 12582914 AAAAARAABAAAADpAAD          4          2        640          4          4    4194302        160          0      16143                 16777218 AAAAARAABAAAADpAAE          5          2      12800          6          5    4194302         80          0     952916                 20971522 AAAAARAABAAAADpAAF          6          2       5120          7          6    4194302        128          3  391355879                 25165826 AAAAARAABAAAADpAAG          7          1       1280                          4194302       2048          3  393536480                 29360130 7 rows selected. --//FILE#=7我已经删除了.数据文件已经删除了. 6.相关视图如下: SYS@book> @ view v_$hm old   6:  WHERE view_name LIKE UPPER ('%' || '&&1' || '%') new   6:  WHERE view_name LIKE UPPER ('%' || 'v_$hm' || '%') OWNER  VIEW_NAME                      C30 ------ ------------------------------ ------------------------------ SYS    GV_$HM_CHECK                   GV$HM_CHECK SYS    GV_$HM_CHECK_PARAM             GV$HM_CHECK_PARAM SYS    GV_$HM_FINDING                 GV$HM_FINDING SYS    GV_$HM_INFO                    GV$HM_INFO SYS    GV_$HM_RECOMMENDATION          GV$HM_RECOMMENDATION SYS    GV_$HM_RUN                     GV$HM_RUN SYS    V_$HM_CHECK                    V$HM_CHECK SYS    V_$HM_CHECK_PARAM              V$HM_CHECK_PARAM SYS    V_$HM_FINDING                  V$HM_FINDING SYS    V_$HM_INFO                     V$HM_INFO SYS    V_$HM_RECOMMENDATION           V$HM_RECOMMENDATION SYS    V_$HM_RUN                      V$HM_RUN 12 rows selected. --//可以用视图来代替检查报告来查看特定检查的结果.可用的视图有v$hm_run,v$hm_finding,v$hm_recommendation --//执行失败,仅仅了解看看.