[oracle 설정] UNDO tablespace 사용량 급증 현상 해결

발단

oracle DB 환경을 관리하던 중 DB서버 이동을 하게 되었음.

신규 서버에 oracle DB가 다시 세팅되고 며칠간 모니터링 수행 중 Tablespace 중 UNDOTBS 사용량이 아주 빠른 속도로 증가되는 것을 발견.

원인 파악을 하여 바로 조치하는 것이 제일 베스트이지만 당장의 서비스 운영을 위해서 임시조치로 해당 테이블스페이스를 증설한 상태에서 원인 파악에 들어갔음.

---

사전조사 및 참고자료

UNDOTBS(Undo Tablespace)란?

읽기 일관성을 유지하기 위해 사용되는 Tablespace로 데이터베이스 운영 중 많은 사용자들에게서 DML 작업이 이루어지는데 이때 Rollback하게 되는 경우를 대비하여 DML 작업이 발생했을 때 삭제 및 수정 이전의 값에 대한 정보를 UNDO Segment에 저장합니다. 이러한 Undo Segment에 대한 관리 공간으로 Undo Tablespace를 사용합니다.

 

AUM(Automatic Undo Management)이란?

oracle 9i 부터 제공하는 undo 관리 기능이며 오라클 서버가 자동적으로 undo Data 및 Tablespace를 관리한다.

반대로 MUM 방식은 DBA의 설정 및 작업으로 수동적으로 관리되는 방식.

MUM/AUM 비교

AUM 사용 여부 확인

[확인 1] AUM 사용 여부 확인 SQL

show parameter undo;

NAME	TYPE	VALUE	설명
undo_management	string	AUTO	undo 관리 방식 (AUTO/MANUAL)
undo_retention	integer	900	undo 데이터 유지시간(default:900)
undo_tablespace	string	UNDOTBS1	관리 대상 undo Tablespace Name

undo_management의 값이 AUTO 이기 때문에 현재 DB는 AUM 방식을 사용하는 것을 확인 가능하다.

UNDO_EXTENTS 상태 조회 및 상태 설명

[확인2]UNDO_EXTENTS 상태 조회 SQL

SELECT DISTINCT STATUS, SUM(BYTES)/(1024*1024) MB, COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

STATUS	MB	COUNT(*)
UNEXPIRED	50	51
EXPIRED	150	99

UNDO_EXTENTS 상태 조회 결과이며 각 상태에 대해서 설명은 아래와 같다.

EXTENT STATUS	설명
EXPIRED EXTENT	undo_retention 시간을 초과한 Extent. 사용 중인 트랜잭션이 없고 undo retention 도 완료되어서 언제든지 트랜잭션에 할당될 수 있는 상태
UNEXPIRED EXTENT	undo_retention 시간을 초과하지 않은 Extent. 사용 중인 트랜잭션이 없으나, 언두 유지 시간이 완료되지 않아서 트랜잭션에 할당되지 않고 보존되어 있는 상태
ACTIVE EXTENT	트랜잭션에 할당되어 언두 데이터를 기록하고 있는 상태
FREE EXTENT	생성 이후 트랜잭션에 한 번도 할당되지 않았거나, SMON에 의한 주기적 정리가 완료되어 있는 상태

 

Extent Stealing

트랜잭션이 끝났지만 언두유지시간에 의하여 유지되고 있는 언두 익스텐트를 재사용하는 기능

 

Extent  할당 순서

1. 자기 자신의 Extent의 프리 블록을 찾는다.

2. 다음 Extent가 만료된 Extent인지 확인한다.

3. 언두 테이블스페이스에서 새로운 Extent를 할당한다.

4. 오프라인 트랜잭션 테이블에서 expired Extent를 가져온다(steal).

5. 온라인 트랜잭션 테이블에서 expired Extent를 가져온다(steal).

6. autoextend가 가능하다면 파일을 확장하여 Extent를 할당한다.

7. 자신의 트랜잭션 테이블에서 unexpired Extent를 재사용한다.

8. 오프라인 트랜잭션 테이블에서 unexpired Extent를 가져온다(steal).

9. 에러(ORA-30036)를 발생한다.

 

RETENTION 상태 확인

[확인3] RETENTION 상태 확인 SQL

select RETENTION from DBA_TABLESPACES where tablespace_name ='UNDOTBS1';

조회 결과는 아래 두 값 중 하나

guarantee  : 언두 유지 시간 엄격히 적용

noguarantee : 언두 유지 시간을 엄격하게 적용하지 않고 테이블 스페이스 크기와 연관하여 관리

 

Tablespace 확장성 사용 여부 확인

[확인 4] Tablespace 확장성 사용 여부 확인

select autoextensible from DBA_DATA_FILES where tablespace_name ='UNDOTBS1';

조회 결과는 아래 두 값 중 하나

on(yes) : 테이블스페이스 공간이 부족할 시 자동적으로 확장 가능하게 하는 옵션

off(no)  : 테이블스페이스 공간이 부족할 시 자동적인 확장을 제한하는 옵션

 

Automatic Undo Retention 적용 조건 표

Automatic Undo Retention  적용 조건

해당 표가 이번 이슈해결에 가장 큰 도움을 받은 자료이다.

---

원인 분석

상단에 UNDO 관리에 대한 참고자료 항목에 정리해 보았다 관련 자료를 참고하여 현재 문제가 발생한 DB에서 각각의 설정을 확인하였다.

 

[확인 1] AUM 사용 여부 조회 (확인 결과 AUTO 값 확인)

[확인 2]UNDO_EXTENTS 결과를 확인하였을 때 UNEXPIRED EXTENT값이 줄어들지 않는 것을 확인

[확인 3]RETENTION 상태 조회 (noguarantee 값 확인)

[확인 4]TABLESPACE autoextensible 값 확인 (off 값 확인)

 

위에 상태 확인 결과를 토대로 "Automatic Undo Retention 적용 조건 표"에 대입해 보았다.

현재 상태는 GUARANTEE : NO / AUTOEXTEND : NO에 해당하며 이 경우 언두 유지시간은 345,600초와 UNDO_RETENTION(900초) 설정 값 중 큰 값을 따르기 때문에 큰 값인 345,600초 즉 4일이라는 기간 동안 언두를 유지하도록 되어 있던 것이었다. 현재 시스템상 매분마다 들어오는 데이터가 많고 매분마다 반복해서 작업이 있기 때문에 undo Tablespace 사용량이 증가하는 것을 쉽게 확인할 수 있었던 것이다.

---

조치방법

undo Tablespace autoextensible 옵션 yes로 적용하고 maxsize는 현재 생성된 dbf 파일 사이즈만큼 설정하면 해당 dbf 파일이 이전 이슈가 발생하였을 때 무분별하게 확장하지 않도록 방지할 수 있게 설정할 수 있고 이에 따라 참고자료 항목에 Automatic Undo Retention 적용 조건 표 두 번째 조건 GUARANTEE : NO / AUTOEXTEND : YES에 맞추어 관리하도록 변경 후 언두 유지시간은 MAXQURTLEN + 300초와 UNDO_RETENTION(900초) 중 큰 값을 따르게 되는 것을 볼 수 있으며 모니터링 진행 결과 정상적으로 UNDO Tablespace가 관리되는 모습을 볼 수 있었다.

ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\undo01.dbf' AUTOEXTEND ON MAXSIZE 1024M;

---

참고사이트 출처

http://wiki.gurubee.net/display/CORE/3.2+AUM+vs.+MUM

http://wiki.gurubee.net/pages/viewpage.action?pageId=26742857

https://sarc.io/index.php/oracledatabase/145-2014-04-23-05-17-38