ora-0600 [4194] UNDO

http://www.eygle.com/archives/2006/02/howto_resolve_ora_600_4194.html

ORA-00600: internal error code, arguments: [4194], [4], [3], [], [], [], [], []

 

出現這個問題一般是undo出現問題了

事件發生：

SQL>startup 開啟完畢，但馬上又被關閉：

查看log:

Errors in file /orahome/admin/STCSMES/bdump/stcsmes_pmon_4523.trc:

ORA-00600: internal error code, arguments: [4194], [4], [3], [], [], [], [], []

Fri Dec 16 08:29:43 2011

Errors in file /orahome/admin/STCSMES/bdump/stcsmes_pmon_4523.trc:

ORA-00600: internal error code, arguments: [4194], [4], [3], [], [], [], [], []

PMON: terminating instance due to error 472

 

 

修改pfile文件

Undo_management=’MANUAL’;

SQL>create spfile from pfile;

SQL>startup;

SQL>create undo tablespace undotbs2 datafile ‘/oradata/STCSMES/undotbs02.dbf’ size 2000M autoextent on next 100M maxsize unlimited;

---注：剛開始建立undo tablelspace 時寫成create tablespace導致之後不能使用該新建立的undo tablespace

ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

SQL>alter tablespace undotbs1 offline;

SQL>alter system set undo_tablespace=undotbs2 scope=spfile;

SQL>alter system set undo_management=auto scope=spfile;

SQL>shutdown immediate;

SQL>startup;

正常啟動之後：

SQL>show parameter undo;

SQL> show parameter undo;

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS2

SQL>drop tableslpace undotbs1 including datafiles;

到這裡ora-600 [4194]的問題處理完成，但是查看log還是有錯誤

1.      查看job 243

SQL>Select job,schema_user,what from dba_jobs where job=243;

       JOB  SCHEMA_USER     WHAT

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

       243 SYSMAN

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();

該job是用來維護em的，每一分鐘執行一次，如果沒有開啟em的可以直接關閉

2.      關閉job243

Sql>exec dbms_job.broken(243,true);

3.      查看obj#239是什麽東西

SQL> select owner,object_name,object_id,object_type from dba_objects where object_id=239

OWNER      OBJECT_NAME      OBJECT_ID OBJECT_TYPE
----------    ---------------           ----------      -------------------
SYS           I_JOB_NEXT             239              INDEX

4.      查看i_job_next 是哪個表的index

SQL>select owner, index_name, table_name  from dba_indexes where

index_name= 'I_JOB_NEXT';

OWNER      INDEX_NAME    TABLE_NAME
----------      ----------            ----------
SYS             I_JOB_NEXT     JOB$

5.      查看index i_job_next 是table job$哪個列上的索引

SQL> col index_name format a10;
SQL> col index_owner format a10;
SQL> col table_name format a10;
SQL> col column_name format a15; 

SQL>  select INDEX_OWNER, INDEX_NAME, TABLE_NAME, COLUMN_NAME
   from dba_ind_columns  where INDEX_NAME = 'I_JOB_NEXT';

INDEX_OWNE   INDEX_NAME     TABLE_NAME      COLUMN_NAME
----------             ----------              ----------             ---------------
SYS                    I_JOB_NEXT       JOB$                   NEXT_DATE

6.      重建index

Sql>Create index i_job_next rebuild;

或是：

SQL>drop index i_job_next;

SQL>create index i_job_next on job$(next_date);

將job243 broken

SQL>exec dbms_job.broken(243,true)

PL/SQL procedure successfully completed.

 

附加：http://www.eygle.com/archives/2006/02/howto_resolve_ora_600_4194.html

 

如果undotbs不能offline则在pfile中增加隐含参数：

_corrupted_rollback_segments='_SYSMAU11$','_SYSMAU12$','_SYSMAU13$'

再利用init启动数据库 

 

 

 

 

 

在metalink上描述是：

DESCRIPTION:

 

  A mismatch has been detected between Redo records and rollback (Undo)

  records.

 

  We are validating the Undo record number relating to the change being

  applied against the maximum undo record number recorded in the undo block.

 

  This error is reported when the validation fails.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24849178/viewspace-713096/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24849178/viewspace-713096/