oracle8i回滚段表空间出现坏块的解决方法

答案：
今天早上刚到公司便接到网通客户的投诉电话，说网管数据库出问题了，数据库有坏块，回滚段里的部分数据不能读取，需要帮忙解决。

我查看了一下swappALRT.log文件，发现有以下错误：

Tue Sep 21 10:34:08 2004
Errors in file E:\oracle\admin wapp\bdump wappSMON.TRC:
ORA-01578: ORACLE data block corrupted (file # 2, block # 24497)
ORA-01110: data file 2: 'E:\ORACLE\ORADATA WAPP\RBS01.DBF'

原来是回滚段表空间数据文件有坏块了。知道了问题的所在，立刻解决，我已经想好了思路，就是新建一个回滚段表空间，把以前坏了的回滚段表空间drop掉，在新的回滚段表空间上建回滚段，所要建的回滚段和以前的一摸一样，让以后产生的回滚数据都写到新建的回滚段上。思路清晰，立刻开始行动了。

?首先停到listener,不允许有新的应用连到数据库上做操作，然后down掉数据库，为了清除掉已有的数据库会话连接资源:

$lsnrctl stop

LSNRCTL for Solaris: Version 8.1.7.3.0 - Production on 21-SEP-2004 17:40:36

(c) Copyright 1998 Oracle Corporation.? All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ipasdb)(PORT=1521)))
The command completed successfully.

$sqlplus internal/oracle

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Sep 21 17:41:24 2004

(c) Copyright 2000 Oracle Corporation.? All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup restrict (以受限模式启动数据库，为了防止其他用户登陆进来做相关操作，这时候只允许管理员登陆）



查找回滚段对应的表空间: 


SQL> select tablespace_name,status from dba_tablespaces; 

TABLESPACE_NAME??????????????? STATUS
------------------------------ ---------
SYSTEM???????????????????????? ONLINE
TOOLS????????????????????????? ONLINE
RBS??????????????????????????? ONLINE
TEMP?????????????????????????? ONLINE
USERS????????????????????????? ONLINE
INDX?????????????????????????? ONLINE
DRSYS????????????????????????? ONLINE
WACOS????????????????????????? ONLINE
NMS??????????????????????????? ONLINE
TEST?????????????????????????? ONLINE
FS???????????????????????????? ONLINE
PERFSTAT?????????????????????? ONLINE

12rows selected.

回滚段表空间为RBS.

查看当前回滚段表空间里是否有活动的事物:

SQL> SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk? FROM v$session s,v$transaction t WHERE s.saddr=t.ses_addr;

no rows selected.

没有活动事物，太好了，可以放心的drop回滚段了，这正是我想要的结果。

接下来查找回滚段存储参数信息:
SQL> col tablespace_name format a10
SQL> col SEGMENT_NAME format a12
SQL> set line 120
SQL> select SEGMENT_NAME,OWNER,TABLESPACE_NAME,initial_extent,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_rollback_segs;


SEGMENT_NAME OWNER? TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------ ------ ---------- -------------- ----------- ----------- ----------- ------------
SYSTEM?????? SYS??? SYSTEM????????????? 57344?????? 57344?????????? 2???????? 505??????????? 0
RBS0???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS1???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS2???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS3???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS4???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS5???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS6???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS7???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS8???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS9???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS10??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS11??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS12??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS13??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS14??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS15??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS16??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS17??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS18??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS19??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS20??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS21??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS22??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS23??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS24??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS26??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS27??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS28??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
RBS25??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0
APPRBS?????? PUBLIC RBS01???????????? 2097152??? 10485760????????? 50?????? 32765??????????? 0

31 rows selected.

把initial_extent,next_extent,min_extents,max_extents,pct_increase的值都记录下来，留做以后创建新的回滚段使用。

创建LMT管理方式的回滚段表空间(我的数据库是oracle817):

SQL> create tablespace rbs01 datafile '/opt/oracle/db02/oradata/ORCL/rbs01.dbf' size 1024M?autoextend on next 1M maxsize unlimited extent management local;

Tablespace created.

先在该表空间下建立一个回滚段rbs31做一个测试:

SQL> create public rollback segment RBS31 tablespace rbs01 storage(initial 524288 next 524288 MINEXTENTS 8 MAXEXTENTS 4096 OPTIMAL 4194304);
create public rollback segment RBS31 tablespace rbs01 storage(initial 524288 next 524288 MINEXTENTS 8 MAXEXTENTS 4096 OPTIMAL 4194304)
*
ERROR at line 1:
ORA-25151: Rollback Segment cannot be created in this tablespace

出错了，居然没有建成功,shit.

查了一下metalink发现对于oracle8i来讲在LMT方式管理的表空间下不能创建回滚段，但9i解决了该问题。

metalink上的解释:

Explanation 
----------- 
Rollback segments cannot be created in locally managed tablespaces (a new feature in Oracle 8.1) with allocation type of AUTOALLOCATE. They must be created in locally managed tablespaces with allocation type of UNIFORM or in dictionary managed tablespaces. 

NOTE: This restriction has been lifted in Oracle 9. 

接下来drop刚刚建立的rbs01表空间,重新建立rbs01表空间:

SQL> create tablespace rbs01 datafile '/opt/oracle/db02/oradata/ORCL/rbs01.dbf' size 1024M 
?autoextend on next 1M maxsize unlimited;
Tablespace created.

SQL> select EXTENT_MANAGEMENT from dba_tablespaces where tablespace_name='RBS01';

EXTENT_MAN
----------
DICTIONARY

这回表空间不是LMT的，是DMT的，呵呵！

下面才是真正开始要做的工作，总之两个字，细心，因为是生产库，

上一个：Linux上备份Oracle数据库脚本(16.34)
下一个：查看一个Oracle表的数据定义语句。