The following approach is recommended when considering DBMS _REPAIR for addressing data block corruption:
These tasks are discussed in succeeding sections.
Task 1: Detect and Report Corruptions
Your first task, before using DBMS _REPAIR, should be the detection and reportingof corruptions. Reporting not only indicates what is wrong with a block, but also identifies the associated repair directive. You have several options, in addition to DBMS _REPAIR, for detecting corruptions. Table describes the different detection methodologies.
Comparison of Corruption Detection Methods
DBMS_REPAIR: Using the CHECK _OBJECT and ADMIN _TABLES Procedures
The CHECK _OBJECT procedure checks and reports block corruptions for a specified object. Similar to the ANALYZE ... VALIDATE STRUCTURE statement for indexes and tables, block checking is performed for index and data blocks. Not only does CHECK _OBJECT report corruptions, but it also identifies any fixes that would occur if FIX _CORRUPT _BLOCKS is subsequently run on the object. This information is made available by populating a repair table, which must first be created by the ADMIN_TABLES procedure.
After you run the CHECK _OBJECT procedure, a simple query on the repair table shows the corruptions and repair directives for the object. With this information, you can assess how best to address the problems reported.
DB _VERIFY: Performing an Offline Database Check
Typically, you use DB_VERIFY as an offline diagnostic utility when you encounter data corruption problems.
ANALYZE: Corruption Reporting
The ANALYZE TABLE ... VALIDATE STRUCTURE statement validates the structure of the analyzed object. If the database successfully validates the structure, a message confirming its validation is returned to you. If the database encounters corruption in the structure of the object, an error message is returned to you. In this case, drop and re -create the object.
DB _BLOCK _CHECKING (Block Checking Initialization Parameter)
You can set block checking for instances using the DB _BLOCK _CHECKING initialization parameter (the default value is FALSE). This checks data and index blocks whenever they are modified. DB _BLOCK _CHECKING is a dynamic parameter, modifiable by the ALTER SYSTEM SET statement. Block checking is always enabled for the system tablespace.
Task 2: Evaluate the Costs and Benefits of Using DBMS _REPAIR
Before using DBMS_REPAIR you must weigh the benefits of its use in relation to the liabilities. You should also examine other options available for addressing corrupt objects. Begin by answering the following questions:
To determine if there are corruptions and repair actions, execute the CHECK_OBJECT procedure, and query the repair table.
It is possible that you do not have access to rows in blocks marked corrupt. However, a block could be marked corrupt even though there are still rows that you can validly access.
It is also possible that referential integrity constraints are broken when blocks are marked corrupt. If this occurs, disable and reenable the constraint; any inconsistencies are reported. After fixing all problems, you should be able to
successfully reenable the constraint. Logical corruption can occur when there are triggers defined on the table. For example, if rows are reinserted, should insert triggers be fired or not? You can address these issues only if you understand triggers and their use in your installation.
Free list blocks can become inaccessible. If a corrupt block is at the head or tail of a free list, space management reinitializes the free list. There then can be blocks that should be on a free list, but are not. You can address this by running the REBUILD _FREELISTS procedure.
Indexes and tables are out of sync. You can address this by first executing the DUMP _ORPHAN _KEYS procedure (to obtain information from the keys that might be useful in rebuilding corrupted data). Then issue the ALTER INDEX... REBUILD ONLINE statement to get the table and its indexes back in sync.
You can retrieve data from the index when a data block is marked corrupt. The DUMP _ORPHAN _KEYS procedure can help you retrieve this information. Of course, retrieving data in this manner depends on the amount of redundancy between the indexes and the table.
Task 3: Make Objects Usable
In this task DBMS _REPAIR makes the object usable by ignoring corruptions during table and index scans.
Corruption Repair: Using the FIX _CORRUPT _BLOCKS and SKIP _CORRUPT _BLOCKS Procedures
You make a corrupt object usable by establishing an environment that skips corruptions that remain outside the scope of DBMS _REPAIR capabilities.
If corruptions involve a loss of data, such as a bad row in a data block, all such blocks are marked corrupt by the FIX _CORRUPT _BLOCKS procedure. Then, you can run the SKIP _CORRUPT _BLOCKS procedure, which skips blocks marked corrupt for the object. When skip is set, table and index scans skip all blocks marked corrupt. This applies to both media and software corrupt blocks.
Implications when Skipping Corrupt Blocks
If an index and table are out of sync, then a SET TRANSACTION READ ONLY transaction can be inconsistent in situations where one query probes only the index, and then a subsequent query probes both the index and the table. If the table block is marked corrupt, then the two queries return different results, thereby breaking the rules of a read-only transaction. One way to approach this is to not skip corruptions when in a SET TRANSACTION READ ONLY transaction. A similar issue occurs when selecting rows that are chained. Essentially, a query of the same row may or may not access the corruption, thereby producing different results.
Task 4: Repair Corruptions and Rebuild Lost Data
After making an object usable, you can perform the following repair activities.
Recover Data Using the DUMP _ORPHAN _KEYS Procedures
The DUMP _ORPHAN _KEYS procedure reports on index entries that point to rows in corrupt data blocks. All such index entries are inserted into an orphan key table that stores the key and rowid of the corruption.
After the index entry information has been retrieved, you can rebuild the index using the ALTER INDEX ... REBUILD ONLINE statement.
Repair Free Lists Using the REBUILD _FREELISTS Procedure
Use this procedure if free space in segments is being managed using free lists (SEGMENT SPACE MANAGEMENT MANUAL).
When a block marked "corrupt" is found at the head or tail of a free list, the free list is reinitialized and an error is returned. Although this takes the offending block off the free list, it causes you to lose free list access to all blocks that followed the corrupt block.
You can use the REBUILD _FREELISTS procedure to reinitialize the free lists. The object is scanned, and if it is appropriate for a block to be on the free list, it is added to the master free list. Free list groups are handled by distributing blocks in an equitable fashion, one block at a time. Any blocks marked "corrupt" in the object are ignored during the rebuild.
Fix Segment Bitmaps Using the SEGMENT _FIX _STATUS Procedure
Use this procedure if free space in segments is being managed using bitmaps (SEGMENT SPACE MANAGEMENT AUTO).
This procedure either recalculates the state of a bitmap entry based on the current contents of the corresponding block, or you can specify that a bitmap entry be set to a specific value. Usually, the state is recalculated correctly and there is no need to force a setting.
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 10g Tutorial
Overview Of Administering An Oracle Database
Creating An Oracle Database
Starting Up And Shutting Down
Managing Oracle Database Processes
Managing Control Files
Managing The Redo Log
Managing Archived Redo Logs
Managing Datafiles And Tempfiles
Managing The Undo Tablespace
Using Oracle-managed Files
Using Automatic Storage Management
Managing Space For Schema Objects
Managing Partitioned Tables And Indexes
Managing Hash Clusters
Managing Views, Sequences, And Synonyms
General Management Of Schema Objects
Detecting And Repairing Data Block Corruption
Managing Users And Securing The Database
Managing Automatic System Tasks Using The Maintenance Window
Using The Database Resource Manager
Moving From Dbms_job To Dbms_scheduler
Overview Of Scheduler Concepts
Using The Scheduler
Administering The Scheduler
Distributed Database Concepts
Managing A Distributed Database
Developing Applications For A Distributed Database System
Distributed Transactions Concepts
Managing Distributed Transactions
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.