Oracle Bad block (ORA-01578) processing method

Oracle
Oracle's bad block is ORA-01578 wrong, and may also be accompanied by ORA-01110 errors, which can be tricky for beginners or for those with no practical experience. I had been deeply hurt, write this article is to hope to help everyone.

A situation when there is a problem

1, I have a billing process to stop, the report is ORA-01578 wrong, the application-related table Tg_bill03 do sql>select from TG_CDR03 where rownum<10; this is possible, but do sql> Select COUNT (*) from tg_bill03; ORA-01578 wrong.

2, check the Alter<sid>.log to see a few error information:

Errors in FILE/ORACLE816/APP/ADMIN/BILLING/UDUMP/ORA_7281_BILLING.TRC:

Ora-01578:oracle data Block corrupted (file # 126, Block # 88490)

Ora-01110:data file 126: '/dev/vgjf7/rdata471 '



Second, after the analysis of the causes of this problem

1. Ten this Oracle database server opens asynchronous I/O (async io) or adds a write process.

2, the hardware I/O has been wrong.

3, operating system I/O or cache appeared my problem, such as the operating system for asynchronous I/O patch did not hit.

4, manually modified data file data, I simulate this error is this way.



Third, the solution

There are a lot of solutions to this problem, and if you're using an archiving approach, you can solve it based on point-in-time recovery. But here's a convenient workaround, because my library isn't open for archiving. Metaline about the ORA-01578 text is also a lot, but I always feel that after all not so practical, can not solve the actual problem.

1, the first step to solve this problem is first you want to determine what paragraph, which section is bad, is the index or table?

A, open Alter<sid>.log, find ORA-01578 error information, and record the file# and block values, I here is 126 and 88490.

B, execute the following statement to see which section is broken

Sql>select * from Dba_extents

2 where file_id=<f>

3 and <B> between block_id and block_id+blocks-1;

Here's F refers to file#,b refers to block#

My results indicate that the TG_BILL03 appears to be a bad block.



2, if the determination of the bad is the index section, then you can relax a sigh of relief, as long as the cable phase removed and then rebuilt on it, if the bad is the table, it should go down.



3. Record the form statement of this table

For my convenience, it is recommended to use Pl/sql developer to complete, if you do not have the http://www.allroundautomations.com/plsqldev.html to download a, the procedure is this.

A, with the owner of the table with Pl/sql developer connected to Oracle

B, locate the table tg_bill03 in the tree bar to the left, right-click the table->view->view SQL, and log SQL to rebuild the index in the following steps.

4, the actual processing, take my table as an example

A, the owner of the TG_BILL03 into Oracle

B, using diagnostic events 10231

sql> ALTER SYSTEM SET EVENTS ' 10231 Trace name context forever,level 10 ';

C, create a temporary table tg_bill_tmp of the table in addition to the bad block of data are retrieved

Sql>create TABLE tg_bill03_tmp as SELECT * from TG_BILL03;

C, renamed the original table, and the tg_bill03_tmp as Tg_bill03

Sql>alter table tg_bill03 Rename to Tg_bill03_bak;

Sql>alter table Tg_bill03_tmp to TG_BILL03;

D, recreate indexes, constraints, authorizations, trigger, and other objects on TG_BILL03

E, use the business relationship between the tables to make up the data in the bad block.



Iv. How to minimize the loss of problems and problems

Analysis of the cause of the problem, I think we can take the following several measures

1, in order to improve performance for the operating system to open asynchronous I/O, be sure to contact Oracle and operating system technical support to the operating system and asynchronous I/O-related patches to play the full.

2, to develop a good backup recovery strategy, preferably have the table exp backup

3, to timely check the status of the hardware, timely replacement of drive parts.



Knot: In fact, the bad block involves a lot of content, if the failure of the rollback section of the table space, data dictionary (system table space) or online logs, these processing is very difficult, need to contact Oracle's supporter. However, these aspects of the bad chance of very little, in the future article I will also do the introduction.