首页 » ORACLE » Corrupted free block & ORA-19566 when using rman backup after restore DB

Corrupted free block & ORA-19566 when using rman backup after restore DB

A few days ago, a friends asked me to help him restore the DB on windows platform , db version was 10.1.0.2,the datafiles all exists, However, the oracle software installed directory of C drive is formatted.

The basic recovery process:
re-install oracle soft same as orginal db (dropted);
create database and db name same as orignal db;
re-create control file and modify datafiles path to original datafile;
recover database unsing backup controlfile until cancel;
alter database open resetlogs;

open completed, but then to do backup database using rman, backup failure and found corrupted block in the db, The error output is following:

RMAN-00571: ====================================================== 
RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: ===================================================== 
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/10/2015 12:18:24
 ORA-19566: exceeded limit of 0 corrupt blocks for file  E:\ORACLE2\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

Check the file #

select file_id,tablespace_name from  dba_data_files where file_name='E:\ORACLE2\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF';

file_id     tablespace_name
-------			--------------
4						USERS

Validate the datafile ,If the backup is in disk using Following Command:

RMAN> RUN { 
allocate channel ch01 TYPE disk; 
BACKUP VALIDATE CHECK LOGICAL datafile 4; 
}

or
Validate the database

RMAN> run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
backup check logical validate database;
release channel ch1;
release channel ch2;
release channel ch3;
}

Now Check the view for block corruption

SQL> SELECT * FROM v$database_block_corruption;

   FILE#     BLOCK#     BLOCKS  CORRUPTION_CHANGE# CORRUPTIO 
--------     -------    ------  -----------------  ---------	
       4     376        265     0                  ALL ZERO

You can also use the following script to find object name of this corrupted block as well.

SELECT owner, segment_name, segment_type, partition_name, 
FROM dba_extents 
   WHERE file_id=4
   AND 376 BETWEEN block_id AND block_id+blocks-1;

no rows selected

TIP:
If you get no rows, that means you have a corrupted block reported that is not part of any segment.I to checked the DBA_EXTENTS, but there were no extents with the block 376 file 4.

SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2  , greatest(e.block_id, c.block#) corr_start_block#
  3  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5  - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6  , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1  10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13  , header_block corr_start_block#
 14  , header_block corr_end_block#
 15  , 1 blocks_corrupted
 16  , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22  , greatest(f.block_id, c.block#) corr_start_block#
 23  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 24  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 26  , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1  30  AND f.block_id + f.blocks - 1 >= c.block#
 31  ORDER BY file#, corr_start_block#;

OWNER    SEGMENT_TYPE SEGMENT_NAME    PARTITION_NAME      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
-------  ----------   ----------      ------------        ------  ---------        -------------  -------------     ---------
                                                            4     376              640            265              Free Block

Tip:
I got a block corruption on a free block, not associated with any objects extents.You have nong to an object. Thus if an object with corrupted blocks is dropped, those blocks remain FRACTURED until reused by a new object or allo way to recover this block as RMAN will not mind free extents, RMAN reads blocks on the Physical level, so it is not aware if they belocated to an existing segment. At that time, Oracle will reformat the block (renew it) and thus remove the fracture.

Solution 1:

SQL> select file_id,tablespace_name,bytes,AUTOEXTENSIBLE ,file_name from  dba_data_files where file_id=4;

   FILE_ID  TABLESPACE_NAME      BYTES     AUTOEX     FILE_NAME
----------  ------              ---------- -------    --------------------------------------------------
      4      USERS            5242880     YES          E:\ORACLE2\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
      
SQL> select file_name from  dba_data_files where  tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE2\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

Tip:
the tablespace repored corrupted block just have single datafile.

dbv file=E:\oracle2\product\10.1.0\oradata\orcl\USERS01.DBF
 

DBVERIFY: Release 10.1.0.2.0 - Production on 星期二 3月 10 17:23:27 2015

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

DBVERIFY - 开始验证: FILE = E:\oracle2\product\10.1.0\oradata\orcl\USERS01.DBF
页 376 标记为损坏
Corrupt block relative dba: 0x01000178 (file 4, block 376)
Completely zero block found during dbv: 

页 377 标记为损坏
Corrupt block relative dba: 0x01000179 (file 4, block 377)
Completely zero block found during dbv: 

页 378 标记为损坏
Corrupt block relative dba: 0x0100017a (file 4, block 378)
Completely zero block found during dbv: 
...

页 637 标记为损坏
Corrupt block relative dba: 0x0100027d (file 4, block 637)
Completely zero block found during dbv: 

页 638 标记为损坏
Corrupt block relative dba: 0x0100027e (file 4, block 638)
Completely zero block found during dbv: 

页 639 标记为损坏
Corrupt block relative dba: 0x0100027f (file 4, block 639)
Completely zero block found during dbv: 

页 640 标记为损坏
Corrupt block relative dba: 0x01000280 (file 4, block 640)
Completely zero block found during dbv: 


DBVERIFY - 验证完成

检查的页总数: 640
处理的页总数 (数据): 28
失败的页总数 (数据): 0
处理的页总数 (索引): 2
失败的页总数 (索引): 0
处理的页总数 (其它): 32
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 313
标记为损坏的总页数: 265
流入的页总数: 0

To create a table and try to reformat the “empty block” above corrupted manually

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

     BYTES
----------
   4784128

SQL> alter database datafile 'E:\ORACLE2\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF' size 6m autoextend off;

数据库已更改。

alter table anbob.fill 
     allocate extent (DATAFILE 'E:\ORACLE2\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF' SIZE 5M);  

create user anbob identifeid by anbob default tablespace users;

grant connect,resource to anbob;

create table anbob.fill (
       n number,
       c varchar2(4000)
     ) nologging tablespace users ;


CREATE OR REPLACE TRIGGER  anbob.fill_trigger
    AFTER INSERT ON anbob.fill 
    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)=640)
   and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=4) THEN
       RAISE corrupt;
  END IF;
  EXCEPTION
    WHEN corrupt THEN
       RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
  END;
  /
  
  
BEGIN
FOR i IN 1..100000000 LOOP
INSERT /*+ APPEND */ INTO anbob.fill select i, lpad('FORMATED',3092, 'X') from dual;
commit ;
END LOOP;
END; 
/
   
   第 1 行出现错误:
ORA-20000: Corrupt block has been formatted
ORA-06512: 在 "ANBOB.FILL_TRIGGER", line 10
ORA-04088: 触发器 'ANBOB.FILL_TRIGGER' 执行过程中出错
ORA-06512: 在 line 3

TIP:
ok, the marked corrupt block has been re-formatted. now we can to verify the datafile using “backup validate” with rman or “dbv” utility

RMAN> RUN { 
allocate channel ch01 TYPE disk; 
BACKUP VALIDATE CHECK LOGICAL datafile 4; 
}

SQL> select * from v$database_block_corruption;
no rows selected


DBVERIFY: Release 10.1.0.2.0 - Production on 星期二 3月 10 17:41:48 2015
Copyright (c) 1982, 2004, Oracle.  All rights reserved.

DBVERIFY - 开始验证: FILE = E:\oracle2\product\10.1.0\oradata\orcl\USERS01.DBF
DBVERIFY - 验证完成

检查的页总数: 1280
处理的页总数 (数据): 511
失败的页总数 (数据): 0
处理的页总数 (索引): 2
失败的页总数 (索引): 0
处理的页总数 (其它): 47
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 720
标记为损坏的总页数: 0
流入的页总数: 0

To drop temporary objects

drop user anbob cascade;

Solution 2:
Use maxcorrupt to the number that is reported in above query, i.e above i have been shown 265 block

RMAN> SET MAXCORRUPT FOR DATAFILE 4 to 265;

RMAN> BACKUP DATABASE;

Refrences:
“How to Format Corrupted Block Not Part of Any Segment” (Doc ID 336133.1)

打赏

对不起,这篇文章暂时关闭评论。