오라클 삭제된 데이터 간단하게 복구하기(UNDO)

테이블 데이터 복구 프로세스

원본 테이블은 새로운 데이터가 계속 들어올 수 있기 때문에 백업 테이블을 이용해 복구해야 된다.

데이터 복구를 위해 백업 테이블을 만드는 방법(CTAS)

CREATE TABLE SYS.TEST_221122_0709
AS
SELECT * FROM SYS.TEST
AS OF TIMESTAMP TO_TIMESTAMP('2022/11/22 07:09', 'YYYY/MM/DD HH24:MI');

TEST 테이블 만들고 10분 정도 지나고 복구 TEST 해야 잘 된다.

전체 테이블 복구

CREATE TABLE SYS.TEST 
(
  ENAME       VARCHAR2(20 BYTE), 
	MOB_NUMBER  VARCHAR2(20 BYTE)
) 
TABLESPACE SYSTEM;

INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일일','010-0000-0001');
INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일이','010-0000-0002');
INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일삼','010-0000-0003');
INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일사','010-0000-0004');
INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일오','010-0000-0005');
INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일육','010-0000-0006');
INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일칠','010-0000-0007');
INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일팔','010-0000-0008');
INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일구','010-0000-0009');
INSERT INTO SYS.TEST (ENAME,MOB_NUMBER) VALUES('김일십','010-0000-0010');
COMMIT;

SYS.TEST 테이블을 만들고 테스트 데이터를 INSERT 한 후 COMMIT 한다.

DELETE FROM SYS.TEST;
COMMIT;

SYS.TEST 테이블의 데이터를 지우고 COMMIT 한다.

-- TEST_221122_0709 테이블 5분 전 데이터 테이블 생성
CREATE TABLE SYS.TEST_221122_0709
AS 
SELECT * 
FROM SYS.TEST 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

CTAS를 이용해 5분 전의 데이터 테이블을 생성한다.

SELECT * FROM SYS.TEST_221122_0709;

SYS.TEST_221122_0709 테이블을 조회하면 테이블에 5분 전 데이터가 삽입이 된 것을 볼 수 있다.

SELECT /*insert*/ * FROM SYS.TEST_221122_0709;

위의 SQL문을 입력하면 INSERT문이 자동으로 만들어진다. 테이블 이름을 SYS.TEST_221122_0709에서 SYS.TEST로 변경 후 INSERT문을 실행시키고 COMMIT을 하면 데이터 복구가 잘 수행되었다.

CREATE TABLE SYS.TEST_221122_0709
AS 
SELECT * 
FROM SYS.TEST 
AS OF TIMESTAMP TO_TIMESTAMP('2022/11/22 07:09','YYYY/MM/DD HH24:MI');

위의 SQL문은 내가 원하는 정확한 시점으로 데이터를 복구하는 쿼리문이다.

일부 삭제된 데이터 복구

위와 동일하게 진행하면 된다.

UNDO 파라미터

undo_retention은 읽기 일관성을 위한 데이터 보유 기간을 설정하는 것이다. 디폴드 값은 900으로 15분이다. 보통 12시간 43200, 24시간 86400 적용하는 편이다.

파라미터 변경

alter system set undo_retention=86400 scope=spfile;

위의 SQL문으로 파라미터를 변경 후 DB를 내렸다 다시 올려주면 적용된다.

undo_retention을 유지하려면 꼭 UNDO 테이블 스페이스의 데이터 파일도 같이 늘려주어야 한다.

UNDO 데이터 파일 만들기

ALTER TABLESPACE UNDOTBS1 
ADD DATAFILE '/oracle/app/oracle/oradata/DB이름/undotbs02.dbf'
SIZE 1024M;

UNDO 데이터 파일 크기 변경

ALTER TABLESPACE DATAFILE '/oracle/app/oracle/oradata/DB이름/undotbs02.dbf'
RESIZE 2048M;

업계에서 보통 undo_retention은 24시간에서 UNDO 테이블 스페이스 공간은 200에서 300기가로 적용한다.

UNDO 상태 정보

SELECT 
    TABLESPACE_NAME, 
    STATUS, 
    ROUND(SUM(BYTES/1024/1024)) AS "SIZE(MB)"
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME, STATUS
ORDER BY 1,2;

UNDO 상태는 active, unexpired, expired가 있다. UNDO 공간에 문제가 발생할 때 자주 사용하는 쿼리이므로 알고 있으면 좋다.

UNDO 상태 정보(UNDO 전체 크기)

SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    BYTES/1024/1024 "SIZE(MB)"
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'UNDOTBS1'
ORDER BY 1, 2;

UNDO 사용량이 높은 SQL

SELECT 
    MAXQUERYID AS "SQL_ID", 
    SUM((ACTIVEBLKS*8)/1024) AS "ACTIVE_BLOCK", 
    MAX((UNEXPIREDBLKS*8)/1024)  
FROM V$UNDOSTAT
WHERE MAXQUERYID IS NOT NULL
GROUP BY MAXQUERYID
ORDER BY 2 DESC;

SQL TEXT 분석

SELECT 
    SQL_ID, 
    PARSING_SCHEMA_NAME, 
    SERVICE, 
    SQL_FULLTEXT
FROM V$SQL
WHERE SQL_ID = '위의 undo 사용량 높은 SQL_ID값';

시간 계산 꿀팁

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

-- +시간 계산하기
SELECT '현재시간' AS "시간구분",SYSDATE FROM DUAL
UNION ALL
SELECT '+1초', SYSDATE+(INTERVAL '1' SECOND) FROM DUAL
UNION ALL
SELECT '+1분', SYSDATE+(INTERVAL '1' MINUTE) FROM DUAL
UNION ALL
SELECT '+1시간', SYSDATE+(INTERVAL '1' HOUR) FROM DUAL
UNION ALL
SELECT '+1일', SYSDATE+(INTERVAL '1' DAY) FROM DUAL
UNION ALL
SELECT '+1개월', SYSDATE+(INTERVAL '1' MONTH) FROM DUAL;

-- -시간 계산하기
SELECT '현재시간' AS "시간구분",SYSDATE FROM DUAL
UNION ALL
SELECT '-1초', SYSDATE-(INTERVAL '1' SECOND) FROM DUAL
UNION ALL
SELECT '-1분', SYSDATE-(INTERVAL '1' MINUTE) FROM DUAL
UNION ALL
SELECT '-1시간', SYSDATE-(INTERVAL '1' HOUR) FROM DUAL
UNION ALL
SELECT '-1일', SYSDATE-(INTERVAL '1' DAY) FROM DUAL
UNION ALL
SELECT '-1개월', SYSDATE-(INTERVAL '1' MONTH) FROM DUAL;

실무에서 많이 쓰인다고 한다 잘 알아두도록 하자.

참고

https://www.youtube.com/watch?v=uGoxhq9jZeA&list=PLKaW9UT2TL3Lpqrh_lJ8PHyHpR7NgdrPP&index=7