ORA-01578 ORA-01110 ORA-26040: Data block was loaded using the NOLOGGING option

Alert log shows following ORA- errors:

ORA-01578:ORACLE data block corrupted (file # 13, block # 652351)
ORA-01110:data file 13:'/media/sf_software/oradata/OEMREP/EMPDBREPOS/mgmt.dbf'
ORA-26040:Data block was loaded using the NOLOGGING option

Find out the segment impacted, and confirm this is an empty table.

SQL>select OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME 
    from dba_extents 
   where file_id =13 and 652351 between block_id AND block_id+blocks-1;

OWNER    SEGMENT_NAME           SEGMENT_TYPE   TABLESPACE_NAME
-------- ---------------------  -------------- ----------------
SYSMAN   MOS_PA_EVAL_TARGETS_E  TABLE          MGMT_TABLESPACE
SQL> select count(*) from SYSMAN.MOS_PA_EVAL_TARGETS_E;

COUNT(*)
----------
0
SQL>select FILE#, BLOCK#, BLOCKS,scn_to_timestamp(NONLOGGED_START_CHANGE#), 
           scn_to_timestamp( NONLOGGED_END_CHANGE#), OBJECT# 
    from v$nonlogged_block;

FILE# BLOCK#   BLOCKS  SCN_TO_TIMESTAMP(NONLOGGED_START_CHANGE#)  SCN_TO_TIMESTAMP(NONLOGGED_END_CHANGE#)  OBJECT#
---------------------- -----------------------------------------  ---------------------------------------  -------
13   652351   1        12-MAY-20 11.58.27.000000000 AM           12-MAY-20 10.59.29.000000000 PM           91461

Truncate or move the table. 

SQL>truncate table SYSMAN.MOS_PA_EVAL_TARGETS_E;

or

SQL> alter table SYSMAN.MOS_PA_EVAL_TARGETS_E move tablespace MGMT_TABLESPACE;

luckily, the table is empty. it is easy to deal with. If table is not empty, then we can skip corrupted blocks to avoid DML ORA- errors.

Skip corrupt blocks 

SQL> select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E;

select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 13, block # 652351)
ORA-26040: Data block was loaded using the NOLOGGING option
BEGIN
  DBMS_REPAIR.skip_corrupt_blocks (
    schema_name => 'SYSMAN',
    object_name => 'MOS_PA_EVAL_TARGETS_E',
    object_type => DBMS_REPAIR.table_object,
    flags       => DBMS_REPAIR.skip_flag);
END;
/
 
SQL> select owner, table_name, SKIP_CORRUPT  from dba_tables 
      where table_name='MOS_PA_EVAL_TARGETS_E'

OWNER      TABLE_NAME                     SKIP_COR
---------- ------------------------------ --------
SYSMAN     MOS_PA_EVAL_TARGETS_E          ENABLED

Backup the table data, truncate the table , and then restore the table data

SQL> create table SYSMAN.STG_MOS_PA_EVAL_TARGETS_E as 
            select * from SYSMAN.MOS_PA_EVAL_TARGETS_E;

SQL> Truncate table SYSMAN.MOS_PA_EVAL_TARGETS_E;

SQL>Insert into SYSMAN.MOS_PA_EVAL_TARGETS_E 
                select * from SYSMAN.STG_MOS_PA_EVAL_TARGETS_E ;

The nologging corrupt block is returned to free space list

SQL> Select BYTES from dba_free_space 
     where file_id=13 and 652351 between block_id and block_id + blocks -1;

BYTES
----------
65536

SQL> select * from dba_extents 
     where file_id =13 and 652351 between block_id AND block_id+blocks-1;

no rows selected

After a little while, the nologging corrupt block will be reused

SQL> Select BYTES from dba_free_space
where file_id=13 and 652351 between block_id and block_id + blocks -1; 

no rows selected

SQL> select count(*) from dba_extents
where file_id =13 and 652351 between block_id AND block_id+blocks-1; 

COUNT(*)
----------
1

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