DBMS_REPAIR package Usage Details

DBMS_REPAIR package Usage Details

Oracle provides the DBMS_REPAIR package to discover, identify, and modify Bad blocks in data files.

Any tool is not omnipotent. Using this package may cause data loss, inconsistent data returned from tables and indexes, and damage to integrity constraints. Therefore, when an error occurs, you should first recover from physical backup or logical backup. Using dbms_repair is only a method used without backup. This method usually causes data loss.

The working principle of the dbms_repair package is relatively simple. It is to mark the detected bad block so that subsequent dml operations can skip this block. At the same time, the dbms_repair package also provides a process for saving the key values marked as bad blocks in the index and repairing freelist and segment bitmap.

Note that the dbms_repair package is not authorized and can only be executed by sys users.

The following uses an example to briefly introduce the use of the dbms_repair package.

Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

I. Construct a test environment

First, create a table space for testing. Because you need to use UtralEdit to open the data file and modify part of the content to simulate errors, the data file should be smaller.

SQL> CREATE TABLESPACE TEST DATAFILE 'e: ORACLEORADATATESTTEST. dbf' SIZE 1 M
2 extent management local autoallocate segment space management manual;

The tablespace has been created.

SQL> CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2 (30) TABLESPACE TEST;

The table has been created.

SQL> INSERT INTO TEST SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

You have created 6232 rows.

SQL> COMMIT;

Submitted.

SQL> CREATE INDEX IND_TEST_ID ON TEST (ID );

The index has been created.

SQL> CREATE INDEX IND_TEST_NAME ON TEST (NAME );

The index has been created.

To ensure that oracle has written the inserted data to the data file, the database is restarted.

SQL> CONN/@ TEST AS SYSDBA
Connected.
SQL> SHUTDOWN
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL> STARTUP
The ORACLE routine has been started.

Total System Global Area 89201304 bytes
Fixed Size 453272 bytes
Variable Size 62914560 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database has been loaded.
The database has been opened.

 

Ii. Generation of simulated errors

When you use UtralEdit to open a data file, any location in the data file will cause a data file error. However, we need to locate the error location in the TEST table.

SQL> CONN YANGTK/YANGTK @ TEST
Connected.

SQL> SELECT SUBSTR (ROWID, 10, 6), ID, NAME FROM TEST WHERE ID = 123;

SUBSTR (ROWID ID NAME
----------------------------------------------------
AAAAAG 123 ALL_REPCONFLICT

How to find the data of the TEST table in the data file? You can use ROWID to locate the position of a record in a data file. Select any record (for example, above ID = 123) and obtain its ROWID. We know that the value of ROWID is 10 ~ The 15th digit indicates that the BLOCK of the record is the BLOCK of the data file.

A Indicates 0, B is 1, and G is 6. This indicates that this record is stored in the Sixth block of the data file.

SQL> SHOW PARAMETER DB_BLOCK_SIZE

NAME TYPE VALUE
--------------------------------------------------------------
Db_block_size integer 16384

The BLOCK size is 16 kb.

SQL> SELECT TO_CHAR (6*16384, 'xxxxxx') FROM DUAL;

TO_CHAR
-------
18000

SQL> SELECT TO_CHAR (7*16384, 'xxxxxx') FROM DUAL;

TO_CHAR
-------
1C000

Use UtralEdit to open the data file and locate the file at H (open in binary mode. If the file is not opened in binary mode, use the CTRL + H shortcut key to switch ). According to the above calculation, we can find that the record is between 18000h and 1C000h.

The storage mode of Number 123 in the database is 03C20218. 03 indicates that there are three places, C2 indicates that the highest bit is bits, 02 indicates that the highest bit is, and the lowest Bit is 23.

The specific value can be obtained through the following query:

SQL & gt; SELECT DUMP (123) FROM DUAL;

DUMP (123)
---------------------
Typ = 2 Len = 3: 194,2, 24

SQL> SELECT TO_CHAR (194, 'xx'), TO_CHAR (2, 'xx'), TO_CHAR (24, 'xx') FROM DUAL;

TO _
---------
C2 2 18

If you are interested in how the specific NUMBER type is stored in the database, refer to another article.

The following uses the UtralEdit search function to find 03C20218, change it to 03C20216, and save it.

The ROWID of oracle is located in the file, which is more complicated. The UtralEdit function can be used for the same purpose.

According to the preceding query, if ID = 123, the value of NAME is ALL_REPCONFLICT.

Next we will use UtralEdit to open the file, use CTRL + H to switch to the text format, and directly find the ALL_REPCONFLICT string. Click CTRL + H to switch back to the binary format. Skip a byte (0F in this example) and you will see the value of 123 03C20218. After modification, save the modification and exit.

SQL & gt; SELECT * FROM TEST WHERE ID = 123;

ID NAME
----------------------------------------
123 ALL_REPCONFLICT

At this time, the query can still get the correct results, because oracle uses the results in db_cache. In order for oracle to "View" the changes, the database must be restarted.

SQL> CONN/@ TEST AS SYSDBA
Connected.
SQL> SHUTDOWN
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL> STARTUP
The ORACLE routine has been started.

Total System Global Area 89201304 bytes
Fixed Size 453272 bytes
Variable Size 62914560 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database has been loaded.
The database has been opened.
SQL> CONN YANGTK/YANGTK @ TEST
Connected.

SQL & gt; SELECT * FROM TEST WHERE ID = 123;
SELECT * from test where id = 123
*
ERROR is located in row 1st:
ORA-01578: ORACLE data block corruption (file number 7, block number 6)
ORA-01110: data file 7: 'e: ORACLEORADATATESTTEST. dbf'

The simulated Bad blocks have been successfully simulated. Start to enter the topic section and use the DBMS_REPAIR table to process the Bad blocks.

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page