Summary of processing ideas for Oracle three types of bad blocks (no physical backup)

The occurrence of bad blocks is rare, but the production system occasionally appears. If there is a physical backup, the processing is relatively simple, directly block-level recover can be, but if only a logical backup? There are four kinds of cases to deal with, and here's a summary:

One, the data part of the block is broken, when the SQL execution scan to this block will be reported ORA-01578:

ERROR at line 1:
Ora-01578:oracle data Block corrupted (file #, block # 12)
Ora-01110:data file: '/u01/app/oracle/oradata/rwdb_production/t6. DBF '

However, this bad block does not affect the database restart, but will be reported when restarting to the open stage:

Thu 10:39:00 CST 2014
Corrupt Block Found
TSN = Tsname = corrupt
RFN = +, BLK = RDBA = 88080396
Objn = 591083, OBJD = 591083, OBJECT = user_tab, subobject =
SEGMENT OWNER = rwuser, SEGMENT TYPE = Table SEGMENT

When the DB is open, it scans the status of the data file, where you can see clearly what type of segment, what user, and what object has a bad block. You can also check further with the DBV tool:

[Email Protected]]/u01/app/oracle/admin/rwdb>dbv file=/u01/app/oracle/oradata/rwdb_production/t6. Dbf

Dbverify-verification Complete

Total Pages examined:25
Total Pages processed (Data): 0
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:13
Total Pages Marked corrupt:1
Total Pages influx:0
Highest block scn:1132783081 (0.1132783081)

It is also clear to see that total Pages Marked corrupt is 1, which is 1 bad blocks.

Processing:

There are many ways to deal with this type of bad block, and the recommended approach on support is as follows:

1, with Dbms_repair. Skip_corrupt_blocks processing, that is, skipping bad blocks.

2, build a temporary table with CTAs, the above broken table is User_tab, can build a temporary table user_tab_tmp

3. Rename the User_tab table: ALTER TABLE User_tab Rename to User_tab_corrupt

4. Change user_tab_tmp to Production system official table: ALTER TABLE user_tab_tmp Rename to User_tab

5. Rebuilding the index of the User_tab table

This recovery method, the data inside the bad block will be discarded. If you are doing a logical backup every day, you can also compare the current user_tab with the User_tab in the logical backup yesterday to maximize data recovery.

Second, datafile head block is broken (block 1th)

A 8k block size data file, we open with UltraEdit, in 16 binary display, where 00000000~00001FF0 is block No. 0, and the beginning is 1th block, that is the head block of the data file. This block is very dangerous, the database restart will directly error:

Sql> Conn/as sysdba;
Connected to an idle instance.
Sql> startup;
ORACLE instance started.

Total System Global area 935329792 bytes
Fixed Size 2100680 bytes
Variable Size 385876536 bytes
Database buffers 541065216 bytes
Redo buffers 6287360 bytes
Database mounted.
Ora-01122:database file failed verification check
Ora-01110:data file: '/u01/app/oracle/oradata/rwdb_production/t9. DBF '
Ora-01210:data file header is media corrupt

As you can see, the database is not open.

Alert report the following things:
Thu 15:11:10 CST 2014
ALTER DATABASE OPEN
Read of rdba:0x06000001 (file, Block 1) failed with ORA-01210.
Hex Dump of (file, Block 1) in Trace file/u01/app/oracle/admin/rwdb/udump/rwdb_ora_5850.trc
Corrupt block relative dba:0x06000001 (file, block 1)
Bad header found during datafile header read
Data in bad block:
type:49 format:1 rdba:0x31000031
Last Change scn:0x0000.00000000 seq:0x31 flg:0x31
spare1:0x0 spare2:0x0 spare3:0x0
Consistency value in TAIL:0X00000B01
Check value in block header:0x3131
Block checksum disabled
Trying reread from disk.
Reread of rdba:0x06000001 (file, Block 1) failed with ORA-01210
ORA-1122 signalled During:alter DATABASE OPEN ...

The DBV can also detect:

[Email Protected]]/u01/app/oracle/admin/rwdb/bdump>dbv file=/u01/app/oracle/oradata/rwdb_production/t9. Dbf

Dbverify-verification Complete

Total Pages examined:25
Total Pages processed (Data): 1
Total Pages Failing (Data): 0
Total Pages processed (Index): 0
Total Pages Failing (Index): 0
Total Pages processed (other): 10
Total Pages processed (SEG): 0
Total Pages Failing (Seg): 0
Total Pages empty:13
Total Pages Marked corrupt:1
Total Pages influx:0
Highest block scn:1132794574 (0.1132794574)

If the Midnight dry upgrade cut (to restart the server), encountered this problem, side without DBA, or very tricky, the business will be interrupted directly, if the HA system, bad datafile and just on the magnetic array, then by switching can not solve the problem, because the Oracle resource group will not come. For databases that are not in archive mode, the workaround is as follows:

sql> ALTER DATABASE datafile '/u01/app/oracle/oradata/rwdb_production/t9. DBF ' OFFLINE DROP;

Database altered.

sql> ALTER DATABASE open;

Database altered.

Here, regardless of data loss or not, first started the database again, so as not to trouble big! ：）

Third, the DataFile OS head block is broken (block No. 0)

What is DataFile's No. No. 0 block? Officially known as the head of the operating system, the contents are not written by Oracle, but are file system-related information such as the file size recorded by the operating system. Block No. 0 is broken, you can restart the database normally, no problem. However, if you need to rebuild your control files on one day, you will face an error:

Ora-27047:unable to read the header block of file

For block No. 0 detection, with the front of the DBV is not detected at all (DBV command seemingly do not detect block No. 0), need to use Dbfsize to detect:

[Email protected]]/u02/backup>dbfsize/u01/app/oracle/oradata/rwdb_production/t8. Dbf
/u01/app/oracle/oradata/rwdb_production/t8. Dbf:header Block magic number is bad

Here is a hint of what magic number is broken.

Workaround:

Resize the problem data file size, Os block head will be rewritten, the problem can be resolved.

ALTER DATABASE DataFile '/U01/APP/ORACLE/ORADATA/RWDB_PRODUCTION/T8. DBF ' Resize <new size>

Four, the whole datafile are broken (various bad blocks of the complex)

I have encountered once, the entire document is bad, this problem, can be seen as the above bad block of the collective outbreak of the situation.

Such as:

Dbvfile=/data1/app/oracle/oradata/rwdb/irdbroamerts_01.dbf

Total Pages examined:1926

Total Pages processed (Data): 0

Total Pages Failing (Data): 0

Total Pages processed (Index): 0

Total Pages Failing (Index): 0

Total Pages processed (other): 0

Total Pages processed (SEG): 0

Total Pages Failing (Seg): 0

Total Pages empty:0

Total Pages Marked corrupt:1926

Total Pages influx:0

Highest block scn:0 (0.0)

As you can see here, a total of 1926 blocks were detected, all broken. If you encounter a database restart, you cannot open successfully.

If there is an Rman backup, this is a simple situation, the direct restore, recover bad data files on the line. If there is no physical backup, first use the solution of the second case above to open the database and then restore the data. The method of recovering data is different from the second one, since the block in this file is broken, and then skip which block is meaningless. The steps to recover data are as follows:

1. Find out what objects are in this DBF

2. IMPDP the above object into a new tablespace with the latest logical backup (if IRDBROAMERTS_01 is not a unique file under Tablespace, you can not create a new tablespace)

This article is from the "database" blog, so be sure to keep this source http://weikle.blog.51cto.com/3324327/1582760

Summary of processing ideas for Oracle three types of bad blocks (no physical backup)