Oralce SYSAUX Tablespace 용량관리

/*-----------------------------------------------------------------------------------------
WORKAROUND로 아래와 같이 AWR PARTITION을 SPLIT해서 정리하기를 권고하고 있습니다.
alter session set "_swrf_test_action" = 72; 
 
하지만 WORKAROUND로 수행할 경우 ASH 데이터가 삭제되지 않는 문제가 발생합니다.
이 경우 아래의 명령을 선행해서 수행해준 후 SPLIT을 하면 됩니다.
 
exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size => 1); 
alter session set "_swrf_test_action" = 72;
alter session set events 'immediate trace name awr_test level 15';

-----------------------------------------------------------------------------------------*/


/*-------------------------------------------------------------------------------------
본 작업은 SYSAUX TABLESPACE의 대량 증가에 따른 수동 정리 작업 매뉴얼.
오라클 BUG로서 SNATPSHOT RETENTION이 지나도 삭제되지 않는 데이터를 삭제해주는 작업으로
본 작업시 대량의 DB FILE SCATTERED READ 발생, 대량의 아카이브 발생 한다
업무 영향도 고려 후 작업을 수행해야 한다
-------------------------------------------------------------------------------------*/

1. 현재 SYSAUX TABLESPACE를 점유하고 있는 객체 정보 조회

TABLESPACE_NAME	USAGE	TOTAL_BYTES FREE BYTES
--------------- ------- ----------- ----------
SYSAUX			95%		1.7GB		84MB

SELECT OCCUPANT_NAME,
       ROUND( SPACE_USAGE_KBYTES/1024) "SPACE (M)",
       SCHEMA_NAME,
       MOVE_PROCEDURE
FROM   V$SYSAUX_OCCUPANTS
ORDER BY 2 DESC;

OCCUPANT_NAME              Space (M)   SCHEMA_NAME          MOVE_PROCEDURE                      
-------------------------- ----------- -------------------- ------------------------------------
SM/AWR                            2505 SYS                                                      
SM/OPTSTAT                         165 SYS                                                      
LOGMNR                             128 SYSTEM               SYS.DBMS_LOGMNR_D.SET_TABLESPACE    
EM                                  90 SYSMAN               emd_maintenance.move_em_tblspc      
SM/ADVISOR                          67 SYS                                                      
SM/OTHER                             8 SYS                                                      
SMON_SCN_TIME                        5 SYS                                                      
WM                                   4 WMSYS                DBMS_WM.move_proc                   
JOB_SCHEDULER                        4 SYS                                                      
AO                                   2 SYS                  DBMS_AW.MOVE_AWMETA                 
XSOQHIST                             2 SYS                  DBMS_XSOQ.OlapiMoveProc             
EM_MONITORING_USER                   2 DBSNMP                                                   
SQL_MANAGEMENT_BASE                  2 SYS                                                      
PL/SCOPE                             2 SYS                                                      
LOGSTDBY                             1 SYSTEM               SYS.DBMS_LOGSTDBY.SET_TABLESPACE    
STREAMS                              1 SYS                                                      
AUTO_TASK                            0 SYS                                                      
TSM                                  0 TSMSYS                                                   
EXPRESSION_FILTER                    0 EXFSYS                                                   
ULTRASEARCH_DEMO_USER                0 WK_TEST              MOVE_WK                             
ULTRASEARCH                          0 WKSYS                MOVE_WK                             
TEXT                                 0 CTXSYS               DRI_MOVE_CTXSYS                     
ORDIM/SI_INFORMTN_SCHEMA             0 SI_INFORMTN_SCHEMA   ordsys.ord_admin.move_ordim_tblspc  
ORDIM/ORDPLUGINS                     0 ORDPLUGINS           ordsys.ord_admin.move_ordim_tblspc  
ORDIM/ORDDATA                        0 ORDDATA              ordsys.ord_admin.move_ordim_tblspc  
ORDIM                                0 ORDSYS               ordsys.ord_admin.move_ordim_tblspc  
STATSPACK                            0 PERFSTAT                                                 
XSAMD                                0 OLAPSYS              DBMS_AMD.Move_OLAP_Catalog          
XDB                                  0 XDB                  XDB.DBMS_XDB.MOVEXDB_TABLESPACE     
AUDIT_TABLES                         0 SYS                  DBMS_AUDIT_MGMT.move_dbaudit_tables 
SDO                                  0 MDSYS                MDSYS.MOVE_SDO                      



SELECT * FROM (
    SELECT SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 AS MB 
    FROM DBA_SEGMENTS 
    WHERE OWNER='SYS' 
    AND TABLESPACE_NAME='SYSAUX' 
    ORDER BY 3 DESC
    )
WHERE ROWNUM < 11;

SEGMENT_NAME             SEGMENT_TYPE       MB 
------------------------ ------------------ ---
WRH$_EVENT_HISTOGRAM_PK  INDEX PARTITION    408
WRH$_EVENT_HISTOGRAM     TABLE PARTITION    264
WRH$_SYSSTAT_PK          INDEX PARTITION    168
WRH$_LATCH               TABLE PARTITION    160
WRH$_SQLSTAT             TABLE PARTITION    136
WRH$_LATCH_PK            INDEX PARTITION    136
WRH$_SYSSTAT             TABLE PARTITION    104
WRH$_PARAMETER_PK        INDEX PARTITION     96
WRH$_PARAMETER           TABLE PARTITION     80
WRH$_SEG_STAT            TABLE PARTITION     72


------------------------------------------------------------------------------------------------------------------
-- AWR 데이터가 가장 많이 점유 하고 있음을 확인
-- 데이터 대부분이 파티션 객체임을 확인
------------------------------------------------------------------------------------------------------------------


2. AWR 데이터 보관 주기 확인
SELECT RETENTION FROM DBA_HIST_WR_CONTROL;

RETENTI
-------
+00008 00:00:00.0



3. 현재 저장된 SNAP 정보 확인
SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT;

MIN(SNAP_ID) MAX(SNAP_ID
------------ -----------
       2335         2541



SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM WRH$_ACTIVE_SESSION_HISTORY;

MIN(SNAP_ID) MAX(SNAP_ID
------------ -----------
          1         2541



SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM WRH$_EVENT_HISTOGRAM;

MIN(SNAP_ID) MAX(SNAP_ID
------------ -----------
          1         2541



SELECT MIN(SNAP_ID),MAX(SNAP_ID) FROM WRH$_SQL_BIND_METADATA;

MIN(SNAP_ID) MAX(SNAP_ID
------------ -----------
       2335         2591

------------------------------------------------------------------------------------------------------------------
-- DBA_HIST_SNAPSHOT, SQL_BIND_METADATA는 현재 SNAP 보관 주기(8일)에 맞게 저장되어 있으나
-- ACTIVE_SESSION_HISTORY, EVENT_HISTOGRAM은 과거 데이터까지 저장되어 있음
------------------------------------------------------------------------------------------------------------------


4. 파티션 정보 확인
SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'WRH$_ACTIVE_SESSION_HISTORY';

TABLE_NAME                     PARTITION_NAME                
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_2601808742_0      
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN     



5. 파티션 분할
-- 현재 SNAP 데이터만 남기고 과거 데이터를 분할 제거하기 위한 신규 파티션 생성(SPLIT)
ALTER SESSION SET "_swrf_test_action" = 72;

/*
SQL> alter session set "_swrf_test_action" = 72
ORA-02097: 지정된 값이 부적당해서 매개변수를 수정할 수 없습니다
ORA-13509: AWR 테이블로 갱신하는 중 오류가 발생했습니다.
ORA-01658: ORA-01658: SYSAUX 테이블스페이스에 세그먼트에 대한 INITIAL 확장 영역을 작성할 수 없습니다
 테이블스페이스에 세그먼트에 대한 INITIAL 확장 영역을 작성할 수 없습니다
-- SYSAUX TABLESPACE에 여유 공간이 없으면 SPLIT 불가
-- SYSAUX TABLESPACE 용량 증가 후 재작업
*/

SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'WRH$_ACTIVE_SESSION_HISTORY';

TABLE_NAME                     PARTITION_NAME                
------------------------------ ------------------------------
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_2601808742_0       
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_2601808742_2625    
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN     

6. SNAPSHOT 정리
EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 1,2335,2601808742)


7. TABLE SHRINK
-- SNAPSHOT을 삭제한 이후 SHRINK를 수행해야만 빈 공간을 반환해서 TABLESPACE의 빈 공간을 확보할 수 있다

SELECT * FROM (
    SELECT 'ALTER TABLE ' || SEGMENT_NAME || ' SHRINK SPACE CASCADE;' AS SQL,SEGMENT_TYPE,BYTES/1024/1024 AS MB 
    FROM DBA_SEGMENTS 
    WHERE OWNER='SYS' 
    AND TABLESPACE_NAME='SYSAUX' 
    AND SEGMENT_TYPE LIKE 'TABLE%'
    ORDER BY 3 DESC
    )
WHERE ROWNUM < 21;

SQL                                                               SEGMENT_TYPE       MB         
----------------------------------------------------------------- ------------------ -----------
ALTER TABLE WRH$_EVENT_HISTOGRAM SHRINK SPACE CASCADE;            TABLE PARTITION            264
ALTER TABLE WRH$_LATCH SHRINK SPACE CASCADE;                      TABLE PARTITION            160
ALTER TABLE WRH$_SQLSTAT SHRINK SPACE CASCADE;                    TABLE PARTITION            136
ALTER TABLE WRH$_SYSSTAT SHRINK SPACE CASCADE;                    TABLE PARTITION            104
ALTER TABLE WRH$_PARAMETER SHRINK SPACE CASCADE;                  TABLE PARTITION             80
ALTER TABLE WRH$_SEG_STAT SHRINK SPACE CASCADE;                   TABLE PARTITION             72
ALTER TABLE WRH$_ACTIVE_SESSION_HISTORY SHRINK SPACE CASCADE;     TABLE PARTITION             60
ALTER TABLE WRH$_SYSTEM_EVENT SHRINK SPACE CASCADE;               TABLE PARTITION             60
ALTER TABLE WRH$_LATCH_MISSES_SUMMARY SHRINK SPACE CASCADE;       TABLE PARTITION             48
ALTER TABLE WRI$_OPTSTAT_HISTGRM_HISTORY SHRINK SPACE CASCADE;    TABLE                       41
ALTER TABLE WRH$_DLM_MISC SHRINK SPACE CASCADE;                   TABLE PARTITION             32
ALTER TABLE WRH$_SYSMETRIC_HISTORY SHRINK SPACE CASCADE;          TABLE                       30
ALTER TABLE WRH$_ROWCACHE_SUMMARY SHRINK SPACE CASCADE;           TABLE PARTITION             20
ALTER TABLE WRH$_SERVICE_STAT SHRINK SPACE CASCADE;               TABLE PARTITION             19
ALTER TABLE WRH$_MVPARAMETER SHRINK SPACE CASCADE;                TABLE PARTITION             17
ALTER TABLE WRH$_SQL_PLAN SHRINK SPACE CASCADE;                   TABLE                       12
ALTER TABLE WRI$_ADV_SQLT_PLANS SHRINK SPACE CASCADE;             TABLE                       12
ALTER TABLE WRI$_OPTSTAT_HISTHEAD_HISTORY SHRINK SPACE CASCADE;   TABLE                       11
ALTER TABLE WRH$_SERVICE_WAIT_CLASS SHRINK SPACE CASCADE;         TABLE PARTITION              9
ALTER TABLE WRH$_DB_CACHE_ADVICE SHRINK SPACE CASCADE;            TABLE PARTITION              8

20 rows selected.


TABLESPACE_NAME	USAGE	TOTAL_BYTES FREE BYTES
--------------- ------- ----------- ----------
SYSAUX			58%		1.7GB		700MB



※파티션 불리가 일어 나지 않거나 스넵이 변화가 없다면 버그가 있는것이고 패치 요청을 해야한다


-- 관련 링크
http://jhdba.wordpress.com/tag/_swrf_test_action/
https://jonujoy.wordpress.com/2014/04/04/how-to-clean-up-sysaux-manually/
https://aprakash.wordpress.com/2014/09/21/sysaux-growing-rapidly-what-can-be-done/
https://aprakash.wordpress.com/2014/09/24/purging-sysaux/
http://jhdba.wordpress.com/2014/07/09/tidying-up-sysaux-removing-old-snapshots-which-you-didnt-know-existed/
http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/