首页手记 undo_management设置与隐含参数

undo_management设置与隐含参数

标签：

Oracle

一环境：

oracle版本：10.2.0.1.0

当前undo表空间：

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS4

当前系统内的还原表空间：

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name like '%UNDO%';

TABLESPACE_NAME STATUS

------------------------------ ---------

UNDOTBS4 ONLINE

当前undo表空间undotbs4的所有回滚段状况如下：

SQL> select * from v$rollname; （查询当前系统活动回滚段）

USN NAME

---------- ----------------------------------------

0 SYSTEM

1 _SYSSMU1$

2 _SYSSMU2$

3 _SYSSMU3$

4 _SYSSMU4$

5 _SYSSMU5$

6 _SYSSMU6$

7 _SYSSMU7$

8 _SYSSMU8$

9 _SYSSMU9$

11 _SYSSMU11$

11 rows selected.

SQL> select segment_id,segment_name,tablespace_name,status from dba_rollback_segs;（查询系统内所有回滚段信息）

SEGMENT_ID SEGMENT_NAME TABLESPACE_NAME STATUS

---------- ------------------------------ ------------------------------ ----------------

0 SYSTEM SYSTEM ONLINE

1 _SYSSMU1$ UNDOTBS4 ONLINE

2 _SYSSMU2$ UNDOTBS4 ONLINE

3 _SYSSMU3$ UNDOTBS4 ONLINE

4 _SYSSMU4$ UNDOTBS4 ONLINE

5 _SYSSMU5$ UNDOTBS4 ONLINE

6 _SYSSMU6$ UNDOTBS4 ONLINE

7 _SYSSMU7$ UNDOTBS4 ONLINE

8 _SYSSMU8$ UNDOTBS4 ONLINE

9 _SYSSMU9$ UNDOTBS4 ONLINE

11 _SYSSMU11$ UNDOTBS4 ONLINE

11 rows selected.

SQL>

二测试

当系统有一个还原表空间undotbs4

测试1 undo_management=auto ，用参数*._offline_rollback_segments和*._corrupted_rollback_segments标记当前undo表空间undotbs4的所有段，查询当前oracle所有活动回滚段。

测试2 undo_management=manual ，用参数*._offline_rollback_segments和*._corrupted_rollback_segments标记当前undo表空间undotbs4的所有段，查询当前oracle所有活动回滚段。

当系统有多个还原表空间，undotbs4、undotbs3、undotbs2、undotbs1

测试3 undo_management=auto ，用参数*._offline_rollback_segments和*._corrupted_rollback_segments标记当前undo表空间undotbs4的所有段，查询当前oracle所有活动回滚段。

测试4 undo_management=manual，用参数*._offline_rollback_segments和*._corrupted_rollback_segments标记当前undo表空间undotbs4的所有段，查询当前oracle所有活动回滚段。

三实验过程

当系统仅有一个还原表空间undotbs4

测试1：

initCRM.ora 内容如下：

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS4'

*.user_dump_dest='/oracle/app/admin/CRM/udump'

*._offline_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

oracle启动过程如下：

SQL> startup pfile='/oracle/app/db1/dbs/initCRM.ora';

ORACLE instance started.

Total System Global Area 322961408 bytes

Fixed Size 2020480 bytes

Variable Size 83889024 bytes

Database Buffers 230686720 bytes

Redo Buffers 6365184 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

告警文件错误信息如下：

Errors in file /oracle/app/admin/CRM/udump/crm_ora_9154.trc:

ORA-30012: undo tablespace 'UNDOTBS4' does not exist or of wrong type

Tue Nov 13 22:13:15 2012

Error 30012 happened during db open, shutting down database

USER: terminating instance due to error 30012

Instance terminated by USER, pid = 9154

ORA-1092 signalled during: ALTER DATABASE OPEN...

总结：库都启动不了，回滚段信息就不要看了。

-------------------------------------------------------------------------------

测试2：

initCRM.ora 内容如下：

*.undo_management='MANUAL'

*.undo_tablespace='UNDOTBS4'

*.user_dump_dest='/oracle/app/admin/CRM/udump'

*._offline_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

oracle启动过程如下：

SQL> startup pfile='/oracle/app/db1/dbs/initCRM.ora';

ORACLE instance started.

Total System Global Area 322961408 bytes

Fixed Size 2020480 bytes

Variable Size 83889024 bytes

Database Buffers 230686720 bytes

Redo Buffers 6365184 bytes

Database mounted.

Database opened.

当前oracle所有活动回滚段状态如下:

select segment_id,segment_name,tablespace_name,status from dba_rollback_segs

SEGMENT_ID SEGMENT_NAME TABLESPACE_NAME STATUS

---------- ------------------------------ ------------------------------ ----------------

0 SYSTEM SYSTEM ONLINE

1 _SYSSMU1$ UNDOTBS4 OFFLINE

2 _SYSSMU2$ UNDOTBS4 OFFLINE

3 _SYSSMU3$ UNDOTBS4 OFFLINE

4 _SYSSMU4$ UNDOTBS4 OFFLINE

5 _SYSSMU5$ UNDOTBS4 OFFLINE

6 _SYSSMU6$ UNDOTBS4 OFFLINE

7 _SYSSMU7$ UNDOTBS4 OFFLINE

8 _SYSSMU8$ UNDOTBS4 OFFLINE

9 _SYSSMU9$ UNDOTBS4 OFFLINE

11 _SYSSMU11$ UNDOTBS4 OFFLINE

11 rows selected.

SQL> select * from v$rollname;

USN NAME

---------- ------------------------------

0 SYSTEM

当系统有多个还原表空间时进行如下测试:

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name like '%UNDO%';

TABLESPACE_NAME STATUS

------------------------------ ---------

UNDOTBS1 ONLINE

UNDOTBS2 ONLINE

UNDOTBS3 ONLINE

UNDOTBS4 ONLINE

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string MANUAL

undo_retention integer 900

undo_tablespace string UNDOTBS4

----------------------------------------------------------------------------------------------------------

测试3

initCRM.ora 内容如下：

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS4'

*.user_dump_dest='/oracle/app/admin/CRM/udump'

*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

*._offline_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

oracle启动过程如下：

SQL> startup pfile='/oracle/app/db1/dbs/initCRM.ora';

ORACLE instance started.

Total System Global Area 322961408 bytes

Fixed Size 2020480 bytes

Variable Size 83889024 bytes

Database Buffers 230686720 bytes

Redo Buffers 6365184 bytes

Database mounted.

Database opened.

当前oracle所有活动回滚段状态如下:

SEGMENT_ID SEGMENT_NAME TABLESPACE_NAME STATUS

---------- ------------------------------ ------------------------------ ----------------

0 SYSTEM SYSTEM ONLINE

1 _SYSSMU1$ UNDOTBS4 OFFLINE

2 _SYSSMU2$ UNDOTBS4 OFFLINE

3 _SYSSMU3$ UNDOTBS4 OFFLINE

4 _SYSSMU4$ UNDOTBS4 OFFLINE

5 _SYSSMU5$ UNDOTBS4 OFFLINE

6 _SYSSMU6$ UNDOTBS4 OFFLINE

7 _SYSSMU7$ UNDOTBS4 OFFLINE

8 _SYSSMU8$ UNDOTBS4 OFFLINE

9 _SYSSMU9$ UNDOTBS4 OFFLINE

11 _SYSSMU11$ UNDOTBS4 OFFLINE

12 _SYSSMU12$ UNDOTBS1 OFFLINE

13 _SYSSMU13$ UNDOTBS1 OFFLINE

14 _SYSSMU14$ UNDOTBS1 OFFLINE

15 _SYSSMU15$ UNDOTBS1 OFFLINE

16 _SYSSMU16$ UNDOTBS1 OFFLINE

17 _SYSSMU17$ UNDOTBS1 OFFLINE

18 _SYSSMU18$ UNDOTBS1 OFFLINE

19 _SYSSMU19$ UNDOTBS1 OFFLINE

20 _SYSSMU20$ UNDOTBS1 OFFLINE

21 _SYSSMU21$ UNDOTBS1 OFFLINE

22 _SYSSMU22$ UNDOTBS2 OFFLINE

23 _SYSSMU23$ UNDOTBS2 OFFLINE

24 _SYSSMU24$ UNDOTBS2 OFFLINE

25 _SYSSMU25$ UNDOTBS2 OFFLINE

26 _SYSSMU26$ UNDOTBS2 OFFLINE

27 _SYSSMU27$ UNDOTBS2 OFFLINE

28 _SYSSMU28$ UNDOTBS2 OFFLINE

29 _SYSSMU29$ UNDOTBS2 OFFLINE

30 _SYSSMU30$ UNDOTBS2 OFFLINE

31 _SYSSMU31$ UNDOTBS2 OFFLINE

32 _SYSSMU32$ UNDOTBS3 OFFLINE

33 _SYSSMU33$ UNDOTBS3 OFFLINE

34 _SYSSMU34$ UNDOTBS3 OFFLINE

35 _SYSSMU35$ UNDOTBS3 OFFLINE

36 _SYSSMU36$ UNDOTBS3 OFFLINE

37 _SYSSMU37$ UNDOTBS3 OFFLINE

38 _SYSSMU38$ UNDOTBS3 OFFLINE

39 _SYSSMU39$ UNDOTBS3 OFFLINE

40 _SYSSMU40$ UNDOTBS3 OFFLINE

41 _SYSSMU41$ UNDOTBS3 OFFLINE

42 _SYSSMU42$ UNDOTBS4 ONLINE

42 rows selected.

SQL> select * from v$rollname;

USN NAME

---------- ------------------------------

0 SYSTEM

42 _SYSSMU42$

总结:事实上当我们用参数做如下设置时

*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$','_SYSSMU42$'

*._offline_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$','_SYSSMU42$'

启动后，oracle会产生另一个非系统回滚段_SYSSMU43$如下：

SQL> select * from v$rollname;

USN NAME

---------- ------------------------------

0 SYSTEM

43 _SYSSMU43$

SQL> select segment_id,segment_name,tablespace_name,status from dba_rollback_segs

2 ;

SEGMENT_ID SEGMENT_NAME TABLESPACE_NAME STATUS

---------- ------------------------------ ------------------------------ ----------------

0 SYSTEM SYSTEM ONLINE

1 _SYSSMU1$ UNDOTBS4 OFFLINE

2 _SYSSMU2$ UNDOTBS4 OFFLINE

3 _SYSSMU3$ UNDOTBS4 OFFLINE

。。。。。。。。。。。。。。。此处省略数行。。。。。。。。。

43 _SYSSMU43$ UNDOTBS4 ONLINE

43 rows selected.

也就是说当undo_management=auto时，即使用隐含参数_corrupted_rollback_segments和_offline_rollback_segments标记当前undo表空间所有回滚段，数据库启动时oracle也总会创建一个非系统回滚段，直到undo表空间容量耗尽（猜测）

------------------------------------------------------------------------------------------------------------------------------------------------

测试4：

initCRM.ora参数如下

*.undo_management='MANUAL'

*.undo_tablespace='UNDOTBS4'

*.user_dump_dest='/oracle/app/admin/CRM/udump'

*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

*._offline_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU11$'

oracle启动过程如下：

SQL> startup pfile='/oracle/app/db1/dbs/initCRM.ora';

ORACLE instance started.

Total System Global Area 322961408 bytes

Fixed Size 2020480 bytes

Variable Size 83889024 bytes

Database Buffers 230686720 bytes

Redo Buffers 6365184 bytes

Database mounted.

Database opened.

当前oracle所有活动回滚段状态如下:

SQL> select segment_id,segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_ID SEGMENT_NAME TABLESPACE_NAME STATUS

---------- ------------------------------ ------------------------------ ----------------

0 SYSTEM SYSTEM ONLINE

1 _SYSSMU1$ UNDOTBS4 OFFLINE

2 _SYSSMU2$ UNDOTBS4 OFFLINE

3 _SYSSMU3$ UNDOTBS4 OFFLINE

4 _SYSSMU4$ UNDOTBS4 OFFLINE

5 _SYSSMU5$ UNDOTBS4 OFFLINE

6 _SYSSMU6$ UNDOTBS4 OFFLINE

7 _SYSSMU7$ UNDOTBS4 OFFLINE

8 _SYSSMU8$ UNDOTBS4 OFFLINE

9 _SYSSMU9$ UNDOTBS4 OFFLINE

11 _SYSSMU11$ UNDOTBS4 OFFLINE

12 _SYSSMU12$ UNDOTBS1 OFFLINE

13 _SYSSMU13$ UNDOTBS1 OFFLINE

14 _SYSSMU14$ UNDOTBS1 OFFLINE

15 _SYSSMU15$ UNDOTBS1 OFFLINE

16 _SYSSMU16$ UNDOTBS1 OFFLINE

17 _SYSSMU17$ UNDOTBS1 OFFLINE

18 _SYSSMU18$ UNDOTBS1 OFFLINE

19 _SYSSMU19$ UNDOTBS1 OFFLINE

20 _SYSSMU20$ UNDOTBS1 OFFLINE

21 _SYSSMU21$ UNDOTBS1 OFFLINE

22 _SYSSMU22$ UNDOTBS2 OFFLINE

23 _SYSSMU23$ UNDOTBS2 OFFLINE

24 _SYSSMU24$ UNDOTBS2 OFFLINE

25 _SYSSMU25$ UNDOTBS2 OFFLINE

26 _SYSSMU26$ UNDOTBS2 OFFLINE

27 _SYSSMU27$ UNDOTBS2 OFFLINE

28 _SYSSMU28$ UNDOTBS2 OFFLINE

29 _SYSSMU29$ UNDOTBS2 OFFLINE

30 _SYSSMU30$ UNDOTBS2 OFFLINE

31 _SYSSMU31$ UNDOTBS2 OFFLINE

32 _SYSSMU32$ UNDOTBS3 OFFLINE

33 _SYSSMU33$ UNDOTBS3 OFFLINE

34 _SYSSMU34$ UNDOTBS3 OFFLINE

35 _SYSSMU35$ UNDOTBS3 OFFLINE

36 _SYSSMU36$ UNDOTBS3 OFFLINE

37 _SYSSMU37$ UNDOTBS3 OFFLINE

38 _SYSSMU38$ UNDOTBS3 OFFLINE

39 _SYSSMU39$ UNDOTBS3 OFFLINE

40 _SYSSMU40$ UNDOTBS3 OFFLINE

41 _SYSSMU41$ UNDOTBS3 OFFLINE

42 _SYSSMU42$ UNDOTBS4 OFFLINE

43 _SYSSMU43$ UNDOTBS4 OFFLINE

43 rows selected.

SQL> select * from v$rollname;

USN NAME

---------- ------------------------------

0 SYSTEM

实验完毕

总结如下：当我们用隐含参数_offline_rollback_segments、_corrupted_rollback_segments处理回滚段某些问题的时候，undo_management=manual为好。

oracle隐含参数oracle

点击查看更多内容

为 TA 点赞

若觉得本文不错，就分享一下吧！

评论

评论

共同学习，写下你的评论

评论加载中...

展开查看更多评论

作者其他优质文章

正在加载中

PIPIONE

手记
篇

粉丝

147

获赞与收藏

702

关注作者，订阅最新文章

阅读免费教程

后端通用面试教程

41个小节 30603 343

网络编程入门教程

20个小节 12566 237

Pandas 入门教程

25个小节 18501 337

推荐

评论

收藏

共同学习，写下你的评论



感谢您的支持，我会继续努力的～

扫码打赏，你说多少就多少

赞赏金额会直接到老师账户

支付方式

打开微信扫一扫，即可进行扫码打赏哦

今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与放弃机会

点击
抽奖

慕课手记新用户专享福利

恭喜你，你的运气太好了，居然抽中了 100个积分！

恭喜你，抽中了价值元的专栏！

太棒了，直接落到你账户里！

积分商城里的罗技鼠标、机械键盘、
Kindle 阅读器、小米平衡车
Apple iPad （10.2英寸）、大额优惠券
在等着你去兑换了噢

作者：

免费赠送

兑换码：1111222211 复制

优惠券可用于购买实战课、体系课
无门槛使用

先去看看，有什么好东西马上兑换我爱学习，选课去


热搜

最近搜索清空

undo_management设置与隐含参数

阅读免费教程