Oracle bad block repair

Oracle bad block repairs: one or more data blocks in the Oracle database (the capacity of a data block is specified by the db_block_size parameter when the database is created, the default value is 8 KB. Because normal data blocks have fixed valid content formats and Bad blocks, the database process cannot properly parse the data block content, and the database process reports errors or even hangs, cascading causes exceptions throughout the database instance. I. the causes of Bad blocks are roughly as follows: 1.1 hardware problems when the Oracle process processes a data block, it first reads it into the physical memory space. After processing, then, a specific process writes the data back to the disk. If a memory fault or CPU computing error occurs during this process, the content of the memory data block is disordered, finally, the data block content written back to the disk is incorrect. Similarly, if the storage subsystem encounters an exception, the data block is damaged. 1.2 Operating system bugs because the Oracle process reads and writes data blocks, they are all completed by calling the operating system kernel (system call). If the operating system has problems calling the kernel, this will inevitably cause the Oracle process to write illegal content. 1.3 OS I/O errors or buffer problems 1.4 memory or paging problems Oracle software bugs specific Oracle software versions may cause abnormal bugs in data block content. 1.5 Non-Oracle processes disrupt the Oracle shared memory area as described above, when the data block content is read into the host's physical memory, if other non-Oracle processes, the shared memory area used by Oracle is disrupted, leading to confusion in the data block content written back to the disk. 1.6 abnormal shutdown, power loss, service termination, abnormal shutdown, power loss, service termination, abnormal process termination, and damage the integrity of data blocks, resulting in Bad blocks. Note: This is why the database cannot be started due to sudden power outages. The reason for the formation of Bad blocks is complex. When a bad block occurs, in order to find the exact cause, a large amount of analysis time and troubleshooting operations are required, and you even need to reproduce the block multiple times to find out the root cause. However, when a fault occurs in the production system, we will implement emergency contingency measures as soon as possible to ensure the availability of the system to reduce the downtime, thus damaging the faulty site, the root cause analysis is more difficult. 2. Prevention of Bad blocks is highly destructive, but it is not unavoidable. 2.1 On the Metalink.oracle.com website, Oracle regularly releases the "known issues description" based on a specific software version ". Oracle software bugs that may cause bad blocks are addressed as high-severity issues within Oracle. In the "known issues description, these bugs are marked as critical (Noticable) Problems (marked as * or +). Oracle will issue a warning (Alert) notice in some cases. In this document, Oracle provides patches or countermeasures. 2.2 Oracle provides the Backup Recovery tool-Recovery Manager, which provides the ability to scan files to check Bad blocks. On the Recovery Manager interface, use: RMAN> backup check logical validate datafile n; to CHECK whether the data file contains bad blocks without generating actual BACKUP output. 2.3 Dbv tool check Note: Because dbv requires that the file must be followed by a file extension, if you store it on a bare device, you must use ln to link the bare device to a file, then, use dbv to check the linked file. Analyze table tablename validate structure cascade it performs a bad block check, but does not mark the bad block as upt. The detection results are saved in the user trace file under the USER_DUMP_DEST directory. 2.4 export the entire database using the exp tool to detect Bad blocks that cannot be detected in the following situations: the Bad blocks above the HWM are the bad blocks in the index that will not be found. The Bad blocks in the data dictionary will not be found. Combined with the database performance, the db_block_checksum and db_blockchecking parameters are considered comprehensively. When we use the Recovery Manager for actual database backup, we also perform a bad block check. However, when you use the Recovery Manager to scan Bad blocks and backups online, you need to run the data library in archive log ), otherwise, it can only be performed if the database is not opened. For operating system problems and hardware faults, the corresponding vendor's support is required. At the same time, avoid running other user processes on the Data Warehouse host, avoid abnormal downtime, and reduce the probability of Bad blocks. Iii. Identify bad block faults when encountering bad block problems, the abnormal performance of the database is usually: Report ORA-01578 errors. Reports Ora-1110 errors. Reports A ORA-00600 error where the first parameter is 2000-8000, Cache layer 2000-4000, Transaction layer 4000-6000, Data layer 6000-8000. The Corrupt block dba: 0x160c5958. found is displayed in the Trace file. Failed to analyze the object. Background processes, such as DBWR and LGWR, have long abnormal waits, such as "LGWR wait for redo copy ". 4. in summary of Oracle data block damage recovery, you can use the DBV command to check whether there are bad blocks. Before restoration, use the DBV command to check whether there are bad blocks in the data file. dbv file = d: \ oracle \ oradata \ mydb \ RONLY. DBF blocksize = 8192 view the data file number and block number of the bad data block. You can perform a full table scan on the table, for example, select count (*) from tablename, refer to blog: http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5015164.aspx 4.1 If no backup is available: 4.1.1, using exp/imp to restore will definitely cause data loss, in this case, you should export the data, recreate the table, and then import the data to restore the damaged data blocks as much as possible. However, export is not allowed in the case of bad blocks, run the following command: Exp test/test file = t. dmp tables = t; the Export command reports a ORA-01578 error during execution, prompting the file of that file number and which block of this file is corrupt, such as: ORA-01578: ORACLE data block corruption (File No. 4, Block No. 35) for the above prompt, first query the objects damaged: 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 damaged Bad blocks are indexes, which can be solved by re-indexing. If the damaged data (segment_type is table), you can set the following internal event so that the Exp operation skips the Bad blocks. Alter session set events = '10231 trace name context forever, level 10'; then run the Export command again to export the relevant Table, and then run the Drop Table command to delete the relevant Table, then, recreate the table and finally import the data. 4.1.2. Using DBMS_REPAIR to restore using DBMS_REPAIR will also lose data. I will not introduce it in detail here. If you are interested, you can refer to oracle online document 4.2 to use Rman for recovery: First, you must have the latest Rman backup set and then execute the following command: RMAN> backup validate datafile 4; check whether the data file no. 4 contains bad blocks for query: select * from v $ database_block_partition uption where file # = 4; if the file no. 4 contains bad blocks, the damaged block number will be displayed in the result set. Execute the following command based on the displayed result to restore: RMAN> blockrecover datafile 4 block 35 from backupset; after the command is executed, the broken blocks can be restored without causing data loss. However, the database must be running in archive mode. Otherwise, RMAN cannot function, in addition, we have performed the latest database backup through RMAN. 4.3 When Using bbed for restoration, We must copy data files. Bbed is short for block browse edit. It is a tool used to directly view and modify data in data files. BBED can be found in windows 8i under $ ORACLE_HOME/bin. 9i does not seem to be released with the software. Therefore, this tool is not available in windows and needs to be compiled in linux: then add $ ORACLE_HOME/rdbms/lib to the PATH of the environment variable, and then you can directly run bbed in the command. The default password For BBED is blockedit. For Oracle Internal Use only, be cautious when using Oracle without technical support. For details about how to use BBED, refer to blog: http://blog.csdn.net/tianlesoftware/archive/2009/12/14/5006580.aspx 5. For more information about how to find the name and rowid of the data table contained in Bad blocks, see the blog: http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx 5.1. first, you must know that the data file is broken. Find the file_id, relative_fno, and tablespace_name of the file. You can use dba_data_files to query file_id (the unique sequence number of the entire database) and RELATIVE_FNO (relative to the sequence number in. find the bad block ID (which can be implemented by running dbverify), and assume that the bad block ID is 1234. 5. 3. run the following query. Find the owner, segment_type, segment_name, and other information of the block Based on the file_id and block id of the Bad block. select owner, file_id, segment_name, segment_type, block_id, blocks from dba_extents where file_id = 13 and block_id <= 1234 and (block_id + blocks-1)> = 1234; 5.4. based on the file_id, owner, segment_name, block_id of the Bad Block, if it is a data table, use the following query to obtain the rowid of the corresponding bad block. Assume owner: DAVE segment_name: BL file_id: 13 block_id: 162 run the following query to obtain the rowid contained in the block (the following method may not be available if no index is available): 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; 6. How to simulate the basic knowledge of bad DBA, there are many ways to create bad blocks. You can use ultraedit or dd commands, and use orapatch tool 6.1 orapatch: $ orapatch open tools001.dbf writepatch> set hex -- use a hex patch> display 177 -- orapatch uses the 512-byte working mode. If you want to destroy 11th blocks, it is: 8 k/512*11 + 1 (file header) patch> find 00400003 -- select a vertex to be edited patch> modify 00400002 -- destroy patch> exit 6.2 use the editor to open datafile in 8192 bytes (db block is 8192) the following are the starting 20 bytes of a block and the ending 4 bytes 06 02 00 00 08 00 c0 02 6c 43 0d 00 00 00 01 0200 00 00 00 -- block head 06 02 6c 43 -- block tail has scn (6c 43) the block (06) type (01) corresponds to the seq: 0x01 of the head, any one of the four bytes at the end of the block, or the corresponding byte at the beginning of the block, will have a ora-1578, but if the database has an error of the ora-1578, it is not necessarily caused by the following btw: UE is not very easy to use winhex. It's good. Oh 6.3. For details about how to use BBED, refer to blog: http://blog.csdn.net/tianlesoftware/archive/2009/12/14/5006580.aspx 7. How to Use dbms_repair to mark and skip Bad blocks, but when the data volume is large or 7*24, we use dbms_repair for processing. Dbms_repair is provided from oracle8i. Preparations: create tablespace block datafile '/u01/block. dbf 'size 5 M; 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 a management table: SQL> conn sys/admin as sysdba; connected. SQL> exec values ('repair _ table', 'users'); PL/SQL procedure successfully completed SQL> exec DBMS_REPAIR.ADMIN_TABLES ('orphan _ table, 'users'); PL/SQL procedure successfully completed 7. 2. check Bad blocks: partition/* Formatted on 23:41:32 (QP5 v5.115.810.9015) */Set serveroutput on; DECLARE cc NUMBER; BEGIN dbms_repair.check_object (schema_name => 'sys ', -- Note Here is the username 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 created REPAIR_TABLE:/* Formatted on 13:18:19 (QP5 partition) */SELECT object_name, relative_file_id, block_id, marked_corrupt, corrupt_description, repair_description, CHECK_TIMESTAMP FROM repair_table, Fix_corrupt_blocks, modify MARKED_CORRUPT to true, and update CHECK_TIMESTAMP. After 9i, after check_object, the value of MARKED_CORRUPT has been identified as TRUE. So you can proceed directly to step 4.. Locate the Bad Block: dbms_repair.fix_corrupt_blocks can locate the bad block only after writing the bad block information to the defined REPAIR_TABLE. /* Formatted on 13:29:01 (QP5 v5.115.810.9015) */DECLARE cc NUMBER; BEGIN partition (schema_name => 'sys ', object_name => 'dmm', fix_count => cc ); DBMS_OUTPUT.put_line (a => TO_CHAR (cc); END; 7. 4. skip Bad blocks: Although we have previously located Bad blocks, if we access table: SQL> select count (*) from SYS. DMM; ORA-01578: ORACLE data BLOCK corruption (file number 14, BLOCK number 154) ORA-01110: Data File 14: 'd: \ BLOCK. dbf' still gets the error message. Skip_corrupt_blocks must be used to skip Bad blocks:/* Formatted on 13:30:17 (QP5 v5.115.810.9015) */exec partition (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 the index; dump_orphan_keys/* Formatted on 13:34:55 (QP5 v5.115.810.9015) */DECLARE cc NUMBER; BEGIN partition (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 use the following command to know the information about the lost rows: SQL> SELECT * FROM ORPHAN_TABLE; based on this result, we can determine whether to rebuild the index. 7. 6. rebuilding f Reelist: rebuild_freelists/* Formatted on 13:37:57 (QP5 v5.115.810.9015) */exec Merge (schema_name => 'sys ', object_name => 'dmm'); 8. set internal events so that exp can skip Bad blocks. We can use the event setting method to handle bad blocks: First simulate Bad blocks, and then use dbv to check. At this time, dbms_repair is not required, the following method is used: 8.1. in this case, if there is a backup, it needs to be restored from the backup. If there is no backup, the data in the bad part must be lost. export is not allowed at this time: E: \> exp system/admin file = t. dmp tables = t the following error is reported: the specified table will be exported through the regular path ..... exporting table TEXP-0005 6: ORACLE error 1578ORA-01578: ORACLE data BLOCK corruption (file number 4, BLOCK number 35) ORA-01110: data file 4: 'e: \ ORACLE \ ORADATA \ EYGLE \ BLOCK. dbf' the export is successfully terminated with a warning. 8.2. Different situations need to be treated differently. If the loss is not data but important internal oracle information, set event cannot be used. First, you need to check the corrupted object and use the following SQL:/* Formatted on 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 data is lost, OK, you can set internal events, cause exp to skip these corrupted block 10231 EVENTS. Specify that the database skips the damaged block alter system set events = '1996 trace name context forever, level 10' during full table scan '; then we can export uncorrupted data E: \> exp system/adm In file = t. dmp tables = t: the specified table to be exported will be exported through the regular path... the exported table T 8036 rows will be exported successfully without warning. Then we can drop table, recreate, then import data 8.4. recreate the table, and then import SQL> connect sys/admin as sysdba connected. SQL> drop table t; the table is discarded. E: \> imp system/admin file = t. dmp tables = t. importing the system object to system .. importing table "T" 8036 rows is being imported successfully to terminate the import, but a warning is displayed. E: \> exitSQL> select count (*) from t; COUNT (*) -------- 8036 complete data recovery. the number of lost rows can be reduced from 2 to 8192. If you want to cancel the events setting, perform the following operations: 1. if you set the comment in the initialization parameter 2. if you set alter system set events = '1996 trace name context off' on the command line ';