[Oracle] Undo Tablespace 의 공간이 확보되는데 걸리는 시간

* UNDO 테이블 스페이스란?
INSERT/UPDATE/DELETE 등으로 데이터를 변경하는 작업을 할 때, 변경 전의 데이터를 보관해 두는 공간이라고 보면 됨. 
사용자가 만약 COMMIT을 하지 않고 ROLLBACK을 한다면, 이 공간에 저장되어 있는 데이터를 가지고 ROLLBACK을 하게 된다

 

대용량의 데이터 재분석을 잘못 실행하는 바람에 UNDO 테이블 스페이스가 꽉 차버리는 문제가 발생했다. 

그것도 실제 고객사가 사용하는 운영 환경에서.. 

 

이미 UNDO 테이블 스페이스 용량이 100GB 이상으로 충분히 크게 설정이 되어 있기 때문에, 

단순히 UNDO 테이블 스페이스를 늘려서 해결하는 것은 좋지 않은 방법..인거 같아서 어떻게 할까 고민에 빠짐. 

 

팀장님한테 문의하니까,,

일정 시간이 지나면, UNDO 테이블 스페이스가 확보되니까 그냥 기다리면 될거에요~ 하고 가심 하하

그 일정 시간이 언젠데?????

일단 모르겠으니까 여기저기 찾아보면서 UNDO 테이블 스페이스가 확보될 때까지 기다려보기로 했다. 

뭐.. 금방 되것지 하는 마음으로...

근데 젠장, 아무리 기다려도 기다려도 용량 확보가 안됨. 

이 문제가 발생한건 금요일 퇴근시간 즈음.. 나는 똥줄이 타기 시작한다. 

주말내내 시스템에서 돌아가는 분석들이 많이 있는데 그것들이 다 안 될까봐 스트레스 지수 급 상승. 

 

퇴근시간이 지나서도 용량 확보가 안됨. 

그래서 정말 좋지 않은 방법이지만, 최후의 수단이다 하면서 DB 를 올렸다 내리기로 함. 

근데 이건 내가 할 수 있는 권한을 가진게 아니라서

서버 관리자에게 급하게 연락해서 아예 DB 서버 좀 재부팅 해 달라고 요청함 ㅠㅠ

나는 DB 를 올렸다 내리면 UNDO 테이블의 용량이 확보되는 줄 알고 있었음. 

근데 이건 잘못 알고 있던 사실이었음. 하하

 

DB 서버 재부팅 했다는 연락이 오면, 다 해결되것지~~ 하면서 룰루랄라 하고 연락만 기다렸음. 

연락이 옴.

이제 난 두 발 뻗고 주말을 보낼 수 있겠지~ 하면서 테이블 스페이스 용량을 확인해 봄. 

젠장. 왜 똑같음??? 왜 용량 확보 안됨???

주말에 발 뻗고 지내긴 글렀음. 

 

그래서 여기 저기서 도움을 요청하여 알아보니, 

TEMP 스페이스는 DB를 올렸다 내리면 다시 확보가 되지만

UNDO 테이블 스페이스는 DB를 올렸다 내리는거랑은 상관이 없다는 이야기를 주워들음.

그렇구나... 그런거였구나.. 살려줘..

 

그래서 결국엔,,  UNDO 테이블 스페이스 공간의 부족으로 인해 주말동안에 시스템에서 문제가 발생하면 안 되니까

어쩔 수 없이 임시로 UNDO 테이블 스페이스를 확장하였다. 

이렇게 하여 주말은 별 문제가 생기진 않을테지만.. 담당자로서 어쨌거나 주말내내 좌불안석이었다.

UNDO 테이블 스페이스가 평생 줄어들지 않고 차기만 할 것 같은 불안감에.. ㅋㅋ

그래서 주말동안 나는 가득찬 UNDO 테이블 스페이스가 언제 확보되는가???

하는 점에 대해 이것 저것 자료를 엄청 찾아 봤다. 

그러던 중, UNDO 테이블 스페이스 보유 시간을 확인하는 아래의 쿼리를 발견했다. 

select * from v$parameter where name = 'undo_retention';

 

* UNDO_RETENTION이란?

일관성 읽기를 위해 제공되는 UNDO 데이터의 보유기간을 결정. 

 

UNDO_RETENTION이라는 파라미터의 VALUE에 있는 초만큼 UNDO 테이블에 데이터를 보유하고 있고, 기본값은 900초, 즉 15분이란다. 

그럼 지금 문제가 발생한 DB는 하루가 지나도록 UNDO 테이블 스페이스가 확보되지 않으니, 

위 쿼리를 조회했을 때, 900초라는 값보다 훨씬 크겠지?? 하면서 조회를 해봄. 

 

UNDO_RETENTION 조회 결과

 

엥??? 900초인데???

15분이라는 시간은 지나도 벌써 몇번을 지났는데,, 왜 우리 DB는 UNDO 테이블 스페이스가 줄지 않는 것일까...?

또 고민에 빠짐. 고민 무한루프.

그래서 또 다시 검색 시작..

그러다가 UNDO 테이블 스페이스가 언제 확보되느냐에 대한 아주 간단 명료한 자료를 찾았다!!!

이 자료 하나로 단박에 이해됨. 

 

일단 UNDO 테이블 스페이스 보유 시간은, 단순히 위의 쿼리에서 UNDO_RETENTION 의 값으로만 확인하는게 아니였다. 

UNDO 테이블 스페이스의 RETENTION이라는 파라미터의 값이 GUARANTEE냐 아니냐 

그리고 Tablespace의 확장을 자동으로 관리하느냐 수동으로 관리하느냐 하는 점도 알아야 했다. 

 

1. RETENTION 상태 확인

SELECT RETENTION
FROM	DBA_TABLESPACES
WHERE	TABLESPACE_NAME = 'UNDOTBS1';
-------------------------------------------------------------------------------------------
조회 결과.
GUARANTEE : UNDO 유지 시간 엄격히 적용
NOGUARANTEE : UNDO 유지 시간을 엄격하게 적용하지 않고, 테이블 스페이스 크기와 연관하여 처리

 

실제 내가 실행해본 쿼리 결과.   

 

2. Tablespace 확장 방법 확인

SELECT AUTOEXTENSIBLE
FROM	DBA_DATA_FILES
WHERE	TABLESPACE_NAME = 'UNDOTBS1';
-----------------------------------------------------------------------------------------------
조회결과. 
ON(YES) : 테이블 스페이스 공간 부족 시 자동적으로 확장 가능하게 하는 옵션. 
OFF(NO) : 테이블 스페이스 공간 부족 시 자동적인 확장을 제한하는 옵션. (수동으로 테이블 스페이스를 추가해 줘야 함)

 

실제 내가 실행해 본 쿼리 결과. 

우리 DB의 UNDO 테이블 스페이스에 대해서는 5개의 Datafile이 존재하고 있으며, 세개는 자동확장, 두개는 수동확장으로 셋팅되어 있다.  

 

Automatic Undo Retention 적용 조건 표

GUARANTEE	AUTO EXTEND	UNDO 유지 시간	의미
NO	NO	345600초(4일)와 UNDO_RETENTION 설정 값 중 큰 값	UNDO 테이블 스페이스 확장이 불가능하므로 오라클은 언두 유지 시간을 345600초와 UNDO_RETENTION 설정 값 중 큰 값으로 유지한다. UNDO 테이블 스페이스의 공간이 부족한 경우 UNDO 유지 시간 값에 상관없이 EXTEND 훔치기가 발생한다.
NO	YES	MAXQUERYREN(초) + 300초와 UNDO_RETENTION 설정 값 중 큰 값	UNDO 테이블 스페이스의 여유 공간이 부족할 경우 자동 확장이 가능하므로 SQL이 수행되는 시간을 참조하여 UNDO 유지 시간을 동적으로 변경한다. 테이브르페이스 공간 부족 등으로 UNDO 테이블 스페이스 확장이 불가능할 경우, EXTEND 훔치기가 발생한다.
YES	NO	345600초(4일)와 UNDO_RETENTION 설정 값 중 큰 값	UNDO 테이블 스페이스 확장이 불가능하므로 오라클은 언두 유지 시간을 345600초와 UNDO_RETENTION 설정 값 중 큰 값으로 유지한다. UNDO 테이블 스페이스에 여유공간이 없을 경우 ORA-30036 unable to extend segment by %s in undo tablespace ^% 에러를 발생시키며 트랜잭션이 실패한다.
YES	YES	MAXQUERYREN(초) + 300초와 UNDO_RETENTION 설정 값 중 큰 값	UNDO 테이블스페이스의 여유 공간이 부족할 경우 자동 확장이 가능하므로 쿼리가 수행되는 시간을 참조하여 UNDO 유지 시간을 동적으로 변경한다. UNDO 테이블 스페이스 확장이 불가능할 경우 ORA-30036 unable to extend segment by %s in undo tablespace ^% 에러를 발생시키며 트랜잭션이 실패한다.

* 첨부된 표에 있는 MAXQUERYREN 이라는 값은 10분동안 가장 오래 수행된 쿼리를 초로 나타낸 값이고, v$undostat 테이블에서 확인할 수 있다.  

 

아무튼 우리 시스템의 DB에서 정보를 확인한 결과와 이 표를 대조해보면, 우리 DB는 1, 2번의 경우임을 알 수 있다. 

금요일 퇴근시간 즈음에 발생한 문제였기 때문에 1번의 경우는,

4일이 지난 월요일쯤이 되어야 UNDO 테이블 스페이스 공간이 확보된다는 소리. 

그리고 진짜 월요일 오전이 되니 테이블 스페이스 공간이 아주 깨끗하게 확보되었다. 하하

 

2번의 경우에 해당되는 테이블스페이스는 MAXQUERYREN의 숫자를 확인해 보진 않았으나, 

대용량의 데이터 분석이었기 때문에 쿼리 실행 시간이 굉장히 오래 걸렸을 것이다. 

그 시간 + 300초 만큼 UNDO 테이블 스페이스를 차지하고 있었으니, 확보되는데 시간이 오래 걸리것지 당근..

 

정말 저 표만 금요일 저녁에 알았더라면, 

주말내내 스트레스 받지 않았을텐데, 저거를 몰라서 주말동안 내내 스트레스 받았다. 

그런데 결국 주말 지나니까 자동으로 공간이 확보되서 해결됨 ㅎㅎ

 

이번 이슈로 인해 스트레스는 만땅 받았지만 

UNDO 테이블 스페이스 공간이 언제 확보되는지 아주 제대로 공부하고 알게 되었다. 

 

 

 

참고한 블로그. 

https://jckim-dev.tistory.com/17

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

 

참고하면 좋을 블로그. 

https://sksggg123.github.io/db/undo-retention/

undo_retention 이슈 및 작업 정리

https://blog.naver.com/PostView.nhn?blogId=owner71&logNo=20154387815 

오라클 > UNDO Tablespace관리

https://bae9086.tistory.com/138

UNDO(언두) Segment