在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 onlineSQL> 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$ OFFLINESEGMENT_NAME STATUS
------------------------------ ----------------
ROLL NEEDS RECOVERY
ROLL02 ONLINE13 rows selected.
现在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 onlineSQL> 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$ OFFLINESEGMENT_NAME STATUS
------------------------------ ----------------
ROLL NEEDS RECOVERY
ROLL02 ONLINE13 rows selected.
环境 9i
undo_management = manual
set transaction use rollback segment roll
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;
你先试试,如果不行把错误信息再帖出来。
> 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
除非你的数据库不能open
这是一个未公开的初始化参数,目的只有一个,就是使得数据库可以open
使用以后要立即备份数据库
删除回滚段后要记得将参数_corrupted_rollback_segment
> _allow_resetlogs_corruption=true去掉
*.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
这确实导致了数据不一致。
http://www.csdn.net/Develop/list_article.asp?author=%20hrb_qiuyb
SQL> select * from v$rollname; USN NAME
---------- ------------------------------
0 SYSTEM
12 R2SQL> select tablespace_name, contents from dba_tablespaces;TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
TEMP TEMPORARY
INDX PERMANENT
TOOLS PERMANENT
USERS PERMANENT
RO UNDO6 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 OFFLINESQL> 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#>';语句杀掉这些事务?