前几天有一台RAC节点的库宕机了,下面图片是当时的日志信息。上去查看alter日志发现大量的ORA-00600 4194的错误
数据库可以启动到mount状态之后就启动不到open状态了,但是隔几分钟就自动open了。
SQL> select file_name,file_id from dba_data_files where file_id=3;
FILE_NAME FILE_ID
-------- ----------
/ora/portalsp/undotbs01.dbf 3
最后同事排查发现是一个参数导致出现上面错误:
对于本次门户系统的ora-600错误,我们发现是由于设置隐藏参数_allow_resetlogs_corruption导致的,该参数设置true的时候,意味着Oracle会跳过使用redo日志维持数据库文件一致性的操作,强行启动数据库,也就意味着数据文件将会处于损坏的状态,随后运行时会遇到各种内部错误。
我们向SR确认,得到以下结论,所有使用 下划线开头的参数都是内部参数,用于调整Oracle的默认行为,如果要调整这种参数,请务必开SR与Oracle Support进行确认, 除非非常清楚可能的影响,一般不要设置这种不公开的参数。 所以,以后,针对于下划线开头的参数,
不要轻易进行设置。
ORA-600[4194]内部错误一般由重做记录与回滚记录不匹配引发。Oracle在验证Undo record number时,会对比redo change 和回滚段中的undo record number,若发现2者存在差异则报该4194错误。其错误argument[a][b],a代表回滚块中的最大undo record number,b代表重做日志中记录的undo record number。这个错误可能由回滚段或者redo log日志文件错误引起。
ORA-00600[4194]错误的根本原因是 redo记录与回滚段(rollback/undo)记录之间的不一致。当ORACLE在验证undo记录时相对应的变化需要应用到undo数据块的最大undo记录上,此时若检验出错则会报ORA-00600[4194]
此错误不像ORA-600[2662]或ORA-600[4000]错误那样必然导致数据库无法打开,因为它很少出现在前滚阶段;当数据库被打开,smon开始执行事务恢复或一些回滚段的管理工作时则很有可能触发该错误。
ORA-600[4194]的2个的含义:
Arg [a] Maximum Undo record number in Undo block
Arg [b] Undo record number from Redo block
这个ORA-600[4194] 报错属于ORACLE内核从cache层到事务undo处理,可能的影响是进程失败或者可能的回滚段坏块。
可能的bug 包括:
8240762 10.2.0.5,
11.1.0.7.10,
11.2.0.1
Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] /
SMON may spin to recover transaction
3210520 9.2.0.5, 10.1.0.2 OERI[kjccqmg:esm] / OERI[4194] / corruption possible in RAC
792610 8.0.6.0, 8.1.6.0 Rollback segment corruption
下面是网络上面的资料,有两篇文章,可以参考如何解决这个错误。
启动数据库出现Ora-00600 4194错误,观察alert文件,主要错误日志如下:
Sat Jan 21 13:55:21 2006
Errors in file /opt/oracle/admin/conner/bdump/conner_smon_17113.trc:
ORA-00600: internal error code, arguments: [4194], [43], [46], [], [], [], [], []
Sat Jan 21 13:55:21 2006
Errors in file /opt/oracle/admin/conner/udump/conner_ora_17121.trc:
ORA-00600: internal error code, arguments: [4194], [45], [44], [], [], [], [], []
4194错误通常说明UNDO段出现问题,最好的办法是通过备份进行恢复,如果没有备份,那么可以通过特殊的初始化参数进行强制启动,本文就Oracle的隐含参数进行恢复说明(由于实际情况可能各不相同,进行测试前请先行备份),仅供参考。
首先确定当前回滚段名称,这可以从alert文件中获得:
Sat Jan 21 13:55:21 2006
Undo Segment 11 Onlined
Undo Segment 12 Onlined
Undo Segment 13 Onlined
Successfully onlined Undo Tablespace 16.
对应的AUM (auto undo management) 下的回滚段名称为:
'_SYSSMU11$','_SYSSMU12$','_SYSSMU13$'
修改init<sid>.ora参数文件,使用Oracle隐含参数_corrupted_rollback_segments将回滚段标记为损坏,此时启动数据库,Oracle会跳过对于这些回滚段的相关操作,强制启动数据库。
._corrupted_rollback_segments='_SYSSMU11$','_SYSSMU12$','_SYSSMU13$'
使用init<sid>.ora参数文件启动数据库:
[oracle@jumper dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Jan 21 13:56:47 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=initconner.ora
ORACLE instance started.
Total System Global Area 97588504 bytes
Fixed Size 451864 bytes
Variable Size 33554432 bytes
Database Buffers 62914560 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
此时数据库正常Open。
观察alert文件可以获得如下信息:
Sat Jan 21 13:57:03 2006
SMON: enabling tx recovery
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 13:57:03 2006
Database Characterset is ZHS16GBK
Sat Jan 21 13:57:03 2006
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 13:57:04 2006
Created Undo Segment _SYSSMU1$
Undo Segment 1 Onlined
Completed: ALTER DATABASE OPEN
aSat Jan 21 14:02:11 2006
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
此时可以重新创建新的UNDO表空间,删除出现问题的表空间,修改参数文件,由参数文件生成新的spfile,重新启动数据库:
SQL> create undo tablespace undotbs1
2 datafile '/opt/oracle/oradata/conner/undotbs1.dbf' size 10M;
Tablespace created.
SQL> alter system set undo_tablespace=undotbs1;
System altered.
SQL> drop tablespace undotbs2;
Tablespace dropped.
此时的alert文件记录的:
Sat Jan 21 14:03:29 2006
create undo tablespace undotbs1
datafile '/opt/oracle/oradata/conner/undotbs1.dbf' size 10M
Sat Jan 21 14:03:29 2006
Created Undo Segment _SYSSMU2$
Created Undo Segment _SYSSMU3$
Created Undo Segment _SYSSMU4$
Created Undo Segment _SYSSMU5$
Created Undo Segment _SYSSMU6$
Created Undo Segment _SYSSMU7$
Created Undo Segment _SYSSMU8$
Created Undo Segment _SYSSMU9$
Created Undo Segment _SYSSMU10$
Created Undo Segment _SYSSMU14$
Starting control autobackup
Control autobackup written to DISK device
handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20060121-00'
Completed: create undo tablespace undotbs1
datafile '/opt/ora
Sat Jan 21 14:03:43 2006
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Undo Segment 14 Onlined
Successfully onlined Undo Tablespace 1.
Undo Segment 1 Offlined
Undo Tablespace 16 successfully switched out.
Sat Jan 21 14:03:43 2006
ALTER SYSTEM SET undo_tablespace='UNDOTBS1' SCOPE=MEMORY;
Sat Jan 21 14:07:18 2006
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 14:08:06 2006
drop tablespace undotbs2
Sat Jan 21 14:08:07 2006
Starting control autobackup
Control autobackup written to DISK device
handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20060121-01'
Completed: drop tablespace undotbs2
修改参数文件,变更undo表空间,并取消_corrupted_rollback_segments设置:
*.undo_tablespace='UNDOTBS1'
由参数文件创建spfile文件。
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 97588504 bytes
Fixed Size 451864 bytes
Variable Size 33554432 bytes
Database Buffers 62914560 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
重起数据库,观察alert文件:
Sat Jan 21 14:08:36 2006
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Undo Segment 14 Onlined
Successfully onlined Undo Tablespace 1.
此时数据库恢复正常,通常建议立即全库exp,然后重新建库,再imp恢复数据库。
最后附上几次启动数据库的日志供参考:
Starting ORACLE instance (normal)
Sat Jan 21 13:55:15 2006
WARNING: EINVAL creating segment of size 0x0000000007400000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
_latch_class_1 = 10000
_latch_classes = 98:1
processes = 150
timed_statistics = FALSE
event =
shared_pool_size = 20971520
large_pool_size = 16777216
java_pool_size = 0
_kghdsidx_count = 1
control_files = /opt/oracle/oradata/conner/control01.ctl,
/opt/oracle/oradata/conner/control02.ctl,
/opt/oracle/oradata/conner/control03.ctl
db_block_size = 8192
db_2k_cache_size = 20971520
db_cache_size = 41943040
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest = /opt/oracle/oradata/conner/archive
_disable_logging = TRUE
db_files = 8
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
_allow_resetlogs_corruption= TRUE
_corrupted_rollback_segments=
undo_management = AUTO
undo_tablespace = UNDOTBS2
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
global_names = TRUE
instance_name = conner
utl_file_dir = /tmp
job_queue_processes = 0
_system_trig_enabled = FALSE
hash_join_enabled = TRUE
background_dump_dest = /opt/oracle/admin/conner/bdump
user_dump_dest = /opt/oracle/admin/conner/udump
core_dump_dest = /opt/oracle/admin/conner/cdump
audit_trail = DB
sort_area_size = 524288
db_name = conner
open_cursors = 300
sql_trace = FALSE
optimizer_mode = CHOOSE
star_transformation_enabled= FALSE
optimizer_index_cost_adj = 10000
query_rewrite_enabled = FALSE
_left_nested_loops_random= FALSE
_use_nosegment_indexes = FALSE
pga_aggregate_target = 20480000
aq_tm_processes = 0
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Sat Jan 21 13:55:16 2006
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=8
ARC0: Archival started
Sat Jan 21 13:55:16 2006
ARCH: STARTING ARCH PROCESSES COMPLETE
Sat Jan 21 13:55:16 2006
ARC0: Thread not mounted
ARC1 started with pid=9
ARC1: Archival started
ARC1: Thread not mounted
Sat Jan 21 13:55:16 2006
ALTER DATABASE MOUNT
Sat Jan 21 13:55:20 2006
Successful mount of redo thread 1, with mount id 3192212932.
Sat Jan 21 13:55:20 2006
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Sat Jan 21 13:55:20 2006
ALTER DATABASE OPEN
Sat Jan 21 13:55:20 2006
Beginning crash recovery of 1 threads
Sat Jan 21 13:55:20 2006
Started first pass scan
Sat Jan 21 13:55:21 2006
Completed first pass scan
0 redo blocks read, 0 data blocks need recovery
Sat Jan 21 13:55:21 2006
Started recovery at
Thread 1: logseq 42, block 3, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 1 Seq 42 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/conner/redo01.log
Sat Jan 21 13:55:21 2006
Completed redo application
Sat Jan 21 13:55:21 2006
Ended recovery at
Thread 1: logseq 42, block 3, scn 2073.1734548
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Sat Jan 21 13:55:21 2006
Thread 1 advanced to log sequence 43
Thread 1 opened at log sequence 43
Current log# 3 seq# 43 mem# 0: /opt/oracle/oradata/conner/redo03.log
Successful open of redo thread 1.
Sat Jan 21 13:55:21 2006
ARC0: Media recovery disabled
Sat Jan 21 13:55:21 2006
SMON: enabling cache recovery
Sat Jan 21 13:55:21 2006
Undo Segment 11 Onlined
Undo Segment 12 Onlined
Undo Segment 13 Onlined
Successfully onlined Undo Tablespace 16.
Sat Jan 21 13:55:21 2006
SMON: enabling tx recovery
Sat Jan 21 13:55:21 2006
Database Characterset is ZHS16GBK
Sat Jan 21 13:55:21 2006
Errors in file /opt/oracle/admin/conner/bdump/conner_smon_17113.trc:
ORA-00600: internal error code, arguments: [4194], [43], [46], [], [], [], [], []
Sat Jan 21 13:55:21 2006
Errors in file /opt/oracle/admin/conner/udump/conner_ora_17121.trc:
ORA-00600: internal error code, arguments: [4194], [45], [44], [], [], [], [], []
Sat Jan 21 13:55:26 2006
Recovery of Online Redo Log: Thread 1 Group 3 Seq 43 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/conner/redo03.log
Sat Jan 21 13:55:26 2006
Recovery of Online Redo Log: Thread 1 Group 3 Seq 43 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/conner/redo03.log
Sat Jan 21 13:55:26 2006
Recovery of Online Redo Log: Thread 1 Group 3 Seq 43 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/conner/redo03.log
Sat Jan 21 13:55:26 2006
Recovery of Online Redo Log: Thread 1 Group 3 Seq 43 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/conner/redo03.log
Sat Jan 21 13:55:26 2006
Errors in file /opt/oracle/admin/conner/bdump/conner_smon_17113.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [43], [46], [], [], [], [], []
Sat Jan 21 13:55:26 2006
Errors in file /opt/oracle/admin/conner/udump/conner_ora_17121.trc:
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [45], [44], [], [], [], [], []
Error 607 happened during db open, shutting down database
USER: terminating instance due to error 607
Instance terminated by USER, pid = 17121
ORA-1092 signalled during: ALTER DATABASE OPEN...
Sat Jan 21 13:56:58 2006
Starting ORACLE instance (normal)
Sat Jan 21 13:56:58 2006
WARNING: EINVAL creating segment of size 0x0000000006400000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
_latch_class_1 = 10000
_latch_classes = 98:1
processes = 150
timed_statistics = FALSE
shared_pool_size = 20971520
large_pool_size = 0
java_pool_size = 0
_kghdsidx_count = 1
control_files = /opt/oracle/oradata/conner/control01.ctl,
/opt/oracle/oradata/conner/control02.ctl,
/opt/oracle/oradata/conner/control03.ctl
db_block_size = 8192
db_2k_cache_size = 20971520
db_cache_size = 41943040
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest = /opt/oracle/oradata/conner/archive
_disable_logging = TRUE
db_files = 8
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
_allow_resetlogs_corruption= TRUE
_corrupted_rollback_segments= _SYSSMU11$, _SYSSMU12$, _SYSSMU13$
undo_management = AUTO
undo_tablespace = UNDOTBS2
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
global_names = TRUE
instance_name = conner
utl_file_dir = /tmp
job_queue_processes = 0
_system_trig_enabled = FALSE
hash_join_enabled = TRUE
background_dump_dest = /opt/oracle/admin/conner/bdump
user_dump_dest = /opt/oracle/admin/conner/udump
core_dump_dest = /opt/oracle/admin/conner/cdump
audit_trail = DB
sort_area_size = 524288
db_name = conner
open_cursors = 300
sql_trace = FALSE
optimizer_mode = CHOOSE
star_transformation_enabled= FALSE
optimizer_index_cost_adj = 10000
query_rewrite_enabled = FALSE
_left_nested_loops_random= FALSE
_use_nosegment_indexes = FALSE
pga_aggregate_target = 20480000
aq_tm_processes = 0
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
Sat Jan 21 13:56:58 2006
ARCH: STARTING ARCH PROCESSES
RECO started with pid=7
ARC0 started with pid=8
ARC0: Archival started
ARC1 started with pid=9
Sat Jan 21 13:56:58 2006
ARCH: STARTING ARCH PROCESSES COMPLETE
Sat Jan 21 13:56:58 2006
ARC1: Archival started
ARC1: Thread not mounted
Sat Jan 21 13:56:58 2006
ARC0: Thread not mounted
Sat Jan 21 13:56:58 2006
ALTER DATABASE MOUNT
Sat Jan 21 13:57:03 2006
Successful mount of redo thread 1, with mount id 3192214826.
Sat Jan 21 13:57:03 2006
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Sat Jan 21 13:57:03 2006
ALTER DATABASE OPEN
Sat Jan 21 13:57:03 2006
Beginning crash recovery of 1 threads
Sat Jan 21 13:57:03 2006
Started first pass scan
Sat Jan 21 13:57:03 2006
Completed first pass scan
0 redo blocks read, 0 data blocks need recovery
Sat Jan 21 13:57:03 2006
Started recovery at
Thread 1: logseq 44, block 2, scn 2073.1754552
Recovery of Online Redo Log: Thread 1 Group 4 Seq 44 Reading mem 0
Mem# 0 errs 0: /opt/oracle/oradata/conner/redo04.log
Sat Jan 21 13:57:03 2006
Completed redo application
Sat Jan 21 13:57:03 2006
Ended recovery at
Thread 1: logseq 44, block 2, scn 2073.1774553
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Sat Jan 21 13:57:03 2006
Thread 1 advanced to log sequence 45
Thread 1 opened at log sequence 45
Current log# 2 seq# 45 mem# 0: /opt/oracle/oradata/conner/redo02.log
Successful open of redo thread 1.
Sat Jan 21 13:57:03 2006
ARC0: Media recovery disabled
Sat Jan 21 13:57:03 2006
SMON: enabling cache recovery
Sat Jan 21 13:57:03 2006
Successfully onlined Undo Tablespace 16.
Sat Jan 21 13:57:03 2006
SMON: enabling tx recovery
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 13:57:03 2006
Database Characterset is ZHS16GBK
Sat Jan 21 13:57:03 2006
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 13:57:04 2006
Created Undo Segment _SYSSMU1$
Undo Segment 1 Onlined
Completed: ALTER DATABASE OPEN
aSat Jan 21 14:02:11 2006
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 14:03:29 2006
create undo tablespace undotbs1
datafile '/opt/oracle/oradata/conner/undotbs1.dbf' size 10M
Sat Jan 21 14:03:29 2006
Created Undo Segment _SYSSMU2$
Created Undo Segment _SYSSMU3$
Created Undo Segment _SYSSMU4$
Created Undo Segment _SYSSMU5$
Created Undo Segment _SYSSMU6$
Created Undo Segment _SYSSMU7$
Created Undo Segment _SYSSMU8$
Created Undo Segment _SYSSMU9$
Created Undo Segment _SYSSMU10$
Created Undo Segment _SYSSMU14$
Starting control autobackup
Control autobackup written to DISK device
handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20060121-00'
Completed: create undo tablespace undotbs1
datafile '/opt/ora
Sat Jan 21 14:03:43 2006
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Undo Segment 14 Onlined
Successfully onlined Undo Tablespace 1.
Undo Segment 1 Offlined
Undo Tablespace 16 successfully switched out.
Sat Jan 21 14:03:43 2006
ALTER SYSTEM SET undo_tablespace='UNDOTBS1' SCOPE=MEMORY;
Sat Jan 21 14:07:18 2006
SMON: about to recover undo segment 11
SMON: mark undo segment 11 as needs recovery
SMON: about to recover undo segment 12
SMON: mark undo segment 12 as needs recovery
SMON: about to recover undo segment 13
SMON: mark undo segment 13 as needs recovery
Sat Jan 21 14:08:06 2006
drop tablespace undotbs2
Sat Jan 21 14:08:07 2006
Starting control autobackup
Control autobackup written to DISK device
handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20060121-01'
Completed: drop tablespace undotbs2
Sat Jan 21 14:08:23 2006
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 1
Sat Jan 21 14:08:23 2006
ALTER DATABASE CLOSE NORMAL
Sat Jan 21 14:08:23 2006
SMON: disabling tx recovery
SMON: disabling cache recovery
Sat Jan 21 14:08:23 2006
Shutting down archive processes
Archiving is disabled
Sat Jan 21 14:08:23 2006
ARCH shutting down
ARC0: Archival stopped
Sat Jan 21 14:08:23 2006
ARCH shutting down
ARC1: Archival stopped
Sat Jan 21 14:08:23 2006
Thread 1 closed at log sequence 45
Successful close of redo thread 1.
Sat Jan 21 14:08:23 2006
Completed: ALTER DATABASE CLOSE NORMAL
Sat Jan 21 14:08:23 2006
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sat Jan 21 14:08:30 2006
Starting ORACLE instance (normal)
Sat Jan 21 14:08:31 2006
WARNING: EINVAL creating segment of size 0x0000000006400000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
_latch_class_1 = 10000
_latch_classes = 98:1
processes = 150
timed_statistics = FALSE
shared_pool_size = 20971520
large_pool_size = 0
java_pool_size = 0
_kghdsidx_count = 1
control_files = /opt/oracle/oradata/conner/control01.ctl,
/opt/oracle/oradata/conner/control02.ctl,
/opt/oracle/oradata/conner/control03.ctl
db_block_size = 8192
db_2k_cache_size = 20971520
db_cache_size = 41943040
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest = /opt/oracle/oradata/conner/archive
_disable_logging = TRUE
db_files = 8
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
_allow_resetlogs_corruption= TRUE
_corrupted_rollback_segments=
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
global_names = TRUE
instance_name = conner
utl_file_dir = /tmp
job_queue_processes = 0
_system_trig_enabled = FALSE
hash_join_enabled = TRUE
background_dump_dest = /opt/oracle/admin/conner/bdump
user_dump_dest = /opt/oracle/admin/conner/udump
core_dump_dest = /opt/oracle/admin/conner/cdump
audit_trail = DB
sort_area_size = 524288
db_name = conner
open_cursors = 300
sql_trace = FALSE
optimizer_mode = CHOOSE
star_transformation_enabled= FALSE
optimizer_index_cost_adj = 10000
query_rewrite_enabled = FALSE
_left_nested_loops_random= FALSE
_use_nosegment_indexes = FALSE
pga_aggregate_target = 20480000
aq_tm_processes = 0
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Sat Jan 21 14:08:31 2006
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=8
ARC0: Archival started
Sat Jan 21 14:08:31 2006
ARCH: STARTING ARCH PROCESSES COMPLETE
Sat Jan 21 14:08:31 2006
ARC0: Thread not mounted
ARC1 started with pid=9
ARC1: Archival started
ARC1: Thread not mounted
Sat Jan 21 14:08:31 2006
ALTER DATABASE MOUNT
Sat Jan 21 14:08:36 2006
Successful mount of redo thread 1, with mount id 3192249311.
Sat Jan 21 14:08:36 2006
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Sat Jan 21 14:08:36 2006
ALTER DATABASE OPEN
Sat Jan 21 14:08:36 2006
Thread 1 opened at log sequence 45
Current log# 2 seq# 45 mem# 0: /opt/oracle/oradata/conner/redo02.log
Successful open of redo thread 1.
Sat Jan 21 14:08:36 2006
ARC1: Media recovery disabled
Sat Jan 21 14:08:36 2006
SMON: enabling cache recovery
Sat Jan 21 14:08:36 2006
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Undo Segment 14 Onlined
Successfully onlined Undo Tablespace 1.
Sat Jan 21 14:08:36 2006
SMON: enabling tx recovery
Sat Jan 21 14:08:36 2006
Database Characterset is ZHS16GBK
Completed: ALTER DATABASE OPEN
ORA-00600: 内部错误代码, 参数: [4194]或[4193]
数据库版本:Oracle 11.2.0.1.0
数据库服务器操作系统:Windows server 2008
问题现象:alter database open resetlogs;时报错如下
ORA-1092 signalled during: ALTER DATABASE OPEN...
Doing block recovery for file 3 block 261754
No block recovery was needed
Errors in file e:\app\administrator\diag\rdbms\klnew\klnew\trace\klnew_ora_2072.trc (incident=92624):
ORA-00600:内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-01092: ORACLE 实例终止。强制断开连接
ORA-00600: 内部错误代码, 参数: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: e:\app\administrator\diag\rdbms\klnew\klnew\incident\incdir_92624\klnew_ora_2072_i92624.trc
Doing block recovery for file 3 block 261754
No block recovery was needed
No Resource Manager plan active
Errors in file e:\app\administrator\diag\rdbms\klnew\klnew\trace\klnew_smon_3776.trc (incident=87732):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []
Incident details in: e:\app\administrator\diag\rdbms\klnew\klnew\incident\incdir_87732\klnew_smon_3776_i87732.trc
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x66288933] [PC:0x9237D88, kgegpa()+38]
Dump file e:\app\administrator\diag\rdbms\klnew\klnew\trace\alert_klnew.log
解决方案:
解决问题时间充足时,可以通过trace日志找到损坏的回滚段,通过隐含参数屏蔽损坏的回滚段;
需要尽快解决问题时,可以直接通过隐含参数屏蔽所有的回滚段,之后启动数据库,创建新的UNDO表空间;
设置undo_management由原来的auto改成manual(undo_tablespace= SYSTEM),后可以启动数据库,但是执行Expdp或应用进行前台操作时,会报错:ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP';
ORA-01552: cannot use system rollback segment for non-system tablespace 'NNC_DATA01';
1:查看正在使用的回滚段
select segment_name, tablespace_name, status from dba_rollback_segs;
2: 使用_corrupted_rollback_segments参数可以使数据库在启动的时候,忽略损坏的回滚段,使数据库正常启动.
*._corrupted_rollback_segments=(_SYSSMU1_3086899707$,_SYSSMU2_1531987058$,_SYSSMU3_478608968$,_SYSSMU4_1451910634$,_SYSSMU5_2520346804$,_SYSSMU6_1439239625$,_SYSSMU7_1101470402$,_SYSSMU8_1682283174$,_SYSSMU9_3186340089$,_SYSSMU10_378818850$)
另外: _offline_rollback_segments参数可以让指定的回滚段处于OFFLINE状态
3: undo_management改回audo;
此时启动数据库会自动创建另一个回滚段,其他的10个回滚段会自动offline;
4:创建新的UNDO表空间
create tablespace undotbs2 datafile 'D:\APP_10.2.0.4\CJC_DATAFILE\UNDOTBS02.DBF' size 10M autoextend on;
alter system set undo_tablespace=UNDOTBS2 scope=spfile;
5:Drop tablespace <undo tablespace name> including contents and datafiles
</undo tablespace
1.数据库开启的情况下,重建undotbs,然后重新指定到新undotbs上。
2.未打开情况下,修改undo_management 参数为 manual或者(也有说并且的)提供新的回滚段。
其中:
4193:表示undo和redo不一致(Arg [a] Undo record seq number,Arg [b] Redo record seq number );
4194:表示undo和redo不一致(Arg [a] Maximum Undo record number in Undo block,Arg [b] Undo record number from Redo block)