| corrupt blocks issue [message #537490] |
Wed, 28 December 2011 23:28  |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
Dear all,
If my db have never backup before,and now there are some corrupt blocks,i want to backup my db,it raise error,how can i do ?
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c2 channel at 12/29/2011 03:17:08
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/oracl/hxl01.dbf
|
|
|
|
|
|
|
|
|
|
| Re: corrupt blocks issue [message #537506 is a reply to message #537500] |
Thu, 29 December 2011 01:32  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You need to identify which objects contain the corrupt blocks and mark them as corrupted: this will make the objects usable, though possibly incomplete. If you are lucky, the corrupt blocks are in unused extents (in which case no problem) or in indexes (which can be rebuilt). If they are table segments, you may be able partially to recover the rows if there indexes that contain some of the columns.
Read up on the dbms_repair package.
|
|
|
|
|
|
| Re: corrupt blocks issue [message #537513 is a reply to message #537508] |
Thu, 29 December 2011 01:59 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
Thanks,
The dbv show the corrupt blocks just as flowing,how can i fix it?
[oracle@hxl ~]$ dbv file=/u01/app/oracle/oradata/oracl/hxl01.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Dec 29 05:16:55 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/oracl/hxl01.dbf
Page 12 is marked corrupt
Corrupt block relative dba: 0x0140000c (file 5, block 12)Bad header found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x23928978
last change scn: 0x0000.00105544 seq: 0x1 flg: 0x04
spare1: 0x55 spare2: 0x66 spare3: 0x0
consistency value in tail: 0x28ee0601
check value in block header: 0x51ef
computed block checksum: 0xb059
Page 192 is marked corrupt
Corrupt block relative dba: 0x014000c0 (file 5, block 192)Bad header found during dbv:
Data in bad block:
type: 68 format: 5 rdba: 0x34120999
last change scn: 0x7272.72056055 seq: 0x72 flg: 0x72
spare1: 0x67 spare2: 0x78 spare3: 0x7272
consistency value in tail: 0x00000001
check value in block header: 0x7272
block checksum disabled
Page 2048 is influx - most likely media corrupt
Corrupt block relative dba: 0x01400800 (file 5, block 2048)Fractured block found during dbv:
Data in bad block:
type: 0 format: 2 rdba: 0x01400800
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x34020201
check value in block header: 0xae40
computed block checksum: 0x4fe6
DBVERIFY - Verification complete
Total Pages Examined : 2048
Total Pages Processed (Data) : 4
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2030
Total Pages Marked Corrupt : 3
Total Pages Influx : 1
Highest block SCN : 1062336 (0.1062336)
|
|
|
|
| Re: corrupt blocks issue [message #537514 is a reply to message #537513] |
Thu, 29 December 2011 02:06 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
For each error execute:
Select 'Block &2 of file '||f.file_name||' :
'||decode(e.owner, NULL, e.segment_name,
e.owner||'.'||e.segment_name)||
decode(e.partition_name, NULL, '', '.'||e.partition_name)||
' ('||e.segment_type||')' obj
from dba_extents e, dba_data_files f
where f.file_id = &1
and e.file_id = &1
and &2 between e.block_id and e.block_id+e.blocks-1
/
Where &1 is the number of the file (5) and &2 the number of the block (12, 192, 2048).
Regards
Michel
|
|
|
|
| Re: corrupt blocks issue [message #537517 is a reply to message #537514] |
Thu, 29 December 2011 02:41 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
Thanks,
Michel, just only the block 12 have contains object,how can i do?
Select 'Block 12 of file '||f.file_name||' :
'||decode(e.owner, NULL, e.segment_name,
e.owner||'.'||e.segment_name)||
decode(e.partition_name, NULL, '', '.'||e.partition_name)||
' ('||e.segment_type||')' obj
from dba_extents e, dba_data_files f
where f.file_id = 5
and e.file_id = 5
and 12 between e.block_id and e.block_id+e.blocks-1
/
OBJ
----
Block 12 of file /u01/app/oracle/oradata/oracl/hxl01.dbf :
HXL.TB_TEST (TABLE)
Select 'Block 192 of file '||f.file_name||' :
'||decode(e.owner, NULL, e.segment_name,
e.owner||'.'||e.segment_name)||
decode(e.partition_name, NULL, '', '.'||e.partition_name)||
' ('||e.segment_type||')' obj
from dba_extents e, dba_data_files f
where f.file_id = 5
and e.file_id = 5
and 192 between e.block_id and e.block_id+e.blocks-1
/
no rows selected
Select 'Block 2048 of file '||f.file_name||' :
'||decode(e.owner, NULL, e.segment_name,
e.owner||'.'||e.segment_name)||
decode(e.partition_name, NULL, '', '.'||e.partition_name)||
' ('||e.segment_type||')' obj
from dba_extents e, dba_data_files f
where f.file_id = 5
and e.file_id = 5
and 2048 between e.block_id and e.block_id+e.blocks-1
/
no rows selected
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: corrupt blocks issue [message #537675 is a reply to message #537565] |
Fri, 30 December 2011 03:25 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
hi,
Michel,i have use dbms_repair to mark block 12, 192, 2048 as corrupted, select the table tb_test have no any problem,but I can not backup the datafile,how can i remove the corrupted block and make rman backup success ?
SQL> select count(1) from hxl.tb_test;
COUNT(1)
----------
1568
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c2 channel at 12/30/2011 04:17:02
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/app/oracle/oradata/oracl/hxl01.dbf
|
|
|
|
|
|
|
|
|
|
| Re: corrupt blocks issue [message #537716 is a reply to message #537713] |
Fri, 30 December 2011 07:17 |
|
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
We already explain it many times, take some time and and search for it, this is not the purpose of this topic.
A side question, why do you use count(1) and not COUNT(*)?
Regards
Michel
[Updated on: Fri, 30 December 2011 07:17] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: corrupt blocks issue [message #537774 is a reply to message #537753] |
Sat, 31 December 2011 00:56 |
|
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
andy huang wrote on Sat, 31 December 2011 04:02hi,
Is there some way to remove the corrupt blocks? I drop the table tb_test,the corrupt blocks are remain also.
It does not matter, the block will be reformatted (and so fixed) when it will be reallocated to an object.
If you really want to remove the corruption then create a table and allocate extends untill they reach all corruptions (note there is no way to force to allocate a specific block).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
