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

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

留下评论

在修改_allow_resetlogs_corruption参数为TRUE，并强制打开数据库后，遭遇下述错误信息： 该错误信息来自alert告警日志文件：

Thu Feb 17 14:07:30 2011
Errors in file /u01/app/admin/orcl/bdump/orcl_smon_26850.trc:
ORA-00600: internal error code, arguments: [4194], [41], [31], [], [], [], [], []

通常ORA-00600 4194级的错误是跟回滚段错误相关！ 继续查看TRACE文件（/u01/app/admin/orcl/bdump/orcl_smon_26850.trc） 看到有下述相关信息：

 Acq rbs _SYSSMU1$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 1 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU2$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 2 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU3$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 3 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU4$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 4 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU5$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 5 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU6$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 6 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU7$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 7 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU8$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 8 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU9$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 9 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU10$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 10 Onlined

Google之，获取相关解决办法： ① 启动数据库，获取回滚段信息：

SYS@orcl > conn / as sysdba;
Connected to an idle instance.
SYS@orcl > startup
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
SYS@orcl > select * from v$rollname;
ERROR:
ORA-03114: not connected to ORACLE
SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > select * from V$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
         1 _SYSSMU1$
         2 _SYSSMU2$
         3 _SYSSMU3$
         4 _SYSSMU4$
         5 _SYSSMU5$
         6 _SYSSMU6$
         7 _SYSSMU7$
         8 _SYSSMU8$
         9 _SYSSMU9$
        10 _SYSSMU10$
11 rows selected.
SYS@orcl >

此时，数据库虽可以使用，但是还是有问题的，过一段儿时间，莫名其妙的宕机！！！再次尝试启动数据库的时候，却非常正常，并未报错！！！

SYS@orcl > startup
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
SEGMENT_NAME
------------------------------
_SYSSMU11$
12 rows selected.
SYS@orcl > select segment_name from dba_rollback_segs;
select segment_name from dba_rollback_segs
*
ERROR at line 1:
ORA-03135: connection lost contact
SYS@orcl >

② 开始通过调整参数的方式，来修复数据库：

SYS@orcl > conn / as sysdba;
Connected to an idle instance.
SYS@orcl > create pfile from spfile;
File created.
SYS@orcl >

修改pfile，在该文件中，添加如下信息：

[oracle@rhel10g dbs]$ tail -2  /u01/app/oracle/dbs/initorcl.ora
*.undo_management='MANUAL'
_corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$',
'_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$',
'_SYSSMU9$','_SYSSMU10$','_SYSSMU11$'

这两个参数表示，回滚段手工管理，并设置_corrupted_rollback_segments后面的那11个回滚段为损坏状态！ ③ 然后，用该PFILE来启动数据库：

SYS@orcl > startup pfile=/u01/app/oracle/dbs/initorcl.ora
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select * from V$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
SEGMENT_NAME
------------------------------
_SYSSMU11$
12 rows selected.

数据库正常启动，并未像刚才的那样，莫名其妙的“死”掉！！！但是，数据看出现下述症状，普通用户在数据库内无法做事务操作，而SYS用户却可以。

SYS@orcl > select count(*) from m;
  COUNT(*)
----------
       176
SYS@orcl > insert into m select * from m;
176 rows created.
SYS@orcl >

上述看到，SYS用户正常做操作。而HR用户执行事务就报错：ORA-01552！

SYS@orcl > conn hr/hr;
Connected.
HR@orcl > select count(*) from t;
  COUNT(*)
----------
       214
HR@orcl > insert into t select * from t;
insert into t select * from t
            *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
HR@orcl >

④ 删除，并重建UNDO表空间：

SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SYS@orcl > col file_name for a40
SYS@orcl > col tablespace_name for a15
SYS@orcl > set line 120
SYS@orcl > select file_name,tablespace_name from dba_data_files;
FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ---------------
/u01/app/oradata/orcl/users01.dbf        USERS
/u01/app/oradata/orcl/sysaux01.dbf       SYSAUX
/u01/app/oradata/orcl/undotbs1.dbf       UNDOTBS1
/u01/app/oradata/orcl/system01.dbf       SYSTEM
/u01/app/oradata/orcl/example01.dbf      EXAMPLE
SYS@orcl > drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SYS@orcl > select file_name,tablespace_name from dba_data_files;
FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ---------------
/u01/app/oradata/orcl/users01.dbf        USERS
/u01/app/oradata/orcl/sysaux01.dbf       SYSAUX
/u01/app/oradata/orcl/system01.dbf       SYSTEM
/u01/app/oradata/orcl/example01.dbf      EXAMPLE
SYS@orcl > create undo tablespace undotbs1 datafile '/u01/app/oradata/orcl/undotbs1.dbf' size 50m;
Tablespace created.
SYS@orcl >

⑤ 关闭实例，生成新的参数文件：

SYS@orcl > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl >

⑥ 修改PFILE，这次要去掉_corrupted_rollback_segments参数的设置，并将undo_management改回为AUTO：

[oracle@rhel10g ~]$ tail -3 /u01/app/oracle/dbs/initorcl.ora
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/admin/orcl/udump'
*.undo_management='auto'

⑦ 然后创建SPFILE：

SYS@orcl > create spfile from pfile;
File created.
SYS@orcl > !strings /u01/app/oracle/dbs/spfileorcle.ora | grep undo
strings: '/u01/app/oracle/dbs/spfileorcle.ora': No such file
SYS@orcl > !strings /u01/app/oracle/dbs/spfileorcl.ora | grep undo
*.undo_management='auto'
*.undo_tablespace='UNDOTBS1'
SYS@orcl >

⑧ 用新SPFILE启动数据库，并做校验：

SYS@orcl > startup
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select count(*) from m;
  COUNT(*)
----------
       352
SYS@orcl > select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
         1 _SYSSMU1$
         2 _SYSSMU2$
         3 _SYSSMU3$
         4 _SYSSMU4$
         5 _SYSSMU5$
         6 _SYSSMU6$
         7 _SYSSMU7$
         8 _SYSSMU8$
         9 _SYSSMU9$
        10 _SYSSMU10$
11 rows selected.
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
11 rows selected.
SYS@orcl > select count(*) from m;
  COUNT(*)
----------
       352
SYS@orcl > conn hr/hr;
Connected.
HR@orcl > select count(*) from t;
  COUNT(*)
----------
       214
HR@orcl > insert into t select * from t;
214 rows created.
HR@orcl > commit;
Commit complete.
HR@orcl > select count(*) from t;
  COUNT(*)
----------
       428
HR@orcl >

运行一段时间，发现数据库并无其它异常。至此，解决ORA-00600 4194的错误！

发表评论

邮箱地址不会被公开。 必填项已用*标注