Shulou(Shulou.com)06/01 Report--

On July 22, 2014, study the recovery of bad blocks in the database:

Oracle calls standard C system functions to read and write data blocks, so bad blocks can be caused by the following reasons:

Hardware Icano error

Operating system Icano error or buffering problem

Memory or paging issu

Disk repair tool

Part of a data file is being overwritten

Oracle failed to access an unformatted system block

Partial overflow of data file

Oracle or bug of the operating system

Encountered "ORA-01578:ORACLE data block corrupted" error

How to handle it: 1.rman 's recover command can recover only damaged data blocks while the database remains in open state.

two。 If there is no backup, as a last resort, you can also use the stored procedure of the DBMS_REPAIR package to isolate the damaged blocks while saving as much data as possible.

The rman backup command is also a good tool for checking bad data blocks. Once the ORA-19566 is read, there will be a problem.

At this point, you can use backup validate tablespace user to observe the detailed information, and you can view the number of bad blocks and tracking files.

Grep'corrupt'/u01/app/oracle/diag/rdbms/br/br/trace/**.trc

Recovery data blocks: rman "recover datafile 5 block 203

Batch recovery of damaged blocks: recover corruption list

Data block Bad Block No.1 Bad Block, need to do:

Run {

Sql 'alter database datafile 5 offline'

Restore datafile 5

Recover datafile 5

Sql'alter database datafile 5 online'

}

Restore using exp/imp

In this case, the data will definitely be lost. In this case, the method of exporting the data and then rebuilding the table and then importing it should be taken to restore the data in the damaged data block as far as possible, but the export is not allowed in the case of bad blocks. The following command: Exp test/test file=t.dmp tables=t

The export command will report an ORA-01578 error during execution, and the error prompt will prompt the file with that file number and which block in this file is damaged, such as: ORA-01578:ORACLE data block corruption (file number 4, block number 35)

For the above tips, first query which objects are corrupted:

Select tablespace_name,segment_type,owner,segment_name From dba_extents Where file_id=4 and 35 between block_id and block_id+blocks-1

If the corrupted block is an index, it can usually be resolved by index reconstruction, and if the corrupted data (segment_type is table), the Exp operation can skip the bad block by setting the following internal event.

Alter session set events='10231 trace name context forever,level 10'

Then re-execute the export command, export the related table, then execute the Drop Table command to delete the related table, then rebuild the table and finally import the data.

Restore using DBMS_REPAIR

Of course, using DBMS_REPAIR also leads to data loss. There is no detailed introduction here. If you are interested, you can check out oracle's online articles.

3. Use dbms_repair package to handle bad blocks

1) first set up a repair_table to store the bad block information detected by dbms_repair.check_object

SQL > declare

2begin

3dbms_repair.admin_tables

4 (table_name = > 'REPAIR_TABLE',-- table name

5table_type = > dbms_repair.repair_table

6action = > dbms_repair.create_action

7tablespace = > 'USERS');-- to specify the tablespace in which the table is stored

8end

9/

The PL/SQL process completed successfully.

SQL > col owner format A10

SQL > col object_name format A20

SQL > col object_type format A20

SQL > select owner, object_name, object_type

2from dba_objects

3where object_name like'% REPAIR_TABLE'

OWNEROBJECT_NAMEOBJECT_TYPE

SYSREPAIR_TABLETABLE

SYSDBA_REPAIR_TABLEVIEW

Oracle automatically creates a DBA_REPAIR_TABLE view.

2) use dbms_repair.check_object to detect bad blocks

SQL > set serveroutput on size 100000

SQL > declare

2rpr_count int

3begin

4rpr_count: = 0

5dbms_repair.check_object (

6schema_name = > 'SYS',-- specifies the object schema, that is, the owner of the object

7object_name = > 'TEST',-- specifies the object name, that is, the table name

8repair_table_name = > 'REPAIR_TABLE'

9corrupt_count = > rpr_count)

10dbms_output.put_line ('repair block count:'

11 | | to_char (rpr_count))

12end

13/

Repair block count: 4

The PL/SQL process completed successfully.

SQL > select object_name, block_id, corrupt_type, marked_corrupt

2corrupt_description, repair_description

3from repair_table

OBJECT_NAMEBLOCK_ID CORRUPT_TYPE MARKED_COR

--

CORRUPT_DESCRIPTION

REPAIR_DESCRIPTION

TEST196148 TRUE

Mark block software corrupt

TEST206148 TRUE

Mark block software corrupt

TEST236148 TRUE

Mark block software corrupt

TEST316148 TRUE

Mark block software corrupt

By running dbms_repair.check_object, the bad block information is stored in the repair_ table table, where a field marked_corrupt is used to identify whether the block is identified as a bad block, and when it is identified as true, the block is identified as a bad block. This step is somewhat similar to the description in the oracle document. According to the oracle document, there is no bad block identification when the dbms_repair.check_object is executed, that is, the value of the marked_corrupt column should be false, but only after the dbms_repair.fix_corrupt_blocks process is executed.

3) use dbms_repair.fix_corrupt_blocks to identify bad blocks

SQL > declare

2fix_block_count int

3begin

4fix_block_count: = 0

5dbms_repair.fix_corrupt_blocks (

6schema_name = > 'SYS'

7object_name = > 'TEST'

8object_type = > dbms_repair.table_object

9repair_table_name = > 'REPAIR_TABLE'

10fix_count = > fix_block_count)

11dbms_output.put_line ('fix blocks count:' | |

12to_char (fix_block_count))

13end

14/

Fix blocks count: 0

The PL/SQL process completed successfully.

We can see fix blocks count=0, that is, bad block identification has already been done in the previous step of check_object, which can actually be omitted. (but it hasn't been tested! )

SQL > select count (*) from test

Select count (*) from test

*

An error occurred on line 1:

ORA-01578: ORACLE block corruption (file number 7, block number 19)

ORA-01110: data file 7:'G:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORA10G\ TEST01.DBF'

At this time, the query still reported an error, because we only identified the bad block, and when we scanned the whole table, we still found the bad block and reported an error.

4) use the dbms_repair.dump_orphan_keys procedure to save the index key value of the bad block, and then execute the skip_corrupt_blocks process before we can rebuild the index, otherwise the new index will still reference the bad block when rebuilding the index. The first step is to create an ORPHAN_KEY_TABLE, which is used to store the index key values of bad blocks.

SQL > declare

2begin

3dbms_repair.admin_tables

4 (table_name = > 'ORPHAN_KEY_TABLE'

5table_type = > dbms_repair.orphan_table

6action = > dbms_repair.create_action

7tablespace = > 'USERS')

8end

9/

The PL/SQL process completed successfully.

Then execute the procedure dbms_repair.dump_orphan_keys to store the bad block key values in the table created above:

SQL > declare

2orph_count int

3begin

4orphorphcountpurl = 0

5dbms_repair.dump_orphan_keys (

6schema_name = > 'SYS'

7object_name = > 'name of the ID_INX',-- index

8object_type = > dbms_repair.index_object

9repair_table_name = > 'REPAIR_TABLE',-- gets information about bad blocks from this table

10orphan_table_name = > 'ORPHAN_KEY_TABLE'

11key_count = > orph_count)

12dbms_output.put_line ('orphan-index entries:' | | to_char (orph_count))

13end

14/

Orphan-index entries: 491

The PL/SQL process completed successfully.

SQL > declare

2orph_count int

3begin

4orphorphcountpurl = 0

5dbms_repair.dump_orphan_keys (

6schema_name = > 'SYS'

7object_name = > 'NAME_INX'

8object_type = > dbms_repair.index_object

9repair_table_name = > 'REPAIR_TABLE'

10orphan_table_name = > 'ORPHAN_KEY_TABLE'

11key_count = > orph_count)

12dbms_output.put_line ('orphan-index entries:' | | to_char (orph_count))

13end

14/

Orphan-index entries: 491

The PL/SQL process completed successfully.

Dump_orphan_keys is performed on each index.

SQL > select index_name, count (*) from orphan_key_table

2group by index_name

INDEX_NAMECOUNT (*)

ID_INX491

NAME_INX491

5) use skip_corrupt_blocks to skip bad blocks when querying or DML

SQL > declare

2begin

3dbms_repair.skip_corrupt_blocks (

4schema_name = > 'SYS'

5object_name = > 'TEST'

6object_type = > dbms_repair.table_object

7flags = > dbms_repair.skip_flag)

8end

9/

The PL/SQL process completed successfully.

SQL > select table_name, skip_corrupt from dba_tables

2where table_name = 'TEST'

TABLE_NAMESKIP_COR

TESTENABLED

6) use dbms_repair.rebuild_freelists to rebuild the freelists so that the block is no longer placed in the freelists, that is, the block will no longer be used.

SQL > declare

2begin

3dbms_repair.rebuild_freelists (

4schema_name = > 'SYS'

5object_name = > 'TEST'

6object_type = > dbms_repair.table_object)

7end

8/

Declare

*

An error occurred on line 1:

ORA-10614: Operation not allowed on this segment

ORA-06512: in "SYS.DBMS_REPAIR", line 400,

ORA-06512: in line 3

However, we can see that this operation does not seem to be possible for objects under SYS users.

4. Rebuild the index

SQL > select count (id) from test

COUNT (ID)

-

19998

SQL > select count (name) from test

COUNT (NAME)

-

19998

SQL > select count (*) from test

COUNT (*)

-

19507

We can see the above three queries, and the results of the first and second queries with and without indexes are not the same. Let's try using rebuild.

SQL > alter index id_inx rebuild

The index has changed.

SQL > alter index name_inx rebuild

The index has changed.

SQL > select count (id) from test

COUNT (ID)

-

19998

SQL > select count (name) from test

COUNT (NAME)

-

19998

SQL > select count (*) from test

COUNT (*)

-

19507

It may not be possible to rebuild the index through rebuild. Only through DROP and then CREATE.

SQL > drop index id_inx

The index was deleted.

SQL > drop index name_inx

The index was deleted.

SQL > create index id_inx on test (id)

The index has been created.

SQL > create index name_inx on test (name)

The index has been created.

SQL > select count (id) from test

COUNT (ID)

-

19507

SQL > select count (name) from test

COUNT (NAME)

-

19507

SQL > select count (*) from test

COUNT (*)

-

19507

At this point, the table can be used normally, but some data has been lost at the same time, so the importance of the data and the consequences of recovery should be fully taken into account when using dbms_repair for recovery. At the same time, we should also consider whether there are other ways to recover, otherwise the loss may outweigh the gain in the end.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.