本帖最后由 不懂 于 2017-10-23 15:38 编辑
linux oracle11203 RAC更换undo;原来的undo为undotabs1,undotabs2,我又新建了01,02,替换原来的undo
节点1
alter system set undo_tablespace=UNDOTBS01 sid=XX1
节点2
alter system set undo_tablespace=UNDOTBS02 sid=XX2
--问题来了,节点1一直有段online,截至到发帖都一周了
alert log 日志
undo moved to pending Switch-Out states ,已经一周了,还是没有完全offline,也查不到具体的undo信息
SQL> select segment_name,tablespace_name,file_id,block_id,status
2 from dba_rollback_segs
3 where tablespace_name = 'UNDOTBS1'
4 and status = 'ONLINE';
SEGMENT_NAME TABLESPACE_NAME FILE_ID
------------------------------ ------------------------------ ----------
BLOCK_ID STATUS
---------- ----------------
_SYSSMU15367_1524718003$ UNDOTBS1 3
34664 ONLINE
SQL> select file#,block#,status,ts#,objd from v$bh where file#=3 and block#=34664
2 ;
FILE# BLOCK# STATUS TS# OBJD
---------- ---------- ---------- ---------- ----------
3 34664 xcur 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
FILE# BLOCK# STATUS TS# OBJD
---------- ---------- ---------- ---------- ----------
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
FILE# BLOCK# STATUS TS# OBJD
---------- ---------- ---------- ---------- ----------
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
3 34664 cr 2 4294967295
33 rows selected.
SQL> SELECT t1.usn,t1.status,t2.name
2 FROM V$ROLLSTAT T1, V$ROLLNAME T2
3 WHERE T1.USN = T2.USN and T2.NAME in ('_SYSSMU15367_1524718003$');
USN STATUS NAME
---------- --------------- ------------------------------
15367 PENDING OFFLINE _SYSSMU15367_1524718003$
SQL> select addr,xidusn,status,start_time from gv$transaction;
no rows selected
SQL> SELECT
2 a.usn,
3 a.name,
4 b.status,
5 c.tablespace_name,
6 d.addr,
7 e.sid,
8 e.serial#,
9 e.username,
10 e.program,
11 e.machine,
12 e.osuser
13 FROM
14 v$rollname a,
15 v$rollstat b,
16 dba_rollback_segs c,
17 v$transaction d,
18 v$session e
19 WHERE
20 a.usn=b.usn AND
21 a.name =c.segment_name AND
22 a.usn=d.xidusn AND
23 d.addr=e.taddr AND
24 b.status='PENDING OFFLINE';
no rows selected