在Oracle数据库中如何找出损坏索引呢？ 下面我们工钱结构一个案例，将索引块损坏。如下案例所示：
SQL> create tablespace test_data 2 datafile '/u01/app/oracle/oradata/gsp/test_data_01.dbf' 3 size 200M autoextend off 4 logging 5 segment space management auto 6 extent management local; Tablespace created. SQL> create tablespace test_index 2 datafile '/u01/app/oracle/oradata/gsp/test_idx_01.dbf' 3 size 200M autoextend off 4 logging 5 segment space management auto 6 extent management local; Tablespace created. SQL> create user kerry 2 identified by 123456 3 default tablespace test_data; User created. SQL> grant connect to kerry; SQL> grant resource to kerry;
上述剧本是建设表空间，建设用户kerry并授权，然后利用kerry账号登录数据库，结构测试数据，在TEST表上建设索引IX_TEST
首页
SQL> show user;
USER is "KERRY"
SQL>
SQL> CREATE TABLE TEST(ID NUMBER(10), NAME VARCHAR2(64));
Table created.
SQL> DECLARE I NUMBER;
2 BEGIN
3 FOR I IN 1..1000 LOOP
4 INSERT INTO TEST VALUES(I, LPAD('T', 60));
5 END LOOP;
6 COMMIT;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> CREATE INDEX IX_TEST ON KERRY.TEST(NAME) TABLESPACE TEST_INDEX;
Index created.
然后利用下面剧本找到索引段数据库文件ID，以及索引段的第一个块的块号。
SQL> show user; USER is "SYS" SQL> col segment_name for a32; SQL> col header_file for 9999; SQL> col header_block for 9999; SQL> select segment_name 2 ,header_file 3 ,header_block 4 ,blocks 5 from dba_segments ds 6 where ds.owner='KERRY' and ds.segment_name='IX_TEST'; SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS -------------------------------- ----------- ------------ ---------- IX_TEST 8 130 16 SQL>
结构坏块的要领有不少（譬喻BBED等），这里我们利用RMAN下面的呼吁clear,可以标志数据块为corrupt,标志数据文件8中130号数据块为坏块。
[oracle@DB-Server ~]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Thu Sep 13 17:41:05 2018 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: GSP (DBID=644393201) RMAN> recover datafile 8 block 130 clear; Starting recover at 13-SEP-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK Finished recover at 13-SEP-18 RMAN>
那么我们先来看看利用那些要领验证索引损坏了，
1：利用ANALYZE阐明验证索引布局
[oracle@DB-Server ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 13 17:42:03 2018 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> analyze index kerry.ix_test validate structure; analyze index kerry.ix_test validate structure * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 8, block # 130) ORA-01110: data file 8: '/u01/app/oracle/oradata/gsp/test_idx_01.dbf'
昆山软件定制开拓 损坏索引 ( Corrupt Indexes )" class="aligncenter size-full wp-image-29934" title="73542-20180914114108688-163205428" src="/uploads/allimg/c180920/153IWF1XB0-11503.png" />
如上截图所示，假如索引损坏（Corrupt Index），那么利用analyze index validate structure就会报错。要查抄整个数据库所有的损坏索引（Corrupt Indexes）的话，就可以借助下面剧本：
spool analy_index.sql SET PAGESIZE 50000; SELECT 'ANALYZE INDEX ' || OWNER || '.' || INDEX_NAME|| ' VALIDATE STRUCTURE;' FROM DBA_INDEXES; spool off; @analy_index.sql
2：利用系统视图v$database_block_corruption查察损坏索引
如下所示，我们利用这个脚原来查察呈现坏块的索引，发明这个剧本无法找出坏块索引。
set pagesize 50 linesize 170
col segment_name format a30
col partition_name format a30
SELECT DISTINCT file#,
segment_name,
segment_type,
tablespace_name,
partition_name
FROM dba_extents a,
v$database_block_corruption b
WHERE a.file_id = b.file#
AND a.block_id <= b.block#
AND a.block_id + a.blocks >= b.block#;
昆山软件定制开拓 损坏索引 ( Corrupt Indexes )" class="aligncenter size-full wp-image-29935" title="73542-20180914114109657-1162968927" src="/uploads/allimg/c180920/153IWF1951Z-24U6.png" />
原因阐明如下，
SQL> SELECT file_id,
2 segment_name,
3 segment_type
4 FROM dba_extents
5 WHERE file_id = 8 ;
no rows selected
SQL> SELECT file_id,
2 segment_name,
3 segment_type
4 FROM dba_extents
5 WHERE owner = 'KERRY';
FILE_ID SEGMENT_NAME SEGMENT_TYPE
---------- -------------------------------- ------------------
7 TEST TABLE
7 TEST TABLE
SQL>
因为一个段的第一个区的第一个块是FIRST LEVEL BITMAP BLOCK，第二个块是SECOND LEVEL BITMAP BLOCK，这两个块是用来打点free block的，第三个块是PAGETABLE SEGMENT HEADER，这个块才是segment里的HEADER_BLOCK，再后头的块就是用来记录数据的。关于这些常识，可以参考我博客ORACLE关于段的HEADER_BLOCK的一点浅析。而我们最上面的例子，是将第一个块结构为坏块，所以导致上面SQL无法查出。 我们从头结构案例，如我们将索引段的数据块结构为坏块，譬喻下面，将块号148工钱结构坏块。那么此时这个剧本就能找出坏块索引了。所以综上述尝试可以看出，这个剧本查找坏块索引是有条件的，要看索引段损坏的块是什么范例
SQL> SELECT FILE_ID,
2 BLOCK_ID,
3 BLOCKS
FROM DBA_EXTENTS
4 5 WHERE OWNER ='&OWNER'
6 AND SEGMENT_NAME = '&TABLE_NAME';
Enter value for owner: KERRY
old 5: WHERE OWNER ='&OWNER'
new 5: WHERE OWNER ='KERRY'
Enter value for table_name: IX_TEST
old 6: AND SEGMENT_NAME = '&TABLE_NAME'
new 6: AND SEGMENT_NAME = 'IX_TEST'
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
8 144 8
8 152 8
SQL> SELECT HEADER_FILE
2 , HEADER_BLOCK
3 , BYTES
4 , BLOCKS
5 , EXTENTS
FROM DBA_SEGMENTS
6 7 WHERE OWNER='&OWNER' AND SEGMENT_NAME='&SEGMENT_NAME';
Enter value for owner: KERRY
Enter value for segment_name: IX_TEST
old 7: WHERE OWNER='&OWNER' AND SEGMENT_NAME='&SEGMENT_NAME'
new 7: WHERE OWNER='KERRY' AND SEGMENT_NAME='IX_TEST'
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
----------- ------------ ---------- ---------- ----------
8 146 131072 16 2
SQL>
昆山软件定制开拓 损坏索引 ( Corrupt Indexes )" class="aligncenter size-full wp-image-29936" title="73542-20180914114110259-775673133" src="/uploads/allimg/c180920/153IWF1b0Z-35010.png" />
RMAN> recover datafile 8 block 148 clear;
昆山软件定制开拓 损坏索引 ( Corrupt Indexes )" class="aligncenter size-full wp-image-29937" title="73542-20180914114110840-323204719" src="/uploads/allimg/c180920/153IWF201910-4D14.png" />
联系电话：0512-55008018
传真：0512-55008018
邮箱：sales@baoding-soft.com
地址：昆山市巴城镇学院路828号浦东软件园区1栋4楼A座
微信交流
关注我们