How To Format Empty Failing (Index) Pages Marked by DBVERIFY

ISSUE

Following another post “ORA-00600 internal error code arguments [ktbdchk1: bad dscn] after DG switchover”, there is another smaller file number 8 with 22 indexes pages identified by dbverify as failing pages as below:

Total Pages Failing   (Index): 22

Here we are going to demonstrate how to format those failing index pages.

SOLUTION

Identify the index segments and rebuild them online accordingly.

From the dbverify log below and run the query to get index segments owner and names for rebuilding:

itl[20] has higher commit scn(0x0001.ff7137bc) than block scn (0x0001.0651314d)
Page 15795600 failed with check code 6056

SQL>SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id =8 and 15795600 between block_id AND block_id + blocks-1;

After rebuilt all the impacted indexes and run dbverify and sql query above, all the failing pages(blocks) are returned to freelist queue. They can be verified by query against dba_free_space view as well.

SQL> select name, bytes/1024/1024/1024 from v$datafile where file#=8;

NAME                                          BYTES/1024/1024/1024
--------------------------------------------  --------------------
+DG1/testdb/datafile/users_tbl.583.845563563  188

Create a table to use all free space, big pctfree to speed up inserting to make use of all available free space.

SQL> create table EMPTY( n number ) tablespace users_tbl pctfree 99;

Table created.

To get min/max free block size.

SQL> select TABLESPACE_NAME,FILE_ID,BLOCKS,count(*)
from dba_free_space
where TABLESPACE_NAME='USERS_TBL'
group by TABLESPACE_NAME,FILE_ID,BLOCKS 
order by 2;

TABLESPACE_NAME BLOCKS COUNT(*)
-------------- ------- ----------
USERS_TBL         8       3
USERS_TBL        16       3
USERS_TBL        32       2
USERS_TBL        40       1
USERS_TBL        48       2
.........
..
USERS_TBL     75904       1

21 rows selected.

To get how many times of minimum free space blocks in total available free space.

SQL> select sum(BLOCKS)/8  
       from dba_free_space
      where TABLESPACE_NAME='USERS_TBL';

SUM(BLOCKS)/8
-----------
66944

Turn datafile autoextend off.

SQL> alter database datafile '+DG1/testdb/datafile/USERS_TBL.583.845563563' autoextend off;

Database altered.

Allocate all available free space to table EMPTY with smallest freespace blcoks.

SQL>BEGIN
      for i in 1..66944 loop
        EXECUTE IMMEDIATE 'alter table EMPTY allocate extent ( size 64K) ';
      end loop;
    END;
    /

Insert data into table EMPTY until encountering unable  to allocate space ORA- error.

SQL> Begin
       FOR i IN 1..100000000 loop
         for j IN 1..10000 loop
           Insert into EMPTY VALUES(i+j);
        end loop;
        commit;
      END LOOP;
   END;
  /

Now drop table to release the free space.

SQL> drop table EMPTY;

Table dropped.

Turn datafile autoextend on.

SQL> alter database datafile '+DG1/tetsdb/datafile/users_tbl.583.845563563' autoextend on;

Database altered.

Run DBVEIFY to confirm all pages are all right now with ZERO pages failing.

DBVERIFY - Verification starting : FILE = +DG1/testdb/datafile/users_tbl.583.845563563

DBVERIFY - Verification complete
Total Pages Examined : 26869760
Total Pages Processed (Data) : 4391230
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 682945
Total Pages Failing (Index): 0
Total Pages Processed (Lob) : 20668189
Total Pages Failing (Lob) : 0
Total Pages Processed (Other): 49949
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1077447
Total Pages Marked Corrupt : 0