Ora - 00600 [13013], [5001]

I experience an Ora-00600 error. Let's look what is the error and the way to resolve it.

Error received

ORA-00600: internal error code, arguments: [13013], [5001], [4722], [29361923], [9], [29361923], [17], []

Let's look into details of how to solve this issue.

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 command check if table/index has corruption or not

SQL> Analzye table <owner>.<table name> validate structure cascade 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(data block address)

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

RFILE# Block#

---------- ----------

37 65717

You can 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 an 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

To Identify the index please do the following

Analyze table with "validate structure cascade" fails with ORA-1499 and the trace file contains message "Table/Index row count mismatch". Example:

SQL> analyze table test validate structure cascade;
analyze table test validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file 

The associated trace file contains:

Table/Index row count mismatch
table 6559 : index 10000, 0
Index root = tsn: 6 rdba: 0x01400091

It means: A table scan returned 6559 rows and an index scan returned 10000 rows.
"Index root" is the segment header information for the index:
rdba: 0x01400091 is the Index segment header relative data block address. It is decimal 20971665 which is Rfile#=5 Block#=145 :

SQL> select dbms_utility.data_block_address_file(20971665)  "Rfile#"  
2    ,dbms_utility.data_block_address_block(20971665) "Block#"  
3    from dual;  

Rfile#     Block#  
---------- ----------  
5          145  

Running the next query can identify the associated index:

QUERY 1: 

SQL> select owner, segment_name, segment_type 
2    from  dba_segments 
3    where header_file = 5 
4    and header_block = 145; 

OWNER    SEGMENT_NAME    SEGMENT_TYPE 
-------- --------------- ------------------ 
SCOTT    I_TEST          INDEX 

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

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.

Reference: MOS note id - 816784.1

Manual Oracle Database Creation

This document briefs about the step by step procedure for creating a database on unix environment.

Before starting, it is best to have the relevant environment variables set (ORACLE_SID, ORACLE_HOME, PATH, etc...). Best to have this setup in a .profile file.

Also create all the directory structures required.

Step 1: Make an init<SID>.ora in your $ORACLE_HOME/dbs directory. To simplify, you can copy init.ora to init<SID>.ora and modify the file.

            Path names, file names, and sizes will need to be modified

            Example parameter settings:

*.timed_statistics=TRUE
*.undo_management=AUTO
*.undo_tablespace=UNDOTBS
*.audit_file_dest='/oracle/U04/app/admin/rbdb1/adump'
*.audit_trail=db,extended
*.compatible='11.2.0.4.0' -- # set compatible value as required
*.control_files='/oracle/U04/app/oradata/rbdb1/control01.ctl','/oracle/U04/app/fast_recovery_area/rbdb1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='rbdb1'
*.db_recovery_file_dest='/oracle/U04/app/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/oracle/U04/app'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5047844864
*.sga_max_size=5047844864

Step 2: Run the following sqlplus command to connect to the database:

$ sqlplus '/ as sysdba'

Step 3: Startup up the database in NOMOUNT mode:

SQL> startup nomount

Step 4: Create the Database by issuing the following in the SQL prompt:

SQL> CREATE DATABASE db_name -- replace with your desired DB name
-- orabliss.blogspot.com
MAXLOGFILES 255
MAXINSTANCES 1
MAXDATAFILES 256
MAXLOGHISTORY 256
DATAFILE '/u01/oracle/rbdb1/system_01.dbf' SIZE 400M REUSE
SYSAUX DATAFILE '/u01/oracle/rbdb1/sysaux_01.dbf' SIZE 500M REUSE
UNDO TABLESPACE "UNDOTBS" DATAFILE '/u01/oracle/rbdb1/undotbs_01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET US7ASCII
LOGFILE GROUP 1 ('/u01/oracle/rbdb1/redo1_01.dbf') SIZE 100M REUSE,
GROUP 2 ('/u01/oracle/rbdb1/redo1_02.dbf') SIZE 100M REUSE;

Sysaux data file required for database version 10g and above.

Step 5: Create a Temporary Tablespace :

            Path names, file names, and sizes will need to be modified

SQL> CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/u01/oracle/rbdb1/temp_01.dbf'
-- change path, file name and size as per your specifications
SIZE 40M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

Step 6: Run the following scripts as sys to create data dictionary views and tables.

SQL> @?/rdbms/admin/catalog.sql;
SQL> @?/rdbms/admin/catblock.sql;
SQL> @?/rdbms/admin/catproc.sql;
SQL> @?/rdbms/admin/catoctk.sql;

Step 7: Run the following scripts as SYSTEM user to create product user profile and command line help.

SQL> conn system/password -- pass SYSTEM user password
SQL> @?/sqlplus/admin/pupbld.sql;
SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql;

Step 8: Place the database in archivelog mode.

SQL> alter database archivelog;

Step 9: Reset the sys and system password and create password file if required.

SQL> ALTER USER SYS IDENTIFIED BY password; -- pass desired password
SQL> ALTER USER SYSTEM IDENTIFIED BY password; -- pass desired password

Step 10: Backup created database

Step 11: Create, configure and start the listener.

Step 12: Schedule the required backup and monitoring scripts in cron