oracle8i回滾段表空間出現(xiàn)壞塊的解決方法
2024-08-29 13:48:21
供稿：網(wǎng)友
今天早上剛到公司便接到網(wǎng)通客戶的投訴電話，說網(wǎng)管數(shù)據(jù)庫出問題了，數(shù)據(jù)庫有壞塊，回滾段里的部分?jǐn)?shù)據(jù)不能讀取，需要幫忙解決。
我查看了一下swappALRT.log文件，發(fā)現(xiàn)有以下錯誤：
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'
原來是回滾段表空間數(shù)據(jù)文件有壞塊了。知道了問題的所在，馬上解決，我已經(jīng)想好了思路，就是新建一個回滾段表空間，把以前壞了的回滾段表空間drop掉，在新的回滾段表空間上建回滾段，所要建的回滾段和以前的一摸一樣，讓以后產(chǎn)生的回滾數(shù)據(jù)都寫到新建的回滾段上。思路清楚，馬上開始行動了。
?首先停到listener,不答應(yīng)有新的應(yīng)用連到數(shù)據(jù)庫上做操作，然后down掉數(shù)據(jù)庫，為了清除掉已有的數(shù)據(jù)庫會話連接資源:
$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 (以受限模式啟動數(shù)據(jù)庫，為了防止其他用戶登陸進(jìn)來做相關(guān)操作，這時候只答應(yīng)治理員登陸）
查找回滾段對應(yīng)的表空間:
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.
查看當(dāng)前回滾段表空間里是否有活動的事物:
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回滾段了，這正是我想要的結(jié)果。
接下來查找回滾段存儲參數(shù)信息:
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的值都記錄下來，留做以后創(chuàng)建新的回滾段使用。
創(chuàng)建LMT治理方式的回滾段表空間(我的數(shù)據(jù)庫是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發(fā)現(xiàn)對于oracle8i來講在LMT方式治理的表空間下不能創(chuàng)建回滾段，但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的，呵呵！
下面才是真正開始要做的工作，總之兩個字，細(xì)心，因為是生產(chǎn)庫，不敢馬虎。
SQL> set feedback off
SQL> set pages 0
SQL> select 'alter rollback segment 'segment_name' offline;'? from dba_rollback_segs;
做一個腳本把除system回滾段以外的回滾段都o(jì)ffline掉，省的一個一個敲，腳本結(jié)果如下:
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;
腳本做好了，別忘了執(zhí)行。
執(zhí)行完后開始在新的回滾段表空間下建回滾段，存儲參數(shù)和原來保持一致：
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;
也是做了個腳本，免的一個一個敲！
下面的大回滾段要單獨建，總之，系統(tǒng)里面最好要有一個大的回滾段，有大事物的時候就派上用場了。
SQL> create public rollback segment APPRBS tablespace rbs01 storage(initial 2097152 next 10485760 MINEXTENTS 50 MAXEXTENTS 32765);
Rollback segment created.
查看新建的回滾段狀態(tài)：
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狀態(tài)。
繼續(xù)做腳本讓除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;
執(zhí)行以上腳本后，刪除原來的undo表空間RBS：
SQL>drop tablespace rbs including contents;
Tablespace dropped.
做到這里即完成了所要求的工作，好了，剩下的就留做數(shù)據(jù)測試了，收工，明天等數(shù)據(jù)庫測試結(jié)果。
