在insert table test 的时候，shutdown abort。
现在drop undo后open database，手上没有UNDOTBS01.DBF的备份，想重建undo tablespace ，总不成功。我应该怎么做呢？
脚本如下：
SQL> startup
ORACLE instance started.
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'O:/ORACLE/ORADATA/LEEGLE/UNDOTBS01.DBF'
SQL> alter database datafile 2 offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'O:/ORACLE/ORADATA/LEEGLE/UNDOTBS01.DBF'
--报错！
SQL> drop rollback segment roll;
drop rollback segment roll
*
ERROR at line 1:
ORA-01545: rollback segment 'ROLL' specified not available
--报错！
SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-01548: active rollback segment 'ROLL' found, terminate dropping tablespace
--报错！
SQL> alter rollback segment roll offline;
alter rollback segment roll offline
*
ERROR at line 1:
ORA-01598: rollback segment 'ROLL' is not online
--报错！奇怪，好像矛盾也
SQL> alter rollback segment roll online;
alter rollback segment roll online
*
ERROR at line 1:
ORA-01636: rollback segment 'ROLL' is already online
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ OFFLINE
_SYSSMU2$ OFFLINE
_SYSSMU3$ OFFLINE
_SYSSMU4$ OFFLINE
_SYSSMU5$ OFFLINE
_SYSSMU6$ OFFLINE
_SYSSMU7$ OFFLINE
_SYSSMU8$ OFFLINE
_SYSSMU9$ OFFLINE
_SYSSMU10$ OFFLINE
SEGMENT_NAME STATUS
------------------------------ ----------------
ROLL NEEDS RECOVERY
ROLL02 ONLINE
13 rows selected.
which [等级：◆◆(初级)] (信誉值: 100) 回复于: 2004-7-1 22:53:45 Top
首先要问一下，UNDOTBS01.DBF这个数据文件里除了回滚段还有其他的用户表吗？
匿名 [等级：◆◆◆(初级)] (信誉值: 95) 回复于: 2004-7-1 23:40:22 Top
只有回滚段。
环境 9i
undo_management = manual
set transaction use rollback segment roll
Small dragon [等级：◆◆(初级)] (信誉值: 100) 回复于: 2004-7-14 15:58:46 Top
up
小猪快跑 [等级：◆(初级)] (信誉值: 100) 回复于: 2004-7-14 18:51:14 Top
up!
飘 [等级：★★(中级)] (信誉值: 120) 回复于: 2004-7-2 10:10:32 Top
用这个方法
将数据关闭
> svrmgrl >Shutdown abort
>
> 2、修改初始化参数文件
> $ vi $ORACLE_HOME/dbs/init< sid >.ora
> 添加以下参数
> rollback_segments=(system)
> _corrupted_rollback_segments=(r01,r02,r03,r04)
> _allow_resetlogs_corruption=ture
>
> 3、重新装载数据库
> svrmgrl >Startup mount
>
> 4、从数据库的控制文件中将回滚段表空间rbs的数据文件离线并去掉。
>
> svrmgrl >alter database datafile
> 'O:/ORACLE/ORADATA/LEEGLE/UNDOTBS01.DBF'offline drop >
> 5、 重建新的回滚段
>
> ---- 将旧回滚段及回滚表空间删除。
> svrmgrl >alter rollback_segment r01 offline drop;
> svrmgrl >alter rollback_segment r02 offline drop;
> svrmgrl >alter rollback_segment r03 offline drop;
> svrmgrl >alter rollback_segment r04 offline drop;
> svrmgrl >drop tablespace rbs including contents;
> ---- 重建新的回滚表空间及回滚段。
> svrmgrl >connect internal
> svrmgrl >create rollback segment ro tablespace system;
> svrmgrl >alter rollback segment ro online;
> svrmgrl >create tablespace rbs datafile
> ‘##/##/rbs01.dbf’ size ##k;
> svrmgrl >create rollback segment r01 tablespace rbs;
> svrmgrl >create rollback segment r02 tablespace rbs;
> svrmgrl >create rollback segment r03 tablespace rbs;
> svrmgrl >create rollback segment r04 tablespace rbs;
> svrmgrl >alter rollback segment r01 online;
> svrmgrl >alter rollback segment r02 online;
> svrmgrl >alter rollback segment r03 online;
> svrmgrl >alter rollback segment r04 online;
>
>
> svrmgrl >Shutdown abort
> $ vi $ORACLE_HOME/dbs/init< sid >.ora
> rollback_segments=(r01,r02,r03,r04)
> 将参数_corrupted_rollback_segment
> _allow_resetlogs_corruption=true去掉
> svrmgrl >Startup normal
which [等级：◆◆(初级)] (信誉值: 100) 回复于: 2004-7-2 11:44:55 Top
建议不要使用corrupted_rollback_segments
除非你的数据库不能open
这是一个未公开的初始化参数，目的只有一个，就是使得数据库可以open
使用以后要立即备份数据库
飘 [等级：★★(中级)] (信誉值: 120) 回复于: 2004-7-2 15:22:49 Top
就先用我这个办法，把回滚段删除。备份后再使用我的方法，
删除回滚段后要记得将参数_corrupted_rollback_segment
> _allow_resetlogs_corruption=true去掉
which [等级：◆◆(初级)] (信誉值: 100) 回复于: 2004-7-2 9:20:26 Top
你先试试这个：
1、正常shutdown数据库 ： shutdown immediate;
2、修改初始化参数文件：找到ROLLBACK_SEGMENTS 那一行，将括号里的roll去掉
3、保存，然后以restricted模式mount数据库：startup restrict mount;
4、Offline drop 损坏的那个数据文件：alter database datafile 'O:/ORACLE/ORADATA/LEEGLE/UNDOTBS01.DBF' offline drop;
5、打开数据库：alter database open;
6、打开后删掉数据文件所在表空间：drop tablespace <tbsname> including contents;
7、重建表空间以及回滚段，建完后online
8、使所有用户都可以登录:alter system disable restricted session;
你先试试，如果不行把错误信息再帖出来。
which [等级：◆◆(初级)] (信誉值: 100) 回复于: 2004-7-2 9:22:02 Top
补充一点，第二步要将所有在损坏数据文件的回滚段名都去掉，而不仅仅是roll
匿名 [等级：◆◆◆(初级)] (信誉值: 95) 回复于: 2004-7-6 20:52:10 Top
是9i的环境，ROLLBACK_SEGMENTS 为空。
*.undo_management='MANUAL'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'O:/ORACLE/ORADATA/LEEGLE/UNDOTBS01.DBF'
SQL> alter database datafile 2 offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from test.test;
select * from test.test
*
ERROR at line 1:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'O:/ORACLE/ORADATA/LEEGLE/UNDOTBS01.DBF'
SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-01548: active rollback segment 'RB01' found, terminate dropping tablespace
匿名 [等级：◆◆◆(初级)] (信誉值: 95) 回复于: 2004-7-6 21:42:47 Top
to:LGQDUCKY(飘)
_corrupted_rollback_segments=(r01,r02,r03,r04) 起关键作用。
奇怪的是我没commit的数据居然不rollback，都insert了。
insert时新的数据写到datafile中，旧的值保留在rollback中，_corrupted_rollback_segments 这个参数使rollback失效，这样datafile 中的块就标记为commit了。是吗？
这确实导致了数据不一致。
晨钟暮鼓 [等级：★★★★(高级)] (信誉值: 149) 回复于: 2004-7-7 12:34:47 Top
参照一下我的以前写的文章,里面有相关的内容.
http://www.csdn.net/Develop/list_article.asp?author=%20hrb_qiuyb
匿名 [等级：◆◆◆(初级)] (信誉值: 95) 回复于: 2004-7-7 22:15:38 Top
谢谢hrb_qiuyb(晨钟暮鼓)
我对 方法II.B:重建回滚表空间 感兴趣，不用 _corrupted_rollback_segments
做以下试验
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
12 R2
SQL> select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
TEMP TEMPORARY
INDX PERMANENT
TOOLS PERMANENT
USERS PERMANENT
RO UNDO
6 rows selected.
SQL> select count(*) from test.test;
select count(*) from test.test
*
ERROR at line 1:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'O:/ORACLE/ORADATA/LEEGLE/RO.ORA'
SQL> drop tablespace ro including contents;
drop tablespace ro including contents
*
ERROR at line 1:
ORA-01546: tablespace contains active rollback segment 'R2'
SQL> alter rollback segment r2 offline;
Rollback segment altered.
SQL> drop tablespace ro including contents;
drop tablespace ro including contents
*
ERROR at line 1:
ORA-01546: tablespace contains active rollback segment 'R2'
SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS FROM V$ROLLSTAT V, DBA_ROL
LBACK_SEGS WHERE TABLESPACE_NAME = 'RO' AND SEGMENT_ID = USN;
SEGMENT_NAME ACTIVE_TX STATUS
------------------------------ ---------- ---------------
R2 1 PENDING OFFLINE
SQL> SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK" FROM V$SESSION S,
V$TRANSACTION T, V$ROLLNAME R
2 WHERE R.NAME IN ('R2') AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN;
SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK" FROM V$SESSION S, V$TR
ANSACTION T, V$ROLLNAME R
*
ERROR at line 1:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'O:/ORACLE/ORADATA/LEEGLE/RO.ORA'
SQL>
我改如何是好，如何用ALTER SYSTEM KILL SESSION '<SID>, <SERIAL#>';语句杀掉这些事务？
匿名 [等级：◆◆◆(初级)] (信誉值: 95) 回复于: 2004-7-7 22:18:50 Top
大家可以从 select * from X$KSPPI 中找到一些 "_"开头的隐含参数。