Index Online Rebuild Fails with ORA-08104: this index object <#> is being online built or rebuilt".

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Symptoms

Index online rebuild fails with error "ORA-08104: this index object 556463 is being online built or rebuilt".

Cause

First online rebuild attempt had failed.  Until SMON performs the clean up, subsequent rebuilds against the index will fail with ORA-8104 or ORA-8106.

Solution

DBMS_REPAIR.ONLINE_INDEX_CLEAN() is only available starting in release 10.2

For 10.1, you need to wait for SMON process to finish the cleanup job.

Note: SMON will perform the cleanup and does this once every 60 minutes. SMON cleanup is only successful if there are no transactions against the base table [or [sub]partition] at the time of the attempted cleanup.  In an environment where there are likely to be uncommitted transactions, this makes cleanup a bit 'hit and miss'.  To speed up the process, you can stop your application which uses the table and wait until the cleanup is done.

Get Oracle Certifications for all Exams
Free Online Exams.com

How to use DBMS_REPAIR.ONLINE_INDEX_CLEAN ?

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Goal

When you try to rebuild an index online for the second time, after terminating the first session you tried to rebuild the index online, you get the following error : 

ORA-08104: this index object ##### is being online built or rebuilt

Fix

To resolve this issue you should refer to the following method run the rebuild using DBMS_REPAIR.ONLINE_INDEX_CLEAN function:

--Parameters--:

object_id : ALL_INDEX_ID (= 0) : cleanup all index objects that qualify

> 0 : cleanup sepcified index object

wait_for_lock : LOCK_WAIT=1 : retry getting DML locks on underlying table [[sub]partition] object.

There is an internal retry limit, after which the lock get will give up

LOCK_NOWAIT=0: do not retry

--Returns--:

TRUE : all indexes specified were cleaned up

FALSE : one or more indexes could not be cleaned up. Some indexes may have been cleaned up succesfully.

--Exceptions--:

ORA-24120 covers the following:

a negative (or NULL) index object_id was specified

a negative (or NULL) wait_for_lock was specified the given object_id was not an index object that needed cleanup

--Example Usage of online_index_clean--:

DECLARE

   isClean BOOLEAN;

BEGIN

  isClean := FALSE;

WHILE isClean=FALSE

   LOOP

     isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID, DBMS_REPAIR.LOCK_WAIT);

DBMS_LOCK.SLEEP(10);

   END LOOP;

EXCEPTION

WHEN OTHERS THEN

RAISE;

END;

/

NOTE: This operation is a maintenance operation and it will take a large down window if the indexes are large.

Get Oracle Certifications for all Exams
Free Online Exams.com

Index Online Rebuild Fails with ORA-08104: this index object <#> is being online built or rebuilt".

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Symptoms

Index online rebuild fails with error "ORA-08104: this index object 556463 is being online built or rebuilt".

Cause

First online rebuild attempt had failed.  Until SMON performs the clean up, subsequent rebuilds against the index will fail with ORA-8104 or ORA-8106.

Solution

DBMS_REPAIR.ONLINE_INDEX_CLEAN() is only available starting in release 10.2

For 10.1, you need to wait for SMON process to finish the cleanup job.

Note: SMON will perform the cleanup and does this once every 60 minutes. SMON cleanup is only successful if there are no transactions against the base table [or [sub]partition] at the time of the attempted cleanup.  In an environment where there are likely to be uncommitted transactions, this makes cleanup a bit 'hit and miss'.  To speed up the process, you can stop your application which uses the table and wait until the cleanup is done.

Get Oracle Certifications for all Exams
Free Online Exams.com

Physical and Logical Block Corruptions

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Oracle classifies the Data File Block corruptions as Physical and Logical.  This is also referred as intra block corruptions.  This document is intended to provide detailed information and errors example about it.

Physical Block Corruptions

This kind of block corruptions are normally reported by Oracle with error ORA-1578 and the detailed corruption description is printed in the alert log. 

Corruption Examples are:

• Bad header - the beginning of the block (cache header) is corrupt with invalid values
• The block is Fractured/Incomplete - header and footer of the block do not match
• The block checksum is invalid
• The block is misplaced
• Zeroed out blocks / ORA-8103

Detailed Corruption Description:

Fractured Block

A Fractured block means that the block is incomplete. Information from the block header does not match the block tail.

Corrupt block relative dba: 0x0380e573 (file 14, block 58739)
Fractured block found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0380e573
last change scn: 0x0288.8e5a2f78 seq: 0x1 flg: 0x04
consistency value in tail: 0x00780601
check value in block header: 0x8739, computed block checksum: 0x2f00
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380e573 (file 14, block 58739) found same corrupted data

Bad Checksum

Block Checksums are used to identify if the block was changed by something external to Oracle and after the block was last written by Oracle.
Checksum is calculated by DBWR or direct loader before writing the block to disk and stored in the block header. Every time that the block is read and if db_block_checksum is different than false, Oracle calculates a checksum and compares it to the one stored in the block header. Reference Note 30706.1

Example of a corrupt block due to invalid checksum:

Corrupt block relative dba: 0x0380a58f (file 14, block 42383)
Bad check value found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0380a58f
last change scn: 0x0288.7784c5ee seq: 0x1 flg: 0x06
consistency value in tail: 0xc5ee0601
check value in block header: 0x68a7, computed block checksum: 0x2f00
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380a58f (file 14, block 42383) found same corrupted data

A value different than zero (0x0) in "computed block checksum" means that the checksum
differs and the result of this comparison is printed. 

Block Misplaced

This is when Oracle detected that the content of the block being read belongs to a different block and the checksum is valid:

Corrupt block relative dba: 0x0d805a89 (file 54, block 23177)
Bad header found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0d805b08 ----> Block is different than expected 0x0d805a89
last change scn: 0x0692.86dc08e3 seq: 0x1 flg: 0x04
consistency value in tail: 0x08e30601
check value in block header: 0x2a6e, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

 

Logical Block Corruptions

This is when block contains a valid checksum and the structure below the beginning of the block is corrupt (Block content is corrupt). It may cause different ORA-600 errors. 

The detailed corruption description for Logical Corruptions are not normally printed in the alert.log. DBVerify will report what is logically corrupted in the block.

Corruption Examples are:

• row locked by non-existent transaction - ORA-600 [4512],etc
•  the amount of space used is not equal to block size
• avsp bad
• etc.

When db_block_checking is enabled, it may produce the internal errors ORA-600 [kddummy_blkchk] or ORA-600 [kdBlkCheckError].

If db_block_checking is enabled and the block is already logically corrupt on disk, the next block update will mark the block as Soft Corrupt and future reads of this block will produce the error ORA-1578. In that case DBVerify reports this corruption with error "DBV-200: Block, dba <rdba>, already marked corrupted".

Get Oracle Certifications for all Exams
Free Online Exams.com

DROP MATERIALIZED VIEW hangs with 'enq: JI - contention'

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

When running a DROP MATERIALIZED VIEW the session hangs  Due to the JI contention. 

Solution

1.   Killed the running job.    Check DBMS_JOBS_RUNNING view here.

2.  Take the refresh job offline and removed the job.

3.  Finally dropped the MView BR_SEED.ZIP_US which worked , we do not see the job or the Mview any more.

Get Oracle Certifications for all Exams
Free Online Exams.com

Nonclustered Index Structures

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

• The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.
  
• The leaf layer of a nonclustered index is made up of index pages instead of data pages.
  

Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value.

The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

• If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).
  
• If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.
  

Nonclustered indexes have one row in sys.partitions with index_id >0 for each partition used by the index. By default, a nonclustered index has a single partition. When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns . Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit. For more information about allocation units, see Table and Index Organization. The page collections for the B-tree are anchored by root_page pointers in thesys.system_internals_allocation_units system view.

Get Oracle Certifications for all Exams
Free Online Exams.com

Using Clustered Indexes

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

A clustered index is particularly efficient on columns that are often searched for ranges of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. For example, if an application frequently executes a query to retrieve records between a range of dates, a clustered index can quickly locate the row containing the beginning date, and then retrieve all adjacent rows in the table until the last date is reached. This can help increase the performance of this type of query. Also, if there is a column(s) that is used frequently to sort the data retrieved from a table, it can be advantageous to cluster (physically sort) the table on that column(s) to save the cost of a sort each time the column(s) is queried.

Clustered indexes are also efficient for finding a specific row when the indexed value is unique. For example, the fastest way to find a particular employee using the unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column.

Note  PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.

Alternatively, a clustered index could be created on lname, fname (last name, first name), because employee records are often grouped and queried in this way rather than by employee ID

Get Oracle Certifications for all Exams
Free Online Exams.com