更改oracle undo表空间[转]
周五开发人员突然报测试库的非常的慢，登录主机后查看日志文件，发现有很多下面类似的错误，提示回滚段空间不够。
test(oracle):/oracle/app/admin/test/bdump > tail -200 alert_test.log
......
Fri May 30 15:35:30 2008
Failure to extend rollback segment because of 30036 condition
Fri May 30 15:45:09 2008
......
test(oracle):/oracle/app/admin/test/bdump > sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.5.0 - Production on Fri May 30 16:57:59 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
SQL> SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
2 (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
3 FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
4 WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
TABLESPACE_NAME TOTAL USED FREE % USED % FREE
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSTEM 1048576000 260636672 787873792 24.85625 75.1375
......
TEST_ZHJS_TEMP 8587837440 1430781952 7156924416 16.6605617 83.3379121
UNDOTBS 4194304000 4193566720 524288 99.9824219 .0125
USERS 209715200 12517376 197066752 5.96875 93.96875
15 rows selected.
果然，这里回滚段可用空间几乎为0了。而开发人员把一些进程都杀了，所以通过下面的查询也找不到占用回滚段的相关语句的信息。
/*
SELECT r.NAME ,s.sid SID, s.serial#, s.username, s.machine, t.start_time, t.status,
t.used_ublk,USED_UREC, t.cr_get,t.cr_change, t.log_io,t.phy_io,
t.noundo NoUndo, g.extents Extents, substr(s.program, 1, 50)
FROM v$session s, v$transaction t, v$rollname r,v$rollstat g
WHERE t.addr = s.taddr AND t.xidusn = r.usn AND r.usn = g.usn
ORDER BY t.used_ublk desc;
*/
SQL> select segment_name, tablespace_name, r.status,
2 (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
3 max_extents, v.curext CurExtent
4 From dba_rollback_segs r, v$rollstat v
5 Where r.segment_id = v.usn(+)
6 order by segment_name;
SEGMENT_NAME TABLESPACE_NAME STATUS INITIALEXTENT NEXTEXTENT MAX_EXTENTS CUREXTENT
------------------------------ -------------------- ---------------- ------------- ---------- ----------- ----------
SYSTEM SYSTEM ONLINE 112 32765 5
_SYSSMU1$ UNDOTBS ONLINE 128 32765 0
_SYSSMU10$ UNDOTBS ONLINE 128 32765 2
_SYSSMU11$ UNDOTBS OFFLINE 128 32765
_SYSSMU12$ UNDOTBS OFFLINE 128 32765
_SYSSMU13$ UNDOTBS OFFLINE 128 32765
_SYSSMU14$ UNDOTBS OFFLINE 128 32765
_SYSSMU15$ UNDOTBS OFFLINE 128 32765
_SYSSMU16$ UNDOTBS OFFLINE 128 32765
_SYSSMU17$ UNDOTBS OFFLINE 128 32765
_SYSSMU18$ UNDOTBS OFFLINE 128 32765
SEGMENT_NAME TABLESPACE_NAME STATUS INITIALEXTENT NEXTEXTENT MAX_EXTENTS CUREXTENT
------------------------------ -------------------- ---------------- ------------- ---------- ----------- ----------
_SYSSMU2$ UNDOTBS ONLINE 128 32765 2
_SYSSMU3$ UNDOTBS ONLINE 128 32765 0
_SYSSMU4$ UNDOTBS ONLINE 128 32765 1
_SYSSMU5$ UNDOTBS ONLINE 128 32765 2
_SYSSMU6$ UNDOTBS ONLINE 128 32765 1
_SYSSMU7$ UNDOTBS ONLINE 128 32765 1
_SYSSMU8$ UNDOTBS ONLINE 128 32765 1
_SYSSMU9$ UNDOTBS ONLINE 128 32765 2
19 rows selected.
SQL> select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents
Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,
v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
sys.dba_rollback_segs.status status
from v$rollstat, sys.dba_rollback_segs, v$rollname
where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and
v$rollstat.usn (+) = v$rollname.usn order by rownum;
ROWNUM NAME EXTENTS SIZE_IN_BYTES XACTS GETS WAITS WRITES STATUS
---------- ------------------------------ ---------- ------------- ---------- ---------- ---------- ---------- ----------------
1 SYSTEM 7 450560 0 160927 1 40114 ONLINE
2 _SYSSMU1$ 3 1171456 0 6725810 154 2195801118 ONLINE
3 _SYSSMU2$ 3 188416 0 5663142 181 1634155188 ONLINE
4 _SYSSMU3$ 3 1171456 0 5671478 183 1759396042 ONLINE
5 _SYSSMU4$ 3 188416 0 5524314 162 1270790496 ONLINE
6 _SYSSMU5$ 3 188416 0 6997045 251 2399580318 ONLINE
7 _SYSSMU6$ 3 188416 0 5703556 181 1386608058 ONLINE
8 _SYSSMU7$ 3 188416 0 6483778 160 1977374486 ONLINE
9 _SYSSMU8$ 701 4188905472 0 6733413 199 3227452346 ONLINE
10 _SYSSMU9$ 3 188416 0 5624532 189 1248292664 ONLINE
11 _SYSSMU10$ 3 188416 0 4824424 104 856923746 ONLINE
ROWNUM NAME EXTENTS SIZE_IN_BYTES XACTS GETS WAITS WRITES STATUS
---------- ------------------------------ ---------- ------------- ---------- ---------- ---------- ---------- ----------------
12 _SYSSMU11$ OFFLINE
13 _SYSSMU12$ OFFLINE
14 _SYSSMU13$ OFFLINE
15 _SYSSMU14$ OFFLINE
16 _SYSSMU15$ OFFLINE
17 _SYSSMU16$ OFFLINE
18 _SYSSMU17$ OFFLINE
19 _SYSSMU18$ OFFLINE
19 rows selected.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
从回滚段的情况看，有很多offline的回滚段存在，怀疑是undo表空间存在问题，决定重建undo表空间。
test#<[/]lvcreate -L 4096 -n lv_rbs4G08 vg_db_test
Logical volume "/dev/vg_db_test/lv_rbs4G08" has been successfully created with
character device "/dev/vg_db_test/rlv_rbs4G08".
Logical volume "/dev/vg_db_test/lv_rbs4G08" has been successfully extended.
Volume Group configuration for /dev/vg_db_test has been saved in /etc/lvmconf/vg_db_test.conf
test#<[/dev/vg_db_testtd01]chown oracle:dba rlv_rbs4G08
test#<[/dev/vg_db_testtd01]su - oracle
test(oracle):/oracle > sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.5.0 - Production on Fri May 30 17:42:54 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
SQL> create undo tablespace UNDOTBS2
2 datafile '/dev/vg_db_test/rlv_rbs4G08' size 4095m reuse;
Tablespace created.
SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
System altered.
SQL> select USN,EXTENTS,WRITES,GETS,WAITS,HWMSIZE,SHRINKS,
EXTENDS,AVESHRINK,AVEACTIVE,STATUS,
CUREXT,CURBLK
from v$rollstat;
USN EXTENTS WRITES GETS WAITS HWMSIZE SHRINKS EXTENDS STATUS CUREXT CURBLK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ----------
0 7 57250 161043 1 450560 0 0 ONLINE 5 6
2 2 1634159024 5664816 181 545251328 302 844 PENDING OFFLINE 0 0
19 2 272919884 396942 32 122880 0 0 ONLINE 0 0
20 2 267008332 335100 37 122880 0 0 ONLINE 0 1
21 2 215780644 175826 21 122880 0 0 ONLINE 0 0
22 2 71545122 154634 17 122880 0 0 ONLINE 0 0
23 2 87093068 140068 17 122880 0 0 ONLINE 0 1
24 2 51282254 72945 7 122880 0 0 ONLINE 0 1
25 2 28093802 33156 0 122880 0 0 ONLINE 0 1
26 2 28053280 33887 3 122880 0 0 ONLINE 0 1
27 2 204 6 0 122880 0 0 ONLINE 0 1
USN EXTENTS WRITES GETS WAITS HWMSIZE SHRINKS EXTENDS STATUS CUREXT CURBLK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ----------
28 2 912 20 0 122880 0 0 ONLINE 0 1
12 rows selected.
SQL> show parameter undo_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 2400
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS2
至此，这个问题就OK了。
