如何将PENDING OFFLINE状态的回滚段OFFLINE?

问题描述:

swelp:

版本ORACLE 9.2.0.4,采用Auto Undo Management
在v$transaction没有使用到这些PENDING OFFLINE的回滚段，可这些回滚段一个月的状态都是这样，有什么方法可以转为OFFLINE呢？

SQL> SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
2 FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
3 WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.USN;

SID SERIAL# USERNAME ROLLBACK
--- ------- ------------------------------ ------------------------------
64 2034 TEST _SYSSMU240$

SQL> select a.usn,b.name,a.xacts,a.status,a.rssize,a.hwmsize,a.shrinks
2 from v$rollstat a,v$rollname b
3 where a.USN=b.usn;

USN NAME XACTS STATUS RSSIZE HWMSIZE SHRINKS
--- ------------------------------ ----- --------------- ------ ------- -------
0 SYSTEM 0 ONLINE 450560 450560 0
48 _SYSSMU48$ 1 PENDING OFFLINE 117145 1171456 0
50 _SYSSMU50$ 1 PENDING OFFLINE 117145 1171456 0
54 _SYSSMU54$ 1 PENDING OFFLINE 117145 1171456 0
69 _SYSSMU69$ 1 PENDING OFFLINE 117145 1171456 0
71 _SYSSMU71$ 1 PENDING OFFLINE 117145 1171456 0
235 _SYSSMU235$ 0 ONLINE 516096 516096 0
236 _SYSSMU236$ 0 ONLINE 385024 385024 0
237 _SYSSMU237$ 0 ONLINE 516096 516096 0
238 _SYSSMU238$ 0 ONLINE 778240 778240 0
239 _SYSSMU239$ 0 ONLINE 581632 581632 0
240 _SYSSMU240$ 1 ONLINE 450560 450560 0
241 _SYSSMU241$ 0 ONLINE 909312 909312 0
242 _SYSSMU242$ 0 ONLINE 516096 516096 0
243 _SYSSMU243$ 0 ONLINE 319488 319488 0
244 _SYSSMU244$ 0 ONLINE 385024 385024 0

16 rows selected

处理流转：

[hrb_qiuyb：

理论上讲,pending offline表明还有未决的事务在回滚段中，你的问题可能是用多个回滚段表空间引起的。

可以的话做如下的操作并把结果贴上来：

1、 SELECT name, xacts FROM v$rollname, v$rollstat
WHERE status = 'PENDING OFFLINE' AND v$rollname.usn = v$rollstat.usn;

2、select * from x$tuxe where where ktuxecfl='DEAD';

3、选一有问题的回滚段，把header dump发上来：
SQL>alter system dump undo header "_SYSSMU48$";
在user_dump_dest中把生成的trace找到，贴上来。

swelp：
SQL> SELECT name, xacts FROM v$rollname, v$rollstat
2 WHERE status = 'PENDING OFFLINE' AND v$rollname.usn = v$rollstat.usn;

NAME XACTS
------------------------------ -----
_SYSSMU48$ 1
_SYSSMU50$ 1
_SYSSMU54$ 1
_SYSSMU69$ 1
_SYSSMU71$ 1

SQL> SELECT * FROM x$ktuxe
2 WHERE ktuxecfl = 'DEAD';

ADDR INDX INST_ID KTUXEUSN KTUXESLT KTUXESQN KTUXERDBF KTUXERDBB KTUXESCNB KTUXESCNW KTUXESTA KTUXECFL KTUXEUEL KTUXEDDBF KTUXEDDBB KTUXEPUSN KTUXEPSLT KTUXEPSQN KTUXESIZ
---------------- ---- ------- -------- -------- -------- --------- --------- --------- --------- ---------------- ------------------------ -------- --------- --------- --------- --------- --------- --------

SQL> alter system dump undo header "_SYSSMU48$";

System altered

********************************************************************************
Undo Segment: _SYSSMU48$ (48)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 143
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x02c0000d ext#: 2 blk#: 4 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00800022 length: 7
0x008000a1 length: 8
0x02c00009 length: 128

Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1131987750
Extent Number:1 Commit Time: 1131988356
Extent Number:2 Commit Time: 1131988356

TRN CTL:: seq: 0x006d chd: 0x0013 ctl: 0x001f inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x02c0000d.006d.08 scn: 0x0000.01c8be93
Version: 0x01
FREE BLOCK POOL::
uba: 0x02c0000d.006d.08 ext: 0x2 spc: 0x1cba
uba: 0x00000000.006a.01 ext: 0x2 spc: 0x1f98
uba: 0x00000000.006a.01 ext: 0x2 spc: 0x1f98
uba: 0x00000000.006a.01 ext: 0x2 spc: 0x1f98
uba: 0x00000000.006a.01 ext: 0x2 spc: 0x1f98
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x013d 0x001d 0x0000.01c8bfc9 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x01 9 0x00 0x013d 0x0022 0x0000.01c8cf75 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x02 9 0x00 0x013c 0x001e 0x0000.01c8c836 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x03 9 0x00 0x013d 0x0021 0x0000.01c8ca65 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x04 9 0x00 0x013e 0x002a 0x0000.01c8e0f5 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x05 2 0x17 0x013e 0x0002 0x0000.01c8e208 0x02c0000c 0x0000.000.00000000 0x00000001 0x02c0000c
0x06 9 0x00 0x013d 0x0026 0x0000.01c8d4e9 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x07 9 0x00 0x013d 0x0001 0x0000.01c8ced9 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x08 9 0x00 0x0138 0x0028 0x0000.01c8d85f 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x09 9 0x00 0x013d 0x0006 0x0000.01c8d3a2 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x0a 9 0x00 0x013d 0x0007 0x0000.01c8cd9f 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x0b 9 0x00 0x013c 0x0024 0x0000.01c8c10d 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x0c 9 0x00 0x013d 0x000f 0x0000.01c8d688 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x0d 9 0x00 0x013d 0x0002 0x0000.01c8c691 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x0e 9 0x00 0x013d 0x0012 0x0000.01c8cc58 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x0f 9 0x00 0x013d 0x002c 0x0000.01c8d7c7 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x10 9 0x00 0x013c 0x001b 0x0000.01c8c495 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x11 9 0x00 0x013d 0x0019 0x0000.01c8da18 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x12 9 0x00 0x013c 0x000a 0x0000.01c8ccfd 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x13 9 0x00 0x013c 0x0000 0x0000.01c8bf33 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x14 9 0x00 0x013d 0x0029 0x0000.01c8e05a 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x15 9 0x00 0x013c 0x000e 0x0000.01c8cb0d 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x16 9 0x00 0x013c 0x000d 0x0000.01c8c5e4 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x17 9 0x00 0x013d 0x0009 0x0000.01c8d35d 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x18 9 0x00 0x013d 0x0025 0x0000.01c8df16 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x19 9 0x00 0x013d 0x002d 0x0000.01c8dc57 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x1a 9 0x00 0x013d 0x0027 0x0000.01c8e206 0x02c0000d 0x0000.000.00000000 0x00000001 0x00000000
0x1b 9 0x00 0x013d 0x002b 0x0000.01c8c4f6 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x1c 9 0x00 0x013d 0x0017 0x0000.01c8d302 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x1d 9 0x00 0x013c 0x000b 0x0000.01c8bfe3 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x1e 9 0x00 0x013d 0x002f 0x0000.01c8c8d9 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x1f 9 0x00 0x013d 0xffff 0x0000.01c908c8 0x00000000 0x0000.000.00000000 0x00000000 0x00000000
0x20 9 0x00 0x013d 0x0011 0x0000.01c8d9ae 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x21 9 0x00 0x013d 0x0015 0x0000.01c8caa1 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x22 9 0x00 0x013d 0x001c 0x0000.01c8d124 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x23 9 0x00 0x013c 0x0010 0x0000.01c8c3fc 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x24 9 0x00 0x013c 0x0023 0x0000.01c8c17b 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x25 9 0x00 0x013d 0x0014 0x0000.01c8dfb1 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x26 9 0x00 0x013d 0x000c 0x0000.01c8d542 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x27 9 0x00 0x013d 0x001f 0x0000.01c8e207 0x02c0000d 0x0000.000.00000000 0x00000001 0x00000000
0x28 9 0x00 0x013c 0x0020 0x0000.01c8d8be 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x29 9 0x00 0x013d 0x0004 0x0000.01c8e0b5 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x2a 9 0x00 0x013d 0x001a 0x0000.01c8e205 0x02c0000d 0x0000.000.00000000 0x00000001 0x00000000
0x2b 9 0x00 0x013c 0x0016 0x0000.01c8c5a7 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000
0x2c 9 0x00 0x013c 0x0008 0x0000.01c8d822 0x02c0000c 0x0000.000.00000000 0x00000001 0x00000000
0x2d 9 0x00 0x013c 0x002e 0x0000.01c8dca7 0x00000000 0x0000.000.00000000 0x00000000 0x00000000
0x2e 9 0x00 0x013c 0x0018 0x0000.01c8dd02 0x00000000 0x0000.000.00000000 0x00000000 0x00000000
0x2f 9 0x00 0x013c 0x0003 0x0000.01c8ca0b 0x02c0000b 0x0000.000.00000000 0x00000001 0x00000000

hrb_qiuyb:

你看这一块:
0x05 2 0x17 0x013e 0x0002 0x0000.01c8e208 0x02c0000c 0x0000.000.00000000 0x00000001 0x02c0000c
其中的state是2 ,说明未决的分布式的事务。

你查一下dba_2pc_pending这个视图，看state这列有没有值为Collecting, prepared的记录，如果有commit force或rollback force掉就可以了.

或者如果对你的业务没有多大影响的话，下一次重启后就解决掉了。

swelp:

终于可以啦，谢谢hrb_qiuyb

SQL> select * from dba_2pc_pending;

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIXED ADVICE TRAN_COMMENT FAIL_TIME FORCE_TIME RETRY_TIME OS_USER OS_TERMINAL HOST DB_USER COMMIT#
---------------------- -------------------------------------------------------------------------------- ---------------- ----- ------ -------------------------------------------------------------------------------- ----------- ----------- ----------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------------
50.16.315 48801.7153AB65280BE61A833B prepared no 2005-11-15 2006-2-10 ? xmkfib 29942333
48.5.318 48801.714EAB65280BE61A833B prepared no 2005-11-15 2006-2-10 ? xmkfib 29942280
54.30.263 48801.714AAB65280BE61A833B prepared no 2005-11-15 2006-2-10 ? xmkfib 29942254
69.33.259 48801.713EAB65280BE61A833B prepared no 2005-11-15 2006-2-10 ? xmkfib 29942182
71.8.263 48801.713BAB65280BE61A833B prepared no 2005-11-15 2006-2-10 ? xmkfib 29942184
SQL> ROLLBACK FORCE '48.5.318';

Rollback complete

SQL> ROLLBACK FORCE '54.30.263';

Rollback complete

SQL> ROLLBACK FORCE '69.33.259';

Rollback complete

SQL> ROLLBACK FORCE '71.8.263';

Rollback complete

SQL> rollback force 50.16.315;

Rollback complete