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

This article shows you how ORACLE bad blocks are produced, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

The phenomenon of bad block in Oracle database refers to the confusion in one or more data blocks of Oracle database (the capacity of a data block is specified by the db_block_size parameter when creating the database, and the default is 8K). Because the normal data blocks have a fixed legal content format, the emergence of bad blocks leads to the database process can not parse the contents of the data block normally, which makes the database process report errors or even suspend, and cascading leads to the exception of the whole database instance.

I. the cause of the bad block

The causes of bad blocks are roughly as follows:

1.1 hardware issu

When a data block is processed by an Oracle process, it is first read into the physical memory space, and then it is written back to disk by a specific process after the processing is completed. If there is a memory failure and CPU calculation error in this process, it will lead to confusion in the content of the data block in memory, and finally reflect that the content of the data block written back to disk is incorrect. Similarly, if an exception occurs in the storage subsystem, block corruption occurs.

1.2 operating system BUG

Because the Oracle process reads and writes data blocks in the way of operating system kernel calls (system call), if there are problems in the operating system kernel calls, it will inevitably lead to illegal content written by the Oracle process.

1.3 Icano error or buffering problem with the operating system

1.4 memory or paging issu

Oracle software BUG

On a specific version of the Oracle software, an exception BUG may occur that results in the contents of the block.

1.5 non-Oracle processes disrupt Oracle shared memory areas

As mentioned above, when the contents of the data block are read into the physical memory of the host, if other non-Oracle processes disturb the shared memory area used by Oracle, the block content written back to disk is eventually confused.

1.6 abnormal shutdown, power outage, termination of service

Abnormal shutdown, power outage and service termination cause the process to terminate abnormally, which destroys the integrity of the data block and leads to the generation of bad blocks.

Note: this is why a sudden power outage can cause the database to fail to start.

It can be seen from the above that the reasons for the formation of bad blocks are complex. When a bad block occurs, in order to find the exact cause, it takes a lot of analysis time and troubleshooting operations, and even needs to be reproduced many times to find out the root cause. However, when the fault occurs in the production system, in order to reduce downtime, we will implement emergency contingency measures as soon as possible to ensure the availability of the system, which destroys the fault site, so the analysis of the root cause is more difficult.

2. Prevention of bad blocks

The problem of bad blocks is destructive, but not unpreventable.

On the Metalink.oracle.com website, Oracle periodically publishes a "known problem (known issues) description" based on a specific software version. Oracle software BUG, which may cause bad blocks, is treated as a high severity problem within Oracle. In the "known issues description", these BUG are marked as serious (Noticable) problems (marked * or +), and Oracle will also issue Alert notices for some problems. In the documentation, Oracle will provide the appropriate patch or response.

2.2 Oracle provides a backup recovery tool, Recovery Manager, which provides the ability to scan files for bad blocks.

In the Recovery Manager interface, use:

RMAN > BACKUP CHECK LOGICAL VALIDATE DATAFILE n

You can check whether the data file contains bad blocks without producing the actual backup output.

2.3 Dbv tool check

Note: because dbv requires that file be followed by a file extension, if you use a bare device to store

You must use ln to link the bare device to a file, and then use dbv to check the linked file

Check.

ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE

It performs a check for bad blocks, but does not mark the bad blocks as corrupt, and the test results are saved in the user trace file in the USER_DUMP_DEST directory.

2.4 Bad blocks can be detected by exporting the whole database using exp tool

Bad blocks cannot be detected in the following cases:

Bad blocks above HWM will not be found.

Bad blocks in the index will not be found.

Bad blocks in the data dictionary will not be found.

Db_block_checksum and db_blockchecking parameters are comprehensively considered in combination with database performance.

When we use Recovery Manager to back up the actual database, we also have bad blocks

Check. It is important to note, however, that when you scan for bad blocks and backups online using Recovery Manager, you need to count

According to the library is running in archive mode (archive log), otherwise only if the database is not open.

For operating system problems and hardware failures, we need the cooperative support of the corresponding manufacturers. At the same time, avoid counting

According to the library host to run other user processes to avoid abnormal downtime, it will also reduce the probability of bad blocks.

Three. Identification of bad block faults

When you encounter a bad block problem, the abnormal behavior of the database usually includes:

An ORA-01578 error is reported.

An Ora-1110 error is reported.

An ORA-00600 error is reported, where the first parameter is 2000-8000woncache layer 2000-4000recoverytransaction layer 4000-6000 data layer 6000-8000.

Corrupt block dba: 0x160c5958 appears in the Trace file. Found .

Failed to analyze the object.

Background processes, such as DBWR,LGWR, wait for long exceptions, such as "LGWR wait for redo copy".

IV. Summary of Oracle Block damage recovery

You can use the DBV command to detect whether there are bad blocks:

Use the DBV command to check the data file for bad blocks before recovery

Dbv file=d:\ oracle\ oradata\ mydb\ RONLY.DBF blocksize=8192

To view the data file number and block number of the bad data block, you can scan the table once, such as:

Select count (*) from tablename

4.1 in the absence of a backup:

4.1.1. Restore using exp/imp

In this case, it is certain that the data will be lost. In this case, the method of exporting the data and then rebuilding the table and then importing it should be taken to recover the data in the damaged data block as far as possible, but the export is not allowed in the case of a bad block, as follows:

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.

4.1.2. Restore using DBMS_REPAIR

Of course, using DBMS_REPAIR also leads to data loss. There is no detailed introduction here. Those who are interested can check out the online documentation of oracle.

4.2 restore using Rman:

First there is the latest backup set for Rman, and then execute the following command:

RMAN > backup validate datafile 4

Check whether there is a bad block in the No. 4 data file.

Execute the query:

Select * from v$database_block_corruption where file#=4

If there is a bad block in file No. 4, it will be displayed in the result set, the damaged block number will be displayed, and according to the display result, execute the following command to recover:

RMAN > blockrecover datafile 4 block 35 from backupset

After the command is executed, the bad blocks can be recovered without data loss, but it requires that the database must be running in archive mode, otherwise RMAN will not work, and the latest database backup has been made through RMAN

4.3 restore using bbed

There must be a copy of the data file when using bbed to restore.

Bbed is an acronym for block browse edit, which is used to view and modify data files directly.

BBED can be found in windows 8i under $ORACLE_HOME/bin, while 9i does not seem to be released with the software, so

This tool is not available in windows, but needs to be compiled under linux:

Then add $ORACLE_HOME/rdbms/lib to the PATH of the environment variable, and you can bbed directly in the command.

The default password for BBED is blockedit

For Oracle Internal Use only, please be careful to use Oracle without technical support.

5.1. First of all, you must know that the data file is broken. Find out the file_id,relative_fno,tablespace_name of the file.

Using dba_data_files, you can query file_id (the unique serial number of the whole database) and RELATIVE_FNO (relative to the serial number in a tablespace).

5.2. Find the ID of the bad block (you can run the dbverify implementation), assuming that the bad block ID found is 1234.

5.3. Run the following query to find the corresponding owner,segment_type of the bad block based on its file_id,block id

Segment_name and other information

Select owner,file_id,segment_name, segment_type, block_id, blocks

From dba_extents

Where file_id=13 and block_id= 1234

5.4. According to the file_id,owner,segment_name,block_id of the bad block, if it is a data table, use the following query to get the rowid of the corresponding bad block

Suppose owner: DAVE

Segment_name: BL

File_id: 13

Block_id: 162

Run the following query to get the rowid contained in the block (if there is no index, you may not be able to do so in the following way):

Select / * + index (DAVE, i_test)? * / rowid

From DAVE.BL

Where dbms_rowid.rowid_to_absolute_fno (rowid,'DAVE','BL') = 13

And dbms_rowid.rowid_block_number (rowid) = 162;

Sixth, how to simulate bad blocks

The basic knowledge of DBA, there are many ways to create bad blocks, you can use ultraedit, you can also use dd commands, but also stay with orapatch tools

6.1 orapatch tool:

$orapatch open tools001.dbf write

Patch > set hex-use hexadecimal

Patch > display 177-orapatch works in 512 bytes. Suppose you want to destroy the 11th block: 8k/512*11+1 (file header)

Patch > find 00400003-- choose a point to edit

Patch > modify 00400002-destruction

Patch > exit

6.2. open datafile with an editor with a size of 8192 bytes (db's block is 8192)

Here are the first 20 bytes and the last 4 bytes of a block

06 02 00 00 08 00 c0 02 6c 43 0d 00 00 00 01 02

00 00 00-block head

06 02 6c 43-block tail the case where there is scn (6c 43) corresponds to the type (01) of block (06) and the seq: 0x01 of head. As long as you change any of the 4 bytes at the end of block or the corresponding byte at the beginning of block, there must be ora-1578, but if the database has ora-1578 error, it is not necessarily caused in this way.

Btw: UE is not very easy to use. Winhex is not bad.

6.3 BBED tools

7. How to use dbms_repair to mark and skip bad blocks

But when the amount of data is very large, or 724 system, we use dbms_repair to deal with. Dbms_repair is provided from oracle8i.

Preparatory work:

Create tablespace block datafile'/ u01bind block.dbf' size 5m

Create table DMM tablespace block as select * from all_tables

Commit

CREATE INDEX indx_dmm on DMM (TABLE_NAME)

Select count (*) from DMM

COUNT (*)

-

12896

7.1. Create an administrative table:

SQL > conn sys/admin as sysdba

Connected.

SQL > exec DBMS_REPAIR.ADMIN_TABLES ('REPAIR_TABLE',1,1,'USERS')

PL/SQL procedure successfully completed

SQL > exec DBMS_REPAIR.ADMIN_TABLES ('ORPHAN_TABLE',2,1,'USERS')

PL/SQL procedure successfully completed

7.2. Check for bad blocks: dbms_repair.check_object

/ * Formatted on 2009-12-16 23:41:32 (QP5 v5.115.810.9015) * /

Set serveroutput on

DECLARE

Cc NUMBER

BEGIN

DBMS_REPAIR.check_object (schema_name = > 'SYS',-notice here is the user name

Object_name = > 'DMM'

Corrupt_count = > cc)

DBMS_OUTPUT.put_line (TO_CHAR (cc))

END

Normally, the input is 0.

If there are bad blocks, you can view the block corruption information in the REPAIR_TABLE you created:

/ * Formatted on 2009-12-17 13:18:19 (QP5 v5.115.810.9015) * /

SELECT object_name

Relative_file_id

Block_id

Marked_corrupt

Corrupt_description

Repair_description

CHECK_TIMESTAMP

FROM repair_table

Note: under 8i, check_object will only check for bad blocks, and MARKED_CORRUPT is false, so you need to perform the third step: locate bad blocks, locate fix_corrupt_blocks, change MARKED_CORRUPT to true, and update CHECK_TIMESTAMP at the same time. The value of 9i after passing through check_object,MARKED_CORRUPT has been identified as TRUE. So we can go straight to step four.

7.3. Locate bad blocks: dbms_repair.fix_corrupt_blocks

Bad blocks can be located only after the bad block information is written to the defined REPAIR_TABLE.

/ * Formatted on 2009-12-17 13:29:01 (QP5 v5.115.810.9015) * /

DECLARE

Cc NUMBER

BEGIN

DBMS_REPAIR.fix_corrupt_blocks (schema_name = > 'SYS'

Object_name = > 'DMM'

Fix_count = > cc)

DBMS_OUTPUT.put_line (a = > TO_CHAR (cc))

END

7.4. Skip bad blocks:

We located the bad block earlier, but if we visit table:

SQL > select count (*) from SYS.DMM

ORA-01578: ORACLE block corruption (file number 14, block number 154)

ORA-01110: data file 14: d:\ BLOCK.DBF'

You still get the wrong message. Here you need to use skip_corrupt_blocks to skip bad blocks:

/ * Formatted on 2009-12-17 13:30:17 (QP5 v5.115.810.9015) * /

Exec dbms_repair.skip_corrupt_blocks (schema_name = > 'SYS',object_name = >' DMM',flags = > 1)

SQL > select count (*) from SYS.DMM

COUNT (*)

-

12850

Lost 12896 / 12850 / 46 rows of data.

7.5. Handle invalid key values on index; dump_orphan_keys

/ * Formatted on 2009-12-17 13:34:55 (QP5 v5.115.810.9015) * /

DECLARE

Cc NUMBER

BEGIN

DBMS_REPAIR.dump_orphan_keys (schema_name = > 'SYS'

Object_name = > 'INDX_DMM'

Object_type = > 2

Repair_table_name = > 'REPAIR_TABLE'

Orphan_table_name = > 'ORPHAN_TABLE'

Key_count = > CC)

END

You can know the information about the missing lines by using the following command:

SQL > SELECT * FROM ORPHAN_TABLE

Based on this result, we consider whether we need rebuild index.

7.6. Rebuild freelist:rebuild_freelists

/ * Formatted on 2009-12-17 13:37:57 (QP5 v5.115.810.9015) * /

Exec dbms_repair.rebuild_freelists (schema_name = > 'SYS',object_name = >' DMM')

Eighty. Set internal events to make exp skip bad blocks

We can use the method of setting event to deal with bad blocks: first simulate the bad blocks, and then check them with dbv, instead of dbms_repair, use the following method:

8.1. First exp the meter to test it.

In this case, if there is a backup, it needs to be restored from the backup, and if there is no backup, then the data of the bad block must be lost.

Export is not allowed at this time:

E:\ > exp system/admin file=t.dmp tables=t

The error is as follows:

The specified table is about to be exported through the regular path.

. . Exporting table T

EXP-00056: encountered ORACLE error 1578

ORA-01578: ORACLE block corruption (file number 4, block number 35)

ORA-01110: data file 4:'e:\ ORACLE\ ORADATA\ EYGLE\ BLOCK.DBF'

The export terminated successfully with warnings.

8.2. Different situations need to be treated differently, and set event cannot be used if the loss is not data but important oracle internal information.

First, you need to check for damaged objects, using the following SQL:

/ * Formatted on 2009-12-17 14:00:42 (QP5 v5.115.810.9015) * /

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

8.3. If the loss is data, ok, you can set internal events to cause exp to skip these corrupted block

10231 event specifies that the database skips corrupted blocks when performing a full table scan

ALTER SYSTEM SET EVENTS='10231 trace name context forever,level 10'

Then we can export uncorrupted data.

E:\ > exp system/admin file=t.dmp tables=t

The specified table is about to be exported through the regular path.

. . Exporting table T 8036 rows are exported

The export was successfully terminated without warning.

Then we can drop table,recreate and import the data

8.4. Rebuild the table and then import

SQL > connect sys/admin as sysdba

Connected.

SQL > drop table t

The watch has been discarded.

E:\ > imp system/admin file=t.dmp tables=t

. Importing objects from system into system

. . Importing table "T" 8036 rows are imported

The import was terminated successfully with warnings.

E:\ > exit

SQL > select count (*) from t

COUNT (*)

-

8036

Complete data recovery.

The number of rows lost can be subtracted from 2 rows:

8192-8036 = 156rows of data

8.5 if you want to unset events, do the following:

1. If you set it in the initialization parameters

Annotate

two。 If set on the command line

Alter system set events='10231 trace name context off'

The above is how the bad blocks of ORACLE are produced. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are 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.