表空間檢測異常的問題診斷

    不知道大家在工作中的表空間管理情況如何，大體會分為兩派。以前的公司我們更喜歡直接把空間都分配好，比如500G的容量規劃，那就提前準備500G，另外一類是我先給定200G，後續的空間就自動增長，反正容量還是500G。這個其實很大程度上就是個人習慣和公司流程規範的差別了。

   為什麼這麼說呢，因為我在一套環境上收到了一個奇怪的報警。

DBA:        IP: xxxx       Tablespace: PERFSTAT: 122.5%        [Critical!!]

    關鍵就在這個122.5%。看起來很不正常，如果這樣一個報警找不到問題的癥結，那麼這個檢測表空間的指令碼感覺還是有潛在的問題，或者說檢測的結果是會讓人質疑的。

   從我的瞭解，這個指令碼用了很多年，之前還真沒碰到過問題。現在的這套環境就偏偏丟擲了錯誤，我們來挖掘一下。

    首先這個表空間檢測的指令碼是使用我上面所說的第二種情況，即不斷的增大資料檔案，給定一個最大值。其實這樣算出來不是實際的檔案大小情況，和實際結果還是有出入的。

   如果要讓你檢測一下表孔家使用率該怎麼做，很顯然我們可以根據資料檔案的資料字典來得到一個當前值和檔案最大值。

select tablespace_name,
               round(sum(bytes) / (1024 * 1024)) b,
               round(sum(decode(maxbytes, 0, bytes, maxbytes))/(1024 * 1024)) mb
          from dba_data_files
         group by tablespace_name;

另外還有一個檢視需要用，是dba_free_space,這個檢視的結果得到的是表空間的可用情況，這個檢視非常重要。內部會迭代呼叫一些資料字典來綜合得到一個表空間可用率的資料。

select tablespace_name, round(sum(bytes) / (1024 * 1024)) b
          from dba_free_space
         group by tablespace_name;

兩者結合起來，最大值減去可用值就是使用率了。我們看看dba_data_files的數值。

SQL> select file_name,bytes/1024/1024 size_MB ,maxbytes/1024/1024 max_MB from dba_data_files where  tablespace_name='PERFSTAT';
FILE_NAME                                             SIZE_MB     MAX_MB
-------------------------------------------------- ---------- ----
/U02/app/oracle/oradata/xxxx/perfstat01.dbf              3100       2000
/U02/app/oracle/oradata/xxxx/perfstat02.dbf             10240       2000

   看到這裡感覺很奇怪。最大值maxsize竟然比當前值bytes還要低很多。

   看到這裡感覺離bug不遠了。但是不管如何這個問題現在來看還不夠嚴重，我們先想辦法解決。

    一種思路就是修復一下，我們制定表空間最大值

SQL> alter tablespace perfstat autoextend on maxsize 14G;
alter tablespace perfstat autoextend on maxsize 14G
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace PERFSTAT

沒想到這種模式不支援，oerr的幫助資訊提示，我們可以使用alter database datafile的方式來改進。

  所以修復方式就是找到那個資料字典不一致的資料檔案，重新做一下設定一下maxsize值。

SQL> alter database datafile '/U02/app/oracle/oradata/xxxx/perfstat02.dbf' autoextend on maxsize 12G;
Database altered.

這樣操作之後，再次檢視錶空間檢測指令碼，就沒有問題了。

我在MOS上看了下，這個問題原來很常見。

Value in BYTES Column Greater than MAXBYTES Column in DBA_DATA_FILES (文件 ID 197244.1)

文件還寫出了樣例來模擬這個問題。

create tablespace tst
       datafile 'd:\oracle\tst01.dbf' size 5m autoextend on；
alter database datafile 'd:\oracle\tst01.dbf' autoextend on maxsize 10m;
alter database datafile 'd:\oracle\tst01.dbf' resize 20m;
select file_name, bytes, maxbytes, autoextensible from dba_data_files;
FILE_NAME                                     BYTES   MAXBYTES AUT
---------------------------------------- ---------- ---------- ---
D:\ORACLE\TST01.DBF                        20971520   10485760 YES

   看來問題的癥結就在於之前做了resize的操作導致。我的處理方式介於兩者之間，我喜歡建立一個初始大小的檔案，然後resize到一個最大值。看來還是使用方式和習慣的不同在一些場景中會出現較大的偏差。