问题描述:
接到客户投诉,数据库的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@]