ora-0600 [4194] UNDO

最新推荐文章于 2024-02-26 08:00:41 发布

cuandou3689

最新推荐文章于 2024-02-26 08:00:41 发布

阅读量119

点赞数

文章标签：数据库

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启动数据库