Oracle 복구를 빠르게 하는 방법(IMPDP)

오라클 서버에서 .sql 파일 실행법

@파일이름.sql

전체 복구(기본)

ls -lh EXPDP_STR_FULL_20221207*

impdp system/oracle DIRECTORY=STR_DUMP FULL=YES \
DUMPFILE=EXPDP_STR_FULL_20221207.DMP \
LOGFILE=IMPDP_STR_FULL_20221207.log

전체 복구(기본+TDE)

ls -lh EXPDP_STR_FULL_TDE_20221207*

impdp system/oracle DIRECTORY=STR_DUMP FULL=YES \
ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_TDE_20221207.DMP \
LOGFILE=IMPDP_STR_FULL_TDE_20221207.log

전체 복구(압축+TDE)

ls -lh EXPDP_STR_FULL_COMP_20221207*

impdp system/oracle DIRECTORY=STR_DUMP FULL=YES \
ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_COMP_20221207.DMP \
LOGFILE=IMPDP_STR_FULL_COMP_20221207.log

전체 복구(백업 파일 100M 분할)

ls -lh EXPDP_STR_FULL_DIV_20221207

impdp system/oracle DIRECTORY=STR_DUMP FULL=YES \
ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_DIV_20221207_%U.DMP \
LOGFILE=IMPDP_STR_FULL_DIV_20221207.log

전체 복구(Parallel+압축+TDE)

ls -lh EXPDP_STR_FULL_PCOMP_20221207*

impdp system/oracle DIRECTORY=STR_DUMP \
PARALLEL=4 FULL=YES ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_FULL_PCOMP_20221207_%U.DMP \
LOGFILE=IMPDP_STR_STR_FULL_PCOMP_20221207.log

Datapump 스키마(유저) 복구 STR계정 복구(Parallel+압축)

ls -lh EXPDP_STR_STR_P_20221207*

impdp system/oracle DIRECTORY=STR_DUMP SCHEMAS=STR \
PARALLEL=4 \
ENCRYPTION_PASSWORD=oracle123 \
DUMPFILE=EXPDP_STR_STR_P_20221207_%U.dmp \
LOGFILE=IMPDP_STR_STR_P_20221207.log

Datapump 테이블 복구(계정명: STR 테이블명: CUST)

ls -lh EXPDP_STR_CUST_20221207*

impdp system/oracle DIRECTORY=STR_DUMP \
DUMPFILE= EXPDP_STR_CUST_20221207.DMP  \
LOGFILE=IMPDP_STR_CUST_20221207.log \
TABLES=STR.CUST

Datapump 테이블 복구

-- 테이블스페이스 위치 변경: TS_STR_D, TS_STR_I  -->  SYSTEM

ls -lh EXPDP_STR_CUST_20221207*

impdp system/oracle DIRECTORY=STR_DUMP \
DUMPFILE= EXPDP_STR_CUST_20221207.DMP  \
LOGFILE=IMPDP_STR_CUST_20221207.log \
TABLES=STR.CUST \
REMAP_TABLESPACE=TS_STR_D:SYSTEM \
REMAP_TABLESPACE=TS_STR_I:SYSTEM

복구 진행 중에 계속 멈춰 있다면 테이블 스페이스 공간을 확인하자

SELECT 
    OWNER, 
    SEGMENT_TYPE, 
    SEGMENT_NAME,
    TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE  SEGMENT_NAME IN ('CUST','CUST_IDX1');

ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/STR/system01.dbf' RESIZE 2048M;

Datapump 테이블 복구(테이블명 변경: CUST --> CUST_BACKUP_20221003)

ls -lh EXPDP_STR_CUST_20221207*

impdp system/oracle DIRECTORY=STR_DUMP \
DUMPFILE= EXPDP_STR_CUST_20221207.DMP  \
LOGFILE=IMPDP_STR_CUST_20221207.log \
TABLES=STR.CUST \
REMAP_TABLE=CUST:CUST_BACKUP_20221207

참고

https://www.youtube.com/watch?v=fEVnBQJNuvQ