How to fix ORA-26040: Data block was loaded using the NOLOGGING option

Today I faced with new ORA error. After solving I want to share this experience with yours.

So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf).
After recover via RMAN I saw strange error.


RMAN> recover datafile 5 block 443;
Starting recover at 24-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03

starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-MAR-13
RMAN>

SQL> select count(1) from block_corrupt_test;
select count(1) from block_corrupt_test
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 443)
ORA-01110: data file 5: '/u01/app/oracle/oradata/ulfet_db/example01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option


col segment_name format a20
col tablespace_name format a15

SQL> select owner, segment_name, tablespace_name from dba_extents where FILE_ID=5 and 443 between BLOCK_ID and BLOCK_ID+BLOCKS;

OWNER                       SEGMENT_NAME               TABLESPACE_NAME
------------------------ --------------------                  ---------------
SYS                              BLOCK_CORRUPT_TEST   EXAMPLE

Using dbv utility to clarify datafile corruption  

[oracle@localhost ~]$ dbv file=/u01/app/oracle/oradata/ulfet_db/example01.dbf

DBVERIFY: Release 11.2.0.1.0 - Production on Sun Mar 24 22:57:06 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ulfet_db/example01.dbf

DBV-00201: Block, DBA 20971963, marked corrupt for invalid redo application

DBVERIFY - Verification complete

Total Pages Examined         : 12800

Total Pages Processed (Data) : 6586

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 1261

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 3251

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 1702

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 1377097 (0.1377097)

[oracle@localhost ~]$ 

Use below query to get block error and file_name:

SQL> select dbms_utility.data_block_address_block('20971963') block_no,

dbms_utility.data_block_address_file('20971963') file_no

from dual;

  BLOCK_NO    FILE_NO

---------- ----------

       443          5

SQL> 

Now follow up below statements:

Create the repair table (in tablespace where corrupted table resides)

SQL> BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'example');

END;

/  2    3    4    5    6    7    8  

PL/SQL procedure successfully completed.

SQL> 

Identify corrupted blocks:

SQL> set serveroutput on

DECLARE num_corrupt INT;

BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME => 'SYS',

OBJECT_NAME => 'BLOCK_CORRUPT_TEST',

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

corrupt_count => num_corrupt);

DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

/SQL>   2    3    4    5    6    7    8    9   10   11  

number corrupt: 1

PL/SQL procedure successfully completed.

SQL> 

Show corrupted blocks:

SQL> select BLOCK_ID, CORRUPT_TYPE from REPAIR_TABLE;

BLOCK_ID CORRUPT_TYPE

---------- ------------

       443         6148

SQL> 

Mark the identified blocks as corrupted:

SQL> DECLARE num_fix INT;

BEGIN

num_fix := 0;

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SYS',

OBJECT_NAME=> 'BLOCK_CORRUPT_TEST',

OBJECT_TYPE => dbms_repair.table_object,

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

FIX_COUNT=> num_fix);

DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));

END;

/  2    3    4    5    6    7    8    9   10   11   12  

PL/SQL procedure successfully completed.

SQL> 

Allow in the future DML statements to skip the corrupted blocks:

SQL> BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME => 'SYS',

OBJECT_NAME => 'BLOCK_CORRUPT_TEST',

OBJECT_TYPE => dbms_repair.table_object,

FLAGS => dbms_repair.SKIP_FLAG);

END;

/  2    3    4    5    6    7    8  

PL/SQL procedure successfully completed.

SQL> 

Now, check table again:

SQL> select count(1) from block_corrupt_test;

COUNT(1)

------------

10