« 偶遇孟静 | Blog首页 | 《深度解析Oracle》之《Oracle优化工具》 »
ORA-01599与max_rollback_segments
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2008/04/max_rollback_segments.html
在Oracle9i之前，如果 max_rollback_segments 参数设置不当，可能会导致数据库启动时出现ORA-01559错误：
ORA-01599 failed to acquire rollback segment (string), cache space is full (currently has (string) entries)
Cause: The amount statically allocated is not enough based on the value of the MAX_ROLLBACK_SEGMENTS parameter"
这个错误是说 max_rollback_segments 参数设置过小，通过增大该参数可以解决该问题。
max_rollback_segments 参数的含义如下：
Specifies the maximum size of the rollback segment cache in the SGA.
The number specified signifies the maximum number of rollback segments
that can be kept online (that is, status of ONLINE) simultaneously by one instance.
其缺省值为：max(30, transactions/transactions_per_rollback_segment)
而从Oracle9i开始，Oracle使用AUM模式，UNDO的管理自动化，该参数的限制作用不再，以下是我的一个测试，即使设置更小的 max_rollback_segment 数据库仍然能够Online更多的回滚段：
SQL> select * from V$RESOURCE_LIMIT;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- -----------
processes 11 13 150 150
sessions 14 18 170 170
enqueue_locks 9 13 2230 2230
enqueue_resources 9 9 968 UNLIMITED
ges_procs 0 0 0 0
ges_ress 0 0 0 UNLIMITED
ges_locks 0 0 0 UNLIMITED
ges_cache_ress 0 0 0 UNLIMITED
ges_reg_msgs 0 0 0 UNLIMITED
ges_big_msgs 0 0 0 UNLIMITED
ges_rsv_msgs 0 0 0 0
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- -----------
gcs_resources 0 0 35215 35215
gcs_shadows 0 0 35215 35215
dml_locks 0 3 748 UNLIMITED
temporary_table_locks 0 0 UNLIMITED UNLIMITED
transactions 0 4 187 UNLIMITED
branches 0 0 187 UNLIMITED
cmtcallbk 0 0 187 UNLIMITED
sort_segment_locks 0 1 UNLIMITED UNLIMITED
max_rollback_segments 11 11 38 38
max_shared_servers 0 0 20 20
parallel_max_servers 0 0 6 6
22 rows selected.
SQL> show parameter rollback
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
max_rollback_segments integer 37
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> alter system set max_rollback_segments=2 scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 219223120 bytes
Fixed Size 451664 bytes
Variable Size 134217728 bytes
Database Buffers 83886080 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from V$RESOURCE_LIMIT;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- -----------
processes 11 13 150 150
sessions 14 15 170 170
enqueue_locks 9 14 2230 2230
enqueue_resources 9 9 968 UNLIMITED
ges_procs 0 0 0 0
ges_ress 0 0 0 UNLIMITED
ges_locks 0 0 0 UNLIMITED
ges_cache_ress 0 0 0 UNLIMITED
ges_reg_msgs 0 0 0 UNLIMITED
ges_big_msgs 0 0 0 UNLIMITED
ges_rsv_msgs 0 0 0 0
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- -----------
gcs_resources 0 0 35215 35215
gcs_shadows 0 0 35215 35215
dml_locks 0 2 748 UNLIMITED
temporary_table_locks 0 0 UNLIMITED UNLIMITED
transactions 0 4 187 UNLIMITED
branches 0 0 187 UNLIMITED
cmtcallbk 0 0 187 UNLIMITED
sort_segment_locks 0 1 UNLIMITED UNLIMITED
max_rollback_segments 3 3 3 3
max_shared_servers 0 0 20 20
parallel_max_servers 0 0 6 6
22 rows selected.
SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ ONLINE
_SYSSMU2$ ONLINE
_SYSSMU3$ ONLINE
_SYSSMU4$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU10$ ONLINE
11 rows selected.
而且在Oracle9i中 ORA-01559 错误也已经被重新定义：
[oracle@jumper oracle]$ oerr ORA 01559
01559, 00000, "MAXEXTENTS for rollback segment must be greater than 1"
// *Cause: Specified MAXEXTENTS of less than 2 for rollback segment
// *Action: Specify larger MAXEXTENTS
-The End-
历史上的今天...
>> 2005-04-07文章:
>> 2004-04-07文章:
------
这篇 【ORA-01599与max_rollback_segments】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅
By eygle on 2008-04-07 10:44 | Comments (0) | Posted to FAQ | Edit |Pageviews:
搜索本站:
