[ORACLE] UNDO Tablespace Full 모니터링 및 조치

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 방식을 사용

 

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

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

 

 

SELECT autoextensible from DBA_DATA_FILES where tablespace_name ='UNDOTBS1';

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

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

 

SELECT RETENTION from DBA_TABLESPACES where tablespace_name ='UNDOTBS1';

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

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

 

• 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)를 발생한다.

 

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

 

조치 방법

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

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

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

SELECT tablespace_name, autoextensible from DBA_DATA_FILES where tablespace_name like 'UNDOTBS%';

SELECT RETENTION from DBA_TABLESPACES where tablespace_name ='UNDOTBS1';

SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024||'MB', STATUS, AUTOEXTENSIBLE, ONLINE_STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'UNDOTBS1';

select segment_name, sum(bytes)/1024 k, count(extent_id) -- count(extent_id) = extent 개수
from dba_undo_extents
group by segment_name;

select count(*)
from dba_undo_extents
where tablespace_name like 'UNDOTBS2'
and status = 'UNEXPIRED';

--ALTER TABLESPACE UNDOTBS1 ADD DATAFILE SIZE 1G;

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk  
from v$session a, v$transaction b
where a.saddr = b.ses_addr;

select * from dba_tablespaces where tablespace_name like 'UNDO%';

select * from dba_users order by 1;

• [SEGMENT SPACE MANAGEMENT] OPTION : MANUAL (default), AUTO
• DBA_TABLESPACES 에서 조회, MAX_SIZE가 32G로 설정되어 있고 RETENTION 갯수가 많지만 **FREE SPACE → EXPIRED → UNEXPIRED** 순으로 조회하여 공간을 관리하며 사용하므로 크게 이슈는 없을 것으로 예상

 

• 23/11/15 ORACLE UNDO TABLESPACE MONITORING : 99%
• 16일 오전 8시에 모니터링 결과 확인하고 테이블스페이스 변화 확인해볼 것 (용량, 데이터파일 등등)
• IF) 계속 99% 상태로 유지될 시 UNDO TABLESPACE 이동 시켜서 비워볼 것 (UNDOTBS1)