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

1) 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;

2)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/lg6prdb/datafile/tibco.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 tibco 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=’TIBCO’
group by TABLESPACE_NAME,FILE_ID,BLOCKS order by 2;

TABLESPACE_NAME BLOCKS COUNT(*)
————– ——- ———-
TIBCO 8 3
TIBCO 16 3
TIBCO 32 2
TIBCO 40 1
TIBCO 48 2

………

…………….

TIBCO 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_spacewhere TABLESPACE_NAME=’TIBCO’;

SUM(BLOCKS)/8
———–
66944

— turn datafile autoextend off

SQL> alter database datafile ‘+DG1/lg6prdb/datafile/tibco.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;
/

— drop table to release the free space.

SQL> drop table huangj.EMPTY;

Table dropped.

— turn datafile autoextend on

SQL> alter database datafile ‘+DG1/lg6prdb/datafile/tibco.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/lg6prdb/datafile/tibco.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

Advertisements
This entry was posted in Corruptions, DataGuard and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s