- 论坛徽章:
- 0
|
网上搜+楼上帮忙+自己试验，终于解决这个问题啦！
下面就把大概步骤写一下，供大家参考学习！
虽然
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [28], [25], [], [], [], [], []
是比较典型的跟UNDO表空间有关的错误，
但是
本例中压根没有UNDO！
原以为是不是因为没有才出的错啊，那就自己建一个呗，可是也建不了！
后来，发现在alert_sid.log文件里还有下面这个错误：
ORA-01595: error freeing extent (50) of rollback segment (4))
这种情况下应该是rollback回滚段出问题啦！
SQL> show parameter rollback
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
fast_start_parallel_rollback string
LOW
max_rollback_segments integer
73
rollback_segments string
rb1, rb2, rb3, rb4
transactions_per_rollback_segment integer
5
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
RB1 ROLLBACK ONLINE
RB2 ROLLBACK ONLINE
RB3 ROLLBACK ONLINE
RB4 ROLLBACK ONLINE
那就重建rollback回滚段试试呗：
在undo_management=AUTO时，不允许建rollback segment，否则报错
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
SQL>shutdown immediate;
SQL>startup mount;
SQL>show parameter undo; (这里可以查看undo的表空间管理方式应该是auto)
SQL>alter system set undo_management=manual scope=spfile;
SQL>shutdown immediate;
SQL>startup;
数据库实例起来后,再查看undo的表空间管理,应该就是manual了.
修改init<sid>.ora参数文件，使用Oracle隐含参数_corrupted_rollback_segments将回滚段标记为损坏，此时启动数据库，Oracle会跳过对于这些回滚段的相关操作，强制启动数据库：
_corrupted_rollback_segments = (rb1,rb2,rb3,rb4)
SQL> startup pfile=‘init<sid>.ora’；
SQL> SHOW PARAMETER ROLL
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
_corrupted_rollback_segments string
rb1, rb2, rb3, rb4
fast_start_parallel_rollback string
LOW
max_rollback_segments integer
73
rollback_segments string
transactions_per_rollback_segment integer
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
5
SQL>
SQL>
SQL>
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
RB1 ROLLBACK OFFLINE
RB2 ROLLBACK OFFLINE
RB3 ROLLBACK OFFLINE
RB4 ROLLBACK OFFLINE
在只有SYSTEM回滚段ONLINE的情况下也不能建rollback segment，提示错误
ORA-01552: cannot use system rollback segment for non-system tablespace 'ROLLBACK'
解决办法就是：先在SYSTEM表空间建一个rollback segment，把它ONLINE，再建你需要的其它rollback segments，建完后可以把先建的在SYSYEM表空间里的rollback segment删除。
SQL> create rollback segment rb0 tablespace system storage (initial 128K next 128k optimal 2560k maxextents 20);
Rollback segment created.
SQL> alter rollback segment rb0 online;
Rollback segment altered.
SQL> create rollback segment rb01 tablespace rollback storage (initial 4096K next 4096K optimal 81920K minextents 20 maxextents 4096);
Rollback segment created.
SQL> create rollback segment rb02 tablespace rollback storage (initial 4096K next 4096K optimal 81920K minextents 20 maxextents 4096);
Rollback segment created.
SQL> create rollback segment rb03 tablespace rollback storage (initial 4096K next 4096K optimal 81920K minextents 20 maxextents 4096);
Rollback segment created.
SQL> create rollback segment rb04 tablespace rollback storage (initial 4096K next 4096K optimal 81920K minextents 20 maxextents 4096);
Rollback segment created.
SQL> alter rollback segment rb01 online;
Rollback segment altered.
SQL> alter rollback segment rb02 online;
Rollback segment altered.
SQL> alter rollback segment rb03 online;
Rollback segment altered.
SQL> alter rollback segment rb04 online;
Rollback segment altered.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
RB0 SYSTEM ONLINE
RB1 ROLLBACK OFFLINE
RB2 ROLLBACK OFFLINE
RB3 ROLLBACK OFFLINE
RB4 ROLLBACK OFFLINE
RB01 ROLLBACK ONLINE
RB02 ROLLBACK ONLINE
RB03 ROLLBACK ONLINE
RB04 ROLLBACK ONLINE
10 rows selected.
SQL> alter rollback segment rb0 offline;
Rollback segment altered.
SQL> drop rollback segment rb0;
Rollback segment dropped.
SQL> drop rollback segment rb1;
Rollback segment dropped.
SQL> drop rollback segment rb2;
Rollback segment dropped.
SQL> drop rollback segment rb3;
Rollback segment dropped.
SQL> drop rollback segment rb4;
Rollback segment dropped.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
RB01 ROLLBACK ONLINE
RB02 ROLLBACK ONLINE
RB03 ROLLBACK ONLINE
RB04 ROLLBACK ONLINE
修改init<sid>.ora参数文件，变更rollback_segments，并取消_corrupted_rollback_segments设置:
rollback_segments = (rb01,rb02,rb03,rb04)
编辑init.ora参数文件，添加：
rollback_segments = (rb01, rb02, rb03, rb04)
否则，新建的rollback不会自动ONLINE！
别忘了：
SQL> alter system set undo_management=auto scope=spfile;
System altered.
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 438916600 bytes
Fixed Size 456184 bytes
Variable Size 268435456 bytes
Database Buffers 167772160 bytes
Redo Buffers 2252800 bytes
Database mounted.
Database opened.
至此，一切正常啦：
SQL> show parameter undo
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
undo_management string
AUTO
undo_retention integer
900
undo_suppress_errors boolean
FALSE
undo_tablespace string
SQL> show parameter roll
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
fast_start_parallel_rollback string
LOW
max_rollback_segments integer
73
rollback_segments string
rb01, rb02, rb03, rb04
transactions_per_rollback_segment integer
5
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
RB01 ROLLBACK ONLINE
RB02 ROLLBACK ONLINE
RB03 ROLLBACK ONLINE
RB04 ROLLBACK ONLINE |
|
免费注册
楼主: 二十八画生_cu
发表于 2011-04-02 16:19
