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


sys@db11r2> select count(*) from guang.queue_t;
select count(*) from guang.queue_t
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 1282)
ORA-01110: data file 6: '/app/oracle/oradata/DB11R2/guang_tbs_02.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

sys@db11r2> select owner, segment_name, tablespace_name from dba_extents where FILE_ID=6 and 1282 between BLOCK_ID and BLOCK_ID+BLOCKS;

OWNER                          SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
GUANG                          QUEUE_T                        GUANG_TBS


[oracle@gchen-pc dbs]$ dbv file=/app/oracle/oradata/DB11R2/guang_tbs_02.dbf

DBVERIFY: Release 11.2.0.1.0 - Production on Thu Feb 10 23:46:59 2011

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

DBVERIFY - Verification starting : FILE = /app/oracle/oradata/DB11R2/guang_tbs_02.dbf

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

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

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

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

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

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

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

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

DBVERIFY - Verification complete

Total Pages Examined         : 2560
Total Pages Processed (Data) : 965
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1592
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2
Total Pages Marked Corrupt   : 8
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 4656993 (0.4656993)

sys@db11r2> select dbms_utility.data_block_address_block('25167019') block_no,
  2  dbms_utility.data_block_address_file('25167019') file_no
  3  from dual;

  BLOCK_NO    FILE_NO
---------- ----------
      1195          6
sys@db11r2> Select * from v$database_block_corruption;

no rows selected

BLOCKRECOVER DATAFILE 48 BLOCK 142713;;

RMAN> BLOCKRECOVER corruption list;


Script

REM Create the repair table in a given tablespace:

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => '&tablespace_name');
END;
/

REM Identify corrupted blocks for schema.object:

set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

REM Optionally display any corrupted block identified by check_object:

select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION
from REPAIR_TABLE;

REM Mark the identified blocks as corrupted

DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME=> '&object_name',
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;
/

REM Allow future DML statements to skip the corrupted blocks:

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => '&schema_name',
OBJECT_NAME => '&object_name',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/

sys@db11r2> select count(*) from guang.queue_t;

  COUNT(*)
----------
     75525

reference: note

ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING – Error explanation and solution [ID 794505.1]
DBMS_REPAIR SCRIPT [ID 556733.1]

About these ads

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: