oracle8i復原段資料表空間出現壞塊的解決方案

今天早上剛到公司便接到網通客戶的投訴電話，說網管資料庫出問題了，資料庫有壞塊，復原段裡的部分資料不能讀取，需要幫忙解決。

我查看了一下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 (以受限模式啟動資料庫，為了防止其他使用者登陸進來做相關操作，這時候只允許管理員登陸）
ORACLE instance started.

Total System Global Area 1134141116 bytes
Fixed Size                   102076 bytes
Variable Size             311750656 bytes
Database Buffers          819200000 bytes
Redo Buffers                3088384 bytes
Database mounted.
Database opened.

 

尋找復原段對應的資料表空間:

 

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>drop tablespace rbs01 including contents;

Tablespace dropped.

$rm /opt/oracle/db02/oradata/ORCL/rbs01.dbf  （刪除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.

$rm /opt/oracle/db02/oradata/ORCL/rbs.dbf  (刪除rbs資料表空間所在的資料檔案)

做到這裡即完成了所要求的工作，好了，剩下的就留做資料測試了，收工，明天等資料庫測試結果。