UNDO表空间(3)无备份块损坏

UNDO表空间(三)无备份块损坏

show parameter undo 查看undo表空间的几本信息

第一，查看undo表空间使用的是第几个文件

select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
--------------- ---------------------------------------------
USERS /u01/oracle/oradata/ora10g/users01.dbf
SYSAUX /u01/oracle/oradata/ora10g/sysaux01.dbf
UNDOTBS1 /u01/oracle/oradata/ora10g/undotbs01.dbf
SYSTEM /u01/oracle/oradata/ora10g/system01.dbf
MYTBS /u01/oracle/oradata/ora10g/mytbs01.dbf

我们开始做交易

update scott.emp set sal=sal+1 where deptno=30;

然后查看事物的存在

select XIDUSN,XIDSLOT,XIDSQN from v$transaction;

XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
5 27 344

---介绍

XIDUSN:就是事务的回滚段号
XIDSLOT:就是ITL(interesting transaction list)列表中,slot的号
XIDSQN:就是表示这个slot被重复使用的次数

查看使用的回滚段

select segment_name,tablespace_name,file_id from dba_rollback_segs where segment_id=2;

SEGMENT_NAME TABLESPACE_NAME FILE_ID
------------------------- --------------- ----------
_SYSSMU2$ UNDOTBS1 2

这里看到2号被使用

第二，开始破坏回滚段

！cp /etc/passwd /u01/oracle/oradata/ora10g/undotbs01.dbf

再次查看

SQL> SELECT FILE#, STATUS,RECOVER, NAME,error FROM V$DATAFILE_HEADER ;

FILE# STATUS REC NAME ERROR
---------- ---------- --- -------------------------------------------------- --------------------
1 ONLINE NO /u01/oracle/oradata/ora10g/system01.dbf
2 ONLINE /u01/oracle/oradata/ora10g/undotbs01.dbf CANNOT READ HEADER
3 ONLINE NO /u01/oracle/oradata/ora10g/sysaux01.dbf
4 ONLINE NO /u01/oracle/oradata/ora10g/users01.dbf
5 ONLINE NO /u01/oracle/oradata/ora10g/mytbs01.dbf

这里显示的需要回复的REC 2号文件时空说明需要恢复 ERROR 2号文件出现了错误，由于回滚段是存放交易之前的信息的所以这时候就找不到之前的信息了。

因为没有备份所以我们建立一个新的undo表空间来取缔之前的undo表空间

第三，

drop tablespace undotbs1

drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

报错：因为坏块上面的事物还存在，在内存里。

select tablespace_name,segment_name,status from dba_rollback_segs where tablespace_name='UNDOTBS1'

TABLESPACE_NAME SEGMENT_NAME STATUS
--------------- ------------------------- ----------
UNDOTBS1 _SYSSMU1$ OFFLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU10$ OFFLINE

2号正是我们刚才产生事物交易的时候占用的回滚段他的状态是online

SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'UNDOTBS1' AND SEGMENT_ID = 2

SEGMENT_NAME ACTIVE_TX STATUS
------------------------- ---------- --------------------
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 1 PENDING OFFLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE

显示2号正在等待着离线的状态

查看是谁在占用这个回滚段

SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
WHERE R.NAME ='_SYSSMU2$'
AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN

SID SERIAL# USERNAME ROLLBACK
----------- ---------- ------------------------------ ------------------------------
148 28 SCOTT _SYSSMU7$

alter system kill session '148,28';

drop tablespace undotbs1;

drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

发现还是删除不了

SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'UNDOTBS1' AND SEGMENT_ID = 2;

SEGMENT_NAME ACTIVE_TX STATUS
------------------------- ---------- --------------------
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 PENDING OFFLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE
_SYSSMU2$ 0 ONLINE

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

no rows selected

发现已经没有人使用了但是还是无法删除这时候我们使用隐含参数将这个损坏的文件强制离线

shutdown abort

conn /as sysdba

startup nomount 在nomount状态下降强制离线

alter system set "_offline_rollback_segments"='_SYSSMU7$' scope=spfile;

shutdown immediate ----一致性停库

startup nomount

alter database mount;

alter database open;

如果此时启动检测这个文件失败将这个文件离线
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/u01/oracle/oradata/db20/undotbs01.dbf'
ORA-01251: Unknown File Header Version read for file number 2

alter tablespace undotbs1 offline;

alter database open;

select ename,sal from t1 where deptno=30;

select ename,sal from t1 where deptno=30
*
ERROR at line 1:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/oracle/oradata/orcl/undotbs01.dbf'

出错

我们就创建一个undo表空间

create undo tablespace undotbs2 datafile '/u01/oracle/oradata/ora10g/undotbs02.dbf' size 20M;

alter system undo_tablespace=undotbs2; 把undotbs1设置为undo表空间

undotbs1表空间删除掉原来的

drop tablespace undotbs1 including concents cascade constraints;

drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU3$' found, terminate dropping tablespace
发现报错

然后找出具体的undo段

select segment_name from dba_rollback_segs where tablespace_name='UNDOTBS1'

SEGMENT_NAME
------------------------------
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$

发现有这么多我们把这些段写进参数文件然后删除

创建文本参数文件

create pfile from spfile

shutdown immediate; 停库

在参数文件里家进这些回滚段

cd $ORACLE_HOME/

cd dbs

编辑文本参数文件

vi initorcl.ora

加进去那些坏的回滚段

*._corrupted_rollback_segments=(_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

sqlplus / as sysdba

startup pfile=startup pfile='/u01/oracle/product/10.2.0/dbs/initorcl.ora';

启动之后删除老的回滚段

drop tablespace undotbs1 including contents and datafiles cascade constraints;

再次查询

select ename,sal from scott.emp where deptno=30;

ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
BLAKE 2850
TURNER 1500
JAMES 950

发现我们已经恢复完毕。

oracle里可以有哦多个undo表空间但同时只有一个表空有效

UNDO表空间(3)无备份块损坏

热点推荐