Oracle索引index

Oracle内部错误:ORA-00600[25012]一例

2009/06/01 by admin Leave a Comment

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

SQL> select count(*) from WWork;

COUNT(*)
----------
116114

select count(*) from WWork where Work_WorkID = 100;
select count(*) from WWork where Work_WorkID = 100
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [],
[]

Tablespace id 15 is the same where the index is created.

analyze table livelink.WWORK validate structure cascade;

analyze table livelink.WWORK validate structure cascade
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [],
[]

analyze index livelink.WWORK_PRIMARY validate structure;
analyze index livelink.WWORK_PRIMARY validate structure
*
ERROR at line 1:
ORA-08100: index is not valid - see trace file for diagnostics

I'm wondering if the issue could be resolved recreating the index.

alter index WWORK_PRIMARY rebuild online noparallel;

On the alert log file, ORA-00600 began at:
Sun Jun 5 23:29:10 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_26554.trc:
ORA-00600: internal error code, arguments: [ktbair1], [1], [6], [], [], [], [], []
Mon Jun 6 00:35:51 2011
Thread 1 advanced to log sequence 303722
Current log# 19 seq# 303722 mem# 0: /u002/oradata/motpcom/redo19.log
Mon Jun 6 00:35:51 2011
And then below error often raised in alert log:
Mon Jun 6 02:45:00 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_28348.trc:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [], []
Mon Jun 6 02:50:01 2011
I also found other errors:
Mon Jun 6 05:00:01 2011
ORA-01555 caused by SQL statement below (Query Duration=18448 sec, SCN: 0x09f0.52bb91c8):
Mon Jun 6 05:00:01 2011
SELECT COUNT(d.dataid)
FROM allemployees a, networks_dataids d, kuaf k, dversdata v
WHERE k.name=a.user_id(+) AND d.userid=k.id AND a.user_id is null
Mon Jun 6 05:00:13 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_28348.trc:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [], []
Mon Jun 6 05:05:13 2011
And
Tue Jun 7 02:02:14 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_25842.trc:
ORA-07445: exception encountered: core dump [00000001011B8AD8] [SIGSEGV] [Address not mapped to object] [0x000000A88] [] []
Tue Jun 7 02:03:11 2011
And
Tue Jun 7 02:41:35 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_27325.trc:
ORA-00600: internal error code, arguments: [2662], [2544], [1396224900], [28954], [3445424704], [786437], [], []
Tue Jun 7 02:41:35 2011
Errors in file /u001/app/oracle/admin/motpcom/udump/motpcom_ora_27325.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [2544], [1396224900], [28954], [3445424704], [786437], [], []
Tue Jun 7 02:41:59 2011 

SQL> select count(*) FROM WWork where Work_WorkID=100;
select count(*) FROM WWork where Work_WorkID=100
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25012], [15], [8], [], [], [], [],
[]


SQL> alter index WWORK_PRIMARY rebuild online noparallel;

Index altered.

SQL> select count(*) FROM WWork where Work_WorkID=100;

COUNT(*)
----------
0

Index corruptions do not always mean that there is a Bug.
Indexes corrupt when the transaction ID that associates it with its data does not match.
In majority of the cases, this happens when a table has a large amount of DDL or DML processing as in a OLAP
processing. The index buffer cache is over written with an incorrect transaction id and an error results.
The error could be a ORA-00600 [25027] error, an ORA-8102/8103, or another ORA-00600 error.
Possibly, adding another index may resolve the issue of relying on just one index.

In addition, the index space must be reviewed to determine whether there is enough space.
As in the first example above, if there isn't space, then the index can be overwritten and an error can appear.
I suggest reading My Oracle Knowledge Note 33343.1 "How to Find Out How Much Space an Index is Using" which provides select
statements to show the actual usage of blocks within an index. This gives an idea of how 'full' an index is and
allows the DBA to adjust next extent sizes etc.

In addition, you can start the Index Tuning Wizard from Enterprise Manager in order to get advice on the indexes within the database.

We would like to emphasize that the best method to resolve a corrupt index is to drop and recreate it and not use a rebuild.
If this index corrupts again, then we suggest that it be dropped and recreated.


ORA-600 [25012] "Relative to Absolute File Number Conversion Error"


Note: For additional ORA-600 related information please read Note:146580.1

PURPOSE:            
  This article discusses the internal error "ORA-600 [25012]", what 
  it means and possible actions. The information here is only applicable 
  to the versions listed and is provided only for guidance.
 
ERROR:              
  ORA-600 [25012] [a] [b] [c]
 
VERSIONS:
  versions 8.0 and above
 
DESCRIPTION:

  We are trying to generate the absolute file number given a tablespace 
  number and relative file number and cannot find a matching file number
  or the file number is zero.
 
ARGUMENTS:          
  Arg [a] Tablespace Number 
  Arg [b] Relative file number
  Arg [c] Absolute file number (This arg is present is more recent releases)
 
FUNCTIONALITY:      
  KERNEL FILE MANAGEMENT TABLESPACE COMPONENT
 
IMPACT:             
  POSSIBLE PHYSICAL CORRUPTION
 
SUGGESTIONS:        

  The possibility of physical corruption exists.

  Obtain the trace files and alert.log for this error and log a Service Request
  with Oracle Support Services for diagnosis.

  If the Arg [b] Relative file number returns 0 (zero), look for fake indexes
  that can cause this error.

  The following query list fake indexes :

  select a.*,b.flags from dba_objects a, sys.ind$ b
  where a.object_id = b.obj#
  and bitand(b.flags,4096)=4096;

  Known Issues:




Known Bugs
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:


    NB	Bug	Fixed	Description
    	5653641 	11.2.0.1 	Corrupt dictionary from DROP TABLESPACE containing _offline_rollback_segments
    * 	8198906 	10.2.0.5, 11.2.0.1 	OERI [kddummy_blkchk] / OERI [5467] for an aborted transaction of allocating extents
    	4925342 	9.2.0.8, 10.2.0.3, 11.1.0.6 	OERI [25027] / OERI [25012] on IOT analyze estimate statistics
    	3258674 	9.2.0.7, 10.1.0.4, 10.2.0.1 	QMN process may dump or raise an internal error during DML
    	4186885 	10.2.0.1 	Partition numbers for IOT index/overflow segments are not synchronized
    	3751874 	9.2.0.6, 10.1.0.4, 10.2.0.1 	OERI[25012] can occur when _old_connect_by_enabled set to true
    	3430832 	9.2.0.6, 10.1.0.3, 10.2.0.1 	OERI[25012] in DML after ONLINE create index against PARTITIONED table
    	3915343 	9.2.0.7, 10.1.0.5, 10.2.0.1 	OERI [25012] on COMMIT with refresh ON-COMMIT Materialized view over cluster
    	4305391 	9.2.0.7, 10.1.0.5, 10.2.0.1 	OERI[25012] with kditalp can occur during a temporary LOB operation
    P* 	6047085 		Linux x64-64: SGA corruption / crash following any ORA-7445
    	2526334 	9.2.0.3, 10.1.0.2 	OERI:25012 from AND EQUAL with B-Tree and DOMAIN index
    	2531519 	9.2.0.3, 10.1.0.2 	OERI:[25012] from parallel direct load of bitmap managed segments
    	3150268 	9.2.0.5, 10.1.0.2 	OERI[kcbgcur_1] / OERI:25012 deleting rows from PARENT table with LIST subpartitions
    	3070856 	9.2.0.5, 10.1.0.2 	OERI:12700 / 25012 / ktrexc_1 on transported tablespace with SMU
    	3771508 	9.2.0.7 	OERI[kcbgtcr_1] / OERI[25012] selecting from V$AQ in a shared server
    	3900237 	9.2.0.7 	OERI[25012] changing length of indexed column for a global temporary table
    	1834530 	8.1.7.4, 9.0.1.4, 9.2.0.1 	OERI:25012 / wrong results after EXCHANGE 
        PARTITION with indexes with different FREELIST /FREELIST GROUPS
    	1698789 	9.2.0.1 	Wrong results, ORA-1410, ORA-8103, OERI:25012 on SELECT of UNSCOPED REF with ROWID
    	2189615 	8.1.7.4, 9.0.1.4, 9.2.0.1 	OERI:25012 / OERI:504 [cache buffers chains] selecting from certain V$ tables
    	1784708 	8.1.7.3, 9.0.1.1, 9.2.0.1 	OERI:KCBGTCR_1/OERI:25012 accessing LONG / LONG RAW in a HASH CLUSTER
    	1872985 	9.0.1.2, 9.2.0.1 	Dump / OERI:25012 from BITMAP CONVERSION of INDEX on GLOBAL TEMPORARY TABLE
    	1968815 	9.0.1.2, 9.2.0.1 	OERI:25012 possible from SMON following DROP TABLESPACE command
    	2287815 	9.0.1.4, 9.2.0.1 	OERI:KTSPISCHNT / OERI:25012 after exchange PARTITION with bitmap managed segments
    	2184731 	8.1.7.4, 9.2.0.1 	OERI:25012 possible from index prefetch
    	1837529 	8.1.7.3, 9.0.1.1, 9.2.0.1 	OERI:KFTR2BZ_1/OERI:25012 from CREATE sub-partitioned local INDEX ONLINE
    	2214167 	9.0.1.4, 9.2.0.1 	OERI:25012 / wrong results possible after TRUNCATE of bitmap managed index
    	2212389 	9.0.1.4, 9.2.0.1 	OERI:25012 / Cursor frame memory corruption when cursor with BINDS aged out / reloaded
    	1788648 	8.1.7.3, 9.0.1.1, 9.2.0.1 	OERI:25012 [2147483647] possible selecting from certain V$ views
    	1527982 	8.1.7.2, 9.0.1.0 	OERI:25012 / Bitmap indextable mismatch after UPDATE of PARTITION KEY moves rows
    	1949273 	8.1.7.3, 9.0.1.0 	OERI:25012 / ORA-1555 / ORA-22922 accessing LOB data after ALTER TABLE MOVE lob
    	1264970 	8.1.7.1, 9.0.1.0 	OERI:25012 / OERI:6050 possible coalescing index with freelists
    	1396571 	8.0.6.3, 8.1.6.3, 8.1.7.1, 9.0.1.0 	OERI:25012 possible importing with TRANSPORT_TABLESPACE=Y
    	1678963 	8.1.7.2, 9.0.1.0 	OERI:25012 / OERI:4142 possible on TRUNCATE of a table with a CLOB column
    	1297674 	8.0.6.2, 8.1.6.3, 8.1.7.0 	OERI:25012 Analyzing partitioned table with ESTIMATE
    	1138239 	8.1.6.2, 8.1.7.0 	OERI-25012 possible on direct read from plugged in (transportable) tablespace
    	1228658 	8.1.6.2, 8.1.7.0 	Create INDEX on SNAPSHOT/MV can produce corrupt index (OERI:13004 / OERI:25012 / ORA-1499)
    	1108002 	8.1.7.0 	OERI:25012 when DBMS_STATS tries to gather stats for a GLOBAL TEMPORARY table
    	986928 	8.1.7.0 	DBMS_SPACE.UNUSED_SPACE reports OERI:25012 for TEMPORARY tables
    	1312233 	8.1.6.3, 8.1.7.0 	OERI:25012 / OERI:KCBGCUR_1 possible on PQ STAR query with PARTITIONED fact table
    	718499 	8.0.6.1, 8.1.5.0 	OERI:25012 possible on OPS parallel create index
    	677243 	8.0.6.0 	OERI:kdddgbX on DELETE / UPDATE / SELECT for UPDATE with an invalid ROWID
    	679817 	8.0.5.1 	OERI:25012 from view containing 2+ LOB columns
    	595698 	8.0.4.3, 8.0.5.0 	DELETE on table with many child constraints may dump.

        '*' against a bug indicates that an alert exists for that issue.
        '+' indicates a particularly notable bug.
        'P' indicates a port specific bug.
        "OERI:nnnnn" is used as shorthand for ORA-600 [nnnnn]. 

Symptom(s)
~~~~~~~~~~

ORA-600 [25012] errors with same first argument and different
second arguments (second arguments changes during every SELECT Run)

Eg.:

ORA-600 [25012], [5], [1023], [], [], [], []


The first argument corresponds to a tablespace and second argument
corresponds to a file number.

The tablespace points to an index tablespace


Cause
~~~~~

Bug:2184731


Fix
~~~~

Bug:2184731 is fixed in 8.1.7.4 and 9.2

The workaround is to set _db_file_noncontig_mblock_read_count=1.


References
~~~~~~~~~~

Note:100073.1  ORA-600 [25012] "Relative to Absolute File Number 
                 Conversion Error"

Bug:2184731    ORA-600 [25012] WHEN SELECT FROM A TABLE USING AN INDEX

Filed Under: Oracle, Oracle Bug Tagged With: ora-00600, Oracle索引index

Script:To Report Information on Indexes

2007/06/30 by admin Leave a Comment

Reports index fragmentation statistics:

==========
Script #1:
==========

SET ECHO off
REM NAME:   TFSIFRAG.SQL
REM USAGE:"@path/tfsifrag schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Reports index fragmentation statistics
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                     Index Fragmentation Statistic
REM
REM    index name        S_EMP_USERID_UK
REM    leaf rows deleted            0
REM    leaf rows in use            25
REM    index badness            0.000
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr  = &&1
def name  = &&2

ttitle -
  center 'Index Fragmentation Statistic'   skip 2

set heading off

col name                 newline
col lf_blk_rows          newline
col del_lf_rows          newline
col ibadness newline

validate index &ownr..&name;

select
  'index name        '||name,
  'leaf rows deleted '||to_char(del_lf_rows,'999,999,990')  del_lf_rows,
  'leaf rows in use  '||to_char(lf_rows-del_lf_rows,'999,999,990')  lf_blk_rows,
  'index badness     '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness
from
  index_stats
/

undef ownr
undef name
set verify on


==============
Sample Output:
==============

                         Index Fragmentation Statistic


index name                   S_EMP_USERID_UK
leaf rows deleted            0
leaf rows in use             25
index badness                0.000




==========
Script #2:
==========

SET ECHO off
REM NAME:   TFSISTAT.SQL
REM USAGE:"@path/tfsistat schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Report index statistics.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                                Index Statistics
REM
REM    S_EMP_USERID_UK
REM    ----------------------------------------------------------
REM    height                          1
REM    blocks                          5
REM    del_lf_rows                     0
REM    del_lf_rows_len                 0
REM    distinct_keys                  25
REM    most_repeated_key               1
REM    btree_space                 1,876
REM    used_space                    447
REM    pct_used                       24
REM    rows_per_key                    1
REM    blks_gets_per_access            2
REM    lf_rows                        25            br_rows               0
REM    lf_blks                         1            br_blks               0
REM    lf_rows_len                   447            br_rows_len           0
REM    lf_blk_len                  1,876            br_blk_len            0
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr        = &&1
def name        = &&2

ttitle -
  center  'Index Statistics'  skip 2

set heading off

col name   newline
col headsep              newline
col height               newline
col blocks               newline
col lf_rows              newline
col lf_blks        	 newline
col lf_rows_len          newline
col lf_blk_len           newline
col br_rows              newline
col br_blks              newline
col br_rows_len          newline
col br_blk_len           newline
col del_lf_rows          newline
col del_lf_rows_len      newline
col distinct_keys        newline
col most_repeated_key    newline
col btree_space          newline
col used_space    	 newline
col pct_used             newline
col rows_per_key         newline
col blks_gets_per_access newline

validate index &ownr..&name;

select
  name,
  '----------------------------------------------------------'    headsep,
  'height               '||to_char(height,     '999,999,990')     height,
  'blocks               '||to_char(blocks,     '999,999,990')     blocks,
  'del_lf_rows          '||to_char(del_lf_rows,'999,999,990')     del_lf_rows,
  'del_lf_rows_len      '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len,
  'distinct_keys        '||to_char(distinct_keys,'999,999,990')   distinct_keys,
  'most_repeated_key    '||to_char(most_repeated_key,'999,999,990') most_repeated_key,
  'btree_space          '||to_char(btree_space,'999,999,990')       btree_space,
  'used_space           '||to_char(used_space,'999,999,990')        used_space,
  'pct_used                     '||to_char(pct_used,'990')          pct_used,
  'rows_per_key         '||to_char(rows_per_key,'999,999,990')      rows_per_key,
  'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access,
  'lf_rows      '||to_char(lf_rows,    '999,999,990')||'        '||+
  'br_rows      '||to_char(br_rows,    '999,999,990')                  br_rows,
  'lf_blks      '||to_char(lf_blks,    '999,999,990')||'        '||+
  'br_blks      '||to_char(br_blks,    '999,999,990')                  br_blks,
  'lf_rows_len  '||to_char(lf_rows_len,'999,999,990')||'        '||+
  'br_rows_len  '||to_char(br_rows_len,'999,999,990')                  br_rows_len,
  'lf_blk_len   '||to_char(lf_blk_len, '999,999,990')||'        '||+
  'br_blk_len   '||to_char(br_blk_len, '999,999,990')                br_blk_len
from
  index_stats
/

undef ownr
undef name
set verify on


==============
Sample Output:
==============

                                Index Statistics
S_EMP_USERID_UK
----------------------------------------------------------
height                          1
blocks                          5
del_lf_rows                     0
del_lf_rows_len                 0
distinct_keys                  	25
most_repeated_key               1
btree_space                 	1,876
used_space                    	447
pct_used                       	24
rows_per_key                    1
blks_gets_per_access            2
lf_rows                		25
br_rows                 	0
lf_blks				1
br_blks                 	0
lf_rows_len           		447
br_rows_len            		0
lf_blk_len          		1,876
br_blk_len              	0




==========
Script #3:
==========

SET ECHO off
REM NAME:   TFSIKEYS.SQL
REM USAGE:"@path/tfsikeys idx_owner table_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on DBA_IND_COLUMNS and DBA_INDEXES
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Shows the index keys for a particular table.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM             Index Keys Summary
REM
REM    Uniqueness                Index Name                    Column Name
REM    ---------- ----------------------------------------  ------------------
REM    UNIQUE                    SCOTT.S_EMP_ID_PK               ID
REM
REM    UNIQUE                    SCOTT.S_EMP_USERID_UK           USERID
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ixowner	= &&1
def tabname	= &&2

ttitle -
   center  'Index Keys Summary'  skip 2

col uniq    format a10 heading 'Uniqueness'  justify c trunc
col indname format a40 heading 'Index Name'  justify c trunc
col colname format a25 heading 'Column Name' justify c trunc

break -
  on indname skip 1 -
  on uniq

select
  ind.uniqueness                  uniq,
  ind.owner||'.'||col.index_name  indname,
  col.column_name                 colname
from
  dba_ind_columns  col,
  dba_indexes      ind
where
  ind.owner = upper('&ixowner')
    and
  ind.table_name = upper('&tabname')
    and
  col.index_owner = ind.owner
    and
  col.index_name = ind.index_name
order by
  col.index_name,
  col.column_position
/

undef ixowner
undef tabname
set verify on


==============
Sample Output:
==============


         Index Keys Summary


Uniqueness                Index Name                    Column Name
---------- ---------------------------------------- ----------------------
UNIQUE                SCOTT.S_EMP_ID_PK                        ID

UNIQUE                SCOTT.S_EMP_USERID_UK                    USERID

Filed Under: Oracle, Oracle脚本script Tagged With: Oracle索引index, Oracle脚本script

Skip Scanning of Indexes Advantages

2007/01/24 by admin Leave a Comment

What is skip scanning?

In prior releases, a composite index would be used to retrieve the records, if the index prefix (leading) column was included in the predicate of the statement. With Oracle9i, the optimizer can use a composite index even if the prefix column value is not known. The optimizer uses an algorithm called skip scanning to retrieve ROWIDs for values that do not use the prefix column.

How does a skip scan work?

During a skip scan, the B*-tree is probed for each distinct value in the prefix column. Under each prefix column value, the normal search algorithm takes over. The result is a series of searches through subsets of the index, each of which appears to result from a query using a specific value of the prefix column. However, with the skip scan, the value of the prefix column in each subset is obtained from the initial index probe rather than from the command predicate.

The optimizer uses the statistics to determine whether a skip scan retrieval would be more efficient than a full table scan, or other possible retrieval paths, when parsing SQL statements.

Suppose a composite index exists on the two columns, LANGUAGE andTERRITORY, with LANGUAGE as the prefix column. The data values stored in the underlying table result in the combinations of values in the table, as shown below. Each combination can occur multiple times in the table and the resulting index.
In previous releases without the skip scan algorithm, a query on a value in theTERRITORY column was forced to execute a full table scan or a fast full index scan. If the query were more common, then you might have needed to add a new index on the TERRITORY column alone. This new index, could negatively impact the performance of DML on the table.
The skip scan solution provides an improvement without the need for the second index. While not as fast as a direct index look up, the skip scan algorithm is faster than a full table scan in cases where the number of distinct values in the prefix column is relatively low.

Note: The skip scan feature cannot be turned on and off by the user. This feature is always on and is transparent to the users.

What are the advantages of skip scanning?

Skip scans reduce the need to add an index to support occasional queries that do not reference the prefix column of an existing index. This can be useful when high levels of DML activity is expected to degrade because of the existence of too many indexes used to support infrequent queries.
The algorithm is also valuable in the cases where there are no clear advantages as to which column to use as the prefix column in a composite index.
The prefix column should be the most discriminating, but also the most frequently referenced in queries. Sometimes, these two requirements are met by two different columns in a composite index, forcing a compromise or the use of multiple indexes. Skip scanning helps to overcome these problems.

Filed Under: Oracle, Oracle SQL性能调优 Tagged With: Oracle索引index, skip scan