Oracle Block Corruption Fix

oracle-block-corruption

This post describes how to repair multiple or single-block corruption of data files, including system data files, in the Oracle database. Block corruption is a common source of database outages.

Database block becomes corrupted when its content is different from what the Oracle database expects to find. If block corruption is not prevented or repaired, the corruption can download the database and possibly cause loss of key business data. This is one of the real-time issues that you must encounter and correct in production databases.

RMAN (Recovery Manager) and RMAN’s block recovery feature are probably the biggest weapon of Oracle data against corruption. Let’s see how we can prevent corruption with RMAN.

We assume that we have stable and running RMAN backups, and we have detected (or otherwise) block corruption in the alert loops. Below are the known lines in our alert log.

Let’s try the following for Oracle Block Corruption.

alter system set db_block_checksum = typical;
alter system set db_block_checksum = full;

Notes: additional overhead will be occurred
typical – 1-2% increase
full – 4-5% increase

SQL> Select * from v $ database_block_corruption;

     FILE # BLOCK # BLOCKS CORRUPTION_CHANGE # CORRUPTIO


         4 1179053 1 0 FRACTURED
         7 1790484 1 0 FRACTURED
         7 1856051 10 0 CORRUPT
         7 1778635 1 0 FRACTURED
         7 1778658 10 0 CORRUPT
         8 133790 1 0 FRACTURED
         8 205454 1 0 FRACTURED
         8 300572 1 0 CORRUPT
         8 294536 1 0 CORRUPT
         8 279130 1 0 CORRUPT
         8 279099 1 0 CORRUPT

     FILE # BLOCK # BLOCKS CORRUPTION_CHANGE # CORRUPTIO


         8 279048 10 0 CORRUPT
         9 244092 1 0 CORRUPT
         9 215674 10 0 CORRUPT
         9 37499 1 0 CORRUPT
         9 37498 10 0 CORRUPT
         9 215291 10 0 CORRUPT

17 lines selected.

SELECT segment_type, segment_name
  FROM dba_extents
WHERE file_id = 9
   AND block_id <215291    AND block_id + blocks> = 215291;
   
   


SQL> SELECT segment_type, segment_name
  2 FROM dba_extents
  3 WHERE file_id = 4
  4 AND block_id <1179053   5 AND block_id + blocks> = 1179053;

segment_type

SEGMENT_NAME

TABLE
SYS__PRODUCTION_OLD


SQL> SELECT segment_type, segment_name
  2 FROM dba_extents
  3 WHERE file_id = 7
  4 AND block_id <1790484   5 AND block_id + blocks> = 1790484;

segment_type

SEGMENT_NAME

TABLE
SYS__ISLEMRECORD *

EXCLUDE = INDEX: ”=‘ IDX $$ _ 08360001 ’”
EXCLUDE = INDEX: ”=‘ IDX $$ _ 08360021 ’”
EXCLUDE = INDEX: ”=‘ IDX $$ _ 08362000 ’”
EXCLUDE = INDEX: ”=‘ IDX $$ _ 084360001 ’”
EXCLUDE = INDEX: ”=‘ BT_ISCI ’”
EXCLUDE = INDEX: ”=‘ SYS_RECORD ’”

  • According to file_id and block_id, the relevant tables on the CORRUPT or FRACTURED field can be exclude and the desired operations on the database can be performed without any problem.

ORA-02354: error in exporting / importing data
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924:

creates a table to insert rowid of corrupted records

create table corrupt_lobs (corrupt_rowid rowid);

————–

blob and clob fields

SET LINESIZE 80
SET DESCRIBE DEPTH 2
SET DESCRIBE INDENT ON
SET DESCRIBE LINE OFF
desc [OWNER.TABLOADI]

SQL> desc SCHEMA.TABLE
 Name Null? Type
 —————————————– ——– – —————————
 RECORD_KEY NOT NULL NUMBER
 PROTOCOL_NO VARCHAR2 (40)
 SERVISGECIS_NO NUMBER
 CURRENT_REC VARCHAR2 (1)
 SEND_REC VARCHAR2 (1)
 INSPECTION_BASLANGIC VARCHAR2 (20)
 INSPECTION_BITIS VARCHAR2 (20)
 ANATANI BLOB >>>>> Area to check
 EK_TANI BLOB >>>>> Area to check
 STORY VARCHAR2 (4000)
 COMPLAINT VARCHAR2 (4000)
 FINDING VARCHAR2 (4000)
 MUDEHALE BLOB >>>>> Area to check
 REPORT BLOB >>>>> Area to check
 VISIT_ID VARCHAR2 (10)
 HAYATI_BULGU BLOB >>>>> Area to check
 LONG RAW
 EXAM BLOB >>>>> Field to check
 OCCUPATIONAL BLOB >>>>> Area to check

The following pl / sql script is modified and executed for each field.
 

declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init (error_1578, -1578);
pragma exception_init (error_1555, -1555);
pragma exception_init (error_22922, -22922);
n number;
begin
for cursor_lob in (select rowid r, [DOMAIN] from [OWNER.TABLOADI]) loop
begin
n = dbms_lob.inst is (cursor_lob. [DOMAIN] hextoraw ( ‘889911’));
exception
when error_1578 then
insert into corrupt_lobs values ​​(cursor_lob.r);
commit;
when error_1555 then
insert into corrupt_lobs values ​​(cursor_lob.r);
commit;
when error_22922 then
insert into corrupt_lobs values ​​(cursor_lob.r);
commit;
end;
end loop;
end;
/

records are checked after each check,

select * from corrupt_lobs;

if there is record

update [OWNER.TABLOADI] set [DOMAIN] = empty_blob ()
where rowid in (select corrupt_rowid from corrupt_lobs);

The records are updated with empty.

Bad field table is emptied

truncate table corrupt_lobs;

Oracle Table Block Corruption

ORA-01578: ORACLE data block corrupted (file # 26, block # 1791491)
ORA-01110: data file 26: & apos; /u01/app/oracle/oradata/orcl/users11.dbf’

I connected with the SYSTEM user and ran the following commands.

I see if there is corruption in the system with the following sql. At least I can see where oracle has noticed.

SQL> select * from v $ database_block_corruption;

FILE # BLOCK # BLOCKS CORRUPTION_CHANGE # CORRUPTION_TYPE


     4 1179053 1 0 FRACTURED

With this sql, I find out which table (s) the corrupted field corresponds to.

SQL> select OWNER, segment_name, segment_type, tablespace_name, block_id from dba_extents where file_id = 4 and 1179053 between block_id and block_id + blocks-1;

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCK_ID


BUGRA SYS__LOGTABLE TABLE USERS 1179008

Then I connect through the server as sysdba and run the following command. This command is used to pass bad areas.

SQL> exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (‘BUGRA’, ‘SYS__LOGTABLE’);

Then, I connect with BUGRA user from PL / SQL and copy the existing table by creating a new table.

SQL> create table SYS__LOGTABLE_NEW as select * from SYS__LOGTABLE;

Table created

Then I get the create script of the old table and delete the indexes on the existing table as the table’s name will be corrupted.

I’m renaming the tables.

SQL> rename SYS__LOGTABLE to SYS__LOGTABLE_OLD;

SQL> rename SYS__LOGTABLE_NEW to SYS__LOGTABLE;

Then I run the other part of the create script without getting the table creation part.

And then I take the count and look at the difference.

SQL> select count (*) from SYS__LOGTABLE;

COUNT (*)

 48333

SQL> select count (*) from SYS__LOGTABLE_OLD;

COUNT (*)
48 337

recover datafile 26 block 1791491;

the script is run again for each field.

Have a nice day ….

You can also browse the other article.

Oracle Source

Comments