Sysaux table Space Database block corruption/Free block repair

Customers over the database for a long time no maintenance, the recent reflection of insufficient server space. While checking server space, I stumbled across a database backup problem.

After checking that there is a bad block in the database Sysaux table space, the table with the bad block is Wrh$_enqueue_stat, after querying the data on the test library, I determine that the data of the table has little effect on the operation of the whole system.

There is no Rman backup available at this time and block recover is not available. It then decided to delete the table for rebuilding. But after doing so, the original data block instead becomes the free block. To fix the free blocks, I started testing on the test library.

1, Simulation database corruption

1) damage to the simulation database can use DDBD, the tool configuration can refer to the following links:

Http://www.cnblogs.com/jyzhao/p/5139584.html

2) simulate the block to be damaged

Select file_id,block_id from dba_extents where segment_name= ' wrh$_enqueue_stat ';

The result is file_id=2,block_id=4440.

modify/x the file 2 block 4440 offset 255;

3) Rman command, execute backup validate database or backup validate DataFile 2;

Use view v$database_block_corruption to view the corrupted blocks found by the backup operation.

SELECT * from V$database_block_corruption;

4) Table deleted and rebuilt, found that File2 4440 became a free block

SELECT * from Dba_extents where file_id=2 and 4440 between Block_id-1 and block_id+1; #查询无数据

2, bad block repair

1) Table space modified to not automatically grow

ALTER DATABASE DataFile 2 autoextend off;

2) Create the entity table on Sysaux

CREATE TABLE Test (ID varchar ()) tablespace Sysaux;

3) insert into test values (' aaaaaaaaaa ') fails when inserting data until ORA01653 error is reported

INSERT into test values (' aaaaaaaaaa ');

Commit

INSERT INTO Test

SELECT *

From Test;

Commit

4) Create a second entity table

CREATE TABLE test_2 (ID varchar ()) tablespace Sysaux;

--See if File 2 block_id 4440 has data

SELECT * from Dba_extents where file_id=2 and 4440 between Block_id-1 and block_id+1; #模拟到此步, file 2 block_id 4440 query for Test_ 2 of the data.

5) Delete the test table

drop table test_1;

drop table test;

6) Table space growth mode changed to auto-grow

ALTER DATABASE DataFile 2 autoextend on;

7) test the backup in Rman to perform the full provisioning normally.

Rman>backup incremental Level 0 database;

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1884364

Sysaux table Space Database block corruption/Free block repair