Oracle案例08——xx.xx.xx.xx,表空間 SYSAUX 使用率>95%%

Rangle發表於2018-06-19
OracleUX

本例項主要針對Oracle表空間飽滿問題處理方法做個步驟分享。

 一、告警資訊

收到zabbix告警資訊，表空間 SYSAUX 使用率>95%%，系統表空間sysaux使用率超過了95%。

二、處理步驟

1.登入具體資料庫做相應的資料庫空間使用率查詢

set line 200;
set pagesize 20000;
set feedback off;
col tablespace_name for a20;
col c_free_percent for a12;
col c_used_percent for a12;
col m_free_percent for a12;
col m_USED_PERCENT for a12;
select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2)  c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2)   m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percent
from  (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) f
where d.tablespace_name=f.tablespace_name 
order by c_free_percent ;

2.查詢表空間對應的物件佔用情況

select OWNER,segment_name,segment_type,PARTITION_NAME,bytes/1024/1024/1024 Size_GB from dba_segments  where tablespace_name='SYSAUX' order by Size_GB desc

3.根據具體大物件做排查，對可以清理的相關資料清理

根據上述SQL查到的大物件主要是

1    SYS    WRH$_LATCH_CHILDREN    　　　　WRH$_LATCH__1153813778_29290    TABLE PARTITION    29.927734375
2    SYS    WRH$_LATCH_CHILDREN_PK   　　 WRH$_LATCH__1153813778_29290    INDEX PARTITION    14.984375
3    SYS    WRH$_ACTIVE_SESSION_HISTORY   WRH$_ACTIVE_1153813778_29290    TABLE PARTITION    3.6474609375
4    SYS    WRH$_SQLSTAT    　　　　　　　　WRH$_SQLSTA_1153813778_29290    TABLE PARTITION    1.2529296875

WRH$_LATCH_CHILDREN 表示快照使用的，其中分割槽1153813778是DBID， 29290是快照ID

檢視29290的快照ID是什麼時間的

select snap_id, begin_interval_time from sys.dba_hist_snapshot order by snap_id;

select snap_id, begin_interval_time from sys.dba_hist_snapshot where snap_id=29290

 4.清空分割槽WRH$_LATCH__1153813778_29290

select * from   WRH$_LATCH_CHILDREN partition ( WRH$_LATCH__1153813778_29290);
 
alter table WRH$_LATCH_CHILDREN truncate partition WRH$_LATCH__1153813778_29290;

5.清理後表空間檢視

select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2)  c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2)   m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percent
from  (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) f
where d.tablespace_name=f.tablespace_name  and f.tablespace_name='SYSAUX'
order by c_free_percent ;

三、指令碼附錄

1.表空間segment大小查詢

select OWNER,segment_name,PARTITION_NAME,segment_type,bytes/1024/1024/1024 Size_GB from dba_segments  where tablespace_name='SYSAUX' order by Size_GB desc

2.表空間使用率查詢

set line 200;
set pagesize 20000;
set feedback off;
col tablespace_name for a20;
col c_free_percent for a12;
col c_used_percent for a12;
col m_free_percent for a12;
col m_USED_PERCENT for a12;
select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2)  c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2)   m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percent
from  (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) f
where d.tablespace_name=f.tablespace_name 
order by c_free_percent ;

3.檢視快照ID、檢視快照設定資訊、設定快照資訊

select snap_id, begin_interval_time from sys.dba_hist_snapshot order by snap_id;
select * from DBA_HIST_WR_CONTROL;
begin
    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention =>43200,interval =>30, topnsql =>'MAXIMUM');
end;
/

4.統計資訊清理

exec dbms_stats.purge_stats(systimestamp -11);

 

相關文章