今天早上剛到公司便接到網通客戶的投訴電話，說網管數據庫出問題了，數據庫有壞塊，回滾段里的部分數據不能讀取，需要幫忙解決。我查看了一下swappALRT.log文件，發現有以下錯誤：Tue Sep 21 10:34:08 2004 Errors in file E:Oracleadmin wappbdump wappSMON.TRC:ORA-01578: ORACLE data block corrupted (file # 2, block # 24497)ORA-01110: data file 2: 'E:ORACLEORADATA WAPPRBS01.DBF'原來是回滾段表空間數據文件有壞塊了。知道了問題的所在，馬上解決，我已經想好了思路，就是新建一個回滾段表空間，把以前壞了的回滾段表空間drop掉，在新的回滾段表空間上建回滾段，所要建的回滾段和以前的一摸一樣，讓以后產生的回滾數據都寫到新建的回滾段上。思路清楚，馬上開始行動了。?首先停到listener,不答應有新的應用連到數據庫上做操作，然后down掉數據庫，為了清除掉已有的數據庫會話連接資源:$lsnrctl stopLSNRCTL 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/oracleSQL*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 ProductionWith the Partitioning optionJServer Release 8.1.7.3.0 - 64bit ProductionSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL>startup restrict (以受限模式啟動數據庫，為了防止其他用戶登陸進來做相關操作，這時候只答應治理員登陸）查找回滾段對應的表空間: SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME??????????????? STATUS------------------------------ ---------SYSTEM???????????????????????? ONLINETOOLS????????????????????????? ONLINERBS??????????????????????????? ONLINETEMP?????????????????????????? ONLINEUSERS????????????????????????? ONLINEINDX?????????????????????????? ONLINEDRSYS????????????????????????? ONLINEWACOS????????????????????????? ONLINENMS??????????????????????????? ONLINETEST?????????????????????????? ONLINEFS???????????????????????????? ONLINEPERFSTAT?????????????????????? ONLINE12rows 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 a10SQL> col SEGMENT_NAME format a12SQL> set line 120SQL> 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??????????? 0RBS0???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS1???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS2???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS3???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS4???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS5???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS6???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS7???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS8???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS9???????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS10??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS11??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS12??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS13??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS14??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS15??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS16??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS17??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS18??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS19??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS20??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS21??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS22??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS23??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS24??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS26??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS27??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS28??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0RBS25??????? PUBLIC RBS01????????????? 524288????? 524288?????????? 8??????? 4096??????????? 0APPRBS?????? PUBLIC RBS01???????????? 2097152??? 10485760????????? 50?????? 32765??????????? 031 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的，呵呵！下面才是真正開始要做的工作，總之兩個字，細心，因為是生產庫，不敢馬虎。SQL> set feedback offSQL> set pages 0SQL> 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?????????? SYSTEMRBS0???????????????? PUBLIC OFFLINE????????? RBS01RBS1???????????????? PUBLIC OFFLINE????????? RBS01RBS2???????????????? PUBLIC OFFLINE????????? RBS01RBS3???????????????? PUBLIC OFFLINE????????? RBS01RBS4???????????????? PUBLIC OFFLINE????????? RBS01RBS5???????????????? PUBLIC OFFLINE????????? RBS01RBS6???????????????? PUBLIC OFFLINE????????? RBS01RBS7???????????????? PUBLIC OFFLINE????????? RBS01RBS8???????????????? PUBLIC OFFLINE????????? RBS01RBS10??????????????? PUBLIC OFFLINE????????? RBS01RBS11??????????????? PUBLIC OFFLINE????????? RBS01RBS12??????????????? PUBLIC OFFLINE????????? RBS01RBS13??????????????? PUBLIC OFFLINE????????? RBS01RBS14??????????????? PUBLIC OFFLINE????????? RBS01RBS15??????????????? PUBLIC OFFLINE????????? RBS01RBS16??????????????? PUBLIC OFFLINE????????? RBS01RBS17??????????????? PUBLIC OFFLINE????????? RBS01RBS18??????????????? PUBLIC OFFLINE????????? RBS01RBS19??????????????? PUBLIC OFFLINE????????? RBS01RBS20??????????????? PUBLIC OFFLINE????????? RBS01RBS21??????????????? PUBLIC OFFLINE????????? RBS01RBS22??????????????? PUBLIC OFFLINE????????? RBS01RBS23??????????????? PUBLIC OFFLINE????????? RBS01RBS24??????????????? PUBLIC OFFLINE????????? RBS01RBS26??????????????? PUBLIC OFFLINE????????? RBS01RBS27??????????????? PUBLIC OFFLINE????????? RBS01RBS28??????????????? PUBLIC OFFLINE????????? RBS01RBS25??????????????? PUBLIC OFFLINE????????? RBS01APPRBS?????????????? PUBLIC OFFLINE????????? RBS0130 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.做到這里即完成了所要求的工作，好了，剩下的就留做數據測試了，收工，明天等數據庫測試結果。