Home » RDBMS Server » Backup & Recovery » corrupt blocks issue (11.2.0.1 windows xp)
corrupt blocks issue [message #537490] Wed, 28 December 2011 23:28 Go to next message
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 #537491 is a reply to message #537490] Wed, 28 December 2011 23:29 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

use SET MAXCORRUPT FOR DATAFILE command in RMAN
Check the following link:
http://arjudba.blogspot.com/2008/05/ora-19566-exceeded-limit-of-0-corrupt.html
Re: corrupt blocks issue [message #537500 is a reply to message #537491] Thu, 29 December 2011 00:59 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks Kamran,
How can i fix the corrupt blocks before backup?
Re: corrupt blocks issue [message #537501 is a reply to message #537500] Thu, 29 December 2011 01:03 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

For this you need to have RMAN backups from where you can perform block media recovery
Re: corrupt blocks issue [message #537506 is a reply to message #537500] Thu, 29 December 2011 01:32 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #537508 is a reply to message #537500] Thu, 29 December 2011 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
andy huang wrote on Thu, 29 December 2011 07:59
Thanks Kamran,
How can i fix the corrupt blocks before backup?


First run DBVERIFY (dbv) utility.

Regards
Michel

Re: corrupt blocks issue [message #537513 is a reply to message #537508] Thu, 29 December 2011 01:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #537526 is a reply to message #537517] Thu, 29 December 2011 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Cases 192 and 2048 are not real ones as they belong to no objects, you can ignore them (or use dbms_repair to mark them as corrupted).

Case 12 is harder.
As it seems to be a test table, just drop it. Wink
Otherwise, you have to create a table with all the rows that are not in the block, maybe retrieve the information in the block from those that are in the indexes (if any) or just leave them if they are not important.

Regards
Michel
Re: corrupt blocks issue [message #537527 is a reply to message #537526] Thu, 29 December 2011 03:10 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks,
Michel,How can i use dbms_repair to repair the block 12 ?
Re: corrupt blocks issue [message #537528 is a reply to message #537527] Thu, 29 December 2011 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't repair.
dbms_repair does not repair it just flag the block as corrupted in a way Oracle recognizes you know it is corrupted and want ignore it.

Regards
Michel
Re: corrupt blocks issue [message #537534 is a reply to message #537528] Thu, 29 December 2011 03:40 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks,
Is there a way to get the data from corrupt block 12 ?
Re: corrupt blocks issue [message #537536 is a reply to message #537534] Thu, 29 December 2011 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes and no.
If you have some indexes then you can find part of the data.
If you know Oracle blocks then you can dump it and retrieve some data.
But maybe the data were screw up by an IO error or an Oracle bug, so can you rely on it and what's inside the block?
You can also mine the logs and try to retrieve the operations that were made on the block.

Regards
Michel
Re: corrupt blocks issue [message #537542 is a reply to message #537536] Thu, 29 December 2011 04:06 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks,
Michel,just as my example,how can i get the data from corrupt block 12?the table tb_test have no any index.
Re: corrupt blocks issue [message #537548 is a reply to message #537542] Thu, 29 December 2011 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Following the ways I said.

Regards
Michel

[Updated on: Thu, 29 December 2011 04:35]

Report message to a moderator

Re: corrupt blocks issue [message #537565 is a reply to message #537548] Thu, 29 December 2011 06:02 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Re: corrupt blocks issue [message #537675 is a reply to message #537565] Fri, 30 December 2011 03:25 Go to previous messageGo to next message
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 #537676 is a reply to message #537675] Fri, 30 December 2011 03:31 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
As Kamran said,
Quote:
use SET MAXCORRUPT FOR DATAFILE command in RMAN
Re: corrupt blocks issue [message #537679 is a reply to message #537675] Fri, 30 December 2011 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you know there are 3 corruptions and admit them then you have to set the parameter as John said.

In addition, do NOT use count(1) use COUNT(*).

Regards
Michel
Re: corrupt blocks issue [message #537713 is a reply to message #537679] Fri, 30 December 2011 07:09 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks all,
Can you explain the difference between count(1) and COUNT(*)? i think there are the same.

Re: corrupt blocks issue [message #537716 is a reply to message #537713] Fri, 30 December 2011 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 #537749 is a reply to message #537716] Fri, 30 December 2011 20:24 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
Michel,some experts say that the count(1) is more quickly than count(x),do you agree?
Re: corrupt blocks issue [message #537750 is a reply to message #537749] Fri, 30 December 2011 20:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Michel,some experts say that the count(1) is more quickly than count(x),do you agree?
Some experts said the Earth was flat.
post reproducible test case that proves your point.
Re: corrupt blocks issue [message #537751 is a reply to message #537750] Fri, 30 December 2011 20:51 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks all,
But i forget who said.
Re: corrupt blocks issue [message #537752 is a reply to message #537751] Fri, 30 December 2011 20:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But i forget who said.
Then forget what you think you heard.
Re: corrupt blocks issue [message #537753 is a reply to message #537751] Fri, 30 December 2011 21:02 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
Is there some way to remove the corrupt blocks? I drop the table tb_test,the corrupt blocks are remain also.
Re: corrupt blocks issue [message #537754 is a reply to message #537753] Fri, 30 December 2011 21:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there some way to remove the corrupt blocks?
remove from where to where?
>I drop the table tb_test,the corrupt blocks are remain also.
What problem still exists because of the "corrupt block"?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: corrupt blocks issue [message #537761 is a reply to message #537754] Fri, 30 December 2011 23:37 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks,
Is It the only way to drop the datafile which contain corrupt blocks?
Re: corrupt blocks issue [message #537762 is a reply to message #537761] Fri, 30 December 2011 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: corrupt blocks issue [message #537774 is a reply to message #537753] Sat, 31 December 2011 00:56 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
andy huang wrote on Sat, 31 December 2011 04:02
hi,
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

Previous Topic: Parttioned table back
Next Topic: Recovery Catalog error
Goto Forum:
  


Current Time: Fri Mar 29 03:49:00 CDT 2024