mysql中的rbs_数据库的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@]