ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转载:当前REDO日志损坏的解决

转载:当前REDO日志损坏的解决

原创 Linux操作系统 作者:howard_zhang 时间:2008-02-27 16:01:28 0 删除 编辑
    感觉这篇文章在处理这个问题上有些新异,所以加到了BLOG中。

总结起来,遇到日志损坏的解决办法有两种:
一、直接清除损坏的日志文件
二、对于损坏的是当前日志文件,只有进行不完全恢复,然后重建撤销表空间。

Redo 损坏彻底解决!
前几天在看隐含参数,为了验证一下,便加了一大堆进行,结果。。。。居然导致Redo损坏:
ORA-16038: log 3 sequence# 72 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: 'D:ORACLEORADATARMANREDO03.LOG'

Sat May 14 23:16:49 2005
Errors in file d:oracleadminrmanbdumprman_arc0_736.trc:
ORA-16038: log 3 sequence# 72 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: 'D:ORACLEORADATARMANREDO03.LOG'

此时的初始化参数:
processes = 150
timed_statistics = FALSE
_disable_system_state = TRUE
shared_pool_size = 83886080
large_pool_size = 8388608
java_pool_size = 0
trace_enabled = TRUE
backup_tape_io_slaves = TRUE
_dump_system_state_scope = LOCAL
control_files = D:oracleoradatarmancontrol01.ctl, D:oracleoradatarmancontrol02.ctl, D:oracleoradatarmancontrol03.ctl
db_block_size = 4096
db_cache_size = 134217728
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_checkpoint_timeout = 0
_disable_logging = TRUE
db_files = 65534
db_file_multiblock_read_count= 16
fast_start_mttr_target = 0
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = rman
utl_file_dir = d:oracleadminrman
cursor_sharing = SIMILAR
hash_join_enabled = TRUE
background_dump_dest = D:oracleadminrmanbdump
user_dump_dest = D:oracleadminrmanudump
core_dump_dest = D:oracleadminrmancdump
session_max_open_files = 1000
sort_area_size = 2097152
db_name = rman
open_cursors = 300
sql_trace = FALSE
partition_view_enabled = TRUE
star_transformation_enabled= TRUE
query_rewrite_enabled = TRUE
pga_aggregate_target = 10485760
statistics_level = ALL

这个祸手就是 _disable_logging = TRUE。

SQL> select ksppinm,ksppdesc
2 from x$ksppi
3 where ksppinm='_disable_logging' ;

KSPPINM KSPPDESC
-------------------- --------------------
_disable_logging Disable logging

至于为什么要加这个参数,是想看一下这个参数在数据导入时,是否Disable logging。结果事与愿违,倒是弄出错误来。

为了打开数据库,首先想到的是将坏的日志清除掉,但是:
SQL> alter database clear unarchived logfile group 3 ;
alter database clear unarchived logfile group 3
*
ERROR 位于第 1 行:
ORA-01624: 线程3的紧急恢复需要日志1
ORA-00312: 联机日志 3 线程 1: 'D:ORACLEORADATARMANREDO03.LOG'

SQL> alter database clear unarchived logfile group 3 unrecoverable datafile;
alter database clear unarchived logfile group 3 unrecoverable datafile
*
ERROR 位于第 1 行:
ORA-01624: 线程3的紧急恢复需要日志1
ORA-00312: 联机日志 3 线程 1: 'D:ORACLEORADATARMANREDO03.LOG'

清理日志文件行不通了,只有加入隐含参数,进行不完全恢复了。


不完全恢复
先加入隐含参数

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile ;

系统已更改。

SQL> alter system set "_allow_terminal_recovery_corruption"=true scope=spfile ;

系统已更改。

SQL> shutdown abort ;
ORACLE 例程已经关闭。

这两个参数,如果忘了或者记不全,也不要紧,可以从固定表X$KSPPI中查到:


代码:--------------------------------------------------------------------------------
SQL> shutdown abort ;
ORACLE 例程已经关闭。
SQL> startup nomount ;
ORACLE 例程已经启动。
SQL> select ksppinm,ksppdesc
2 from x$ksppi
3 where ksppinm like '_allow%' escape ''
4 ;

KSPPINM KSPPDESC
----------------------------------- ----------------------------------------
_allow_error_simulation Allow error simulation for testing
_allow_resetlogs_corruption allow resetlogs even if it will cause co
rruption

_allow_terminal_recovery_corruption Finish terminal recovery even if it may
cause corruption

_allow_read_only_corruption allow read-only open even if database is
--------------------------------------------------------------------------------


SQL>startup
.....
数据库装载完毕。
ORA-00354: 损坏重做日志块标题
ORA-00353: 日志损坏接近块 51328 更改 1029419 时间
ORA-00312: 联机日志 3 线程 1: 'D:ORACLEORADATARMANREDO03.LOG'

现在开始不完全恢复:
SQL> recover database until cancel;
ORA-00279: 更改 1029419 (在 05/15/2005 01:21:08 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:ORACLEORA92RDBMSARC00078.001
ORA-00280: 更改 1029419 对于线程 1 是按序列 # 78 进行的


指定日志: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件1需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'F:RMANSYSTEM01.DBF'


ORA-01112: 未启动介质恢复

SQL> recover database until cancel;
ORA-00279: 更改 1029419 (在 05/15/2005 01:21:08 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:ORACLEORA92RDBMSARC00078.001
ORA-00280: 更改 1029419 对于线程 1 是按序列 # 78 进行的


指定日志: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: 无法打开存档日志 'D:ORACLEORA92RDBMSARC00078.001'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。


ORA-00308: 无法打开存档日志 'D:ORACLEORA92RDBMSARC00078.001'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。

SQL> recover database until cancel;
ORA-00279: 更改 1029419 (在 05/15/2005 01:21:08 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:ORACLEORA92RDBMSARC00078.001
ORA-00280: 更改 1029419 对于线程 1 是按序列 # 78 进行的


指定日志: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: 无法打开存档日志 'D:ORACLEORA92RDBMSARC00078.001'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。


ORA-00308: 无法打开存档日志 'D:ORACLEORA92RDBMSARC00078.001'
ORA-27041: 无法打开文件
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。


ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件1需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'F:RMANSYSTEM01.DBF'

恢复完成。再用resetlogs打开数据库:

SQL> alter database open resetlogs ;
ERROR 位于第 1 行:
ORA-03113: 通信通道的文件结束

这是打开过程中的ALERT内容:
Sun May 15 01:32:46 2005
alter database open resetlogs
Sun May 15 01:32:47 2005
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 1029419
Resetting resetlogs activation ID 1445126762 (0x5622e26a)
Sun May 15 01:32:55 2005
Assigning activation ID 1444730695 (0x561cd747)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: D:ORACLEORADATARMANREDO01.LOG
Successful open of redo thread 1.
Sun May 15 01:32:55 2005
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun May 15 01:32:55 2005
SMON: enabling cache recovery
Sun May 15 01:32:56 2005
ARC1: Media recovery disabled
Sun May 15 01:32:59 2005
Errors in file d:oracleadminrmanudumprman_ora_1096.trc:
ORA-00600: 内部错误代码,参数: [2662], [0], [1029435], [0], [1037356], [8388785], [], []

Sun May 15 01:33:03 2005
Errors in file d:oracleadminrmanudumprman_ora_1096.trc:
ORA-00600: 内部错误代码,参数: [2662], [0], [1029435], [0], [1037356], [8388785], [], []

Sun May 15 01:33:03 2005
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Sun May 15 01:33:04 2005
Errors in file d:oracleadminrmanbdumprman_pmon_796.trc:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], []

Instance terminated by USER, pid = 1096
ORA-1092 signalled during: alter database open resetlogs ...
Sun May 15 01:38:05 2005
USER: terminating instance due to error 1092
Instance terminated by USER, pid = 1096

等待很久,居然报这个错!其实不用怕,恢复已经完成,只要重新打开就可以了。


ORA-600 问题的解决。
从上面可以看到,报告了一堆600错误。
经过reseglogs后打开数据库后,明显比较慢,并且CPU占用一直是100%,这怎么用啊!以前有人讲过导出数据库再重建,这样未必太麻烦了,这600错误应该可以解决的,即使是Bug,也有相应的补丁可打的。
先看看启动后都报些什么错:

Sun May 15 01:40:33 2005
Errors in file d:oracleadminrmanudumprman_p000_1388.trc:
ORA-00600: internal error code, arguments: [2023], [0], [0], [], [], [], [], []

Completed: ALTER DATABASE OPEN
Sun May 15 01:40:42 2005
Errors in file d:oracleadminrmanudumprman_p000_1388.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 1029420 time
ORA-00334: archived log: 'D:ORACLEORADATARMANREDO01.LOG'
ORA-00600: internal error code, arguments: [2023], [0], [0], [], [], [], [], []

Sun May 15 01:40:43 2005
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun May 15 01:40:46 2005
Errors in file d:oracleadminrmanbdumprman_smon_1304.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 1029420 time
ORA-00334: archived log: 'D:ORACLEORADATARMANREDO01.LOG'

Sun May 15 01:40:47 2005
Errors in file d:oracleadminrmanbdumprman_smon_1304.trc:
ORA-00600: internal error code, arguments: [4136], [0], [], [], [], [], [], []

ORACLE Instance rman (pid = 6) - Error 600 encountered while recovering transaction (6, 19).
Sun May 15 01:44:36 2005
Errors in file d:oracleadminrmanbdumprman_smon_1304.trc:
ORA-00600: internal error code, arguments: [4136], [0], [], [], [], [], [], []

Sun May 15 01:53:52 2005
Errors in file d:oracleadminrmanudumprman_p000_292.trc:
ORA-00600: internal error code, arguments: [2023], [0], [0], [], [], [], [], []

Sun May 15 01:57:27 2005
Errors in file d:oracleadminrmanudumprman_p000_292.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 1029420 time
ORA-00334: archived log: 'D:ORACLEORADATARMANREDO01.LOG'
ORA-00600: internal error code, arguments: [2023], [0], [0], [], [], [], [], []

Sun May 15 01:57:27 2005
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Sun May 15 01:57:30 2005
Errors in file d:oracleadminrmanbdumprman_smon_1304.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 1029420 time
ORA-00334: archived log: 'D:ORACLEORADATARMANREDO01.LOG'

Sun May 15 01:57:30 2005
Errors in file d:oracleadminrmanbdumprman_smon_1304.trc:
ORA-00600: internal error code, arguments: [4136], [0], [], [], [], [], [], []

查了一下4136:
http://metalink.oracle.com/metalink...p_id=612556.995
With knowing only the limited information and circumstances you've provided thus far, I can only tell advise only on a generic level:

This error means that while backing out an undo record, Oracle finds a block in the undo chain is NOT an undo block but has a different block type.

Typcially, if there is no corruption in the redo logs you can
identify the file containing the bad RBS block and treat the problem
as if this file is corrupt.
E.g., if in archivelog mode, restore and roll forward.
Otherwise, restore the database from a valid backup and roll forward
to a point in time prior to the corruption.

OERI with code 4136 comes from kernel transaction undo layer, and might be caused either by block corruptions or ghost transactions.
the best (and the only) choice for you is to log a service request to your service provider (either OSS or third-party) - there's almost nothing you can do with yourself.


从上面可以看出,是回滚段的问题(还真是麻烦!),那就解决回滚断的问题吧!


解决回滚段的问题
解决回滚段的问题,无外乎就是新建Undo tablespace了。这个时候,又得用上另外一个隐含参数了:_corrupted_rollback_segments。

1、先创建一个新的回撤销表空间。
2、再将该表空间置为默认的撤销表空间。
3、创建pfile
4、修改pfile,将该参数加入到初始化参数中去:
_corrupted_rollback_segments=("_SYSSMU1$","_SYSSMU2$","_SYSSMU3$","_SYSSMU4$","_SYSSMU5$","_SYSSMU6$","_SYSSMU7$","_SYSSMU8$","_SYSSMU9$","_SYSSMU10$")

给一段取得回滚段的代码:


代码:--------------------------------------------------------------------------------
declare
segs varchar2(200);
begin
for i in (
select segment_name seg
from dba_rollback_segs
where tablespace_name='UNDOTBS1'
)
loop
segs:=segs||',"'||i.seg||'"';
end loop;
segs:=substr(segs,2);
dbms_output.put_line(segs);
end;

"_SYSSMU1$","_SYSSMU2$","_SYSSMU3$","_SYSSMU4","_SYSSMU5$","_SYSSMU6$","_SYSSMU7","_SYSSMU8$","_SYSSMU9$","_SYSSMU10$"
..--------------------------------------------------------------------------------


此时用新的pfile启动。ALERT的部分内容:
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = FALSE
shared_pool_size = 83886080
_shared_pool_reserved_pct= 20
_shared_pool_reserved_min_alloc= 40960
large_pool_size = 8388608
java_pool_size = 0
trace_enabled = TRUE
backup_tape_io_slaves = TRUE
_object_statistics = TRUE
_dump_system_state_scope = GLOBAL
control_files = D:oracleoradatarmancontrol01.ctl, D:oracleoradatarmancontrol02.ctl, D:oracleoradatarmancontrol03.ctl
db_block_size = 4096
db_cache_size = 134217728
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_checkpoint_timeout = 0
_disable_logging = FALSE
db_files = 65534
db_file_multiblock_read_count= 16
fast_start_mttr_target = 0
_allow_resetlogs_corruption= TRUE
_allow_terminal_recovery_corruption= TRUE
_log_space_errors = TRUE
_rollback_segment_initial= 15
_rollback_segment_count = 20
undo_management = AUTO
undo_tablespace = UNDOTBS2
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = rman
utl_file_dir = d:oracleadminrman
cursor_sharing = SIMILAR
hash_join_enabled = TRUE
background_dump_dest = D:oracleadminrmanbdump
user_dump_dest = D:oracleadminrmanudump
core_dump_dest = D:oracleadminrmancdump
session_max_open_files = 1000
sort_area_size = 2097152
_sort_multiblock_read_count= 16
db_name = rman
open_cursors = 300
sql_trace = FALSE
partition_view_enabled = TRUE
star_transformation_enabled= TRUE
_fast_full_scan_enabled = TRUE
_optim_enhance_nnull_detection= TRUE
_temp_tran_cache = TRUE
_serial_direct_read = TRUE
_ncmb_readahead_enabled = 16
query_rewrite_enabled = TRUE
pga_aggregate_target = 10485760
_smm_auto_cost_enabled = TRUE
_smm_advice_enabled = TRUE
_rowsource_execution_statistics= TRUE
statistics_level = ALL
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
Mon May 16 21:15:15 2005
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=8
ARC0: Archival started
ARC1 started with pid=9
Mon May 16 21:15:15 2005
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC1: Archival started
Mon May 16 21:15:16 2005
ARC0: Thread not mounted
Mon May 16 21:15:17 2005
ARC1: Thread not mounted
Mon May 16 21:15:19 2005
ALTER DATABASE MOUNT
Mon May 16 21:15:24 2005
Successful mount of redo thread 1, with mount id 1444895591.
Mon May 16 21:15:24 2005
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Mon May 16 21:15:25 2005
ALTER DATABASE OPEN
Mon May 16 21:15:25 2005
Beginning crash recovery of 1 threads
Mon May 16 21:15:26 2005
Started first pass scan
Mon May 16 21:15:27 2005
Completed first pass scan
289 redo blocks read, 97 data blocks need recovery
Mon May 16 21:15:27 2005
Started recovery at
Thread 1: logseq 17, block 3, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 2 Seq 17 Reading mem 0
Mem# 0 errs 0: D:ORACLEORADATARMANREDO02.LOG
Mon May 16 21:15:28 2005
Ended recovery at
Thread 1: logseq 17, block 292, scn 0.1218622
97 data blocks read, 97 data blocks written, 289 redo blocks read
Crash recovery completed successfully
Mon May 16 21:15:30 2005
Thread 1 advanced to log sequence 18
Thread 1 opened at log sequence 18
Current log# 3 seq# 18 mem# 0: D:ORACLEORADATARMANREDO03.LOG
Successful open of redo thread 1.
Mon May 16 21:15:31 2005
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon May 16 21:15:31 2005
SMON: enabling cache recovery
Mon May 16 21:15:32 2005
ARC0: Media recovery disabled
Mon May 16 21:15:34 2005
Undo Segment 11 Onlined
Undo Segment 12 Onlined
Undo Segment 13 Onlined
Undo Segment 14 Onlined
Undo Segment 15 Onlined
Undo Segment 16 Onlined
Undo Segment 17 Onlined
Undo Segment 18 Onlined
Undo Segment 19 Onlined
Undo Segment 20 Onlined
Successfully onlined Undo Tablespace 7.
Mon May 16 21:15:34 2005
SMON: enabling tx recovery
Mon May 16 21:15:38 2005
Database Characterset is ZHS16GBK
Mon May 16 21:15:38 2005
SMON: about to recover undo segment 6
Mon May 16 21:15:44 2005
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Mon May 16 21:15:46 2005
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
Mon May 16 21:15:46 2005
Errors in file d:oracleadminrmanudumprman_p000_1040.trc:
ORA-00600: internal error code, arguments: [2023], [0], [0], [], [], [], [], []

SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as available
.................................................................
从这里可以看出,数据库试图恢复segment 6。看一下segment 6的标志:

代码:--------------------------------------------------------------------------------
SQL> select segment_name,status
2 from dba_rollback_segs
3 where segment_name='_SYSSMU6$' ;

SEGMENT_NAME STATUS
------------------------------ ---------------
_SYSSMU6$ NEEDS RECOVERY

..--------------------------------------------------------------------------------

此时1~10的其它回滚段状态为offline,现在都可以删除了:
SQL> drop rollback segment "_SYSSMU1$" ;

回退段已删除。

SQL> drop rollback segment "_SYSSMU2$" ;

回退段已删除。

SQL> drop rollback segment "_SYSSMU3$" ;

回退段已删除。

SQL> drop rollback segment "_SYSSMU4$" ;

回退段已删除。

SQL> drop rollback segment "_SYSSMU5$" ;

回退段已删除。

SQL> drop rollback segment "_SYSSMU6$" ;

回退段已删除。

SQL> drop rollback segment "_SYSSMU7$" ;
drop rollback segment "_SYSSMU7$"
*
回退段已删除。

SQL> drop rollback segment "_SYSSMU8$" ;

回退段已删除。

SQL> drop rollback segment "_SYSSMU9$" ;

回退段已删除。

SQL> drop rollback segment "_SYSSMU10$" ;

回退段已删除。


ALERT文件的内容:
Mon May 16 21:30:00 2005
drop rollback segment "_SYSSMU7$"
Mon May 16 21:30:00 2005
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
Mon May 16 21:30:00 2005
Completed: drop rollback segment "_SYSSMU7$"
Mon May 16 21:35:07 2005
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
Mon May 16 21:39:56 2005
drop rollback segment "_SYSSMU1$"
Mon May 16 21:39:56 2005
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
Mon May 16 21:39:56 2005
Completed: drop rollback segment "_SYSSMU1$"
Mon May 16 21:39:59 2005
drop rollback segment "_SYSSMU2$"
Mon May 16 21:39:59 2005
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
Mon May 16 21:39:59 2005
Completed: drop rollback segment "_SYSSMU2$"
Mon May 16 21:40:01 2005
drop rollback segment "_SYSSMU3$"
Mon May 16 21:40:01 2005
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
Mon May 16 21:40:01 2005
Completed: drop rollback segment "_SYSSMU3$"
Mon May 16 21:40:03 2005
drop rollback segment "_SYSSMU4$"
Mon May 16 21:40:03 2005
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
Mon May 16 21:40:03 2005
Completed: drop rollback segment "_SYSSMU4$"
Mon May 16 21:40:06 2005
drop rollback segment "_SYSSMU5$"
Mon May 16 21:40:06 2005
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
Mon May 16 21:40:06 2005
Completed: drop rollback segment "_SYSSMU5$"
Mon May 16 21:40:09 2005
drop rollback segment "_SYSSMU6$"
Completed: drop rollback segment "_SYSSMU6$"
Mon May 16 21:40:12 2005
drop rollback segment "_SYSSMU7$"
ORA-1534 signalled during: drop rollback segment "_SYSSMU7$" ...
Mon May 16 21:40:15 2005
drop rollback segment "_SYSSMU8$"
Completed: drop rollback segment "_SYSSMU8$"
Mon May 16 21:40:18 2005
drop rollback segment "_SYSSMU9$"
Completed: drop rollback segment "_SYSSMU9$"
Mon May 16 21:40:21 2005
drop rollback segment "_SYSSMU10$"
Completed: drop rollback segment "_SYSSMU10$"

既然这些回滚段已经删除了,那_corrupted_rollback_segments已经不用了,将其清除或者值清空:
ALTER SYSTEM SET _corrupted_rollback_segments='' SCOPE=SPFILE;

当再次打开数据库后,一切就跟原来一样了,CPU也不100%占用了。

尾声
需要注意的是:
在加入_corrupted_rollback_segments参数后,如果没有指定新的默认撤销表空间,数据库起动不了:
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = FALSE
shared_pool_size = 83886080
_shared_pool_reserved_pct= 20
_shared_pool_reserved_min_alloc= 40960
large_pool_size = 8388608
java_pool_size = 0
trace_enabled = TRUE
backup_tape_io_slaves = TRUE
_object_statistics = TRUE
_dump_system_state_scope = GLOBAL
control_files = D:oracleoradatarmancontrol01.ctl, D:oracleoradatarmancontrol02.ctl, D:oracleoradatarmancontrol03.ctl
db_block_size = 4096
db_cache_size = 134217728
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_checkpoint_timeout = 0
_disable_logging = FALSE
db_files = 65534
db_file_multiblock_read_count= 16
fast_start_mttr_target = 0
_allow_resetlogs_corruption= TRUE
_allow_terminal_recovery_corruption= TRUE
_log_space_errors = TRUE
_rollback_segment_initial= 15
_rollback_segment_count = 20
_corrupted_rollback_segments= _SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$
undo_management = AUTO
undo_tablespace =
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = rman
utl_file_dir = d:oracleadminrman
cursor_sharing = SIMILAR
hash_join_enabled = TRUE
background_dump_dest = D:oracleadminrmanbdump
user_dump_dest = D:oracleadminrmanudump
core_dump_dest = D:oracleadminrmancdump
session_max_open_files = 1000
sort_area_size = 2097152
_sort_multiblock_read_count= 16
db_name = rman
open_cursors = 300
sql_trace = FALSE
partition_view_enabled = TRUE
star_transformation_enabled= TRUE
_fast_full_scan_enabled = TRUE
_optim_enhance_nnull_detection= TRUE
_temp_tran_cache = TRUE
_serial_direct_read = TRUE
_ncmb_readahead_enabled = 16
query_rewrite_enabled = TRUE
pga_aggregate_target = 10485760
_smm_auto_cost_enabled = TRUE
_smm_advice_enabled = TRUE
_rowsource_execution_statistics= TRUE
statistics_level = ALL
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
Mon May 16 20:58:43 2005
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=8
ARC1 started with pid=9
Mon May 16 20:58:44 2005
ARC0: Archival started
Mon May 16 20:58:44 2005
ARCH: STARTING ARCH PROCESSES COMPLETE
Mon May 16 20:58:44 2005
ARC0: Thread not mounted
Mon May 16 20:58:45 2005
ARC1: Archival started
Mon May 16 20:58:45 2005
ARC1: Thread not mounted
Mon May 16 20:58:47 2005
ALTER DATABASE MOUNT
Mon May 16 20:58:52 2005
Successful mount of redo thread 1, with mount id 1444888711.
Mon May 16 20:58:52 2005
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Mon May 16 20:58:53 2005
ALTER DATABASE OPEN
Mon May 16 20:58:54 2005
Beginning crash recovery of 1 threads
Mon May 16 20:58:54 2005
Started first pass scan
Mon May 16 20:58:55 2005
Completed first pass scan
97 redo blocks read, 46 data blocks need recovery
Mon May 16 20:58:55 2005
Started recovery at
Thread 1: logseq 13, block 3, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13 Reading mem 0
Mem# 0 errs 0: D:ORACLEORADATARMANREDO01.LOG
Mon May 16 20:58:56 2005
Ended recovery at
Thread 1: logseq 13, block 100, scn 0.1137788
46 data blocks read, 46 data blocks written, 97 redo blocks read
Crash recovery completed successfully
Mon May 16 20:58:57 2005
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 14
Thread 1 opened at log sequence 14
Current log# 2 seq# 14 mem# 0: D:ORACLEORADATARMANREDO02.LOG
Successful open of redo thread 1.
Mon May 16 20:58:58 2005
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon May 16 20:58:58 2005
SMON: enabling cache recovery
Mon May 16 20:58:59 2005
ARC0: Evaluating archive log 1 thread 1 sequence 13
ARC0: Beginning to archive log 1 thread 1 sequence 13
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:ORACLEORA92RDBMSARC00013.001'
ARC0: Completed archiving log 1 thread 1 sequence 13
Mon May 16 20:59:01 2005
Undo Segment 6 Onlined
Successfully onlined Undo Tablespace 1.
Mon May 16 20:59:01 2005
SMON: enabling tx recovery
Mon May 16 20:59:01 2005
Database Characterset is ZHS16GBK
Mon May 16 20:59:06 2005
Errors in file d:oracleadminrmanudumprman_ora_704.trc:
ORA-00600: 内部错误代码,参数: [4193], [0], [70], [], [], [], [], []

Recovery of Online Redo Log: Thread 1 Group 2 Seq 14 Reading mem 0
Mem# 0 errs 0: D:ORACLEORADATARMANREDO02.LOG
Recovery of Online Redo Log: Thread 1 Group 2 Seq 14 Reading mem 0
Mem# 0 errs 0: D:ORACLEORADATARMANREDO02.LOG
Mon May 16 20:59:13 2005
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery
SMON: about to recover undo segment 5
Mon May 16 20:59:14 2005
Errors in file d:oracleadminrmanudumprman_ora_704.trc:
ORA-00607: 当更改数据块时出现内部错误
ORA-00600: 内部错误代码,参数: [4193], [0], [70], [], [], [], [], []

SMON: mark undo segment 5 as needs recovery
Mon May 16 20:59:14 2005
Error 607 happened during db open, shutting down database
Mon May 16 20:59:14 2005
USER: terminating instance due to error 607
Mon May 16 20:59:15 2005
Errors in file d:oracleadminrmanbdumprman_pmon_532.trc:
ORA-00607: Internal error occurred while making a change to a data block

Mon May 16 20:59:15 2005
Errors in file d:oracleadminrmanudumprman_p000_1172.trc:
ORA-00600: internal error code, arguments: [2023], [0], [0], [], [], [], [], []

Mon May 16 20:59:15 2005
SMON: about to recover undo segment 7

Instance terminated by USER, pid = 704
ORA-1092 signalled during: ALTER DATABASE OPEN...


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

下一篇: Oradim工具的用法
请登录后发表评论 登录
全部评论

注册时间:2008-01-22

  • 博文量
    42
  • 访问量
    130683