ORA-600 [6006] ORA-600…

The undo segments are trying to rollback a failed transaction and cannot.

还原段试图回滚一个失败的事物但无法完成

 

ORA-600 [6006] ORA-600 [6856] During Startup Instance, Followed by Termination by SMON (文档 ID 549000.1) 转到底部 

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

修改时间:2014-2-7类型:PROBLEM    
 

In this Document

 Symptoms
 Cause
 Solution

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

Applies to:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.3 [Release 10.2]
Information in this document applies to any platform.
***Checked for relevance on 05-Sep-2013***

Symptoms
The following errors are occurring when starting the instance:

ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []

SMON will eventually terminate the instance.

 

Cause
These errors can occur if the database crashes under certain circumstances.

The undo segments are trying to rollback a failed transaction and cannot.

ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []
Oracle is undoing an index leaf key operation. If the key is not found,
ORA-00600 [6006] is logged.

ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []
SMON is trying to recover a dead transaction. But the undo application runs into an
internal error (trying to delete a row that is already deleted).

Solution
Review the trace files and look for the object(s) involved.

If the trace file does not have a SQL statement, search on the following: "block dump header"

In the block header there will be a seg/obj = hex value.

Convert the hex to dec and this will give you the data_object_id.

The alert.log may also show the affected object, for example:

ORACLE Instance ORCL (pid = 8) - Error 600 encountered while recovering transaction (9999, 36) on object 45879.

SQL>select object_id, data_object_id, owner, object_name from dba_objects where object_id = object# or data_object_id=object# ;

This will be the object you need to work with.

In case no object is shown at select of dba_objects check if object has been already dropped and is still present in recyclebin(in that case the problem can/will still be observed):

SQL> select u.name,r.original_name,t.name from recyclebin$ r, user$ u, ts$ t where r.obj#=&object_id and r.ts#=t.ts# and r.owner#=u.user# ;

 

To implement the solution:

1. shutdown instance (if not already down)

SQL> shutdown immediate
2. set event - event="10513 trace name context forever, level 2" (event disables transaction recovery which was initiated by SMON)

It would be best to create a pfile which is to be used for this action only:
SQL>
connect / as sysdba
startup mount
create pfile='/tmp/init$ORACLE_SID.ora' from spfile;
shutdown immediate
+
echo 'event="10513 trace name context forever, level 2"' >> /tmp/init$ORACLE_SID.ora

*) in case already a pfile is being used make a copy to /tmp and ...
3. startup instance using the pfile as just created:

SQL>
connect / as sysdba
startup pfile=/tmp/init$ORACLE_SID.ora4. Drop the object:

a) If the object is an index the action is to drop the index, in case no create script is present you can extract one by:
SQL>
connect / as sysdba
set long 1000000
select dbms_metadata.get_ddl('INDEX',upper('&object_name'),upper('&owner')) from dual;

b) If the object is a table there will likely be a need to salvage the content of the table before dropping the table.
Possible options:
- exp{dp} table
- CTAS (CreateTableAsSelect)

In case recyclebin is active it might be that object was already in recylebin OR has been moved by drop into the recyclebin, therefore check and purge the recyclebin (if applicable):

SQL> select u.name,r.original_name,t.name from recyclebin$ r, user$ u, ts$ t
     where r.obj#=&object_id and r.ts#=t.ts# and r.owner#=u.user# ;

SQL> purge &tablespace_name;
or
SQL> purge dba_recyclebin;

5. shutdown the instance

SQL>
connect / as sysdba
shutdown immediate
6. remove the event

Only needed in case the event was pushed into the spfile, if true the steps are:
SQL>
connect / as sysdba
startup mount
alter system reset event scope=spfile sid='*';
shutdown immediate

7. startup the instance

SQL>
connect / as sysdba
startup

8. recreate the affected object(s)