경험은 언제나 옳다

Oracle UNDOTBS 공간 정리 및 공간 회수 방법

먼저, Recovery Time Objective (RTO) DBA라면 목표 복구시간을 산정을 할텐데, 해당 부분을 통해 RTO를 줄일 수 있는 방안을 참고하시면 좋을 것 같습니다.

공간을 정리해야되는 이유는, UNDOTBS는 초기 설치 시 최대 32GB까지 확장 가능한 Autoextend 모드로 구축이 됩니다.

대용량 작업, 배치 등등 여러 이슈로 인해 급격하게 늘어날 경우에는 데이터베이스 백업 용량도 같이 늘어나는 문제가 있습니다. 백업 용량이 늘어나면 데이터베이스 복구하는데 시간도 늘어나는 문제도 있습니다.

그래서 공간 회수와 정리가 필수적으로 필요합니다.

UNDOTBS 현재 데이터파일 확인

SELECT TABLESPACE_NAME, FILE_NAME, ROUND(BYTES/1073741824,4), MAXBYTES, AUTOEXTENSIBLE
FROM dba_data_files
WHERE tablespace_name like 'UNDO%';

/* 조회결과 */
TABLESPACE_NAME FILE_NAME ROUND(BYTES/1073741824,4) MAXBYTES AUTOEXTENSIBLE
UNDOTBS1 /vol1/oradata/system/undotbs01.dbf 9.7656 0 NO
UNDOTBS1 /vol1/oradata/system/undotbs02.dbf 9.7656 0 NO
UNDOTBS1 /vol1/oradata/system/undotbs03.dbf 9.7656 0 NO
UNDOTBS1 /vol1/oradata/system/undotbs04.dbf 9.7656 0 NO
UNDOTBS1 /vol1/oradata/system/undotbs05.dbf 9.7656 0 NO

위에 UNDOTBS1 언두 테이블스페이스는 총 50GB로 확인이 됩니다.

UNDO 상태값 확인

SELECT TABLESPACE_NAME
, STATUS
, ROUND(sum(BYTES)/1073741824,4) AS "BYTES(GB)"
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME, STATUS;

/* 실제 값 확인 결과 */
TABLESPACE_NAME STATUS BYTES(GB)
UNDOTBS1 EXPIRED 1.9083
UNDOTBS1 UNEXPIRED 1.9477
UNDOTBS1 ACTIVE 0.0166

EXPIRED(사용만료), UNEXPIRED(Undo Retention 기간 보존으로 인해), ACTIVE(사용중)로 확인됩니다.

※ UNEXPIRED에 대한 자세한 내용은 참조 확인 바랍니다.

테이블스페이스 단편화 확인

SELECT TABLESPACE_NAME, COUNT(*) AS FRAGMENTS,
SUM(BYTES) AS TOTAL,
MAX(BYTES) AS LARGEST
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME LIKE 'UNDO%'
GROUP BY TABLESPACE_NAME
HAVING COUNT(*) > 200
;

UNDOTBS 단편화 확인, 200 이상이라면 단편화가 진행되었다 판단하면 됩니다. 그 이외 테이블스페이스도 확인 가능합니다.

신규 UNDOTBS 생성

-- 재생성 할 UNDOTBS2 생성
-- UNDOTBS2로 생성
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/vol1/oradata/system/undotbs2_001.dbf' SIZE 8G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;
ALTER TABLESPACE UNDOTBS2 ADD DATAFILE '/vol1/oradata/system/undotbs2_002.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;
ALTER TABLESPACE UNDOTBS2 ADD DATAFILE '/vol1/oradata/system/undotbs2_003.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

PARAMETER 조회

-- undo_tablespace parameter values 확인
SELECT NAME
, VALUE
, ISSES_MODIFIABLE
, ISSYS_MODIFIABLE
FROM v$parameter
WHERE name ='undo_tablespace';


NAME VALUE ISSES_MODIFIABLE ISSYS_MODIFIABLE
undo_tablespace UNDOTBS1 FALSE IMMEDIATE

ISSYS_MODIFIABLE이 IMMEDIATE라면, 즉시 변경 가능합니다.

PARAMETER 변경

--즉시 적용
--scope 옵션 제거 해도 default 값이기 때문에 생략가능
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' scope=both;​

UNDO 사용량 체크

--조회 결과 ONLINE 없으면 테이블 스페이스 삭제 가능
SELECT * FROM DBA_ROLLBACK_SEGS
WHERE 1=1
AND STATUS ='ONLINE'
AND TABLESPACE_NAME='UNDOTBS1';

ONLINE 조회 시 결과값 출력되지 않아야만 합니다.

기존 UNDOTBS1 삭제

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

INCLUDING CONTENTS 옵션 : 테이블, 인덱스 오브젝트들까지 같이 삭제하는 명령어, 해당 명령어 생략 시 정상 삭제처리되지 않습니다. OFFLINE 처리되어있는 UNDO Segment가 존재하기 때문에...
DATAFILES : OS에 있는 데이터파일까지 같이 삭제 처리되는 명령어, 명령어 생략 시 데이터베이스에는 삭제처리되었지만, OS에 데이터파일은 존재하게 되어 공간회수 되지 않습니다. 

참조 : Doc ID 1951694.1

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-undo.html