解决回滚段的问题，无外乎就是新建Undo tablespace了。这个时候，又得用上另外一个隐含参数了：_corrupted_rollback_segments。
1、先创建一个新的回撤销表空间。
2、再将该表空间置为默认的撤销表空间。
3、创建pfile
4、修改pfile，将该参数加入到初始化参数中去：
_corrupted_rollback_segments=("_SYSSMU1$","_SYSSMU2$","_SYSSMU3$","_SYSSMU4$","_SYSSMU5$","_SYSSMU6$","_SYSSMU7$","_SYSSMU8$","_SYSSMU9$","_SYSSMU10$"
给一段取得回滚段的代码：
[php]
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$"
..[/php]
此时用新的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:\oracle\oradata\rman\control01.ctl, D:\oracle\oradata\rman\control02.ctl, D:\oracle\oradata\rman\control03.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:\oracle\admin\rman\
cursor_sharing = SIMILAR
hash_join_enabled = TRUE
background_dump_dest = D:\oracle\admin\rman\bdump
user_dump_dest = D:\oracle\admin\rman\udump
core_dump_dest = D:\oracle\admin\rman\cdump
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:\ORACLE\ORADATA\RMAN\REDO02.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:\ORACLE\ORADATA\RMAN\REDO03.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:\oracle\admin\rman\udump\rman_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的标志：
[php]
SQL> select segment_name,status
2 from dba_rollback_segs
3 where segment_name='_SYSSMU6$' ;
SEGMENT_NAME STATUS
------------------------------ ---------------
_SYSSMU6$ NEEDS RECOVERY
..[/php]
此时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%占用了。
1257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
