Shulou(Shulou.com)06/01 Report--

Indexes

An index, like a table, is a kind of segment. The user's data is stored in it, which takes up disk space as well as tables. Index is a tree structure that allows direct access to a data row in a data table. It is introduced to improve query efficiency. It is an object independent of the table and can be stored in a different table space. The index record contains index keywords and pointers (addresses) to the data in the table. There are far fewer Ibind O operations on an index than on a table. Once the index is established, it will be automatically maintained by the Oracle system. There is no need to specify which index to use in the query.

Physically speaking, indexes can be divided into partitioned and non-partitioned indexes, regular B-tree indexes, bitmap (bitmap) indexes, reverse indexes and so on. Among them, the B-tree index is the most common index.

Introduction

This article mainly introduces you the relevant content of ORACLE check for damage Index (Corrupt Indexes), which is shared for your reference and study. I won't say much below. Let's take a look at the detailed introduction.

How to find the corrupted index in the Oracle database? Let's artificially construct a case where the index block is damaged. As shown in the following example:

SQL > create tablespace test_data 2 datafile'/ u01 size size autoextend off4 logging5 segment space management auto6 extent management local;Tablespace created.SQL > create user kerry2 identified by 1234563 default tablespace test_data;User created.SQL > grant connect to kerry;SQL > grant resource to kerry > create user kerry2 identified by 1234563 default tablespace test_data;User created.SQL > grant connect to kerry;SQL > grant resource to kerry

The above script creates the tablespace, creates the user kerry and authorizes it, then logs in to the database using the kerry account, constructs the test data, and creates the index IX_TEST on the TEST table

SQL > show user;USER is "KERRY" SQL > SQL > CREATE TABLE TEST (ID NUMBER (10), NAME VARCHAR2 (64)); Table created.SQL > DECLARE I NUMBER;2 BEGIN3 FOR I IN 1.. 1000 LOOP4 INSERT INTO TEST VALUES (I, LPAD ('Tunable, 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.

Then use the following script to find the index segment database file ID and the block number of the first block of the index segment.

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 16SQL >

There are many ways to construct bad blocks (such as BBED, etc.). Here we use the command clear under RMAN to mark the data block as corrupt and the data block No. 130 in data file 8 as bad block.

[oracle@DB-Server] $rman target / Recovery Manager: Release 11.2.0.1.0-Production on Thu Sep 13 17:41:05 2018Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: GSP (DBID=644393201) RMAN > recover datafile 8 block 130clear;Starting recover at 13-SEP-18using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=12 device type=DISKFinished recover at 13-SEP-18RMAN >

So let's first take a look at the methods used to verify that the index is corrupted, and test it to see if it works.

1: use ANALYZE analysis to verify the index structure

[oracle@DB-Server] $sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 13 17:42:03 2018Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL > 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 Ligand

As shown in the screenshot above, if the index is Corrupt Index, then using analyze index validate structure will report an error. To check all corrupted indexes (Corrupt Indexes) for the entire database, you can use the following script:

Spool analy_index.sqlSET PAGESIZE 50000sSELECTANALYZE INDEX'| | OWNER | |. | | INDEX_NAME | | 'VALIDATE STRUCTURE;' FROM DBA_INDEXES;spool off;@analy_index.sql

2: use the system view v$database_block_corruption to view the corrupt index

As shown below, we use this script to look at the index with a bad block and find that the script cannot find the bad block index.

Set pagesize 50 linesize 170col segment_name format a30col partition_name format a30SELECT 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#

The reason is as follows: there is a bad block record in the view v$database_block_corruption, but after we mark the first block of the index segment as a bad block, there is no record of the index segment in dba_extents. So the index in this case is corrupted, and the SQL statement cannot find the bad block index at all.

SQL > SELECT file_id, 2 segment_name, 3 segment_type 4 FROM dba_extents 5 WHERE file_id = 8; no rows selectedSQL > 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 TABLE7 TEST TABLESQL >

Because the first block of a segment is FIRST LEVEL BITMAP BLOCK, the second block is SECOND LEVEL BITMAP BLOCK, these two blocks are used to manage free block, the third block is PAGETABLE SEGMENT HEADER, this block is the HEADER_BLOCK in segment, and the later block is used to record data. About this knowledge, you can refer to my blog ORACLE on the section of the HEADER_BLOCK analysis. Our top example is to construct the first block as a bad block, so that the above SQL cannot detect it. We reconstruct the case, for example, we construct the data block of the index segment as a bad block, for example, below, the block number is 148 to construct the bad block. At this point, the script can find the bad block index. Therefore, summing up the above experiments, we can see that there are conditions for this script to find the bad block index, depending on the type of damaged block in the index segment.

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: KERRYold 5: WHERE OWNER ='& OWNER'new 5: WHERE OWNER = 'KERRY'Enter value for table_name: IX_TESTold 6: AND SEGMENT_NAME =' & TABLE_NAME'new 6: AND SEGMENT_NAME = 'IX_TEST'FILE_ID BLOCK_ID BLOCKS- 8 144 88 152 8SQL > SELECT HEADER_FILE2, HEADER_BLOCK3, BYTES4, BLOCKS5 EXTENTS FROM DBA_SEGMENTS 6 7 WHERE OWNER='&OWNER' AND SEGMENT_NAME='&SEGMENT_NAME' Enter value for owner: KERRYEnter value for segment_name: IX_TESTold 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 2SQL >

RMAN > recover datafile 8 block 148 clear

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.