Oracle's bad block is ORA-01578 error, and it may also be failed to accompany ORA-01110, which is undoubtedly a tricky for beginners or DBAs without practical experience. I was deeply harmful, and this article wished to help everyone.
First, the scene when you have problems
1. The process of my billing entry is stopped, and the report is ORA-01578 fault, and the related table TG_BILL03 is SQL> SELECT from TG_CDR03 WHERE ROWNUM <10; this is ok, but SQL> Select count (*) from TG_BILL03; time to report ORA-01578.
2, check the ALTER
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, afterward analysis of this problem
1. Ten eight nine this Oracle database Server opens asynchronous I / O (Async IO) or adds a write process.
2, the hardware I / O appears error.
3, the I / O or cache of the operating system appears, such as the operating system does not play for the patch of asynchronous I / O.
4, manually modify the data in the data file, I simulate this error is this way.
Third, solution
The solution to this problem is much, if you use an archive, you can resolve based on time point recovery. But here introduce a relatively convenient solution because my library does not have a rendering. Metaline is also a lot about ORA-01578, but I always feel that it is not so practical after reading, and I can't solve the actual problem.
1. The first step in solving this problem is to first determine what paragraph, which segment is broken, is it index or a table?
A. Open alter
B, which segment is broken in the following statement
SQL> SELECT * FROM DBA_EXTENTS
2 where file_id =
3 and Between block_id and block_id blocks-1;
The f refers to the File #, and the B is Block #
My display results indicate that the tg_bill03 has a bad block.
2, if it is determined that the bad is the index section, you can be relieved as soon as long as you delete this and then rebuild it. If there is a bad time, it should go down.
3. Record the formation formal sentence of this table
For me, it is recommended to use the PL / SQL Developer to complete if you don't have to download one at http://www.allroundautomations.com/plsqldev.html. The operation step is this.
A, connect Oracle with PL / SQL Developer with PL / SQL Developer with the OWNER
B, find this table tg_bill03 in the tree column, right click on the table -> View-> View SQL, record SQL to reconstruct the index in the following steps.
4, actually handled, take my table as an example
A, connect Oracle with TG_BILL03 OWNER
B, use diagnostic events 10231
SQL> ALTER SYSTEM SET Events '10231 Trace Name Context Forever, Level 10'; c, create data from the table TG_BILL_TMP to retrieve the data in the table of Timatal TG_BILL_TMP
SQL> CREATE TABLE TG_BILL03_TMP AS SELECT * FROM TG_BILL03;
C, rename the original table, and TG_BILL03_TMP is TG_BILL03
SQL> ALTER TABLE TG_BILL03 RENAME TG_BILL03_BAK;
SQL> ALTER TABLE TG_BILL03_TMP TG_BILL03;
D, recreate indexes, constraints, authorization, trigger and other objects on TG_BILL03
E, use the business relationship between the tables to make up the data in the bad block.
Fourth, how to minimize the loss of problems and problems?
Analysis of the cause of the problem, I think the following measures can be taken
1. When opening an asynchronous I / O for the operating system for the operating system, it is necessary to contact Oracle and Operating System Technical Support to contact the operating system with the asynchronous I / O related patches.
2, develop a good backup recovery strategy, preferably EXP backup
3. To check the state of the hardware in time, replace the driver component in time.
Fort.1: In fact, the content involved in the bad block is much, if the rollback segment table space, data dictionary (SYSTEM table space) or online log, you need to contact Oracle's Supporter. However, the bad chances of these aspects have few very little, and I will introduce in the later article.
