Thursday, June 9, 2011

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#"  
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

Friday, June 3, 2011

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:

            db_block_size=8192
            db_cache_size=52428800
            background_dump_dest=/u01/32bit/app/oracle/product/9.0.1/bdump
            core_dump_dest=/u01/32bit/app/oracle/product/9.0.1/cdump
            user_dump_dest=/u01/32bit/app/oracle/product/9.0.1/udump
            timed_statistics=TRUE
            control_files=("/u01/rbdb1/control_01.ctl", "/u01/rbdb1/control_02.ctl")
            db_name=rbdb1
            shared_pool_size=52428800
            sort_area_size=524288
            undo_management=AUTO
            undo_tablespace=UNDOTBS

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:
            Path names, file names, and sizes will need to be modified

            CREATE DATABASE <db_name>
            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

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/u01/oracle/rbdb1/temp_01.dbf'
            SIZE 40M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
            EXTENT MANAGEMENT LOCAL;
            ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

Step 6: Run the following scripts as sys to create data dictionary views and tables.
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catoctk.sql;

Step 7: Run the following scripts as system to create product user profile and command line help.
@?/sqlplus/admin/pupbld.sql;
@?/sqlplus/admin/help/hlpbld.sql helpus.sql;

Step 8: Put 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>;
            SQL> ALTER USER SYSTEM IDENTIFIED BY <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