ANBOB™

alert log
###########
Tue Mar 11 22:00:20 2014
Hex dump of (file 47, block 49) in trace file /u01/admin/jzzh/bdump/jzzh1_j001_557202.trc
Corrupt block relative dba: 0x0bc00031 (file 47, block 49)
Bad header found during buffer read
Data in bad block:
type: 50 format: 0 rdba: 0x204d4152
last change scn: 0x2030.20303720 seq: 0x36 flg: 0x3a
spare1: 0x31 spare2: 0x34 spare3: 0x3a30
consistency value in tail: 0x96ea0601
check value in block header: 0x3232
block checksum disabled
Reread of rdba: 0x0bc00031 (file 47, block 49) found same corrupted data
Tue Mar 11 22:00:20 2014
GATHER_STATS_JOB encountered errors. Check the trace file.
Tue Mar 11 22:00:20 2014
Errors in file /u01/admin/xxx/bdump/xxxx_j001_557202.trc:
ORA-01578: ORACLE data block corrupted (file # 47, block # 49)
ORA-01110: data file 47: ‘/dev/rd…’
Tue Mar 11 22:00:38 2014
Corrupt Block Found
TSN = 22, TSNAME = xxx
RFN = 47, BLK = 49, RDBA = 197132337
OBJN = 122157, OBJD = 159649, OBJECT = INDEX_xxxx, SUBOBJECT =SEGMENT
OWNER = xxxx, SEGMENT TYPE = Index Segment
#############

tip:
In the case ,the corrupted block was a index segment , so we can use drop index ,create index to recreate index segment to solution ora-1578.
but backup database using RMAN would be backup corrupted blocks, because all formant data blocks will be backup ，And does not care these data blocks is free no not . Rman backup fails with ORA-19566 error and the block reported corrupt does not belong to any object.

I searched in MOS, The following is workaround

Applies to:
Oracle Database – Enterprise Edition – Version 8.1.7.4 to 11.2.0.1.0 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.

Solution

DISCLAIMER :-The steps given in this note are not always guaranteed to work

A possible way to fix the problem is provided below. Note that it is not guaranteed to work, but it has been known to resolve the problem in several cases.Also if there are many blocks reported corrupt in a
particular datafile pass the highest block number reported corrupt for that datafile ,when prompted for blocknumber in Step 6

When an object is recreated the blocks allocated(even corrupted block) to it are returned to free space. There they await reallocation to an object requiring additional space.
Once they are reallocated to a new extent for an object and only when any DML operation makes use of these block(Even Corrupted blocks which were in free space and now allocated) they would be reformatted just before the DML operation makes modifcation/use of those blocks.(Please note just allocation of extent doesnot format the block)

(Step 7 allocates the extent and step 8 performs DML operation to make use of allocated block in step 7 so that corrupt block gets reused and formatted).

In this note we try to reformat the block Manually.

Step 1 – Identify corrupt datafile

Step 2 Run DBV/Rman validate on affected datafile and check for corrupt block

dbv FILE =/dev/rdb_5g_46

or

Rman> backup validate check logical datafile  ;

Once done query

SQL>Select * from v$database_block_corruption ;

Step 3 – Check whether block is part of any object

in my case.

SELECT tablespace_name, segment_type, owner, segment_name
  FROM dba_extents
  WHERE file_id = 47
  and 49 between block_id AND block_id + blocks - 1;

If it doesn’t belong to an object, double check if it does exists in dba_free_space
to check if the block belongs to file space usage bitmap.

SQL> Select * from dba_free_space where file_id= 
     and  between block_id and block_id + blocks -1;

Step 4 – Better approach to find the blocks affected and verify if it belongs to any segment is to use RMAN, this is MUCH better and fast.

If you have already ran rman validate in step 2 go to directly sqlplus script given below to identify the object

$ rman target / nocatalog
or
$ rman target sys/ nocatalog
 

run {
allocate channel d1 type disk;
allocate channel d2 type disk;
.....................................................................
... multiple channels may be allocated for parallelizing purposes ...
... depends: RMAN - Min ( MAXOPENFILES , FILESPERSET ) ... ... ...
... Defaults: MAXOPENFILES =8, FILESPERSET =64 ... ... ...
.....................................................................
allocate channel dn type disk;
backup check logical validate database;
release channel d1;
release channel d2;
......................................
... release all channels allocated ...
......................................
release channel dn;
}
 

Important note :-
If database in NOARCHIVELOG mode, then above RMAN command must be run in mount stage after database has been shutdown clean.
Else it would error out with

RMAN-03009: failure of backup command on d1 channel at 12/07/2009 18:55:25
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

This restriction is lifted in 11g.

Run the following sql query to find if the block is in free space or occupied

set lines 200 pages 10000
col segment_name format a30

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
ORDER BY file#, corr_start_block#;

Step 5 – Create a dummy table as user other than SYS and SYSTEM

SQL> create table s (
       n number,
       c varchar2(4000)
     ) nologging tablespace  pctfree 99; 
 

Please note in 11gr2 due to deferred segment creation concept query from above user_segments may not report any rows in such cases query user_tables

SQL>Select table_name,tablespace_name from user_tables where table_name='S' ;

Step 6 – Create Trigger On dummy table which throws exception once the corrupted block is reused

Connect as sys and create the following trigger:

Please note when prompted for file number enter the relative file no(rfile# value from v$datafile)

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

Step 7- Allocate space to the table from the affected datafile .

If this is an ASSM tablespace, you may need to repeat this step a few times. That is, create multiple tables and allocate multiple extents.
And periodically look at dba_extents to ensure that the free space is now
allocated to a dummy table.

This is because ASSM will automatically determine the size of the next extent

First find the extent size by querying dba_free_space

SQL> Select BYTES from dba_free_space where file_id= and  between block_id and block_id + blocks -1;

BYTES
---------------- ---------- ---------- ---------- ---------- ------------
 65536

If its 64 K use

For example to allocate space from E:\xxxx\test.ORA:

SQL> alter table scott.s
allocate extent (DATAFILE 'E:\xxxx\test.ORA' SIZE 64K);

Keep allocating till the corrupted block is part of scott.s – check this with the following query:

SQL> select segment_name, segment_type, owner
       from dba_extents 
      where file_id = 
        and  between block_id 
            and block_id + blocks -1 ;

Note: It is advisable to ensure that AUTOEXTEND is OFF for the datafile, to prevent it from growing

Step 8 – Insert data into dummy table To format the block

Sample code (depending on the size of the tablespace it may vary):

BEGIN 
FOR i IN 1..1000000000 LOOP 
INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from dual; 
commit ; 
END LOOP; 
END;

Step 9 – Verify for any corruption in datafile by running DBV and Rman backup

Before running the actual backup you can re-run Rman validate command on the datafile and check v$database_block_corruption doesnot show the block formatted as corrupted.

For Db version <=10gr2

 Rman> Backup validate check logical datafile , ;

For Db version >= 11gr1

 Rman> Backup validate check logical datafile  ;

Or

 Rman> validate datafile  block ,   ;
 

Once done

 SQL>Select * from v$database_block_corruption ;

Step 10 – Drop the dummy table created in step 4

SQL> DROP TABLE scott.s ;

Step 11:- Do a Manual logswitch and checkpoint
Do couple of logswitch and checkpoint so that The block formatted in-memory are written into disk and dbverify no longer reports errors

SQL>Alter system switch logfile ;  --> Do this couple of time
SQL>Alter system checkpoint ;
 

Step 12:- Drop trigger created in step 6

SQL> DROP triggercorrupt_trigger ;

references How to Format Corrupted Block Not Part of Any Segment ID 336133.1

打赏

微信扫一扫，打赏作者吧～