{"id":8068,"date":"2019-03-20T12:08:50","date_gmt":"2019-03-20T12:08:50","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/repair-block-corruption-in-an-oracle-database\/"},"modified":"2026-11-03T19:29:15","modified_gmt":"2026-11-03T19:29:15","slug":"repair-block-corruption-in-an-oracle-database","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/repair-block-corruption-in-an-oracle-database\/","title":{"rendered":"Repair block corruption in an Oracle database"},"content":{"rendered":"

Introduction<\/strong>
\nThis post discusses how to repair multiple or single block corruption of data files, including system data files, in an Oracle \u00a9 database.<\/p>\n

Block corruption is a common source of database outages. A database block is corrupted when its contents are different than what the
\nOracle database expects to find. If block corruption is not prevented or repaired, the corruption can bring down the database and possibly cause the loss of key business data.<\/p>\n

This is one of the real time issues that you need to face and fix in production databases.<\/p>\n

Find and fix block corruption<\/strong><\/p>\n

The following image shows a possible block corruption:<\/p>\n

\"oracle<\/p>\n

Image source<\/em><\/a><\/p>\n

Find the corruption<\/strong><\/p>\n

To find the corruption, execute the following commands:<\/p>\n

 SQL> select * from V$DATABASE_BLOCK_CORRUPTION;<\/p>\n

    FILE#    BLOCK#    BLOCKS    CORRUPTION_CHANGE#  CORRUPTION
\n    —– ———- ———-  ——————  ———-
\n     352     173191      9               0            ALL ZERO<\/p>\n

    SQL> SELECT FILE_ID,RELATIVE_FNO,FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES WHERE FILE_ID=352;<\/p>\n

     FILE_ID   RELATIVE_FNO   FILE_NAME                                          TABLESPACE_NAME
\n    ———- ———— ————————————————– ——————
\n        352        352      \/u01\/apps_st\/samusxxxxxxxx_data2\/system09.dbf              SYSTEM<\/p>\n

    SQL> SELECT owner, segment_name, segment_type FROM dba_extents WHERE RELATIVE_FNO = 352 AND Block_id BETWEEN 173191 AND 173191 + blocks – 1;<\/p>\n

    OWNER      SEGMENT_NAME    SEGMENT_TYPE
\n    ——– —————  ——————
\n    SYS         I_COL3          INDEX
\n    SYS         C_OBJ#          CLUSTER<\/p>\n

Note<\/strong>: In this case, SYS object segment I\\_COL3 has a block corruption. The corrupted block, as reported by the `dbv<\/em>` command, is shown as free in the dba_free_space` view.<\/p>\n

To free the blocks for file 352, execute the following commands:<\/p>\n

    SQL> select * from V$DATABASE_BLOCK_CORRUPTION;<\/p>\n

    FILE#    BLOCK#    BLOCKS    CORRUPTION_CHANGE#  CORRUPTION
\n    —– ———- ———-  ——————  ———-
\n     352     173191      9               0            ALL ZERO<\/p>\n

    SQL> Select * from dba_free_space where file_id =352 and 173191 between block_id and block_id + blocks -1;<\/p>\n

    TABLESPACE_NAME   FILE_ID  BLOCK_ID  BYTES    BLOCKS   RELATIVE_FNO
\n    —————   ——-  ——–  —–  ———- ————
\n       SYSTEM           352     173191   73728       9        352<\/p>\n

 Force clear blocks<\/strong><\/p>\n

After the blocks are free, you can use the following commands to force clear those corrupted blocks. This is also known as formatting corrupted blocks that are not part of any segment.<\/p>\n

1. Create a user and grant the user access to the database.<\/strong><\/p>\n

    create user Scott identified by password default tablespace SYSTEM;
\n    grant resource, connect, create table, create trigger to Scott;<\/em><\/p>\n

 2. Identify the corrupt block against the data file<\/strong><\/p>\n

  [Thu Nov 17 11:59:19 orbdev@samusxxxxxxxx:~ ] $ dbv file=’\/mnt\/apps_st\/samusxxxxxxxx_data2\/system09.dbf’ userid=sys\/xxxxx<\/p>\n

    DBVERIFY: Release 11.2.0.4.0 – Production on Thu Nov 17 11:59:21 2016<\/p>\n

    Copyright (c) 1982, 2011, Oracle and\/or its affiliates.  All rights reserved.<\/p>\n

    DBVERIFY – Verification starting: FILE = \/mnt\/apps_st\/samusxxxxxxxx_data2\/system09.dbf
\n    Page 173191 is marked corrupt
\n    Corrupt block relative dba: 0x5802a487 (file 352, block 173191)
\n    Completely zero block found during dbv:<\/p>\n

    Page 173192 is marked corrupt
\n    Corrupt block relative dba: 0x5802a488 (file 352, block 173192)
\n    Completely zero block found during dbv:<\/p>\n

    Page 173193 is marked corrupt
\n    Corrupt block relative dba: 0x5802a489 (file 352, block 173193)
\n    Completely zero block found during dbv:<\/p>\n

    Page 173194 is marked corrupt
\n    Corrupt block relative dba: 0x5802a48a (file 352, block 173194)
\n    Completely zero block found during dbv:<\/p>\n

    Page 173195 is marked corrupt
\n    Corrupt block relative dba: 0x5802a48b (file 352, block 173195)
\n    Completely zero block found during dbv:<\/p>\n

    Page 173196 is marked corrupt
\n    Corrupt block relative dba: 0x5802a48c (file 352, block 173196)
\n    Completely zero block found during dbv:<\/p>\n

    Page 173197 is marked corrupt
\n    Corrupt block relative dba: 0x5802a48d (file 352, block 173197)
\n    Completely zero block found during dbv:<\/p>\n

    Page 173198 is marked corrupt
\n    Corrupt block relative dba: 0x5802a48e (file 352, block 173198)
\n    Completely zero block found during dbv:<\/p>\n

    Page 173199 is marked corrupt
\n    Corrupt block relative dba: 0x5802a48f (file 352, block 173199)
\n    Completely zero block found during dbv:<\/p>\n

    DBVERIFY – Verification complete<\/p>\n

    Total Pages Examined         : 917504
\n    Total Pages Processed (Data): 253735
\n    Total Pages Failing   (Data) : 0
\n    Total Pages Processed (Index): 335744
\n    Total Pages Failing   (Index): 0
\n    Total Pages Processed (Other): 375
\n    Total Pages Processed (Seg)  : 17
\n    Total Pages Failing   (Seg)  : 0
\n    Total Pages Empty            : 327624
\n    Total Pages Marked Corrupt   : 9
\n    Total Pages Influx           : 0
\n    Total Pages Encrypted        : 0
\n    Highest block SCN            : 3412538527 (1421.3412538527)<\/p>\n

    [Thu Nov 17 12:00:13 orbdev@samusxxxxxxxx:~ ] $<\/p>\n

 3. Find the free space<\/strong><\/p>\n

 Select * from dba_free_space where file_id= <Absolute file number> and <corrupted block number> between block_id and block_id + blocks -1;<\/em><\/p>\n

  SQL> Select * from dba_free_space where file_id=352 and 173191 between block_id and block_id + blocks -1;<\/em><\/p>\n

 TABLESPACE_NAME   FILE_ID   BLOCK_ID     BYTES    BLOCKS   RELATIVE_FNO
\n    ————— ———- ———- ———- ——— ————
\n    SYSTEM              352     173196      73728        9          352<\/p>\n

4. Reformat first corrupted block<\/strong><\/p>\n

Repeat step 4 until all corrupted blocks are reformatted.<\/p>\n

 create table scott.s (n number,c varchar2(4000)) nologging tablespace SYSTEM;<\/p>\n

    select owner,table_name,tablespace_name from dba_tables where table_name=’S’;<\/p>\n

    SQL> CREATE OR REPLACE TRIGGER corrupt_trigger
\n       AFTER INSERT ON scott.s
\n       REFERENCING OLD AS p_old NEW AS new_p
\n       FOR EACH ROW
\n       DECLARE
\n       corrupt EXCEPTION;
\n       BEGIN
\n       IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
\n         and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
\n         RAISE corrupt;
\n       END IF;
\n       EXCEPTION
\n       WHEN corrupt THEN
\n         RAISE_APPLICATION_ERROR(-20000, ‘Corrupt block has been formatted’);
\n       END;
\n     \/<\/em><\/p>\n

  Enter value for blocknumber: 173191
\n    old   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
\n    new   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=173191)<\/em><\/p>\n

    Enter value for filenumber: 352
\n    old   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
\n    new   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=352) THEN<\/em><\/p>\n

  Enter value for blocknumber: 173191
\n    old   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
\n    new   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=173191)<\/em><\/p>\n

    Enter value for filenumber: 352
\n    old   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
\n    new   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=352) THEN<\/em><\/p>\n

    Trigger created.<\/p>\n

  Select BYTES\/1024\/1024 from dba_free_space where file_id=352 and 173191 between block_id and block_id + blocks -1;<\/p>\n

    72K<\/p>\n

    SQL> BEGIN
\n       FOR i IN 1..100000 LOOP
\n          EXECUTE IMMEDIATE ‘alter table scott.s allocate extent (DATAFILE ‘||”’\/mnt\/apps_st\/samusxxxxxxxx_data2\/system09.dbf”’ ||’SIZE 72K)’;
\n       END LOOP;
\n    END;<\/em>
\n    \/<\/p>\n

    SQL> BEGIN
\n      FOR i IN 1..1000000 LOOP
\n         INSERT \/*+ APPEND *\/ INTO scott.s select i, lpad(‘REFORMAT’,3092, ‘R’) from dual;
\n      commit ;
\n      END LOOP;
\n    END;
\n    \/<\/em><\/p>\n

    SQL> Select * from v$database_block_corruption;<\/em><\/p>\n

    no rows selected<\/em><\/p>\n

    DROP TABLE scott.s;<\/em><\/p>\n

    Alter system switch logfile;<\/p>\n

    Alter system checkpoint;<\/p>\n

    DROP trigger corrupt_trigger;<\/em><\/p>\n

 5. Verify that the corrupted blocks are fixed<\/strong><\/p>\n

  SQL> select * from V$DATABASE_BLOCK_CORRUPTION;<\/em><\/p>\n

 no rows selected<\/em><\/p>\n

 [Wed Nov 16 08:12:37 orbdev@samusxxxxxx:~ ] $ dbv file=’\/mnt\/apps_st\/samusxxxxxx_data2\/system09.dbf’ userid=sys\/****<\/em><\/p>\n

    DBVERIFY: Release 11.2.0.4.0 – Production on Wed Nov 16 08:12:41 2016<\/em><\/p>\n

    Copyright (c) 1982, 2011, Oracle and\/or its affiliates. All rights reserved.<\/em><\/p>\n

    DBVERIFY – Verification starting : FILE = \/mnt\/apps_st\/samusxxxxxx_data2\/system09.dbf<\/em><\/p>\n

    DBVERIFY – Verification complete<\/em><\/p>\n

Total Pages Examined     : 655359
\n    Total Pages Processed (Data) : 260868
\n    Total Pages Failing  (Data) : 0
\n    Total Pages Processed (Index): 340482
\n    Total Pages Failing  (Index): 0
\n    Total Pages Processed (Other): 278
\n    Total Pages Processed (Seg): 15
\n    Total Pages Failing (Seg): 0
\n    Total Pages Empty     : 53716
\n    Total Pages Marked Corrupt : 0
\n    Total Pages Influx     : 0
\n    Total Pages Encrypted   : 0
\n    Highest block SCN     : 136830000 (1422.136830000)<\/em><\/p>\n

    [Wed Nov 16 08:14:50 orbldev2@samusxxxxxx:~ ] $<\/em><\/p>\n

 Conclusion<\/strong><\/p>\n

To detect and prevent data corruption, perform the following Maximum Availability Architecture (MAA) best practices:<\/p>\n