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
Make DBA Life Easy 