In this section, examples are presented reflecting the use of the DBMS _REPAIR procedures.
Using ADMIN_TABLES to Build a Repair Table or Orphan Key Table
A repair table provides information about what corruptions were found by the CHECK _OBJECT procedure and how these will be addressed if the FIX _CORRUPT_ BLOCKS procedure is run. Further, it is used to drive the execution of the FIX_ CORRUPT_BLOCKS procedure.
An orphan key table is used when the DUMP _ORPHAN _KEYS procedure is executed and it discovers index entries that point to corrupt rows. The DUMP _ORPHAN _KEYS procedure populates the orphan key table by logging its activity and providing the index information in a usable manner.
The ADMIN _TABLE procedure is used to create, purge, or drop a repair table or an orphan key table.
Creating a Repair Table
The following example creates a repair table for the users tablespace.
For each repair or orphan key table, a view is also created that eliminates any rows that pertain to objects that no longer exist. The name of the view corresponds to the name of the repair or orphan key table, but is prefixed by DBA_ (for example DBA_ REPAIR _TABLE or DBA _ORPHAN _KEY _TABLE).
The following query describes the repair table created in the previous example.DESC REPAIR_TABLE
Creating an Orphan Key Table
This example illustrates the creation of an orphan key table for the users tablespace.
The orphan key table is described in the following query:
Using the CHECK _OBJECT Procedure to Detect Corruption
The CHECK _OBJECT procedure checks the specified objects, and populates the repair table with information about corruptions and repair directives. You can optionally specify a range, partition name, or subpartition name when you would like to check a portion of an object.
Validation consists of checking all blocks in the object that have not previously been marked corrupt. For each block, the transaction and data layer portions are checked for self consistency. During CHECK _OBJECT, if a block is encountered that has a corrupt buffer cache header, then that block is skipped.
Here is an example of executing the CHECK _OBJECT procedure for the scott.dept table.
SQL*Plus outputs the following line, indicating one corruption: number corrupt: 1
Querying the repair table produces information describing the corruption and suggesting a repair action.
At this point, the corrupted block has not yet been marked corrupt, so this is the time to extract any meaningful data. After the block is marked
corrupt, the entire block must be skipped.
Fixing Corrupt Blocks with the FIX_CORRUPT_BLOCKS Procedure
Use the FIX _CORRUPT_BLOCKS procedure to fix the corrupt blocks in specified objects based on information in the repair table that was previously generated by the CHECK _OBJECT procedure. Prior to effecting any change to a block, the block is checked to ensure the block is still corrupt. Corrupt blocks are repaired by marking the block software corrupt. When a repair is performed, the associated row in the repair table is updated with a fix timestamp.
This example fixes the corrupt block in table scott.dept that was reported by the CHECK _OBJECT procedure.
SQL*Plus outputs the following line: num fix: 1 To following query confirms that the repair was done. Finding Index Entries Pointing into Corrupt Data Blocks: DUMP_ORPHAN_KEYS
The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows incorrupt data blocks. For each such index entry encountered, a row is inserted into the specified orphan key table. The orphan key table must have been previously created.This information can be useful for rebuilding lost rows in the table and for diagnostic purposes. In this example, pk_dept is an index on the scott.dept table. It is scanned to determine if there are any index entries pointing to rows in the corrupt data block.
The following line is output, indicating there are three orphan keys: orphan key count: 3 Index entries in the orphan key table implies that the index should be rebuilt. This guarantees that a table probe and an index probe return the same result set.
Rebuilding Free Lists Using the REBUILD_FREELISTS Procedure
The REBUILD _FREELISTS procedure rebuilds the free lists for the specified object. All free blocks are placed on the master free list. All other free lists are zeroed. If the object has multiple free list groups, then the free blocks aredistributed among all free lists, allocating to the different groups in round -robin fashion.
This example rebuilds the free lists for the table scott.dept.
Enabling or Disabling the Skipping of Corrupt Blocks: SKIP _CORRUPT _BLOCKS
The SKIP _CORRUPT _BLOCKS procedure enables or disables the skipping of corrupt blocks during index and table scans of the specified object. When the object is a table, skip applies to the table and its indexes. When the object is a cluster, it applies to all of the tables in the cluster, and their respective indexes.
The following example enables the skipping of software corrupt blocks for the scott.dept table:
Querying scott's tables using the DBA _TABLES view shows that SKIP _CORRUPT is enabled for table scott.dept.
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.