Oracle案例11——Oracle表空間資料庫檔案收縮

Rangle發表於2018-07-04
Oracle資料庫

我們經常會遇到資料庫磁碟空間爆滿的問題，或由於歸檔日誌突增、或由於資料檔案過多、大導致磁碟使用緊俏。這裡主要說的場景是磁碟空間本身很大，但表空間對應的資料檔案初始化的時候就直接頂滿了磁碟空間，導致經常收到磁碟空間滿的報警。

一、錯誤資訊

告警內容如下：

【發現異常】地產客儲系統資料庫Oracle_192.168.xx.xx，192.168.xx.xx,資料庫customer,連線錯誤,0 ORA-00257: archiver error. Connect internal only, until freed.
【發生時間】2018.07.04 09:12:21

二、錯誤原因

上述錯誤一看大致就知道是由於磁碟空間不足，導致歸檔無法完成所致，我們只需要清理足夠的磁碟空間即可。但在磁碟清理的時候發現磁碟空間本身可清理的不多，被很多很大的資料檔案佔用，而實際使用的segment大小總共不足400G，磁碟空間本身1T，所以我們可以通過收縮資料檔案的方式回收磁碟空間。

資料檔案初始化方式：

1.我們建立表空間一般有兩種方式初始化其資料檔案，即指定初始大小為32G（很大的值）或指定初始大小為100M（很小的值）然後通過自動擴充套件方式慢慢按需增長。

2.第一種初始資料檔案方法壞處就是開始不管你用不用到那麼大，都會佔用這麼大的磁碟空間（這種資料遷移的時候可以使用）。第二種初始化方法按需增長，比較好的監控實際使用磁碟空間，所以推薦初始值很小，使用自動擴充套件慢慢增長的方式。

三、處理步驟

1.檢視磁碟空間大小

2.檢視資料庫表空間大小

#!/bin/bash
sqlplus -S /nolog   <<EOF
conn /as sysdba;
set echo off heading on underline on;
column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
column inst_name heading "Instance"  new_value inst_name format a12;
column db_name   heading "DB Name"   new_value db_name   format a12;
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;

prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~


select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v\$database d,
       v\$instance i;
       
set term on feedback off lines 130 pagesize 999 tab off trims on
column MB format 999,999,999  heading "Total MB"
column free format 9,999,999 heading "Free MB"
column used format 99,999,999 heading "Used MB"
column Largest format 999,999 heading "LrgstMB"
column tablespace_name format a20 heading "Tablespace"
column status format a3 truncated
column max_extents format 99999999999 heading "MaxExt"
col extent_management           for a1 trunc   head "M"
col allocation_type             for a1 trunc   head "A"
col Ext_Size for a4 trunc head "Init"
column pfree format a3 trunc heading "%Fr"


break on report
compute sum of MB on report
compute sum of free on report
compute sum of used on report


select  
  d.tablespace_name, 
  decode(d.status, 
    'ONLINE', 'OLN',
    'READ ONLY', 'R/O',
    d.status) status,
  d.extent_management, 
  decode(d.allocation_type,
    'USER','',
    d.allocation_type) allocation_type,
  (case 
    when initial_extent < 1048576 
    then lpad(round(initial_extent/1024,0),3)||'K' 
    else lpad(round(initial_extent/1024/1024,0),3)||'M' 
  end) Ext_Size,
  NVL (a.bytes / 1024 / 1024, 0) MB,
  NVL (f.bytes / 1024 / 1024, 0) free, 
  (NVL (a.bytes / 1024 / 1024, 0) - NVL (f.bytes / 1024 / 1024, 0)) used,
  NVL (l.large / 1024 / 1024, 0) largest, 
  d.MAX_EXTENTS ,
  lpad(round((f.bytes/a.bytes)*100,0),3) pfree,
  (case when round(f.bytes/a.bytes*100,0) >= 20 then ' ' else '*' end) alrt
FROM sys.dba_tablespaces d,
  (SELECT   tablespace_name, SUM(bytes) bytes
   FROM dba_data_files
   GROUP BY tablespace_name) a,
  (SELECT   tablespace_name, SUM(bytes) bytes
   FROM dba_free_space
   GROUP BY tablespace_name) f,
  (SELECT   tablespace_name, MAX(bytes) large
   FROM dba_free_space
   GROUP BY tablespace_name) l
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND d.tablespace_name = l.tablespace_name(+)
  AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
UNION ALL
select 
  d.tablespace_name, 
  decode(d.status, 
    'ONLINE', 'OLN',
    'READ ONLY', 'R/O',
    d.status) status,
  d.extent_management, 
  decode(d.allocation_type,
    'UNIFORM','U',
    'SYSTEM','A',
    'USER','',
    d.allocation_type) allocation_type,
  (case 
    when initial_extent < 1048576 
    then lpad(round(initial_extent/1024,0),3)||'K' 
    else lpad(round(initial_extent/1024/1024,0),3)||'M' 
  end) Ext_Size,
  NVL (a.bytes / 1024 / 1024, 0) MB,
  (NVL (a.bytes / 1024 / 1024, 0) - NVL (t.bytes / 1024 / 1024, 0)) free,
  NVL (t.bytes / 1024 / 1024, 0) used, 
  NVL (l.large / 1024 / 1024, 0) largest, 
  d.MAX_EXTENTS ,
  lpad(round(nvl(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,100),0),3) pfree,
  (case when nvl(round(((a.bytes-t.bytes)/NVL(a.bytes,0))*100,0),100) >= 20 then ' ' else '*' end) alrt
FROM sys.dba_tablespaces d,
  (SELECT   tablespace_name, SUM(bytes) bytes
   FROM dba_temp_files
   GROUP BY tablespace_name order by tablespace_name) a,
  (SELECT   tablespace_name, SUM(bytes_used  ) bytes
   FROM v\$temp_extent_pool
   GROUP BY tablespace_name) t,
  (SELECT   tablespace_name, MAX(bytes_cached) large
   FROM v\$temp_extent_pool
   GROUP BY tablespace_name order by tablespace_name) l
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.tablespace_name = l.tablespace_name(+)
  AND d.extent_management LIKE 'LOCAL'
  AND d.contents LIKE 'TEMPORARY'
  ORDER by 1
/
prompt
exit
EOF

3.查詢可直接收縮表空間資料檔案

這裡檢視的是可以直接收縮的資料檔案大小，比如最開始初始化的資料檔案為32G，在資料檔案高水位以下的為20G，那麼可直接回收的為12G。

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
          ceil(HWM * a.block_size)/1024/1024 ResizeTo,
          (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
          'alter database datafile '''||a.name||''' resize '||
          ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
   from v$datafile a,
        (select file_id,max(block_id+blocks-1) HWM
          from dba_extents
          group by file_id) b
  where a.file# = b.file_id(+)
   and (a.bytes - HWM *block_size)>0;

4.直接收縮資料檔案

alter database datafile '/oracle/oradata/bi/data01.dbf' resize 1548M;

5.再次檢視磁碟空間，已釋放很多，可手動完成歸檔測試。

四、總結

針對oracle的資料檔案收縮（磁碟空間收縮），我們一般可通過當前磁碟空間檢視（df -h）——>執行可直接收縮的查詢命令和收縮命令——>執行大表高水位收縮——>執行表空間高水位收縮（降低檔案高水位線）——>再次執行直接回收表空間資料檔案命令

直接收縮資料檔案的方式參考本文上述步驟即可完成。

大表的高水位收縮參考：https://www.cnblogs.com/rangle/p/9259883.html

那麼如何降低表空間的資料檔案高水位，進而完成表空間資料檔案回收呢？

1.檢視大於10G的資料檔案

select file_name,file_id,tablespace_name,(bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024)  >10  order by file_id;

2.檢視大於10G的資料檔案對應的資料塊資訊

select file_id,max(block_id+blocks-1) HWM,block_id
             from dba_extents
             where file_id =14
              group by file_id,block_id
              order by hwm desc ;

3.檢視大表對應的資料塊資訊

##檢視大表
select file_name,file_id,tablespace_name,(bytes/1024/1024/1024) file_size_gb from dba_data_files where (bytes/1024/1024/1024)  >10  order by file_id;
##檢視大表對應的塊
 select owner,segment_name,file_id,block_id,blocks from dba_extents where segment_name='TABLE_NAME';

4.降低表的高水位 

alter table table_name move;
alter index idx_name rebuild;

5.檢視資料檔案對應的最大的block_id

SELECT MAX(block_id)
          FROM dba_extents
         WHERE tablespace_name = 'TABLESPACE_NAME';

6.執行資料檔案收縮

(block_id+blocks-1)資料檔案的HWM

alter database datafile '/oracle/oradata/bi/data01.dbf' resize xxxM;

 

相關文章