Solved

How to repair of ignore corrupted blocks in oracle in noarchivelog mood

Posted on 2015-02-22
11
513 Views
Last Modified: 2015-04-02
Hi all,

I have a table with partitioned by date .... today when I run query for entire month I get following error...

SQL Error [1578] [72000]: ORA-01578: ORACLE data block corrupted (file # 10, block # 19007437)
ORA-01110: data file 10: '\UDR''

Open in new window



unfortunately all this long the database was in no archive log mood as now I know... so further investigating I found out that this block is in partition 9 of the table which holds 9th of Feb data...

so how can I recover from this issue.... I try to to validate the blocks form rman and then try to recover it... but
I get message that there is no back up for that...

any help... to either ignore it while running query or if I drop the data of 9th feb and reload them will that fix the issue?

thanks for help...
0
Comment
Question by:hi4ppl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 40624144
There is the DBMS_REPAIR package.  However, I highly recommend only using that under the direction of Oracle Support.
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40624151
Hi,

thanks for the help but I dont have oracle support where I work they don't have any support from metalink... and I checked it further and I see this shows in partition only ... if I delete the data of partition table and reload that will that fix this issue?

select * from v$database_block_corruption

file | block# | blocks | corruption_change# | corruption_type |cond_id
10 | 1 | 19007437| 0 | fractured | 0

Open in new window



when I select the block from dba_extents then I see

segment_name | segment_type | block_id
tablename | partitionname | 19007437
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40624169
Based on the reason for the corruption, I would say that reloading it should get around the issue.  A fractured block should be a soft error, and not a hardware problem.  If it is a disk block issue, it could come back when the block is reused.

A fractured block is curious if  you are in noarchive mode.  That would seem like someone attempted a restore at some point.  I would definitely recommend running dbverify to see if there are other issues.  The documentation for that is here -> http://docs.oracle.com/cd/B28359_01/server.111/b28319/dbverify.htm#g1010035

I also highly recommend having a supported system.  Running any kind of production system that is unsupported is just a recipe for disaster.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:hi4ppl
ID: 40624239
Hi, thanks for the replay... no I was not the in archive log mood and I found out when this happen but immediately I turn the on the archive log mood..

what happen was without no reason the database listener was not responding so what I did I restarted the  the services of listner and database from services.msc and immediately after that I didnt' check what was the issue but when I come back in the morning I run query it gives me this error....

and I run

RMAN> validate check logical database;

after 2 hours time it showed only one data files corrupted rest it didn't' show any corruption ...

if I drop and reload the 09th data only which is in the 09 partition will that solve this issue what could be the percentage ... as I think I will spend more time in troubleshooting than reloading which takes only 1 hours + time

regards
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40624260
An instance crash like that will not cause a fractured block.  On the way back up, an in doubt transactions would be rolled back.  It is part of instance recovery that happens automatically without you doing anything.

Restores of fuzzy backups and complete failures of disk array cache are usually the causes of fractured blocks.  Or you could have hit a bug with your disk array, controller or the database itself.

Reloading should correct the situation, but I still highly recommend running as many verifications as you can.  You don't want to find another problem later.
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40624349
Hi,

I run dbverfiy on the same datafile that gives damaged block and I get following result which I guess means only one block have issue and I think it's save to delete data of that day which reside in the partition?


Total Pages Examined         : 78643200
Total Pages Processed (Data) : 47679009
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 220667
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 30743524
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 83024023 (0.83024023)

Open in new window


reagards
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40624361
Yes, I would reload that data as I said.  But, you really should run verifications on all the files.
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40624374
Hi, thanks for replay... I did check all but every way I check like from RMAN, from v$ tables and also from this disk verify utility it all shows this.. but

- should I drop the partition
alter table tablename drop partition paritionname

- or just truncate the data

which one you think would be good idea?
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 40624382
In theory, truncate the partition should work.  It appears to be a soft error, so once the block is rewritten, it should correct itself.
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40625139
Hi,

thanks I have truncate the parition, reload the data analyze the table and all is going well so far... hope everything will go well now...

I have run the database in archivelog mood since this happen any other suggestion to avoid these issue latter?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40625429
Archivelog mode would not have prevented this from happening.  It may have sped up the recovery, but it would not have prevented it.  Archivelog mode may not have helped the recovery either.  Depends on when the corruption happened and how many backups you have.  Archivelog mode may not have been helpful at all.

Having no idea how it happened, it is difficult to prevent from happening again.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question