Techdata VINA 지식 공유

AWR 통계정보 수집을 위해 statistics_level을 ALL로 변경하였다가 이후 LEVEL을 낮췄음에도 

SYSAUX tablespace에 저장된 정보들로인해 디스크 full 상태가 발생한 이슈가 있었다. 

SYSAUX tablespace에 있는 WRI$_ 로 시작하는 테이블들에 AWR 수집 데이터 값이 남아있어 발생한 이슈로 

해당 테이블을 $ORACLE_HOME/rdbms/admin 에 있는 스크립트를 수행하여 삭제 후 재생성할 수 있다.

1. AWR 통계정보 수집 기능을 비활성화를 위해서 statistics level을 Basic으로 변경한다.

SQL> show parameter statistics_level

NAME      TYPE                  VALUE

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

statistics_level            string                  TYPICAL

alter system set statistics_level=basic scope=spfile;

System altered.

2. AWR repository를 삭제하는 동안 트랜잭션이 일어나는 것을 방지하기 위하여 DB를 shutdown 후 restrict 모드로 재기동한다.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup restrict

ORACLE instance started.

Total System Global Area  542871552 bytes

Fixed Size    2254904 bytes

Variable Size  218105800 bytes

Database Buffers  314572800 bytes

Redo Buffers    7938048 bytes

Database mounted.

Database opened.

SQL> show parameter statistics

NAME     TYPE VALUE

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

optimizer_use_pending_statistics     boolean FALSE

statistics_level                string BASIC

timed_os_statistics        integer 0

timed_statistics               boolean FALSE

3. AWR 테이블 삭제 및 재생성을 위해 아래의 스크립트 차례로 수행한다.

SQL> @catnoawr

SQL> @catnomwn

SQL> @catnomtr

SQL> @catnoalr

SQL> @catnofus

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush shared_pool;

System altered.

11g의 경우 추가적으로 아래의 스크립트를 수행한다.

SQL> @dbmsslrt

SQL> @catawrtb

SQL> @execsvrm

4. 모두 삭제됐다면 아래의 쿼리를 통해 정상적으로 삭제되었는지 확인한다. 

   여전히 존재한다면 수동으로 삭제해준다.

SQL> select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%'; 

SQL> drop type AWR_OBJECT_INFO_TABLE_TYPE;

drop type AWR_OBJECT_INFO_TYPE;

drop table WRH$_PLAN_OPERATION_NAME;

drop table WRH$_PLAN_OPTION_NAME;

drop table WRH$_MV_PARAMETER;

drop table WRH$_MV_PARAMETER_BL;

drop table WRH$_DYN_REMASTER_STATS;

drop table WRH$_PERSISTENT_QMN_CACHE;

drop table WRH$_DISPATCHER;

drop table WRH$_SHARED_SERVER_SUMMARY;

drop table WRM$_WR_USAGE

drop table WRM$_SNAPSHOT_DETAILS

5. 모두 삭제되었다면 statistics level 기존 값으로 수정한 후 인스턴스를 재기동한다.

SQL> alter system set statistics_level=TYPICAL scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  542871552 bytes

Fixed Size    2254904 bytes

Variable Size  218105800 bytes

Database Buffers  314572800 bytes

Redo Buffers    7938048 bytes

Database mounted.

Database opened.

SQL> show parameter statistics_level

NAME     TYPE VALUE

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

statistics_level     string TYPICAL