Sysaux table Space Database block corruption/Free block repair

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.