資料庫的rbs表空間中資料檔案rbs01.dbf大小超過10G的處理

問題描述：

接到客戶投訴，資料庫的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

# 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檢視空間，發現空間已經釋放。

# mount /opt/oracle/db02

# 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 切換到另外一個節點，正常。

[@more@]