[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

--//执行失败,仅仅了解看看.