테이블 스페이스 용량 관리는 DBA 의 업무중 하나로,
지속적으로 모니터링 하고 용량을 늘려주는 작업을 해야한다.
DBA 의 운영 업무 중 가장 큰 스트레스 중 하나는 테이블 스페이스의 용량이 부족하여 DB에 심각한 장애가 발생하는 경우이다.
특히, 개발 단계에서 대량 배치 작업이나 데이터 이관 작업을 심야 시간에 진행 시 dba가 항상 상주하여 모니터링 할 수 없는 경우가 발생한다. 이러한 상황에 대비하여 DBA 가 부재한 상황에서 자동으로 테이블 스페이스를 모니터링하고 테이블스페이스의 데이터파일 평균 사용률이 임계치에 달하면 테이블 스페이스를 증설하는 프로시저를 소개하고자 한다.
1. 프로시저 개요
- 기능:
- 테이블스페이스의 데이터파일 평균 사용률이 임계치를 초과한 테이블스페이스에 데이터파일 자동 추가
- 주요 사용 매개변수:
- p_threshold_percent: 테이블스페이스의 데이터파일 평균 사용률 임계치 (%) 설정
- p_datafile_add_size: 추가 데이터파일 크기 지정 (예: 1M, 1G 등)
- 프로시저 흐름도
자동 증설 프로시저 플로우차트
2. 단계별 프로세스 설명
1. SCHEDULE JOB 설정
- 수행 작업:
- 5분 주기로 테이블스페이스의 데이터파일 평균 사용률 임계치 80% 추가할 데이터파일의 크기가 1M 인 테이블스페이스 자동 증가 프로시저 실행
BEGIN
DBMS_SCHEDULER.CREATE_JOB
( JOB_NAME => 'DBADM.JOB_TS_DATAFILE_AUTO_ADD'
, START_DATE => SYSDATE
, REPEAT_INTERVAL => 'FREQ=MINUTELY; INTERVAL=5' -- 수행주기
, JOB_TYPE => 'PLSQL_BLOCK'
, JOB_ACTION => 'BEGIN SP_TS_DATAFILE_AUTO_ADD(80,''1M''); END;'
);
DBMS_SCHEDULER.ENABLE
(NAME => 'DBADM.JOB_TS_DATAFILE_AUTO_ADD');
END;
/현재 진행중인 프로젝트의 개발/테스트 환경에서는 5분 단위로 테이블 스페이스를 모니터링하여 임계치가 80%, 데이터파일 사이즈를 1M 단위로 추가하고 있다.
2. AUTOEXTEND 속성 설정
- 조건:
- 데이터파일이 AUTOEXTEND OFF로 설정되어 있거나, AUTOEXTEND ON이지만 MAXSIZE가 제한된 경우.
- 데이터파일이 AUTOEXTEND OFF로 설정되어 있거나, AUTOEXTEND ON이지만 MAXSIZE가 제한된 경우.
- 수행 작업:
- AUTOEXTEND를 활성화하고 MAXSIZE를 UNLIMITED로 설정.
ALTER DATABASE DATAFILE 'file_name' AUTOEXTEND ON MAXSIZE UNLIMITED;- 실행 결과:
- 데이터파일이 자동 확장이 가능하도록 설정되어, 공간 부족 문제를 예방.
3. 테이블스페이스 사용률 모니터링
- 사용률 계산 로직:
- AUTOEXTEND = YES: (사용된 용량 / 최대 크기) * 100
- AUTOEXTEND = NO: (사용된 용량 / 현재 크기) * 100
- 조건:
- 입력받은 임계치(p_threshold_percent)를 기준으로 테이블스페이스의 데이터파일 평균 사용률을 계산
- AUTOEXTENSIBLE이 활성화된 경우와 비활성화된 경우 각각 다른 방식으로 사용률을 계산
- 실행 결과:
- 임계치 초과 여부를 판단.
SELECT TABLESPACE_NAME, AVG(USAGE_PERCENT)
FROM (사용률 계산 로직)
WHERE USAGE_PERCENT > p_threshold_percent;4. 데이터파일 자동 추가
- 조건:
- 테이블스페이스의 데이터파일 평균 사용률이 임계치를 초과한 경우 지정된 크기(p_datafile_add_size)의 데이터파일을 추가
- 테이블스페이스의 데이터파일 평균 사용률이 임계치를 초과한 경우 지정된 크기(p_datafile_add_size)의 데이터파일을 추가
- 수행 작업:
- 새로운 데이터파일 추가:
- 데이터파일 경로: +DATA
- 크기: p_datafile_add_size
- AUTOEXTEND ON, MAXSIZE UNLIMITED 설정.
- 새로운 데이터파일 추가:
ALTER TABLESPACE tablespace_name
ADD DATAFILE '+DATA' SIZE p_datafile_add_size AUTOEXTEND ON MAXSIZE UNLIMITED;- 실행 결과:
- 테이블스페이스 공간이 자동 확장됨.
5. 작업 내용 기록
- DDL 실행 내역 기록
- 모든 DDL 변경 사항(ALTER DATABASE DATAFILE, ALTER TABLESPACE)을 DBADM.TB_DDL_HISTORY 테이블에 저장
6. 예외 처리 및 재확인 로직
- 예외 처리:
- NO_DATA_FOUND 예외 발생 시 프로시저 종료.
- NO_DATA_FOUND 예외 발생 시 프로시저 종료.
- 재확인 로직:
- 테이블스페이스의 데이터파일 평균 사용률 재확인 후 필요 시 데이터파일 추가를 재실행.
3. 예상 활용 시나리오
아래 시나리오를 통해 부족한 공간이 DBA의 개입 없이 자동으로 확장되는지 확인
CASE 1 : AUTOEXTEND OFF, TABLESPACE SIZE 10M 인 테이블 스페이스에 10M 이상인 테이블 생성
생성된 테이블스페이스와 데이터 파일 확인
ALERT.LOG 확인 – AUTOEXTEND ON MAXSIZE UNLIMITED 로 ALTERE 됨.
CASE 2 : 테이블스페이스의 데이터파일 평균 사용률이 임계치(80%) 를 초과한 테이블스페이스 데이터 파일 자동 추가
AUTOEXTEND 허용된 TABLESPACE에 28G 테이블 생성 (임계치 80% 이상)
AUTOEXTEND 활성화된 상태로 자동 확장되는 것을 볼 수 있다.
테이블 생성 완료 후 테이블스페이스의 데이터파일 평균 사용률 조회. 83% 사용중으로 확인
ALERT.LOG 조회 – TABLESPACE 에 자동으로 데이터파일이 추가됨.
테이블 스페이스의 데이터 파일 조회 시 새롭게 생성된 데이터파일을 확인할 수 있음.
로그 테이블 조회로 수행된 작업 다시 확인.
4. 결론 및 개선 방향
- 결론
- 테이블스페이스 자동관리 프로세스는 DBA 작업을 보조해주어 업무를 자동화하고 장애를 예방하는데 도움이 되지만 작업에 따라서 (전환, 배치) 임계치를 낮추고, 데이터 파일 사이즈를 크기(30G) 키우는 등 DBA 판단하에 변수를 수정하고 많은 테스트 후 사용해야한다. 테이블스페이스를 크게 증설하는 작업의 경우 데이터 파일을 추가하는 도중 공간이 차 장애가 날 수 있기 때문에 임계치를 더 낮게 잡는것을 권장한다.
- 테이블스페이스 자동관리 프로세스는 DBA 작업을 보조해주어 업무를 자동화하고 장애를 예방하는데 도움이 되지만 작업에 따라서 (전환, 배치) 임계치를 낮추고, 데이터 파일 사이즈를 크기(30G) 키우는 등 DBA 판단하에 변수를 수정하고 많은 테스트 후 사용해야한다. 테이블스페이스를 크게 증설하는 작업의 경우 데이터 파일을 추가하는 도중 공간이 차 장애가 날 수 있기 때문에 임계치를 더 낮게 잡는것을 권장한다.
- 오류 처리 강화 및 병렬 처리
- 데이터파일 추가 혹은 변경 실패 시, 보다 상세한 오류 로그 필요
- 대규모 테이블스페이스 및 데이터파일 환경에서 성능 향상을 위해 병렬 처리 고려해볼 필요가 있음
- 병렬 작업으로 대량 데이터 이관 시 스케줄링 된 시간보다 공간의 확장이 더 크다면 에러가 발생할 수 있음.작업 전 예상 용량을 산정해 미리 늘려 두는 것이 좋다.
※주의※ 운영 환경에서 사용하기 위해서는 위험요소가 존재할 수 있으므로 많은 테스트 후 적용이 필요하다. (개발 및 테스트 단계에서 사용하기를 권장)
※ 작업에 따라 임계치와 추가할 데이터파일 사이즈를 조정하여 사용하는 것을 권고한다.
[참고] 프로시저, 로그테이블
CREATE OR REPLACE PROCEDURE DBADM.SP_TS_DATAFILE_AUTO_ADD (
p_threshold_percent IN NUMBER, -- 테이블스페이스 사용률 모니터링 임계치 (%)
p_datafile_add_size IN VARCHAR2 -- 추가할 데이터파일 사이즈 (예: 1M, 1G)
) AUTHID CURRENT_USER IS
v_tablespace_name dba_data_files.tablespace_name%TYPE; -- 테이블스페이스명
v_file_name dba_data_files.file_name%TYPE; -- 파일명
v_count NUMBER;
v_ddl_1 VARCHAR2(4000);
v_ddl_2 VARCHAR2(4000);
BEGIN
-- AUTOEXTEND 미적용 OR 적용되었으나 MAXSIZE가 UNLIMITED로 설정되지 않은 데이터파일 옵션 변경
FOR rec_df IN (
SELECT TABLESPACE_NAME,
FILE_NAME,
AUTOEXTENSIBLE,
MAXBYTES
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'TS$_%' ESCAPE '$'
AND (AUTOEXTENSIBLE = 'NO' OR (AUTOEXTENSIBLE = 'YES' AND MAXBYTES < 34359721984))
) LOOP
v_file_name := rec_df.file_name;
v_ddl_1 := 'ALTER DATABASE DATAFILE ''' || v_file_name || ''' AUTOEXTEND ON MAXSIZE UNLIMITED';
EXECUTE IMMEDIATE v_ddl_1;
DBMS_OUTPUT.PUT_LINE('[1. Alter Datafile] : ' || v_ddl_1);
-- 수행 로그 INSERT
INSERT INTO DBADM.TB_DDL_HISTORY
SELECT SYSDATE,
ORA_LOGIN_USER,
'ALTER',
'DATABASE DATAFILE',
ORA_DICT_OBJ_OWNER,
v_file_name,
PROGRAM,
MACHINE,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
OSUSER,
SQL_ID,
v_ddl_1
FROM V$SESSION
WHERE SID = SYS_CONTEXT('USERENV', 'SID');
COMMIT;
END LOOP;
<<label_add_datafile>>
-- 테이블스페이스 사용률 모니터링 및 임계치 초과 시 데이터파일 추가
FOR rec_ts IN (
SELECT X.TABLESPACE_NAME,
AVG(X.USAGE_PERCENT) AS USAGE_PERCENT
FROM (
SELECT DF.TABLESPACE_NAME,
CASE
WHEN DF.AUTOEXTENSIBLE = 'YES'
THEN ROUND((DF.BYTES - NVL(F.FREE_BYTES, 0)) / DF.MAXBYTES * 100, 2)
ELSE ROUND((DF.BYTES - NVL(F.FREE_BYTES, 0)) / DF.BYTES * 100, 2)
END AS USAGE_PERCENT
FROM DBA_DATA_FILES DF,
(SELECT FILE_ID, SUM(BYTES) AS FREE_BYTES
FROM DBA_FREE_SPACE
GROUP BY FILE_ID) F
WHERE DF.FILE_ID = F.FILE_ID(+)
AND DF.TABLESPACE_NAME LIKE 'TS$_%' ESCAPE '$'
) X
GROUP BY X.TABLESPACE_NAME
HAVING AVG(X.USAGE_PERCENT) > p_threshold_percent
) LOOP
v_tablespace_name := rec_ts.tablespace_name;
v_ddl_2 := 'ALTER TABLESPACE ' || v_tablespace_name ||
' ADD DATAFILE ''+DATA'' SIZE ' || p_datafile_add_size ||
' AUTOEXTEND ON MAXSIZE UNLIMITED';
EXECUTE IMMEDIATE v_ddl_2;
DBMS_OUTPUT.PUT_LINE('[2. Add Datafile] : ' || v_ddl_2);
INSERT INTO DBADM.TB_DDL_HISTORY
SELECT SYSDATE,
ORA_LOGIN_USER,
'ALTER',
'DATABASE DATAFILE',
ORA_DICT_OBJ_OWNER,
v_file_name,
PROGRAM,
MACHINE,
SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
OSUSER,
SQL_ID,
v_ddl_2
FROM V$SESSION
WHERE SID = SYS_CONTEXT('USERENV', 'SID');
COMMIT;
END LOOP;
-- 임계치를 초과하는 테이블 스페이스가 있는지 확인 후 있으면 라벨 <<label_add_datafile>> 수행
BEGIN
SELECT COUNT(*) INTO v_count
FROM (
SELECT DF.TABLESPACE_NAME,
CASE
WHEN DF.AUTOEXTENSIBLE = 'YES'
THEN ROUND((DF.BYTES - NVL(F.FREE_BYTES, 0)) / DF.MAXBYTES * 100, 2)
ELSE ROUND((DF.BYTES - NVL(F.FREE_BYTES, 0)) / DF.BYTES * 100, 2)
END AS USAGE_PERCENT
FROM DBA_DATA_FILES DF,
(SELECT FILE_ID, SUM(BYTES) AS FREE_BYTES
FROM DBA_FREE_SPACE
GROUP BY FILE_ID) F
WHERE DF.FILE_ID = F.FILE_ID(+)
AND DF.TABLESPACE_NAME LIKE 'TS$_%' ESCAPE '$'
) X
GROUP BY X.TABLESPACE_NAME
HAVING AVG(X.USAGE_PERCENT) > p_threshold_percent;
IF v_count >= 1 THEN
GOTO label_add_datafile;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;
END;CREATE TABLE DBADM.TB_DDL_HISTORY
(
EXEC_DTM DATE, -- 실행시간
USERNAME VARCHAR2(128 BYTE), -- 접속계정
ACTION_EVENT VARCHAR2(100 BYTE), -- 수행한작업 EX) CREATE, DROP
OBJECT_TYPE VARCHAR2(32 BYTE),
OBJECT_OWNER VARCHAR2(128 BYTE),
PROGRAM VARCHAR2(48 BYTE),
MACHINE VARCHAR2(64 BYTE), -- 접속기기
IP_ADDRESS VARCHAR2(45 BYTE), -- 접속 IP
OSUSER VARCHAR2(30 BYTE), -- OS유저명
SQL_ID VARCHAR2(13 BYTE),
SQL_TEXT VARCHAR2(4000 BYTE), -- 수행한 쿼리
)
TABLESPACE TS_DBADM_DN01;
CREATE INDEX DBADM.IX_DDL_HISTORY_01 ON DBADM.TB_DDL_HISTORY(OBJECT_NAME);
CREATE INDEX DBADM.IX_DDL_HISTORY_02 ON DBADM.TB_DDL_HISTORY(OBJECT)OWNER);
CREATE INDEX DBADM.IX_DDL_HISTORY_03 ON DBADM.TB_DDL_HISTORY("EXEC_DTM" DESC);