oracle系統表空間過大問題處理

SYSTEM表空間過大

檢查表空間時發現system表空間即將佔滿

清除aud$表空間

1. 檢視資料庫審計功能是否開啟

show parameter audit檢視資料庫審計功能是否開啟；


audit_trail值為DB，說明資料庫審計功能開啟，審計策略也是開啟；


audit_sys_operations為FALSE說明可以審計非sys/system使用者的所有操作，想要審計sys/system的操作，需要手動開啟audit_sys_operations引數，即alter system set audit_sys_operations=TRUE scope=spfile;然後再重啟資料庫。






--檢視審計日誌是否開啟


select name,type,value from v$parameter where name like '%aud%';

2. 進行相關檢視檢查操作

--檢視錶空間大小


SELECT a.tablespace_name "tablespace",total / (1024 * 1024 * 1024) "total(G)",


free / (1024 * 1024 * 1024) "shengyu (G)",


(total - free) / (1024 * 1024 * 1024) "used (G)",


round((total - free) / total, 4) * 100 "rote %"


FROM (SELECT tablespace_name, SUM(bytes) free


FROM dba_free_space


GROUP BY tablespace_name) a,


(SELECT tablespace_name, SUM(bytes) total


FROM dba_data_files


GROUP BY tablespace_name) b


WHERE a.tablespace_name = b.tablespace_name;










--檢視aud$表大小SQL


select bytes/1024/1024 MB,owner,segment_name,tablespace_name 


from dba_segments 


where segment_type='TABLE' and segment_name = 'AUD$';






--sys使用者檢視aud$表，如果sys使用者登入提示許可權不足可以檢視v$pwfile_users或者更改密碼(謹慎)


select count(*) from aud$;

截斷表

 需要注意的是，如果AUD$表過大，那麼直接TRUNCATE AUD$表，系統要立即釋放大量的EXTENTS，會嚴重影響系統效能。可以透過如下2個步驟逐步釋放EXTENTS：


1. 清空資料並保留原來的extents


 TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;


 在這裡，REUSE STORAGE是TRUNCATE的一個引數，表示保持原來的儲存不變。一般情況下，SQL命令“TRUNCATE TABLE TABLE_NAME;”其實就是“TRUNCATE TABLE TABLE_NAME DROP STORAGE;”。DROP STORAGE是TRUNCATE TABLE的預設引數。






2. 逐步回縮extents(執行的時候根據實際情況調整每次回縮空間大小)


 ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 5000M;


 2ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 2000M;


 ....


 ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;

到此為清理aud$表空間完成

分析審計項所佔空間

檢視那種審計佔的最多

select action_name,count(*) from dba_audit_trail group by action_name;


一般是LOGON和LOGOFF型別的審計最多。看要求取消此類審計：

取消審計內容

noaudit session whenever successful;一般來說，如果空間不是佔的特別多，此類審計還是保留為好。可以：






取消對一些登入特別頻繁的使用者的審計，比如DBSNMP使用者


noaudit session by dbsnmp;

SYSAUX表空間使用率過高問題處理

SYSAUX表空間做為SYSTEM表空間的輔助表空間，主要存放EM相關的內容以及表統計資訊，AWR快照，審計資訊等，而假設SYSAUX表空間在預設條件下你假設不做不論什麼配置，隨著時間的推移。會膨脹的越來越大。

1. 檢視錶空間使用

SELECT a.tablespace_name "tablespace",total / (1024 * 1024 * 1024) "total(G)",


free / (1024 * 1024 * 1024) "shengyu (G)",


(total - free) / (1024 * 1024 * 1024) "used (G)",


round((total - free) / total, 4) * 100 "rote %"


FROM (SELECT tablespace_name, SUM(bytes) free


FROM dba_free_space


GROUP BY tablespace_name) a,


(SELECT tablespace_name, SUM(bytes) total


FROM dba_data_files


GROUP BY tablespace_name) b


WHERE a.tablespace_name = b.tablespace_name;

2. 檢視SYSAUX表空間佔比

檢視SYSAUX表空間內各個分類專案佔儲存空間的比重,大頭在AWR報告，不過預設為8天，

SQL> col Item For a30


SQL> col "Space Used(GB)" For a10


SQL> col Schema For a20


SQL> col "MoveProcedure" For a200


SQL> SELECT occupant_name"Item",


           round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",


           schema_name "Schema",


           move_procedure "MoveProcedure"


      FROM v$sysaux_occupants


    ORDER BY 2 Desc;


    

3. 修改統計資訊的保持時間

預設31天，這裡改為15天。過期統計資訊會自動刪除

SQL> select dbms_stats.get_stats_history_retention from dual;


 31


--修改統計資訊保持時間


SQL> exec dbms_stats.alter_stats_history_retention(15);


SQL> select dbms_stats.get_stats_history_retention from dual;


 15

4. 修改AWR快照的儲存時間

改動AWR快照的儲存時間為7天(7 24 60),每小時收集一次，也能夠透過EM介面檢視和改動

--檢查當前系統的保留時間為8天,1小時取樣一次


SQL> select * from dba_hist_wr_control;


 DBID SNAP_INTERVAL                           RETENTION                               TOPNSQL


------------------------------------------------- -------------------------------------------------


1494575446 +0000001:00:00.0                       +0000800:00:00.0                       DEFAULT






--修改awr快照儲存時長


SQL> begin


         dbms_workload_repository.modify_snapshot_settings(


            interval => 60,


            retention => 10080,--分鐘


            topnsql => 100


          );


end;


ORA-13541: 系統移動窗體基線大小 (691200) 大於保留時間 (604800)


ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY", line 174


ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY", line 222


ORA-06512: 在 line 2

這裡 691200(8246060)。604800(7246060)都是以秒為單位的。 發現執行報錯，由於當前系統移動窗體大於如今所設的時間窗體。

--檢視系統的當前的MOVING_WINDOW_SIZE。


SQL> select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;


DBID BASELINE_NAME                   BASELINE_TYPEMOVING_WINDOW_SIZE


--------------------------------------------------- ------------- ------------------


1494575446SYSTEM_MOVING_WINDOW        MOVING_WINDOW                  8






--改動系統移動窗體其大小為7，即7天。


SQL> exec dbms_workload_repository.modify_baseline_window_size(7);






--再次執行改動AWR快照的儲存時間


SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>60,retention=> 7*24*60 );

5. 刪除AWR快照

刪除AWR快照，再次檢視SYSAUX表空間使用率

--查詢最最小和最大快照ID


SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot;


  MIN(SNAP_ID) MAX(SNAP_ID)


  ------------ ------------


        26705         27066






--注意，該方法有很大的坑，包底層是透過delete刪除的會產生大量的redo，undo以及歸檔日誌，會把空間撐滿，可以使用其他手動刪除方法


--刪除最早的24個AWR快照，也就是最早的24小時的快照。（因為從八天變為7天減少24小時，所以手動刪除第八天的awr快照，應該也可以等到時）


SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id =>26705,high_snap_id => 26705+24);