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版本發生該情況時，在交易期間可以透過新增表空間來解決這個問題。後續再對錶進行遷移，釋放異常表所佔的空間。在10G Oracle版本發生該情況時，可以執行如下指令碼釋放表所佔的異常表空間。

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檔案編號' SIZE 500M AUTOEXTEND

ON NEXT 100M MAXSIZE 4000M

其中未使用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檔案編號' SIZE 500M AUTOEXTEND

ON NEXT 100M MAXSIZE 4000M

其中未使用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

where owner='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;