Skip to content

How to solve ora 600 errors? – knowledge article for the new oracle dba




you are a new oracle dba and in the first week of your job you were asked by your manager to handle a ticket that is regarding a ora-600 error.you have no clue what that error is and you wanted to jump into google and search once your panic came down.

Summary is having ora-600 error is not normal and most of the time the errors are due to oracle bugs , table corruption , index corruption or some other data file corruption. The first thing you should do when handling such a ticket is to find the trace file that gets generated whenever the ora-600 error is generated.you can look in the alert.log to find the exact path of the trace file. Once you get the trace file you can note down the error code and search in oracle metalink to see if any known bug exists. If not then read the below article from oracle support that explains how to check various possibilities to find out why the error is happenning.

Subject: How to resolve ORA-00600 [13013], [5001]
  Doc ID: 816784.1 Type: HOWTO
  Modified Date: 21-SEP-2009 Status: REVIEWED

In this Document
  Goal
  Solution
     Section 1> What information needs to be Collected
     Section 2 >How to resolve if a Index is corrupted
     Section 3> How to resolve if table is corrupted
     How to resolve when Smon terminates the instance due to Ora-00600[13013]
     How to resolve the issue if the object involved belongs to system tablespace
     Are there any known bugs
  References


Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.4
Information in this document applies to any platform.

Goal

The Goal of this article is to resolve Ora-00600[13013] and check if there are any known bugs associated with this error to prevent future occurence of this issue

 

Solution

  

Section 1> What information needs to be Collected

ORA-600 [13013] [a] [b] [c] [d] [e] [f]

This format relates to Oracle Server 8.0.3 to 10.1

Arg [a] Passcount
Arg [b] Data Object number
Arg [c] Tablespace Relative DBA of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Relative DBA of block being updated (should be same as [c])
Arg [f] Code

The Second argument would give the information about the data object id.

This would give information about the object involved.

SQL>Select object_name,object_type,owner from dba_objects where data_object_id=<value reported in argment b>

Once the Object is identified run the following :

The below command check if the table has corruption or not .

SQL> Analzye table <owner>.<table name> validate structure online ;

If this goes fine table doesnot have corruption. For next command.

If the above command fails with ORA-1498 go to Section 3

The below comman check if table/index has corruption or not

SQL>Analzye table <owner>.<table name> validate structure case online ;

If the above command errors out with ora-1499 it indicates a corruption in index.

Go to section 2 for resolution

Run dbverify on the datafile reported in the error

Arg [c] in the ora-0600[13011] would give the Relative DBA

For example

ORA-00600: internal error code, arguments: [13013], [5001], [57353], [155254965], [261],
[151062605], [17], []

Arg [c] –> rdba–>155254965

Use this value and find the file and block number for this dba

select dbms_utility.data_block_address_file(155254965) Rfile# ,dbms_utility.data_block_address_block(155254965) "Block#" from dual;

RFILE# Block#
———- ———-
37      65717

You an run dbveirfy on datafile with rfile#=37

SQL>Select name from v$datafile where rfile#=37

dbv file=<location of datafile> blocksize=<db_block_size>

Section 2 >How to resolve if a Index is corrupted

You would need to drop and recreate the index

Ensure before dropping the Index

SQL>Spool /tmp/createindex.sql

SQL>Set long 100000000

SQL>Select dbms_metadata.get_ddl(‘INDEX’,'<Index name>’,<‘user name>’) from dual

SQL>Spool off

Refer the Following note to Identify the index

<Note 563070.1>

Title: ORA-1499. Table/Index row count mismatch

Please note if there is just one index in the table then you can use dbms_metadata.get_ddl to get the script of the index and drop and recreate it.

Section 3> How to resolve if table is corrupted

Option a>  Backup is available

Ora-1498 would be reported on the table.

The trace file from Ora-1498 would contain following information

Example

Block Checking: DBA = 1066265208, Block Type = KTB-managed data block —>
file 254,block 911992
data header at 0xc00000010118e07c
kdbchk: avsp(816) > tosp(812)
Block header dump: 0x3f8dea78
Object id on Block? Y
seg/obj: 0x155b452 csc: 0x05.7b2b4ee6 itc: 3 flg: E typ: 1 – DATA
brn: 0 bdba: 0x3f8dde0c ver: 0x01
inc: 0 exflg: 0

Note the DBA value reported in the trace file  DBA = 1066265208
Convert this to find the file number and block number having issue

Sql>Select dbms_utility.data_block_address_file(‘1066265208’) from dual ;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(‘1066265208’)
————————————————–
254

Sql>Select dbms_utility.data_block_address_block(‘1066265208’) from dual ;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(‘1066265208’)
—————————————————
911992

Run dbverify on the datafile containing the table

dbv file=<location of datafile> blocksize=<db_block_size>

Corruption would be reported on the block.

If you have an Rman backup do a Rman block recovery (Take the file number and block number affected from the trace file)

Rman> Blockrecover datafile <no> block <block number>

 Or

If you have a user managed backup you can do an restore and recovery from good copy of the problematic datafile

Option b> Backup is not available

If no backups are available then use event 10231 at session level to create a salvage table

SQL>alter session set events ‘10231 trace name context forever, level 10’
SQL> Create table <owner>.salvage_table as select * from <Corrupted table> ;

Rename the Original table to old.

Rename salvage table to Original table name

or

You can use dbms_repair script to mark the block soft corrupt.

Note 556733.1

DBMS_REPAIR SCRIPT

How to resolve when Smon terminates the instance due to Ora-00600[13013]

If Smon is terminating the instance then.

Set event 10513 and startup the database

event="10513 trace name context forever, level 2"

SQL>Startup mount ;

SQL>Show parameter event

SQL>Alter datatabase open ;

Identify the object involved using information from Section 1.

How to resolve the issue if the object involved belongs to system tablespace

System objects are very important.
Please open a Service request with Oracle support if system tables are involved.

Are there any known bugs

 

There is a  internal bug number 5085288

 fixed in 11.1 

Details

 ORA-600 [13013] [5001] error can occur on a MERGE command if the DELETE pass encounters a consistent read (CR) error due to the update pass having updated the same
row and column previously.

Check for availability of one off patch using <patch 5085288>

Bug 4549673

Abstract: ORA-30926 / OERI:13030 during update
Fixed-Releases: 9208 A204 B106 

Details:
ORA-30926 (in Oracle 9i) or ORA-600 [13030] (in Oracle10g) can occur
during an update DML. This can occur if an internal ORA-1551 error
occurs and is trapped (1551 errors are not visible to client
code and are trapped and handled internally) 

Fixed In Ver: 11.0 

Check for availability of one off patch using Patch 5085288

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
How to solve ora 600 errors? - knowledge article for the new oracle dba, 10.0 out of 10 based on 1 rating

Post a Comment

You must be logged in to post a comment.