ORA-01578ORA-01110 bad block solution

In one case, the following error message is found in the trace file.

D: \ oracle \ product \ 10.2.0 \ admin \ dbserver \ udump \ orcl_ora_5888.trc

Corrupt block relative dba: 0x09848269 (file 38, block 295529)

Bad header found during buffer read

Data in bad block:

Type: 1 format: 5 rdba: 0x30322d2d

Last change scn: 0xfffe. fffefffe seq: 0xfe flg: 0xff

Spare1: 0x0 spare2: 0x30 spare3: 0x0

Consistency value in tail: 0x2d2d3533

Check value in block header: 0 xfffe

Computed block checksum: 0x91f9

Reread of rdba: 0x09848269 (file 38, block 295529) found same specified upted data

Fri Dec 27 08:28:08 2013

Corrupt Block Found

TSN = 36, TSNAME = HGHIS

RFN = 38, BLK = 295529, RDBA = 159679081

OBJN = 119544, OBJD = 119544, OBJECT = EMR_MONITOR_RESULT, SUBOBJECT =

Segment owner = HGHIS, segment type = Table Segment

Fri Dec 27 08:54:18 2013

Hex dump of (file 38, block 295561) in trace file d: \ oracle \ product \ 10.2.0 \ admin \ dbserver \ udump \ orcl_ora_6796.trc

Corrupt block relative dba: 0x09848289 (file 38, block 295561)

Bad header found during buffer read

Data in bad block:

Type: 48 format: 0 rdba: 0x020cc100

Last change scn: 0xb500. 080cc100 seq: 0xa5 flg: 0xba

Spare1: 0x34 spare2: 0x2 spare3: 0xb0b8

Consistency value in tail: 0x395e3031

Check value in block header: 0 xcfcb

Block checksum disabled

Reread of rdba: 0x09848289 (file 38, block 295561) found same specified upted data

Fri Dec 27 08:54:19 2013

Corrupt Block Found

TSN = 36, TSNAME = HGHIS

RFN = 38, BLK = 295561, RDBA = 159679113

OBJN = 119494, OBJD = 119494, OBJECT = sys_lob1_119493c00006 $, SUBOBJECT =

Segment owner = HGHIS, segment type = Lob Segment

Fri Dec 27 09:11:20 2013

Hex dump of (file 38, block 295563) in trace file d: \ oracle \ product \ 10.2.0 \ admin \ dbserver \ udump \ orcl_ora_5584.trc

Unzip upt block relative dba: 0x0984828b (file 38, block 295563)

Bad header found during buffer read

Data in bad block:

Type: 71 format: 7 rdba: 0x064d0001

Last change scn: 0x3038.43584400 seq: 0x31 flg: 0x07

Spare1: 0x44 spare2: 0x4c spare3: 0x771

Consistency value in tail: 0x000a0000

Check value in block header: 0x7800

Computed block checksum: 0xcbf8

Reread of rdba: 0x0984828b (file 38, block 295563) found same specified upted data

Fri Dec 27 09:11:21 2013

Corrupt Block Found

TSN = 36, TSNAME = HGHIS

RFN = 38, BLK = 295563, RDBA = 159679115

OBJN = 119494, OBJD = 119494, OBJECT = sys_lob1_119493c00006 $, SUBOBJECT =

Segment owner = HGHIS, segment type = Lob Segment

Fri Dec 27 09:27:59 2013

According to the above information, we know that 295529, 295561, and 295563 of the No. 38 data file are bad blocks. You can use the DBV tool or RMAN to check the bad block information.

Dbv file = "d: \ oradata \ DATA. DBF" blocksize = 8192

Or

Rman target/

Backup validate check logical database;

Select * from V $ DATABASE_BLOCK_CORRUPTION;

You can check whether the damaged object is a table or a LOB segment Based on the file number and block number.

Select tablespace_name, segment_type, owner, segment_name from dba_extents where file_id = 38 and 295529 between block_id AND block_id + blocks-1;

38 is the file number and 295529 is the block number.

If it is an object, you can recreate it.

Alter index indexname rebuild

If it is a table, you can use the 10231 event to ignore bad blocks, and then use the CTAS method to recreate the last rename table of the table. Do not forget to rebuild index.

Alter session set events '2017 trace name context forever, level 10 ';

Create table tab_new as select * from tab;

Rename tab to tab_bak;

Rename tab_new to new;

Alter index indexname rebuild;
Alter session set events '10231 trace name context off ';

If the damaged segment is LOB, find the segment information first.

Select owner, segment_name, segment_type from dba_extents where file_id = 38 and 295563 between block_id and block_id + blocks-1;

Output:

Owner = HGHIS
Segment_name = sys_lob2017119493c00006 $
Segment_type = LOBSEGMENT

Find the table and LOB Fields

Select table_name, column_name from dba_lobs where segment_name = 'sys _ LOB0000119493C00006 $ 'and owner = 'hghis ';

Output:

Table_name = EMR_CASE
Column_name = WORD

Find the bad rowid of the bad block and use the following plsql script

Create table bad_rows (row_id ROWID, oracle_error_code number );

Set concat off
Set serveroutput on

Declare
N number;
Error_code number;
Bad_rows number: = 0;
Ora1578 EXCEPTION;
PRAGMA EXCEPTION_INIT (ora1578,-1578 );
Begin
For cursor_lob in (select rowid rid, & lob_column from & table_owner. & table_with_lob) loop
Begin
N: = dbms_lob.instr (cursor_lob. & lob_column, hextoraw ('20140901 '));
Exception
When ora1578 then
Bad_rows: = bad_rows + 1;
Insert into bad_rows values (cursor_lob.rid, 1578 );
Commit;
When others then
Error_code: = SQLCODE;
Bad_rows: = bad_rows + 1;
Insert into bad_rows values (cursor_lob.rid, error_code );
Commit;
End;
End loop;
Dbms_output.put_line ('total Rows identified with errors in LOB column: '| bad_rows );
End;
/

Enter value for lob_column: WORD
Enter value for table_owner: HGHIS
Enter value for table_with_lob: EMR_CASE
Bad rowid can be queried
Select * from bad_rows;

Update the empty LOB field to avoid ORA-1578, ORA-26040, if it is CLOB type, change empty_blob () to empty_clob ()

Set concat off
Update & table_owner. & table_with_lob set & lob_column = empty_blob () where rowid in (select row_id from bad_rows );

Move the bad rowid lob block to another tablespace

Alter table & table_owner. & table_with_lob move LOB (& lob_column) store as (tablespace & tablespace_name );

Do not forget rebuild index