工作中遇到的ORACLE故障處理一例

問題描述：

接到客戶投訴，資料庫的rbs資料表空間中資料檔案rbs01.dbf大小超過10G，並一直在增長，另一個rbs02.dbf正常，大小為800M，目前該目錄空間使用率已達87%，可用磁碟空間已剩不多，能否將rbs01,dbf資料檔案回收，請提供解決問題的方法。

解決步驟：

1．撥號至現場，確認空間大小。

SQL> SELECT tablespace_name,max_m,count_blocks free_blk_cnt,sum_free_m,to_char(100*sum_free_m/sum_m, '99.99') || '%'
AS pct_free FROM (SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
(SELECT tablespace_name AS fs_ts_name, max(bytes)/1024/1024 AS max_m,count(blocks) AS count_blocks,sum(bytes/1024/1024)
AS sum_free_m FROM dba_free_space GROUP BY tablespace_name) WHERE tablespace_name=fs_ts_name;

TABLESPACE_NAME                MAX_M FREE_BLK_CNT SUM_FREE_M PCT_FREE

------------------------------ ----- ------------ ---------- --------

NMS_STAT_IDX                   893.1            2     899.25  44.96%

RBS                              402          131 9563.98437  86.98%

2.查看復原段的儲存參數，發現OPTSIZE為空白，致使復原段無法自動收縮。告知現場，批量插入OPTSIZE值-每個20M後復原段自動收縮。

SQL> SELECT n.name, s.extents, s.rssize, s.optsize,s.hwmsize, s.xacts, s.status FROM v$rollname n, v$rollstat s WHERE n.usn = s.usn;

NAME                           EXTENTS RSSIZE    OPTSIZE HWMSIZE XACTS STATUS

------------------------------ ------- ------ ---------- ------- ----- ---------------

RBS0                                21 220119            2432614     0 ONLINE

RBS1                                21 220119            2432614     0 ONLINE

RBS2                                21 220119            2432614     0 ONLINE

RBS3                                21 220119            2422128     0 ONLINE

但此時資料表空間無法RESIZE,提示：

ALTER DATABASE DATAFILE '/opt/oracle/db02/oradata/ORCL/rbs01.dbf' RESIZE 4194304K

ORA-03297: file contains used data beyond requested RESIZE value

3．查看SYSTEM 復原段的歸屬資料表空間，確認不在RBS資料表空間裡――SYSTEM 復原段資料表空間是SYSTEM.

SQL> SELECT owner, s.status, segment_name, f.file_name, f.bytes/1024/1024 "Size

    MB" FROM   dba_rollback_segs s, dba_data_files f WHERE  s.file_id = f.file_id; 

OWNER  STATUS           SEGMENT_NAME                   FILE_NAME                                                                           Size

MB

------ ---------------- ------------------------------ -------------------------------------------------------------------------------- ----------

SYS    ONLINE           SYSTEM                         /opt/oracle/db02/oradata/ORCL/system01.dbf                                           900

PUBLIC ONLINE           RBS0                           /opt/oracle/db02/oradata/ORCL/rbs02.dbf                                                 800

PUBLIC ONLINE           RBS1                           /opt/oracle/db02/oradata/ORCL/rbs02.dbf                                                 800

PUBLIC ONLINE           RBS2                           /opt/oracle/db02/oradata/ORCL/rbs02.dbf                                                 800

PUBLIC ONLINE           RBS3                           /opt/oracle/db02/oradata/ORCL/rbs02.dbf                                                 800

PUBLIC ONLINE           RBS4                           /opt/oracle/db02/oradata/ORCL/rbs02.dbf                                                 800

....

PUBLIC ONLINE           RBS46                          /opt/oracle/db02/oradata/ORCL/rbs02.dbf                                                 800

PUBLIC ONLINE           RBS47                          /opt/oracle/db02/oradata/ORCL/rbs02.dbf                                                 800

PUBLIC ONLINE           RBS49                          /opt/oracle/db02/oradata/ORCL/rbs02.dbf                                                 800

PUBLIC ONLINE           RBS48                          /opt/oracle/db02/oradata/ORCL/rbs02.dbf                                                 800

 

4.建立臨時替代的RBS資料表空間 RBS_TEMP,以及相應的復原段RBS_TEMP_1,RBS_TEMP2, RBS_TEMP3..

SQL>CREATE TABLESPACE "RBS_TEMP" LOGGING

    DATAFILE '/opt/oracle/db02/oradata/ORCL/RBS_TEMP.dbf' SIZE

    500M EXTENT MANAGEMENT LOCAL;

SQL>create public rollback segment rbs_TEMP_1 tablespace RBS_TEMP;

SQL>create public rollback segment rbs_TEMP_2 tablespace RBS_TEMP;

SQL>create public rollback segment rbs_TEMP_3 tablespace RBS_TEMP;

SQL>alter rollback segment RBS_TEMP_1  online;

SQL>alter rollback segment RBS_TEMP_2  online;

SQL>alter rollback segment RBS_TEMP_3  online;

5.把RBS資料表空間中的復原段全部OFFLINE,並且刪除。

SQL>alter rollback segment RBS1 offline;

SQL>alter rollback segment RBS2 offline;

SQL>alter rollback segment RBS3 offline;

....

DROP ROLLBACK SEGMENT RBS1;

DROP ROLLBACK SEGMENT RBS2;

DROP ROLLBACK SEGMENT RBS3;

....

drop tablespace RBS; 

rm RBS 的資料檔案。注意別刪錯。

 

6．從SPFILE中產生 PFILE 檔案，修改INIT檔案（由於是雙機cluster,注意兩台機器都要修改）。

把相應的rollback_segments='RBS0','RBS1','RBS2','RBS3','RBS4','RBS5','RBS6'。。。’RBS48’刪除，並添加上'RBS_TEMP_1','RBS_TEMP_2','RBS_TEMP_3'。
(注意，如果不做此操作，資料庫在啟動時會報01092的錯誤，查看metalink,上面沒有相應的CASE作為參考)

SQL> startup

ORACLE instance started.

 
Total System Global Area 1205480064 bytes

Fixed Size                   731776 bytes

Variable Size             486539264 bytes

Database Buffers          715825152 bytes

Redo Buffers                2383872 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

 

7．建立RBS資料表空間，添加相應的 復原段。注意修改INIT檔案裡的參數。 

遇到的問題：

(1).磁碟空間沒有釋放：

做第四步Rm 刪除RBS資料檔案時，發現磁碟空間沒有釋放。刪除了10個G的資料檔案，剩餘空間還是1.9G.

$ df -k

Filesystem            kbytes    used   avail capacity  Mounted on

/dev/md/dsk/d10      62173692 13783150 47768806    23%    /

                     20971520 18513080 1931224    91%    /opt/oracle/db02

由於白天，沒有敢做veritas命令的fsck. 也沒有做cluster切換。

(2). cluster切換失敗：

到了夜裡，cluster切換髮生失敗，原因時INIT檔案裡RBS參數沒有更新。資料庫不能正常啟動。

解決RBS參數後，資料庫正常啟動。 手工關閉資料庫後，準備用CLUSTER去啟動資料庫。

用 scswitch –Z –g oracle 命令，探索資料庫還是沒有被cluster帶起來，

查看vxdg list

NAME         STATE           ID

rootdg       enabled  1103770865.1025.sr_db2

ipasdg       enabled  1103772024.1098.sr_db2

root@sr_db2 # more /etc/vfstab

發現有如下條目存在：

/dev/vx/dsk/ipasdg/db02_vol     /dev/vx/rdsk/ipasdg/db02_vol    /opt/oracle/db02

        vxfs     2       no     -

/dev/vx/dsk/ipasdg/db03_vol     /dev/vx/rdsk/ipasdg/db03_vol    /opt/oracle/db03

        vxfs     2       no     -

/dev/vx/dsk/ipasdg/db04_vol     /dev/vx/rdsk/ipasdg/db04_vol    /opt/oracle/db04

        vxfs     2       no     -

/dev/vx/dsk/ipasdg/arch_vol     /dev/vx/rdsk/ipasdg/arch_vol    /opt/oracle/arch

        vxfs     2       no     -

/dev/vx/dsk/ipasdg/backup_vol   /dev/vx/rdsk/ipasdg/backup_vol  /backup vxfs

 2       no     -

手工mount /opt/oracle/db02,發現此時卷能夠正常拉起來。依次把db03,db04,nms,wacos,backup.arch 卷拉起來。

此時df -k查看空間，發現空間已經釋放。

root@sr_db2# mount /opt/oracle/db02

root@sr_db2 # df -k

Filesystem            kbytes    used   avail capacity  Mounted on

/dev/md/dsk/d20      62173692 8795392 52756564    15%    /

/proc                      0       0       0     0%    /proc

fd                         0       0       0     0%    /dev/fd

mnttab                     0       0       0     0%    /etc/mnttab

swap                 11375392     136 11375256     1%    /var/run

swap                 11375320      64 11375256     1%    /tmp

/dev/md/dsk/d60        99655    5001   84689     6%    /global/.devices/node@2

/dev/md/dsk/d50        99655    5001   84689     6%    /global/.devices/node@1

/dev/vx/dsk/ipasdg/db02_vol

                     20971520 7766192 13102168    38%    /opt/oracle/db02

scswitch –z –g oracle –h db1 切換到另外一個節點，正常。