Oracle数据库空间突然增大,oracle BUG 5890312导致表空间瞬间暴涨

oracle BUG 5890312导致表空间瞬间暴涨

据悉，最近某些同行出现oracle数据库BUG(5890312)，对业务造成影响。bug发作后，如果是在system表空间上，可能造成整个数据库挂起；如果是其他业务数据表空间，可造成业务发生时由于后台数据库无空间可供分配，导致业务失败，应用程序报错。[@more@]

【问题现象】

在系统运行过程中，数据库某表空间突然暴涨，瞬间CPU使用率达到100%的情况，对于使用裸设备的数据库，由于数据文件大小不能自动扩展，可能出现充满整个表空间的情况，导致数据库服务中止。【原因说明】经过查询，该问题和bug 5987262有关。bug的内容如下：TABLESPACE IS ABNORMALLY INCREASED BY UNFORMATTED BLOCKS。该bug影响的范围是任何平台下的Oracle 9.2.0.8 to 10.2.0.3。该bug的基础BUG 5890312 - HANG OBSERVED WHILE CREATING CTXCAT INDEX。该bug可以通过打patch 5890312来预防。【应急解决方案】

在9i的Oracle版本发生该情况时，在交易期间可以通过添加表空间来解决这个问题。后续再对表进行迁移，释放异常表所占的空间。在10GOracle版本发生该情况时，可以运行如下脚本释放表所占的异常表空间。alter table表名enable row movement;

alter table表名shrink space;

alter table表名disable row movement;【解决方案】

升级oracle数据库，打patch 5890312。详细升级步骤参照oracle官方发布的该patch的readme文件。

【升级步骤】：

1、关闭连接到数据库的应用程序；2、rman全库备份；3、正常关闭rac，停止所有节点、em、isqlplus、监听等；4、使用tar备份两个节点的oracle home目录：$tar cvf ora_base_24_20090605.tar /u01/app/oracle；5、按照官方文档install patch，只需在一个节点上操作，会自动将编译后的文件传送到另一个节点；

－－ftp p5890312_10203_HPUX-IA64.zip到/home/oracle目录

－－unzip p5890312_10203_HPUX-IA64.zip

－－按照官方文档install patch

% cd 5890312

% opatch apply

/u01/app/oracle/product/10.2.0/db_1/OPatch/opatch apply

－－检查打印的信息，确认后输入两次“y”

% y

% y

注：在三节点的rac环境中打这个patch时，在打完第一个节点后，会显示：

Remaining nodes to be patched:

'dcora2' 'dcora3'

What is the next node to be patched?

dcora2－－不需要加单引号

如果link时出错(查看升级log)，可以oracle用户登录出错的节点，执行relink all。6、在两个节点上检查patch是否安装成功；

% opatch lsinventory

7、启动rac；8、启动as；9、启动dds；10、全面检查后台系统；

【bug发作后的处理脚本】

一、当前表出问题

1、找出当前表中出现问题的表、表空间(根据表的大小判断出问题的表，当前表一般不会超过几百M，目前最大的才一百多M)：

set linesize 120

set pagesize 0

column "表名" format a50

column "空间大小(MB)" format a20

column "块数" format a10

column "表空间名" format a20

column "用户名" format a10

with a as(

select segment_name "表名",tablespace_name "表空间名", owner "用户名",sum(bytes/1024/1024) "空间大小(MB)", sum(blocks)"块数"

from dba_extents

where owner in('HS_USER','HS_FUND','HS_OPFUND','HS_SECU','HS_SECUSZ') and segment_type='TABLE'

group by segment_name,tablespace_name,owner

order by "空间大小(MB)" desc

)

select * from a where rownum<=100;

2、交易期间可临时给问题表空间增加数据文件(裸设备)：

ALTER TABLESPACE表空间名

ADD DATAFILE '/dev/vg01/rlvol未使用lv文件编号'SIZE500M AUTOEXTEND

ON NEXT100M MAXSIZE4000M

其中未使用lv文件通过如下方法查找：

a、查看oracle使用了哪些lv(使用裸设备)，在两个实例上执行如下语句(结果一样)

select file_name from dba_data_files order by file_name;

b、使用vgdisplay查看系统中已经创建了多少lv

vgdisplay -v vg01

3、闭市后执行如下脚本(10G)释放异常表所占的空间：

alter table表名enable row movement;

alter table表名shrink space;

alter table表名disable row movement;

二、历史表出问题

1、找出出问题的表空间：

column "表空间名称" format a18

column "占用率(%)" format a12

column "容量(M)" format a12

column "空闲(M)" format a12

column "使用(M)" format a12

column "最大容量(M)" format a15

column "可扩充容量(M)" format a15

column "采样时间" format a20

select a.tablespace_name "表空间名称",

to_char(100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)) "占用率(%)",

to_char(round(a.bytes_alloc/1024/1024,2)) "容量(M)",

to_char(round(nvl(b.bytes_free,0)/1024/1024,2)) "空闲(M)",

to_char(round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2)) "使用(M)",

to_char(round(maxbytes/1024/1024,2)) "最大容量(M)",

to_char(round(maxbytes/1024/1024,2)-a.bytes_alloc/1024/1024) "可扩充容量(M)",

to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"

from (select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes

from dba_data_files f

group by tablespace_name) a,

(select f.tablespace_name,

sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name

order by to_number("可扩充容量(M)") asc

;

2、交易期间可临时给问题表空间增加数据文件(裸设备)：

ALTER TABLESPACE表空间名

ADD DATAFILE '/dev/vg02/rlvol未使用lv文件编号'SIZE500M AUTOEXTEND

ON NEXT100M MAXSIZE4000M

其中未使用lv文件通过如下方法查找：

a、查看oracle使用了哪些lv(使用裸设备)，在两个实例上执行如下语句(结果一样)

select file_name from dba_data_files order by file_name;

b、使用vgdisplay查看系统中已经创建了多少lv

vgdisplay -v vg02

3、找出出问题的表

如下语句生成查询有数据的块数的语句：

column rid format a150

select distinct'select '''||segment_name||'表：'||'''||count(distinct dbms_rowid.rowid_block_number(rowid)) '||'"有数据的块数"'||' from '||owner||'.'||segment_name||';' rid

from dba_extents

whereowner='HS_HIS'and tablespace_name='HS_HIS_DATA' and segment_type='TABLE';

执行生成的sql语句查出每张表t的“有数据的块数”s1,

然后执行如下语句查出表t的分配的块数s2：

select sum(blocks) from dba_extents where segment_name='表名' and segment_tyep='TABLE';

最后计算s2：s1的值，正常情况下，两者差距不大，找出差距最大的表，此表即是有问题的表，

然后可以运行如下脚本释放表所占的异常表空间:

alter table表名enable row movement;

alter table表名shrink space;

alter table表名disable row movement;