Block corruption in oracle table

How to fix block corruption in oracle Table of SAP system
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sorry I did not see this earlier, it was back in my email queue.

Is it an error due to a media corruption? Is it a single block? View the trace file to see.

I would start with dbverify to determine the scope of the corruption. Rarely do I see a single block, it is usually wider scope and or due to disk problem.

Run: dbv file=/ora/oradata/ORA1/file.dbf

If the block is in a table and has data you want to recover, you will need a backup as well as archive logs to recover from. You must go to backups to fully recover.

Have you a configured backup procedure? Using RMAN? etc.?

I would personally choose to restore and recover the whole datafile, but if it is large, you can isolate and do block level. Provide more info on your backups and I will help.
Don_RafaelAuthor Commented:
Thanks for the Reply,

below is the error

BR0301E SQL error -1578 at location stats_tab_collect-20, SQL statement:
ORA-01578: ORACLE data block corrupted (file # 6, block # 225333)
ORA-01110: data file 6: '/oracle/BD5/sapdata5/biw_5/biw.data5'
ORA-06512: at "SYS.DBMS_STATS", line 13159
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at line 1
BR0886E Checking/collecting statistics failed for table SAPBIW./BIC/B0001223000

unfortunately we do not have good backups to restore.

I would like to know the procedure to fix the block corruptions if we do not have the backup..
like what options we have? also i head we can mark the corrupted block  so the table scan will not fail.
coz if do select * on the table ... it comes to point and errors out.

Also appreciate if you can point some instructions, doco to fix these errors if the corruption is in table.

1) Backup your database right now! Before we start doing any sort of repairs. Do you know how to do this? I advise using RMAN.
2) Are you running in archivelog mode?

Below is how to run RMAN from the Oracle user, do a backup, then also validate the database. RMAN will then mark blocks that are corrupt into a corruption list that you can work with. You may have a tiny problem or a very large problem.

-- First do the backup. It MAY fail if you have media problems, we will see.
rman target /
RMAN> configure controlfile autobackup on;
RMAN> backup database;

-- If it fails

-- And then this step doesn't really create a backup, but validates blocks and populates the V$DATABASE_BLOCK_CORRUPTION view
RMAN> backup validate database;

-- Then login via sqlplus and check how many blocks are bad

sqlplus / as sysdba

3) You have no options to _recover_ blocks, since you have no backups. The only option is to mark blocks or rebuild the table while skipping them. You can play around with some SQL and try to select subsets of data from the table to see if certain ranges succeed. If, for example, the block corruption is in a given primary key range, if you query using a divide and conquer approach, you can narrow down where the problem is.

select * from table where id < 1000;
-- block error
select * from table where id < 500;
-- succeeds
select * from table where id >= 500 and id < 750;
-- etc. until you narrow down

You can also use DBMS_REPAIR to do all of the analysis, and mark the bad blocks.

First, see how bad the problem is, then let me know.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Don_RafaelAuthor Commented:
Thanks for the update.

- I have backed it up using BRBACKUP successfully ( no media issues :) )
- also archive logs are turned on.
currently we are evaluating the possibility of rebuilding the table or use one of options as per ur update.

can you please also point me to or suggest steps to fix block corruptions in tables if i had successful backups.

thanks in advance.
If you have archive logs, you can use Oracle LogMiner to recover all of the DML statements for all of the log history you have. So it depends on how far back you can / need to go.

If you had a successful RMAN backup, you could follow the steps above I listed

1) If you know the block # that is corrupt (assuming it is a single block)
RMAN> recover datafile 7 block 123444;

2) If there are many blocks, use:
RMAN> backup validate database;
RMAN> recover corruption list;

In some cases you might have to specify an until clause because even the most recent backup may still have block errors. This is why a good retention policy is important. It may take days, weeks or months to notice a block error. However, if using RMAN in the first place, you'll know about it fast. This is one reason why RMAN is preferred over user-managed backups.
Don_RafaelAuthor Commented:
I appreciate your help, was good starting point for us to look into possible solutions. We use SAP ORACLE tools such as BRtools.and TSM (tivoli) via backint to backup SAP ORACLE databases. RMan will be more advantages but we have to support what our client had already setup.  Thanks for your comments.
I know Tivoli all too well, as I am ex-IBM.

RMAN is Oracle. SAP tools is not a replacement, RMAN is a core technology and is the preferred Oracle backup procedure. Most current 3rd party backup tools make use of RMAN, under the covers. Tivoli / TSM and RMAN do work together via the TSM API routines, so if your client's system is not using RMAN, TSM is not configured as well as it could be. If they were, you would not be in this situation.

As for the B grade, I believe I answered every question you asked, so that is disappointing. Good luck.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.