7.3. Using Various Methods to Detect and Resolve Corruption
There are four methods for detecting corruption:
The ANALYZE TABLE table_name VALIDATE STRUCTURE command
The Oracle DBVERIFY utility used against the offline data files
The init.ora parameter DB_BLOCK_CHECKING, which checks data and index blocks each time they are created or modified
The DBMS_REPAIR package used against a table, index, or partition
Each method is described in the following sections.
7.3.1. Using the ANALYZE Utility
The ANALYZE TABLE table_name VALIDATE STRUCTURE command validates the integrity of the structure of the object being analyzed. This command is either successful or not successful at the object level. Therefore, if this command returns an error for the object being analyzed, you would need to completely rebuild it. If no error is returned, the object is free of corruption and does not need to be re-created. The following is an example of the ANALYZE command, when it detects an error and when it doesn't:
SQL> analyze table test.t3 validate structure; * ERROR at line 1: ORA-01498: block check failure - see trace file SQL> analyze table test.t3 validate structure; Table analyzed. SQL>
As you can see, using the ANAL YZE command is a straightforward process. If the object has any corrupt blocks, the statement fails. This is a good starting point for identifying a database object that is corrupt.
7.3.2. Using the DBVERIFY Utility
DBVERIFY is an Oracle utility that is used to see whether corruption exists in a particular data-file. This utility is most often used on a backup of the database or when the database is not running. However, if necessary, the tool can be used on the database when it is online to minimize availability impacts on high-use databases. The output of a DBVERIFY command verifies index and data blocks that have processed with and without error, the total number of blocks processed, empty blocks, and blocks already marked as corrupt.
NOTE
The DBVERIFY utility uses the term pages instead of blocks. This term refers to blocks within the Oracle datafile.
The Oracle DBVERIFY utility is executed by entering dbv at the command prompt. This utility has nine parameters that can be specified at execution:
FILE
START
END
BLOCKSIZE
LOGFILE
FEEDBACK
PARAFILE
USERID
SEGMENT_ID
Table 7.1 describes these parameters.
| Parameter | Description | Default Value for Parameter |
|---|---|---|
| FILE | Datafile to be verified by the utility. | No default parameter |
| START | Starting block to begin verification. | First block in the datafile |
| END | Ending block to end verification. | Last block in the datafile |
| BLOCKSIZE | Block size of database. This should be the same as the init.ora parameter DB_BLOCK_SIZE. | 8192 |
| LOGFILE | Log file to store the results of running the utility. | No default parameter |
| FEEDBACK | Displays the progress of the utility by displaying a dot for each number of blocks processed. | 0 |
| PARAFILE | The parameter file to store options if you do not want to specify these on the command line. | No default parameter |
| USERID | Username and password. | No default parameter |
| SEGMENT_ID | The segment identifier. | No default parameter |
This help information can also be seen by executing the DBV HELP=Y command, as in the following example:
C:oracleproduct10.1.0db_1database>dbv help=y DBVERIFY: Release 10.1.0.2.0 - Production on Fri Nov 5 20:08:57 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Keyword Description (Default) ---------------------------------------------------- FILE File to Verify (NONE) START Start Block (First Block of File) END End Block (Last Block of File) BLOCKSIZE Logical Block Size (8192) LOGFILE" Output Log (NONE) FEEDBACK Display Progress (0) PARFILE Parameter File (NONE) USERID Username/Password (NONE) SEGMENT_ID Segment ID (tsn.relfile.block) (NONE) C:oracleproduct10.1.0db_1database>
To run the DBVERIFY utility, the BLOCKSIZE parameter must match your database block size, or the following error will result:
C:oracleoradataora101>dbv blocksize=2048 file=users01.dbf DBVERIFY: Release 10.1.0.2.0 - Production on Fri Nov 5 20:12:14 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. DBV-00103: Specified BLOCKSIZE (2048) differs from actual (8192) C:oracleoradataora101>
Once the BLOCKSIZE parameter is set to match the database block size, the DBVERIFY utility can proceed. There are two ways to run this utility: without the LOGFILE parameter specified, and with it specified.
Let's walk through both of these examples. First, without the LOGFILE parameter set:
C:oracleoradataora101>dbv blocksize=8192 file=users01.dbf DBVERIFY: Release 10.1.0.2.0 - Production on Fri Nov 5 20:10:27 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = users01.dbf DBVERIFY - Verification complete Total Pages Examined : 640 Total Pages Processed (Data) : 91 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 18 Total Pages Failing (Index): 0 Total Pages Processed (Other): 128 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 403 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 C:oracleoradataora101>
The following code demonstrates the DBVERIFY utility with the LOGFILE parameter set. The results of this command are written to the file users01.log and not to the screen. The results can be displayed by editing the log file.
C:oracleoradataora101>dbv blocksize=8192 file=users01.dbflogfile=c: empusers01.log DBVERIFY: Release 10.1.0.2.0 - Production on Fri Nov 5 20:14:00 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. C:oracleoradataora101>
In this second example, the output from the first example would appear in the file c: emp users01.log.
The DBVERIFY utility is a useful diagnostic tool to identify the physical structure of Oracle database files.
7.3.3. Using DB_BLOCK_CHECKING
The DB_BLOCK_CHECKING initialization parameter sets block checking at the database level. The default is set to FALSE for all non-system tablespaces. The SYSTEM tablespace is enabled by default. The DB_BLOCK_CHECKING parameter can be dynamically set with the ALTER SYSTEM SET statement. This parameter forces checks for corrupt blocks each time blocks are modified at the tablespace level. A checksum occurs every time a block is modified. The following is an excerpt from an initora101.ora file that gives information on the value of this parameter:
C:oracleproduct10.1.0db_1database>edit initora101.ora db_block_checking = TRUE
As you can see, setting the DB_BLOCK_CHECKING initialization parameter is fairly simple. Adding this parameter forces block checking at the database level. Like any verification procedure, this can place overhead on the database that can affect performance. The trade-off is that the database will be checked for block corruption each time a block is modified. This will catch corruption in near real time.
7.3.4. Using the DBMS_REPAIR Package
The DBMS_REPAIR package is a set of procedures that enables you to detect and fix corrupt blocks in tables and indexes. The DBMS_REPAIR package is made up of multiple stored procedures, as described earlier. Each of these procedures performs different actions. This section focuses on the CHECK_OBJECT procedure and the FIX_CORRUPT_BLOCKS procedure. Table 7.2 shows all of the procedures available in the DBMS_REPAIR package.
The general process for resolving physical corruption is to verify that you have corrupt data blocks and to determine their extent. Next, you need to put the list of corrupt data blocks in a holding table so the corrupt blocks can be identified. These blocks are then marked as corrupt so that they can be skipped over in a query or during normal usage of the table. We will also demonstrate how to fix an index and freelists that could be impacted by physical corruption in a table.
| Procedure Name | Description |
|---|---|
| CHECK_OBJECT | Detects and reports corruptions in a table or index. |
| FIX_CORRUPT_BLOCKS | Marks blocks (that were previously identified by the CHECK_OBJECT procedure) as software corrupt. |
| DUMP_ORPHAN_KEYS | Reports index entries (into an orphan key table) that point to rows in corrupt data blocks. |
| REBUILD_FREELISTS | Rebuilds the freelists 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 an ORA-1578 error when encountering blocks marked corrupt. |
| ADMIN_TABLES | Provides administrative functions (create, drop, purge) for repair of orphan key tables. Note: These tables are always created in the SYS schema. |
Let's walk through an example of how to detect and mark corrupt blocks:
Generate a trace file of the corrupt block, which is automatically created by the ANALYZE command.
SQL> connect / as sysdba Connected. SQL> analyze table test.t3 validate structure; * ERROR at line 1: ORA-01498: block check failure - see trace file
View the trace file to determine bad block information. In this example, the bad block is 5. This is indicated by the output line nrow=5, highlighted at the end of this code listing.
Dump file c:oracleadminora101udumpora101_ora_2236.trc Fri Nov 16 16:21:35 2004 ORACLE V10.1.0.2.0 - Production vsnsta=0 *** 2004.11.16.15.53.02.000 *** SESSION ID:(11.9) 2004.05.08.11.51.09.000 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=44 ktbbhitc=1 Block header dump: 0x01800005 Object id on Block? Y seg/obj: 0xb6d csc: 0x00.1cf5f itc: 1 flg: - typ: 1 - USERS fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc 0x01 xid: 0x0003.011.00000151 uba: 0x008018fb.0645.0d --U- 4 fsc 0x0000.0001cf60 data_block_dump =============== tsiz: 0x6b8 hsiz: 0x18 pbl: 0x38088044 bdba: 0x01800008
flag=----------- ntab=1 nrow=5
Create the repair tables to store and retrieve information from running the DBMS_REPAIR package. The following is the example PL/SQL in a file called repair_tab.sql, which will be used to build the REPAIR_TABLE. This is a custom script that must be created by the DBA. After the script repair_tab.sql is run, query the DBA_OBJECTS table to verify that the REPAIR_TABLE has been created.
SQL> host repair_tab.sql -- Create DBMS Repair Table declare begin dbms_repair.admin_tables (table_name => 'REPAIR_TABLE', table_type => dbms_repair.repair_table, action => dbms_repair.create_action, tablespace => 'USERS'), end; / SQL> SQL> @repair_tab PL/SQL procedure successfully completed. SQL> select owner, object_name, object_type 2> from dba_objects 3> where object_name like '%REPAIR_TABLE'; OWNER OBJECT_NAME OBJECT_TYPE -------------- ---------------- ------------------- SYS DBA_REPAIR_TABLE VIEW SYS REPAIR_TABLE TABLE 2 rows selected. SQL>
Check the object, or table T3, to determine whether there is a corrupt block in the table. Even though you know this from the ANALYZE TABLE table_name VALIDATE STRUCTURE command, you need this information saved in REPAIR_TABLE. The following code calls the DBMS_REPAIR.CHECK_OBJECT procedure that has determined variables such as SCHEMA_NAME and OBJECT_NAME. The corrupt blocks are then stored in REPAIR_TABLE and output is sent to the screen through the DBMS_OUTPUT.PUT_LINE procedure.
SQL> host edit check_obj.sql --determine what block is corrupt in a table set serveroutput on size 100000; declare rpr_count int; begin rpr_count := 0; dbms_repair.check_object( schema_name => 'TEST', object_name => 'T3', repair_table_name => 'REPAIR_TABLE', corrupt_count => rpr_count); dbms_output.put_line('repair block count: ' ||to_char(rpr_count)); end; SQL> @check_obj.sql Server Output ON PL/SQL procedure successfully completed. repair block count: 1 SQL>
Verify that REPAIR_TABLE contains information about table T3 and the bad block. This query has been broken into three queries for display purposes:
SQL> select object_name, block_id, corrupt_type, marked_corrupt, 2 corrupt_description, repair_description 3 from repair_table; OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR ----------- -------- ------------ ---------- T3 3 1 FALSE
SQL> select object_name, corrupt_description 2 from repair_table; OBJECT_NAME CORRUPT_DESCRIPTION ----------- ------------------------------------------- T3 kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=44 ktbbhitc=1 SQL> select object_name, repair_description 2 from repair_table;2 from repair_table; OBJECT_NAME REPAIR_DESCRIPTION ----------- --------------------------- T3 mark block software corrupt
A backup of the table should be created before any attempts are made to fix the block or mark the block as corrupt. Therefore, you should attempt to salvage any good data from the corrupted block before marking it as corrupt.
SQL> connect test/test Connected. SQL> create table t3_bak as 2 select * from t3 3 where dbms_rowid.rowid_block_number(rowid) = 5 4 and dbms_rowid.rowid_to_absolute_fno (rowid, 'TEST','T3') = 4; Table created. SQL> select c1 from t3_bak; C1 -------- 1 2 3 5
Mark block 5 as corrupt, but note that full table scans will still generate an ORA-1578 error.
SQL> host edit fix_blocks.sql -- Create DBMS Fix Corrupt blocks
declare fix_block_count int; begin fix_block_count := 0; dbms_repair.fix_corrupt_blocks ( schema_name => 'TEST', object_name => 'T3', object_type => dbms_repair.table_object, repair_table_name => 'REPAIR_TABLE', fix_count => fix_block_count); dbms_output.put_line('fix blocks count: ' || to_char(fix_block_count)); end; / SQL> SQL> @fix_blocks fix blocks count: 1 PL/SQL procedure successfully completed. SQL> select object_name, block_id, marked_corrupt 2 from repair_table; OBJECT_NAME BLOCK_ID MARKED_COR ------------------------------ ---------- ---------- T3 5 TRUE SQL> select * from test.t3; select * from test.t3 * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 4, block # 5) ORA-01110: data file 4: 'C:oracleoradataora101users01.dbf'
Use the DUMP_ORPHAN_KEYS procedure to dump the index entries that point to the corrupt rows in the corrupt data blocks. This procedure displays the affected index entries. Therefore, the index will need to be rebuilt. You must first create the ORPHAN_KEY_TABLE if it has not been created:
SQL> host more orphan_tab.sql -- Create the orphan_table declare begin dbms_repair.admin_tables (table_name => 'ORPHAN_KEY_TABLE', table_type => dbms_repair.orphan_table, action => dbms_repair.create_action, tablespace => 'USERS'), end; / SQL> SQL> @orphan_tab PL/SQL procedure successfully completed.
Once the ORPHAN_KEY_TABLE is created, you can then dump the orphaned keys into this table. The following example dumps the data into the ORPHAN_KEY_TABLE:
SQL> host more orphan_dump.sql -- Create DBMS Dump orphan/Index entries declare orph_count int; begin orph_count:= 0; dbms_repair.dump_orphan_keys ( schema_name => 'TEST', object_name => 'T3_PK', object_type => dbms_repair.index_object, repair_table_name => 'REPAIR_TABLE', orphan_table_name => 'ORPHAN_KEY_TABLE', key_count => orph_count);
dbms_output.put_line('orphan-index entries: ' || to_char(orph_count)); end; / SQL> SQL> @orphan_dump orphan-index entries: 3 PL/SQL procedure successfully completed. SQL> select index_name, count(*) from orphan_key_table 2 group by index_name; INDEX_NAME COUNT(*) ------------------------------ ---------- T3_PK 3
Mark the corrupt block as skip enabled. This allows for querying the table without retrieving the corrupt block, which would trigger an ORA-1578 error:
SQL> host more corrupt_block_skip.sql -- Skips the corrupt blocks in the tables. declare begin dbms_repair.skip_corrupt_blocks ( schema_name => 'TEST', object_name => 'T3', object_type => dbms_repair.table_object, flags => dbms_repair.skip_flag); end; / SQL> @corrupt_block_skip PL/SQL procedure successfully completed.
SQL> select table_name, skip_corrupt from dba_tables 2 where table_name = 'T3'; TABLE_NAME SKIP_COR ------------------------------ -------- T3 ENABLED
Rebuild the freelists so that the corrupt block is never added to freelists of blocks. This will prevent this block from being used for future data entry. Rebuilding the freelists is performed with the procedure in the DBMS_REPAIR package called REBUILD_FREELISTS.
SQL> host more rebuild_freelists.sql -- Removes the bad block from the freelist of blocks declare begin dbms_repair.rebuild_freelists ( schema_name => 'TEST', object_name => 'T3', object_type => dbms_repair.table_object); end; / SQL> @rebuild_freelists PL/SQL procedure successfully completed.
Finally, you can rebuild the index, and then the table T3 is ready for use. SQL> drop index t3_pk; Index dropped.
SQL> drop index t3_pk; Index dropped. SQL> create index t3_pk on t3 (c1); Index created.
As you can see, using the many procedures in the DBMS_REPAIR package to resolve physical corruption can be a lengthy process. These procedures are detailed, and you must enter the right variables. Make sure your SQL session has SET SERVEROUTPUT ON SIZE 100000, which allows the procedures to return output to the SQL session. This was defined in step 4 of the preceding instructions and remains active in the example until you log out of the SQL session.
|
There are some considerations that you should be aware of when using the DBMS_REPAIR package that could impact the usage of the utility. You should be aware of these situations so you can plan the best usage of the DBMS_REPAIR package. The following is a list of best practices for dealing with corruption. Physical corruption is a serious failure, and you may need to use a combination of recovery techniques to resolve the issue.
What is the extent of the corruption? Use the CHECK_OBJECT procedure and then verify REPAIR_TABLE.
What other options are available to resolve block corruption?
Examine other methods of resolving corrupt objects if necessary, such as rebuilding an object if the data is available.
Try to ignore the corruption by excluding the corrupt row from select statements.
Perform incomplete recovery.
Real World Scenario: Keep Your Recovery Options OpenIt is always a good idea to have more than one method to recover a database. You don't ever want to be in a situation where there is only one choice. This is readily apparent with block corruption. Block corruption can be a difficult recovery process, mainly because you are recovering at a block level. Referential integrity between row data and other database objects is common on complex database applications. Triggers can also make changes to row data that must be carefully handled. All of these complications make the chances of successfully performing block corruption recovery an uncertain option. That is why you should have other methods at the database level, and for key tables, if possible. You need to make sure at a minimum that you have full RMAN backups or user-managed backups performed on a daily basis. Exporting individual key tables can be useful as well. The Flashback Tables and Flashback Versions Query can be useful for reconstructing row data. Remember, it is better to lose some data and have an operational database by performing an incomplete recovery than to not have an operational database due to corruption in a key table. |
What are the side effects of using DBMS_REPAIR on an object?
Referential integrity constraints can be broken due to block corruption. You may need to repair these constraints, which can affect multiple tables.
Triggers on tables can create logical corruption on tables because the physically corrupt rows in the database may impact the way triggers fire. Also, during the rebuild process, triggers need to be identified and thoroughly understood so that logical corruption does not get introduced.
Freelist blocks can become inaccessible due to physical corruption. You need to utilize the REBUILD_FREELISTS procedure.
Indexes can become out of sync from the table when data in the table is physically corrupt. You need to use the DUMP_ORPHAN_KEYS procedure to retrieve the necessary data.
As you can see, resolving physical corruption is a detailed and lengthy process. The DBMS_REPAIR package has many procedures designed for specific uses. It is a good idea to create small SQL scripts to run these individual procedures. You also need to make sure you are aware of all options available for resolving physical corruption. You may need to do more than just use the DBMS_REPAIR package. Follow the best practices for resolving physical corruption by determining the extent of the corruption, keeping all the recovery options open, and being aware of the impacts of using the DBMS_REPAIR package.