고민의 흔적

테이블 복구는 FLASHBACK을 사용하는 게 편리하고 간단하다. FLASHBACK을 사용하려면 undo_retension 파라미터와 undo 테이블 스페이스의 충분한 공간이 필요하다.

FLASHBACK은 데이터나 테이블이 최근에 삭제되었을 때만 유용하게 사용이 가능하다. 이런 단점을 보안하는 방법이 RMAN을 이용한 복구이다.

RMAN 테이블 단위 복구

RMAN을 이용한 복구는 오래된 데이터나 테이블을 삭제했을 때 유용하게 사용한다.

1. Auxiliary DB(보조 DB) 생성 및 시점 복구
2. 테이블 Datapump Export Auxiliary DB
3. 테이블 Datapump Import STR DB

RMAN을 테이블을 복구하기 위해 Auxiliary DB를 따로 만들어야 되기 때문에 운영시스템의 CPU, 메모리, 디스크 공간을 많이 사용해서 정말 필요한 상황이 아니면 테스트 DB를 따로 만들어서 거기에 복구하고 데이터를 옮기는 게 실무에서는 더 적절하다.

RMAN 테이블 복구 제약조건

1. SYS 계정 테이블 복구 불가
2. SYSTEM 테이블 복구 불가
3. SYSAUX 테이블 복구 불가
4. 테이블 NOT NULL 제약 조건 복구 불가(REMAP)

NLS_DATE_FORMAT 변경

export NLS_DATE_FORMAT="YYYY/MM/DD(DY) HH24:MI:SS"

rman_backup.rcv 백업 파일 생성

cat << EOF > /home/oracle/script/rman_backup.rcv
run {
  allocate channel ch1 device type disk format '/RMANBKP/str_%T_full_%U';
  allocate channel ch2 device type disk format '/RMANBKP/str_%T_full_%U';
  allocate channel ch3 device type disk format '/RMANBKP/str_%T_full_%U';
  allocate channel ch4 device type disk format '/RMANBKP/str_%T_full_%U';
  crosscheck archivelog all;
  delete noprompt archivelog all completed before 'trunc(sysdate) -1';
  backup as compressed backupset database tag 'DB_FULL_BACKUP' plus archivelog tag 'ARCHIVELOG';
  backup current controlfile tag 'CONTROLFILE' format '/RMANBKP/str_%T_cont_%U';
  backup spfile tag 'SPFILE' format '/RMANBKP/str_%T_para_%U';
  crosscheck backup;
  delete noprompt backup completed before 'trunc(sysdate) -1';
  release channel ch1;
  release channel ch2;
  release channel ch3;
  release channel ch4;
}

run {
begin
for i in 1..10 loop
  EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
end loop;
end;
/
}
EOF

백업 실행

rman target /

@/home/oracle/script/rman_backup.rcv

list backup summary;

DB 테이블 복구

-- DB 시간 확인(복구 시간 확인)
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS "RECO_TIME" FROM DUAL;
-- <<DB 시간 확인(복구 시간 확인)>>을 확인하고 until time 뒤에 시간을 넣어주세요.
rman target /

-- PARALLEL 4 변경
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

-- RMAN TABLE 복구
recover table STR."TEST" until time "TO_DATE('2023/01/12 12:53:47','YYYY/MM/DD HH24:MI:SS')" auxiliary  destination  '/RMANBKP'
datapump destination '/RMANBKP' dump file 'TEST_20230112.dmp';

RMAN TABLE RENAME 복구

recover table STR."TEST" until time "TO_DATE('2023/01/12 12:53:47','YYYY/MM/DD HH24:MI:SS')" auxiliary  destination  '/RMANBKP'
remap TABLE 'STR'.'TEST':'TEST_20230112_1253'
datapump destination '/RMANBKP' dump file 'TEST_20230112.dmp';

-- PARALLEL 초기화
CONFIGURE DEVICE TYPE DISK CLEAR;

PITR 관련 로그 파일 삭제

## Linux oracle 계정 실행
find /RMANBKP \\( -name "*PITR_STR*" -o -name "STR" \\) | xargs rm -rf
find $ORACLE_BASE/admin -name "*pitr_STR" | xargs rm -rf
find $ORACLE_BASE/diag/rdbms -name "*pitr_str" | xargs rm -rf
find $ORACLE_HOME/dbs \\( -name "hc*dat" -o -name "spfile*ora" -o -name "*PITR_STR" \\) -not \\( -name "hc_STR.dat" -o -name "spfileSTR.ora" \\) | xargs rm -f

## 삭제 확인
find $ORACLE_BASE \\( -name "*pitr_str*" -o -name "*pitr_STR*" -o -name "*PITR_STR" \\)

crontab 자동 백업 삭제

crontab -r

참조

https://www.youtube.com/watch?v=5f8H6c1u3-w&t=603s 

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

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

데이터 복구를 위해 백업 테이블을 만드는 방법(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