Oracle DB export, import

Oracle 10g 부터는 데이터의 백업과 복원을 위해서 기존에 사용되는 exp/imp툴을 대신할 Oracle Data Pump(expdp/impdp) 유틸리티가 제공되어 집니다. exp/imp 툴이 없어진것은 아닙니다. Oracle 11g 부터는 기존의 Original  exp/imp툴에 대한  일반적인 사용지원이 되지 않는다고 합니다.

 

Oracle Data Pump 유틸리티는 더욱 유연하고, 빠른 속도의 기존의 exp/imp를 대체할 유틸리티 라고 합니다. 기존의 exp/imp와의 차이점을 간단히 알아보겠습니다.

 

1. exp/imp는 덤프 파일을 유틸리티를 실행하는 컴퓨터에 생성합니다. expdp/impdp 는 덤프 파일이 데이터베이스 서버의 지정된 폴더에 생성됩니다. 즉, 덤프 파일을 받고 올리기 위해서 서버에 로그인해야만 합니다.

 

2. exp/imp는 덤프파일이 생성된 서버의 버전이 다를 경우 호환이 되지 않습니다. 메이저 버전 뿐만 아니라, 마이너 버전 사이에도 호환이 거의 안되는것 같습니다. expdp/impdp 는 실행시 버전을 지정하여 그에 맞는 덤프 파일을 생성할 수 있습니다.

---

요약

1. expdp, impdp 사용한다.

2. 출발지 DB에서 DATA_PUMP_DIR 경로를 확인한다.

SELECT * FROM ALL_DIRECTORIES;

3. expdp full 방식으로 export 한다. (cmd 창에서)

expdp [db계정]/[db계정PW] full=Y directory=DATA_PUMP_DIR dumpfile=expdp_20211101.dmp logfile=expdp_20211101.log

4. DB dump 파일(.dmp)을 복사하여 목적지 DB가 있는 PC 또는 서버에 복사한다.

5. import하기 전, 출발지 DB에서 tablespace 구성을 확인한다.

 1> 목적지 db에서 sqlplus 또는 toad, orange for oracle에 system 계정으로 접속하여 tablespace 경로를 확인한다.

SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "테이블스페이스"
    ,SUBSTRB(FILE_NAME, 1, 50) AS "파일명"
    ,TO_CHAR(BLOCKS, '999,999,990') AS "블럭수"
    ,TO_CHAR(BYTES, '99,999,999') AS "크기"
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME, FILE_NAME;

 2> tablespace 편집을 누르면 옵션을 확인할 수 있다.  

              

6. 목적지 DB의 tablespace와 비교하여 없는 tablespace를 목적지 DB에서 다 생성할 준비를 한다.

  - 목적지에서 DATAFILE의 경로는 자유롭게 확인하여 설정한다. (위에 5-1 쿼리로 확인 가능)

CREATE TABLESPACE TS_TEMP
     DATAFILE 'D:\app\82103\oradata\orcl\TS_TEMP.DEF'
     SIZE 10M
     AUTOEXTEND ON NEXT 1M
     MAXSIZE UNLIMITED
     LOGGING
     ONLINE 
     EXTENT MANAGEMENT LOCAL AUTOALLOCATE
     BLOCKSIZE 8K
     SEGMENT SPACE MANAGEMENT MANUAL
     FLASHBACK OFF;

7. 출발지 DB에서 사용했던 DB계정과 동일하게 목적지 DB에서 생성하고, 권한을 부여한다. (PW는 달라도 됨)

8. 목적지 DB에서 impdp 실행한다. (cmd 창에서)

impdp [db계정]/[db계정 PW] full=Y directory=DATA_PUMP_DIR dumpfile=EXPDP_20211101.dmp logfile=expdp_20211101.log

---

결론

윈도우 서버에 있는 DB data를 노트북 로컬 DB로 옮기는 작업을 해봤다.

export까지는 문제없었으나, import 시 tablespace를 미리 생성안하면 오류가 나고 진행이 안되서 많이 당황했었다.

위에 요약에서 5, 6번 과정이 핵심인 것 같다.

 

아래는 방식별 상세 내용이다.

요약을 중심으로 확인하면 될 것 같다.

 

---

exp, imp 방식

자신이 사용중인 오라클DB의 데이터를 다른곳 (다른PC)로 옮기고 싶을때 데이터베이스를 덤프/임포트 하는 방법

가장먼저 DB덤프에서 임포트하는 단위는 3가지가 있습니다.

첫째 전체단위, 둘째 사용자 단위, 세번째 테이블 단위입니다.

 

1. 전체다위

덤프(익스포트) |

C:\) exp userid=system/비밀번호 file='C:\파일명.dmp' full=y

임포트

C:\) imp userid=system/비밀번호 file='C:\파일명.dmp' full=y

인데 간혹 버퍼 사이즈가 맞지 않아 에러가 뜨는경우가 있다.

해결방법 : 제가 한 방법으로는 계정을 삭제하고 다시 만들어 

C:\) exp userid=system/비밀번호 file='C:\파일명.dmp' full=y buffer = 100000000

이런식으로 하여 에러를 수정하였습니다.

 

2. 사용자 단위(특정 계정/사용자의 DB )

덤프(익스포트) |

C:\) exp userid=계정명/비밀번호 file='C:\파일명.dmp' 

임포트 |

C:\) imp userid=계정명/비밀번호 file='C:\파일명.dmp' 

인데 간혹 버퍼 사이즈가 맞지 않아 에러가 뜨는경우가 있다.

해결방법 : 제가 한 방법으로는 계정을 삭제하고 다시 만들어 

C:\) exp userid=system/비밀번호 file='C:\파일명.dmp' full=y buffer = 100000000

이런식으로 하여 에러를 수정하였습니다.

 

3. 테이블 단위(특정 계정의 특정 테이블만을 추출)

덤프(익스포트) |

C:\) exp userid=계정명/비밀번호 file='C:\파일명.dmp' tables=테이블명

임포트 |

C:\) imp userid=계정명/비밀번호 file='C:\파일명.dmp' 

인데 간혹 버퍼 사이즈가 맞지 않아 에러가 뜨는경우가 있다.

해결방법 : 제가 한 방법으로는 계정을 삭제하고 다시 만들어 

C:\) exp userid=system/비밀번호 file='C:\파일명.dmp' full=y buffer = 100000000

이런식으로 하여 에러를 수정하였습니다.

---

Oracle Data Pump(expdp/impdp) 방식

디렉토리 생성 및 권한 부여

1. 덤프 파일 생성 디렉토리 생성 (/data/dump 사용)

2. 1에서 생성한 디렉토리 oracle에 등록

 

CREATE OR REPLACE DIRECTORY TEST_DUMP AS '/data/dump';

 

3. 일반유저에게 디렉토리 읽기, 쓰기 권한 부여

GRANT READ, WRITE ON DIRECTORY TEST_DUMP TO [아이디];

• create session
• create table
• create directory
• read, write on directory

4. 권한 확인

 - 디렉토리 확인

SELECT * FROM ALL_DIRECTORIES;

 - 권한 확인

SELECT * FROM ALL_TAB_PRIVS WHERE TABLE_NAME IN (

SELECT DIRECTORY_NAME FROM ALL_DIRECTORIES

);

 - Oracle 11g r2 이상시 권한 확인

SELECT * FROM DATAPUMP_DIR_OBJS;

데이터 export / import

테이블 export / import (특정 테이블을 export / import 한다.)

$ expdp xeuser/xeuser@xe tables=EMP,DEPT directory=TEST_DUMP dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

$ impdp xeuser/xeuser@xe tables=EMP,DEPT directory=TEST_DUMP dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

• xeuser/xeuser@xe - 아이디/비밀번호$SID
• tables=EMP,DEPT - 테이블을 콤마(,)로 분리해서 나열
• directory=TEST_DUMP - 덤프파일이 생성될 디렉토리
• dumpfile=EMP_DEPT.dmp - 생성될 덤프 파일명
• logfile=expdpEMP_DEPT.log - 로그 파일명으로 덤프파일과 같은 위치에 만들어짐

스키마 export / import

$ expdp xeuser/xeuser@xe schemas=XEUSER directory=TEST_DUMP dumpfile=XEUSER.dmp logfile=expdpXEUSER.log

$ impdp xeuser/xeuser@xe schemas=XEUSER directory=TEST_DUMP dumpfile=XEUSER.dmp logfile=impdpXEUSER.log

• schemas=XEUSER - 주어진 사용자(schema)가 가진 전체 객체를 export / import 함

데이터베이스 전체 export / import

$ expdp system/password@xe full=Y directory=TEST_DUMP dumpfile=XE.dmp logfile=expdpXE.log

$ impdp system/password@xe full=Y directory=TEST_DUMP dumpfile=XE.dmp logfile=impdpXE.log

• 전체 작업은 권한이 있는 system 계정으로 진행
• full=Y - 전체 데이터베이스를 대상으로 함

데이터베이스 Link export / import

데이터베이스 Link를 통해서 export / import 하기 위해서는 로컬과 리모트 사용자 모두에게 EXP_FULL_DATABASE / IMP_FULL_DATEBASE 룰이 부여되어 있어야 한다.

다음 예는 로컬계정은 xeuser 이고, 리모트 서버가 REMOTE_SCOTT라는 이름으로 링크 되어 있다고 가정한다. 리모트 계정은 scott 이다.

$ expdp xeuser/xeuser@xe tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DUMP dumpfile=EMP.dmp logfile=expdpEMP.log

$ impdp xeuser/xeuser@xe tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=TEST_DUMP logfile=impdpSCOTT.log remap_schema=SCOTT:XEUSER

• remap_schema-SCOTT:XEUSER - SCOTT계정의 객체들을 XEUSER 계정으로 로드

버전을 지정하여 export (버전은 9.2 이상만 지정 가능)

$ expdp xeuser/xeuser@xe schemas=XEUSER version=10.2 directory=TEST_DUMP dumpfile=XEUSER.dmp logfile=expdpXEUSER.log

그외 자세한 옵션에 대해서는 https://oracle-base.com/articles/10g/oracle-data-pump-10g 참고