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

Formatting empty failing pages to make clean DBVERIFY results.

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

ORA-00600 internal error code arguments [ktbdchk1: bad dscn] after DG switchover

A bug caused error ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn]

ISSUE

After a successful switchover, there are a couple of ORA-00600 errors in alert.log .

Sat Jan 31 22:32:29 2015
Errors in file /u01/app/oracle/diag/rdbms/testdb/TETSDB4/trace/TESTDB4_ora_119810.trc (incident=112769):
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/testdb/tetsDB4/incident/incdir_112769/TESTDB4_ora_119810_i112769.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

Open incident 112796 trace file, we see the current SQL is just a normal INSERT DML :

*** 2015-01-31 22:32:29.749
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=40c9ma8n8k17u) -----
insert into TEST_TABLE (EVENTSEQNO,
JOBSEQNO, EVENTTYPE, EVENTDESCRIPTION,
JOBSTATUS, EVENTDT, BUSINESSREFID, LOGROLE, EVENTLOGDT, TARGETCOMPNAME, TARGETOPNAME, TARGETENDPOINTREF, EVENTINFORMATION, MSGSIZE, RECORDCOUNT) values ( test_Seq.nextval,:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 )

Run DBVERIFY and generates logs as below:

Page 15793939 failed with check code 6056
itl[45] has higher commit scn(0x0001.ff6a9a92) than block scn (0x0001.061dd22a)
Page 15794110 failed with check code 6056
itl[9] has higher commit scn(0x0001.ff71d940) than block scn (0x0001.0656d6b2)
Page 15794200 failed with check code 6056
itl[20] has higher commit scn(0x0001.ff7137bc) than block scn (0x0001.0651314d)
Page 15795600 failed with check code 6056
itl[46] has higher commit scn(0x0001.ff757e17) than block scn (0x0001.066372e8)
Page 15795925 failed with check code 6056

DBVERIFY - Verification complete

Total Pages Examined : 20185088
Total Pages Processed (Data) : 8205902
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 4629967
Total Pages Failing (Index): 2912
Total Pages Processed (Lob) : 5585318
Total Pages Failing (Lob) : 0
Total Pages Processed (Other): 125612
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1638289
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)

Threr is no physical or logical blocks corruption recorded in alert.log or in view v$database_block_corruption or in table INVALID_ROWS after using dbverify and analyze table VALIDATE STRUCTURE cascade and RMAN validate check.

Use sql to identify the affected indexes, then rebuild those indexes and run DBVERIFY, the “Total Pages Failing (Index)” number drops a little bit but not disappearing. Here Page 15795925 is db block#.

SQL>SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id =8 and 15795925 between block_id AND block_id + blocks-1;
SQL> ! cat /u01/app/oracle/product/11.2.0/dbhome_3/rdbms/admin/utlvalid.sql
rem
Rem Copyright (c) 1990, 1995, 1996, 1998 by Oracle Corporation
Rem NAME
REM UTLVALID.SQL
Rem FUNCTION
Rem Creates the default table for storing the output of the
Rem analyze validate command on a partitioned table
Rem NOTES
Rem MODIFIED
Rem syeung 06/17/98 - add subpartition_name
Rem mmonajje 05/21/96 - Replace timestamp col name with analyze_timestamp
Rem sbasu 05/07/96 - Remove echo setting
Rem ssamu 01/09/96 - new file utlvalid.sql
Rem

create table INVALID_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);
SQL> @/u01/app/oracle/product/11.2.0/dbhome_3/rdbms/admin/utlvalid.sql

Table created.
SQL> ANALYZE TABLE esb_tracker.esb_event VALIDATE STRUCTURE CASCADE online;

Table analyzed.

SQL> select * from INVALID_ROWS;

no rows selected
RMAN> validate check logical datafile 8;

Starting validate at 04-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=771 instance=TESTDB4 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00008 name=+DG1/testdb/datafile/testuser_tb.583.845563563
channel ORA_DISK_1: validation complete, elapsed time: 00:09:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 0 731827 25034752 4440865831
File Name: +DG1/testdb/datafile/testuser_tb.583.845563563
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 2477746
Index 0 1231472
Other 0 20593707

Finished validate at 04-FEB-15
SQL>Select * from v$database_block_corruption ;

no rows selected

Use the following query to confirm after rebuilding indexes, those impacted pages are returned to FREELIST. 

For example :

SQL>Select * 
      from dba_free_space 
     where file_id=8 and 15768657 between block_id AND block_id + blocks-1;

TABLESPACE_NAME  FILE_ID BLOCK_ID BYTES    BLOCKS RELATIVE_FNO
---------------- ------- -------- -------- ------ -----------
TESTUSER_TB     8        15761536 67108864  8192   1024

SOLUTION

To set hidden parameter “_ktb_debug_flags”=8 to make this bug fixing effective , though Oracle declared it has been fixed since 11.2.0.2 according to 1498717.1.

SQL>alter system set "_ktb_debug_flags"=8 scope=both sid='*';

Reference:

ORA-1555 / ORA-600 [ktbdchk1: bad dscn] ORA-600 [2663] in Physical Standby after switch-over (Doc ID 1498717.1)