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.
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#;
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)
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。