Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: Reproducing ORA-600 issue deliberately

    Read the Note:155933.1 -Subject: Resolving an ORA-600 [12700] error -Type: HOWTO

    We would like to see that our application act according to a specific logic, and would like to reproduce this error.

    The problem is how, is there any known way ?

    Tried to create an index on a specific tablespace, then shutdown the instance, messed up with the tbs file, but this crashed the whole Oracle.

    Idea, anyone ?


    Thanks,

    Tal (otal@mercury.co.iil)

  2. #2
    Join Date
    Jan 2003
    Location
    Kolkata, India
    Posts
    4
    This will occur when oracle is trying to access a row using its ROWID, which has been obtained from an index.

    A mismatch was found between the index rowid and the data block it is
    pointing to. The rowid points to a non-existent row in the data block.
    The corruption can be in data and/or index blocks.

    many times ORA-600 [12700] can also be reported due to a consistent read (CR) problem.

    The information dumped to the trace file varies greatly between releases:

    - in Oracle 7.3.x it is ORA-600 [12700][a][b] , where
    Arg [a] dba (Data Block Address)
    Arg [b] slot number (number of the row in the block pointed by the dba)

    - in Oracle 8.x and 9.x, it is ORA-600 [12700][a][b][c] , where
    Arg [a] dataobj# from sys.obj$
    Arg [b] relative dba of the data block
    Arg [c] slot number of the row in the data block

    The arguments of the ORA-600 [12700] contains information obtained
    from the index we are using.


    To diagnose this problem, follow the steps below :

    1- Identify the trace file containing the ora-600 error
    2- Identify the possibly corrupted data object ie Check if the data object is corrupt
    3- Identify the possibly corrupted indexes
    4- If you find index corruption, you can re-create the corrupted index
    5- If you find data corruption, you should follow Note:28814.1
    "Handling Oracle Block Corruptions in Oracle7/8/8i"
    6- If no data nor index corruption is found, then this can be a
    consistent read problem. See suggestions below.
    7- If further analysis is required in case of data or index corruption


    -1- Identify the trace file containing the ora-600 error

    Most probably, your alert.log file will contain an entry like :

    Tue Jan 22 12:10:16 2003
    Errors in file /users/ora816/rdbms/log/ora_6496.trc:
    ORA-00600: internal error code, arguments: [12700], [2989], ....

    So the trace file containing the ora-600 dump is named ora_6496.trc

    -2- Identify the possibly corrupted data object

    The ora-600 trace file will contain something similar to :

    ksedmp: internal or fatal error
    ORA-00600: internal error code, arguments: [12700], [2997], [16778259],...
    Current SQL statement for this session:
    select * from t12700bis where a='yyy'

    In this case the problem must be with table "t12700bis".

    The "Current SQL statement for this session:" may well show a more
    complex SQL statement.

    When the current SQL statement does not easily point you to the possible
    corrupted data object, you can use the PL/SQL Procedure "oerr12700"
    to convert the ora-600 Oracle 8.X arguments into some meaningful information :

    Note: PL/SQL code for "oerr12700" procedure is at the end of this Note

    Example output :

    SQL> execute oerr12700( 2989,16777219,4294941081 )
    ORA-600 [12700] [2989],[16777219],[4294941081]
    --------------------------------------------------
    there is an index pointing to a row in SCOTT.T12700
    row is slot 4294941081 in file 4 block 3
    one index entry is pointing to ROWID='AAAAutAAEAAAAADJmZ'
    --------------------------------------------------
    You may want to check the integrity of SCOTT.T12700
    executing :
    dbv file=<file_name> blocksize=<db_block_size> start=3 end=3
    --------------------------------------------------
    IF dbv does not show any corruption, you can try to
    find the corrupted indexes using the queries proposed
    by the procedure oerr12700diag(2989,16777219,4294941081)
    -------------------------------------------------------

    This means there is one index pointing to a non-existent row in SCOTT.T12700
    The row is supposed to be in file=4,block=3 , slot=4294941081.
    Index entry is pointing to ROWID 'AAAAutAAEAAAAADJmZ'.

    At this point, the way to check if the data object is corrupted or not
    is to do an ANALYZE TABLE <tname> VALIDATE STRUCTURE.

    If this command does not return any error, then the data object is sane
    and the most probable cause is a corrupted index.

    Note that the ANALYZE TABLE command results in an exclusive lock being taken
    out on the table for the duration of the command.

    If you can not afford to lock the table in exclusive mode, you can
    try to check the block reported by the PL/SQL script above using "dbv" :

    dbv file=<where-the-data-is> blocksize=<db_block_size> start=<block> end=<block>

    Note: <block> is the block number showed by oerr12700 procedure.

    If dbv does not return any error message and "Pages Failing" is 0,
    then the data is NOT corrupted, so the corruption must be in the indexes.


    -3- Identify the possibly corrupted indexes

    At this point you should be confident there is no data corruption.

    The problem must be with some corrupted index.

    Table identified in -2- above can have several indexes.

    One way of finding the corrupted indexes is to run the command
    "ANALYZE TABLE <tname> VALIDATE STRUCTURE CASCADE" and look at the trace file it generates when it finds an error.

    Again, the ANALYZE TABLE ... command will lock your table in exclusive mode,and you may want to check the indexes without locking anything.

    In this case you can try to identify the corrupted indexes using the
    procedure "oerr12700diag".

    Example output :

    SQL> execute oerr12700diag(2989,16777219,4294941081)
    --------------------------------------------------
    IF dbv did not show any corruption, you can try to
    find the corrupted indexes using following queries:
    -------------------------------------------------------
    If a query returns "no rows selected" index is sane
    If a query returns AAAAutAAEAAAAADJmZ index is corrupted
    ..................................................
    .
    To test SCOTT.T12700 indexes
    .
    .
    To test INDEX I1T12700 you run :
    .
    select rowid "I1T12700 corrupted!" from
    (SELECT /*+ INDEX_FFS(T12700,I1T12700) */
    N,rowid from SCOTT.T12700 where N=N)
    where rowid='AAAAutAAEAAAAADJmZ';


    .
    To test INDEX IT12700 you run :
    .
    select rowid "IT12700 corrupted!" from
    (SELECT /*+ INDEX_FFS(T12700,IT12700) */
    A,rowid from SCOTT.T12700 where A=A)
    where rowid='AAAAutAAEAAAAADJmZ';
    .

    In this particular case , this is the output from the execution of the
    above SQL statements :

    SQL> r
    1 select rowid "I1T12700 corrupted!" from
    2 (SELECT /*+ INDEX_FFS(T12700,I1T12700)
    3 */ N,rowid from SCOTT.T12700 where N=N)
    4* where rowid='AAAAutAAEAAAAADJmZ'

    no rows selected
    .
    This means INDEX I1T12700 does NOT contains any entry pointing to
    the non-existent row. The index is sane.
    .
    SQL> r
    1 select rowid "IT12700 corrupted!" from
    2 (SELECT /*+ INDEX_FFS(T12700,IT12700) */
    3 A,rowid from SCOTT.T12700 where A=A)
    4* where rowid='AAAAutAAEAAAAADJmZ'

    IT12700 corrupted!
    ------------------
    AAAAutAAEAAAAADJmZ
    .

    This means INDEX IT12700 contains one entry pointing to the non-existent SCOTT.T12700's row identified by its rowid='AAAAutAAEAAAAADJmZ'. So, INDEX IT12700 is corrupt.

    To double check, you can see if table contains a row with the ROWID
    reported by "oerr12700" procedure :

    SQL> select * /*+ full (tablename) */ from tablename
    2 where rowid='AAAAutAAEAAAAADJmZ'

    PS:It is possible that the "bad" rowid gets used by an insert after the error (ora-600 [12700]) has occurred.

    This could potentially give 2 rows in the index pointing to the same rowid.

    For example :
    - an index points to slot 3 in a given data block which does not exists.
    SELECT statement will return ora-600 [12700] ...
    - a new insert results in a row going into slot 3
    SELECT does not fail, an UPDATE fails with ora-600 [13013]
    The corrupt index will now have 2 rows pointing to the same rowid (slot 3)
    A sane index will have 1 row pointing to the same rowid (slot 3)

    -4- If you find index corruption, you can re-create the corrupted index

    If further analysis is required you must gather some additional
    information before dropping the corrupted index. See point -7- .

    If you decide to drop the corrupted indexes you should also consider
    additional dependencies and be ready to act accordingly.

    For example, if the corrupted index is being used by existing integrity
    constraints ( Primary Key, Unique, Foreign Key ,...) you will need to drop such constraints before dropping the index.

    -5- If you find data corruption

    -6- If no data or index corruption is found

    Then this could be a consistent read problem.

    - 7- If further analysis is required in case of data or index corruption

    If further analysis is required in case of data or index corruption
    please log the issue with Oracle Support Services.

    To find the root cause of the corruption :
    - database should be in archivelog mode
    - and archived redo log files should be available to dump
    the redo for data and index blocks

    =============================================
    PL/SQL Code for utility procedures "oerr12700" and "oerr12700diag"

    Just run procedures which should be created and used by SYS,
    both are dependent on the dbms_utility and dbms_rowid packages
    provided by Oracle in $ORACLE_HOME/rdbms/admin/dbmsutil.sql


    Hope this will help you in analzing your problem
    If you are still stucking....write me offline
    Sanjit

  3. #3
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    Originally posted by mail2sanjit
    This will occur when oracle is trying to access a row using its ROWID, which has been obtained from an index.

    A mismatch was found between the index rowid and the data block it is
    pointing to. The rowid points to a non-existent row in the data block.
    The corruption can be in data and/or index blocks.

    many times ORA-600 [12700] can also be reported due to a consistent read (CR) problem.

    ...
    ...
    ...

    Sanjit
    Did you read the question ?
    It was "how do I reproduce this error in a clean env?".

    Tal.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •