Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use DBMS_REPAIR to repair bad blocks in Oracle", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use DBMS_REPAIR to repair bad blocks in Oracle" this article.

In the case of physical corruption of Oracle blocks, we can directly use backup to recover if we have a backup. For recovery through backup, Oracel provides us with many ways, cold backup, user-based management, RMAN, and so on. For these ways, we need to implement database-based and file-level recovery. RMAN also provides recovery based on block media. In other words, we do not need to restore the data file at all, but extract it directly from the backup file based on blocks to achieve online recovery. You can refer to the implementation of bad block media recovery (blockrecover) based on RMAN. This is an ideal situation. What if I don't have any backups? We can use the DBMS_REPAIR package that comes with Oracle to fix it. Be careful not to be misled by the title of the article. The repair here is a lossy repair, that is, the damaged data block is marked as bad and is not accessed. Just like our disk has a bad path, find a disk repair tool to mark the bad path out of use, in the same way. The damaged data is powerless, woo. Remember to back it up at any time.

1. The process contained in the DBMS_REPAIR package

Procedure_Name Description

ADMIN_TABLES Provides administrative functions (create, drop, purge) for repair or orphan key tables.

Note: These tables are always created in the SYS schema.

CHECK_OBJECT Detects and reports corruptions in a table or index

DUMP_ORPHAN_KEYS Reports on index entries that point to rows in corrupt data blocks

FIX_CORRUPT_BLOCKS Marks blocks as software corrupt that have been previously identified as corrupt by the CHECK_OBJECT procedure

REBUILD_FREELISTS Rebuilds the free lists of the object

SEGMENT_FIX_STATUS Provides the capability to fix the corrupted state of a bitmap entry when segment space management is AUTO

SKIP_CORRUPT_BLOCKS When used, ignores blocks marked corrupt during table and index scans.

If not used, you get error ORA-01578 when encountering blocks marked corrupt.

2. Some limitations of DBMS_REPAIR

Tables with LOB data types, nested tables, and varrays are supported, but the out-of-line columns are ignored.

Clusters are supported in the SKIP_CORRUPT_BLOCKS and REBUILD_FREELISTS procedures, but not in the CHECK_OBJECT procedure.

Index-organized tables and LOB indexes are not supported.

The DUMP_ORPHAN_KEYS procedure does not operate on bitmap indexes or function-based indexes.

The DUMP_ORPHAN_KEYS procedure processes keys that are no more than 3950 bytes long.

3. Create a presentation environment

-- create tablespaces

SQL > show user

USER is "SCOTT"

SQL > create tablespace tbs_tmp datafile'/ u01 size autoextend on

Tablespace created.

-- create a table object tb_obj and its index

SQL > create table tb_obj tablespace tbs_tmp as select * from dba_objects

Table created.

SQL > create index i_tb_obj on tb_obj (object_id)

Index created.

-- relevant information on the table section

SQL > select segment_name, header_file, header_block,blocks from dba_segments where segment_name = 'TB_OBJ'

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS

-

TB_OBJ 6 130 1280

-- use the dd command that comes with linux to damage the data block

[oracle@wang ~] $dd of=/u01/app/oracle/oradata/DBdb/tbs_tmp.dbf bs=8192 conv=notrunc seek=131 EOF

031 records in

031 records out

13 bytes (13 B) copied, 0.000117639 s, 111kB/s

[oracle@wang ~] $

The following query received an error prompt

SQL > select count (*) from tb_obj

Select count (*) from tb_obj

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 131)

ORA-01110: data file 6:'/ u01 * app *

SQL >

4. Use DBMS_REPAIR to fix bad blocks

Step a creates the corresponding table object:

Use the DBMS_REPAIR.ADMIN_TABLES procedure to create a table object that records the tables that need to be repaired

SQL > BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME = > 'REPAIR_TABLE'

TABLE_TYPE = > dbms_repair.repair_table

ACTION = > dbms_repair.create_action

TABLESPACE = > 'USERS')

END

/

PL/SQL procedure successfully completed.

SQL >

Use the DBMS_REPAIR.ADMIN_TABLES procedure to create a table object to record orphaned indexes that point to bad blocks after the table block is corrupted

SQL > BEGIN

DBMS_REPAIR.ADMIN_TABLES

(

TABLE_NAME = > 'ORPHAN_KEY_TABLE'

TABLE_TYPE = > DBMS_REPAIR.ORPHAN_TABLE

ACTION = > DBMS_REPAIR.CREATE_ACTION

TABLESPACE = > 'USERS'

);

END

/

PL/SQL procedure successfully completed.

Step b verifies damaged objects:

-- use DBMS_REPAIR.CHECK_OBJECT to detect damage on an object and return the number of damaged blocks

SQL > SET SERVEROUTPUT ON

SQL > DECLARE num_corrupt INT

BEGIN

Num_corrupt: = 0

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME = > 'SCOTT'

OBJECT_NAME = > 'TB_OBJ'

REPAIR_TABLE_NAME = > 'REPAIR_TABLE'

CORRUPT_COUNT = > num_corrupt)

DBMS_OUTPUT.PUT_LINE ('number corrupt:' | | TO_CHAR (num_corrupt))

END

/

Number corrupt: 1

PL/SQL procedure successfully completed.

Below, we can query the damaged blocks from repair_table.

You can see from the query below that the column marked_corrupt is all true, indicating that we have marked bad blocks in CHECK_OBJECT

COLUMN object_name FORMAT a10

COLUMN repair_description FORMAT a28

SET LINES 10000

SELECT object_name, block_id, corrupt_type,marked_corrupt,repair_description FROM repair_table

OBJECT_NAM BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION

TB_OBJ 131 6148 TRUE mark block software corrupt

Step c marks bad blocks

Procedure FIX_CORRUPT_BLOCKS is used to mark bad blocks. In this demonstration, we have already marked it in CHECK_OBJECT, such as not performing the following procedure.

-- since the previous step has been marked, the following output is 0

SQL > SET SERVEROUTPUT ON

SQL > DECLARE num_fix INT

BEGIN

Num_fix: = 0

DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

SCHEMA_NAME = > 'SCOTT'

OBJECT_NAME= > 'TB_OBJ'

OBJECT_TYPE = > dbms_repair.table_object

REPAIR_TABLE_NAME = > 'REPAIR_TABLE'

FIX_COUNT= > num_fix)

DBMS_OUTPUT.PUT_LINE ('num fix:' | | TO_CHAR (num_fix))

END

/

Num fix: 0

PL/SQL procedure successfully completed.

Step d DUMP orphaned index key value

Use the DUMP_ORPHAN_KEYS procedure to populate the index key values that point to bad blocks to the ORPHAN_KEY_TABLE,I_TB_OBJ index name

SQL > SET SERVEROUTPUT ON

SQL > DECLARE num_orphans INT

BEGIN

Num_orphans: = 0

DBMS_REPAIR.DUMP_ORPHAN_KEYS (

SCHEMA_NAME = > 'SCOTT'

OBJECT_NAME = > 'Illustrated TBLs OBJ'

OBJECT_TYPE = > dbms_repair.index_object

REPAIR_TABLE_NAME = > 'REPAIR_TABLE'

ORPHAN_TABLE_NAME= > 'ORPHAN_KEY_TABLE'

KEY_COUNT = > num_orphans)

DBMS_OUTPUT.PUT_LINE ('orphan key count:' | | TO_CHAR (num_orphans))

END

/

Orphan key count: 88

PL/SQL procedure successfully completed.

The following query can see that it is exactly equal to the number of records returned above, that is, 88 records.

SQL > select count (*) from orphan_key_table

COUNT (*)

-

eighty-eight

-- verify whether the object can be queried. The following result shows that it still cannot be queried.

SQL > show user

USER is "SCOTT"

SQL > SELECT COUNT (*) FROM TB_OBJ

SELECT COUNT (*) FROM TB_OBJ

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 131)

ORA-01110: data file 6:'/ u01 * app *

SQL >

Step e skips bad blocks

Use SKIP_CORRUPT_BLOCKS to tell Oracle which bad blocks need to be skipped

SQL > BEGIN

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

SCHEMA_NAME = > 'SCOTT'

OBJECT_NAME = > 'TB_OBJ'

OBJECT_TYPE = > dbms_repair.table_object

FLAGS = > dbms_repair.skip_flag)

END

/

PL/SQL procedure successfully completed.

Because there is an orphaned index on the index key, we rebuild the index

SQL > alter index scott.i_tb_obj rebuild

Index altered.

-- verify the result

SQL > show user

USER is "SCOTT"

SQL >

SQL > select count (*) from tb_obj

COUNT (*)

-

86952

SQL >

The above is all the content of the article "how to use DBMS_REPAIR to repair bad blocks in Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.