| |
问：当数据库出现坏块，回滚段里的部分数据不能读取时，应该用什么方法解决。
答： 具体方法如下;
首先应当检查一下swappALRT.log文件，会发现以下错误：
Tue Sep 21 10:34:08 2004
Errors in file E:\oracle\admin\swapp\bdump\swappSMON.TRC:
ORA-01578: ORACLE data block corrupted (file # 2, block # 24497)
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\SWAPP\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 |
出现上例说明出错了，没有立建成功。
这说明对于oracle8i来讲在LMT方式管理的表空间下不能创建回滚段，但9i解决了该问题。
metalink上的解释:
Explanation
-----------
Rollback segments cannot be created in locally
managed tablespaces (a new feature in Oracle 8.1) with allocation
typeof 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 |
接下来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的。
下面是正式的任务：
SQL> set feedback off
SQL> set pages 0
SQL> select 'alter rollback segment '||segment_name||'
offline;'? from dba_rollback_segs; |
做一个脚本把除system回滚段以外的回滚段都offline掉，省的一个一个敲，脚本结果如下:
alter rollback segment RBS0 offline;
alter rollback segment RBS1 offline;
alter rollback segment RBS2 offline;
alter rollback segment RBS3 offline;
alter rollback segment RBS4 offline;
alter rollback segment RBS5 offline;
alter rollback segment RBS6 offline;
alter rollback segment RBS7 offline;
alter rollback segment RBS8 offline;
alter rollback segment RBS9 offline;
alter rollback segment RBS10 offline;
alter rollback segment RBS11 offline;
alter rollback segment RBS12 offline;
alter rollback segment RBS13 offline;
alter rollback segment RBS14 offline;
alter rollback segment RBS15 offline;
alter rollback segment RBS16 offline;
alter rollback segment RBS17 offline;
alter rollback segment RBS18 offline;
alter rollback segment RBS19 offline;
alter rollback segment RBS20 offline;
alter rollback segment RBS21 offline;
alter rollback segment RBS22 offline;
alter rollback segment RBS23 offline;
alter rollback segment RBS24 offline;
alter rollback segment RBS25 offline;
alter rollback segment RBS26 offline;
alter rollback segment RBS27 offline;
alter rollback segment RBS28 offline;
alter rollback segment APPRBS offline; |
然后做个drop回滚段的脚本:
SQL>? select 'drop rollback
segment '||segment_name||';' from dba_rollback_segs;
drop rollback segment RBS0;
drop rollback segment RBS1;
drop rollback segment RBS2;
drop rollback segment RBS3;
drop rollback segment RBS4;
drop rollback segment RBS5;
drop rollback segment RBS6;
drop rollback segment RBS7;
drop rollback segment RBS8;
drop rollback segment RBS9;
drop rollback segment RBS10;
drop rollback segment RBS11;
drop rollback segment RBS12;
drop rollback segment RBS13;
drop rollback segment RBS14;
drop rollback segment RBS15;
drop rollback segment RBS16;
drop rollback segment RBS17;
drop rollback segment RBS18;
drop rollback segment RBS19;
drop rollback segment RBS20;
drop rollback segment RBS21;
drop rollback segment RBS22;
drop rollback segment RBS23;
drop rollback segment RBS24;
drop rollback segment RBS25;
drop rollback segment RBS26;
drop rollback segment RBS27;
drop rollback segment RBS28;
drop rollback segment APPRBS; |
脚本做好了，执行。
执行完后开始在新的回滚段表空间下建回滚段，存储参数和原来保持一致：
SQL> select? 'create public rollback
segment '||segment_name||' tablespace rbs01 storage
(initial 524288 next 524288 MINEXTENTS 8
MAXEXTENTS 4096 OPTIMAL 4194304); from dba_rollback_segs; |
也是做了个脚本，免的一个一个敲！
下面的大回滚段要单独建，总之，系统里面最好要有一个大的回滚段，有大事物的时候就派上用场了。
SQL> create public rollback segment APPRBS
tablespace rbs01 storage(initial 2097152 next 10485760 MINEXTENTS
50 MAXEXTENTS 32765);
Rollback segment created. |
查看新建的回滚段状态：
SQL> select segment_name,owner,status,
tablespace_name from dba_rollback_segs;
SYSTEM?????????????? SYS??? ONLINE?????????? SYSTEM
RBS0???????????????? PUBLIC OFFLINE????????? RBS01
RBS1???????????????? PUBLIC OFFLINE????????? RBS01
RBS2???????????????? PUBLIC OFFLINE????????? RBS01
RBS3???????????????? PUBLIC OFFLINE????????? RBS01
RBS4???????????????? PUBLIC OFFLINE????????? RBS01
RBS5???????????????? PUBLIC OFFLINE????????? RBS01
RBS6???????????????? PUBLIC OFFLINE????????? RBS01
RBS7???????????????? PUBLIC OFFLINE????????? RBS01
RBS8???????????????? PUBLIC OFFLINE????????? RBS01
RBS10??????????????? PUBLIC OFFLINE????????? RBS01
RBS11??????????????? PUBLIC OFFLINE????????? RBS01
RBS12??????????????? PUBLIC OFFLINE????????? RBS01
RBS13??????????????? PUBLIC OFFLINE????????? RBS01
RBS14??????????????? PUBLIC OFFLINE????????? RBS01
RBS15??????????????? PUBLIC OFFLINE????????? RBS01
RBS16??????????????? PUBLIC OFFLINE????????? RBS01
RBS17??????????????? PUBLIC OFFLINE????????? RBS01
RBS18??????????????? PUBLIC OFFLINE????????? RBS01
RBS19??????????????? PUBLIC OFFLINE????????? RBS01
RBS20??????????????? PUBLIC OFFLINE????????? RBS01
RBS21??????????????? PUBLIC OFFLINE????????? RBS01
RBS22??????????????? PUBLIC OFFLINE????????? RBS01
RBS23??????????????? PUBLIC OFFLINE????????? RBS01
RBS24??????????????? PUBLIC OFFLINE????????? RBS01
RBS26??????????????? PUBLIC OFFLINE????????? RBS01
RBS27??????????????? PUBLIC OFFLINE????????? RBS01
RBS28??????????????? PUBLIC OFFLINE????????? RBS01
RBS25??????????????? PUBLIC OFFLINE????????? RBS01
APPRBS?????????????? PUBLIC OFFLINE????????? RBS01
30 rows selected. |
除了system,都是offline状态。
继续做脚本让除system外的回滚段online：
SQL> select 'alter rollback segment
'||segment_name||' online;'? from dba_rollback_segs;
alter rollback segment RBS0 online;
alter rollback segment RBS1 online;
alter rollback segment RBS2 online;
alter rollback segment RBS3 online;
alter rollback segment RBS4 online;
alter rollback segment RBS5 online;
alter rollback segment RBS6 online;
alter rollback segment RBS7 online;
alter rollback segment RBS8 online;
alter rollback segment RBS9 online;
alter rollback segment RBS10 online;
alter rollback segment RBS11 online;
alter rollback segment RBS12 online;
alter rollback segment RBS13 online;
alter rollback segment RBS14 online;
alter rollback segment RBS15 online;
alter rollback segment RBS16 online;
alter rollback segment RBS17 online;
alter rollback segment RBS18 online;
alter rollback segment RBS19 online;
alter rollback segment RBS20 online;
alter rollback segment RBS21 online;
alter rollback segment RBS22 online;
alter rollback segment RBS23 online;
alter rollback segment RBS24 online;
alter rollback segment RBS26 online;
alter rollback segment RBS27 online;
alter rollback segment RBS28 online;
alter rollback segment RBS25 online;
alter rollback segment APPRBS online; |
执行以上脚本后，删除原来的undo表空间RBS：
SQL>drop tablespace rbs including contents;
Tablespace dropped. | |
|
