09 Datapump
1) Datapump 의 장점
(1) 작업 관리의 편의성
Datapump 이전의 exp/imp 방법은 작업이 일단 시작되면 끝날 때까지 제어를 할 수 없었습니다.
예를 들어 어떤 필요성에 의해 일시 중단 시켰다가 다시 시작한다든지 하는 작업을 할 수 없었지만
datapump 는 job 의 제어가 가능해 훨씬 편리해 졌습니다.
(2) 필요한 디스크 공간의 예측
이전 버전의 exp/imp 작업은 작업하다가 디스크 공간의 부족으로 몇 시간 동안 했던 작업을 취소하고
디스크를 추가 후 (또는 파일 삭제 등으로 공간 확보 후) 다시 작업을 수행하는 경우가 종종 있었으나
datapump 는 ESTIMATE 파라미터를 사용하여 해당 작업 시 필요한 디스크 공간을 미리 알 수 있습니다.
(3) 원격지 DB 에 작업 수행 가능
DB Link 라는 기능을 통해 원격지에 있는 데이터베이스에 expdp / impdp 작업을 수행할 수 있습니다.
(4) remapping 기능 지원
스키마 변경이나 테이블 스페이스 변경, 데이터 파일 변경까지 가능합니다.
(5) dump 작업하면서 압축을 동시에 진행
용량이 큰 데이터의 경우 압축을 동시에 진행하므로 dump file 용량을 획기적으로 줄일 수 있습니다.
(6) 아주 빨라진 작업 속도
서버의 환경에 따라 다르지만 일반적으로 이전 버전의 exp/imp 와 비교해서 평균적으로 약 20 배 이상의 성능 향상을 가져와서
대량의 작업도 아주 빨리 수행 할 수 있게 되었습니다.
2) 사용 전 환경 설정하기
Datapump는 export / import 와 다르게 유틸리티가 직접 OS 파일에 I/O를 할 수 없고
오라클에 directory 라는 객체를 통해서 간접으로 접근이 가능합니다.
그래서 datapump를 사용하려면 미리 directory 가 만들어져 있어야 하며 datapump를 수행하는
사용자는 그 directory에 접근할 수 있는 권한이 있어야 합니다.
이 기능을 통해 DBA는 datapump 의 보안관리까지 가능하게 되었습니다.
Datapump를 사용하기 위해서 디렉토리를 생성하고 권한 설정을 하도록 하겠습니다.
$ mkdir /data/dp
$ sqlplus / as sysdba
SQL> create or replace directory datapump as '/data/dp';
SQL> grant read , write on directory datapump to scott;
SQL> grant create any directory to scott;
Datapump는 기존 export 와 구분하기 위해 expdp 로 , import 와 구분하기 위해 impdp 라는 명령을 사용합니다.
3) expdp 실행 모드
(1)Full 모드
Full 이란 파라미터를 사용하며 데이터베이스 전체를 export 받을 수 있습니다.
DBA 권한을 가지고 있거나 export_full_database 권한을 가지고 있어야 수행할 수 있습니다.
(2) schema 모드
Schemas 파라미터를 사용하여 특정 스키마의 전체를 export 받을 수 있습니다.
export 에서의 owner 옵션과 동일합니다.
(3) Tablespace 모드
Tablespaces 파라미터를 사용하며 해당 테이블스페이스에 속한 모든 테이블을 받을 수 있습니다.
만약 transport_tablespace 파라미터를 사용한다면 테이블과 테이블스페이스의 메타 데이터까지
export 받게 되어 다른 서버로 테이블스페이스 전체를 이동시킬 때 아주 유용하게 사용할 수 있
습니다. 단 transport_tablespace 를 사용하려면 양쪽 데이터베이스의 OS가 같아야 하고 block
size 와 characterset 도 같아야 한다는 단점이 있습니다.
(4) table 모드
Tables 파라미터를 사용하며 여러 개의 테이블을 export 받으려면 ,(콤마)로 구 분해주면 됩니다.
*실습 14. scott 계정의 emp,dept 테이블만 백업 받기
$ expdp scott/tiger tables=emp,dept directory=datapump job_name=t1 \
> dumpfile=emp_dept.dmp
* 실습 15. scott schema 전부 백업 받기
$ expdp scott/tiger schemas=scott directory=datapump \
> dumpfile=sco tt01.dmp
*실습 16. DB 전체를 백업 받기
$ expdp system/oracle full=y directory=datapump dumpfile=full01.dmp \
> job_name=a
*실습 17. 일시 중단 후 다시 작업하기
Expdp를 수행하다 보면 서버에 부하를 많이 줘서 일시 중단했다가 나중에 이어서 다시 작업 할 때가 있습니다.
그런 상황일 때 어떻게 하는지 살펴보겠습니다.
DB 전체를 백업 받다가 중간에 Ctrl+c를 눌러 취소하겠습니다.
$ expdp system/oracle full=y directory=datapump \
> dumpfile=full02.dmp job_name=a
<-Ctrl + C 를 눌러서 취소했습니다.
Export> stop_job <- 일시 중단 시킴
$ sqlplus sys/oracle as sysdba;
SQL> col owner_name for a10
SQL> col job_name for a10
SQL> col operation for a10
SQL> col job_mode for a10
SQL> SELECT owner_name , job_name,operation,job_mode,state
2 FROM dba_datapump_jobs;
$ expdp system/oracle attach=system.a <-일시 중단된 작업에 재 접속
Export> start_job
Export> exit
[oracle@localhost ~]$ sqlplus / as sysdba;
SQL> col owner_name for a10
SQL> col job_name for a10
SQL> col operation for a10
SQL> col job_mode for a10
SQL> SELECT owner_name , job_name,operation,job_mode,state
2 FROM dba_datapump_jobs ;
Export > 상태에서 만약 해당 작업을 완전히 취소하고 싶으면 kill_job 입력하시면 됩니다.
* 실습 18. 비정상적으로 종료된 job 취소하기
[oracle@localhost ~]$ mkdir /data/dp_a
[oracle@localhost ~]$ mkdir /data/dp_b
[oracle@localhost ~]$ mkdir /data/dp_c
[oracle@localhost ~]$ mkdir /data/dp_d
$ sqlplus sys/oracle as sysdba
SQL> startup
$ expdp system/oracle full=y directory=dp_a dumpfile=full01.dmp \
> job_name=dp_a
SQL> create directory dp_a as '/data/dp_a';
SQL> create directory dp_b as '/data/dp_b';
SQL> create directory dp_c as '/data/dp_c';
SQL> create directory dp_d as '/data/dp_d';
SQL> grant read,write on directory dp_a to scott;
SQL> grant read,write on directory dp_b to scott;
SQL> grant read,write on directory dp_c to scott;
SQL> grant read,write on directory dp_d to scott;
Step 2. Datapump 수행 중 비정상 종료
SQL> exit
$ expdp system/oracle full=y directory=dp_a dumpfile=full01.dmp \
> job_name=dp_a
Ctrl+C 눌러서 작업 정지 시킴.
Export> stop
Are you sure you wish to stop this job ([yes]/no): yes
$ expdp system/oracle full=y directory=dp_b dumpfile=full02.dmp \
> job_name=dp_b
Export> stop
Are you sure you wish to stop this job ([yes]/no): yes
$ expdp system/oracle full=y directory=dp_c dumpfile=full03.dmp \
> job_name=dp_c
Export> stop
Are you sure you wish to stop this job ([yes]/no): yes
$ sqlplus sys/oracle as sysdba;
SQL> col owner_name for a10
SQL> col job_name for a10
SQL> col operation for a10
SQL> col job_mode for a10
SQL> SELECT owner_name , job_name, operation, job_mode, state
2 FROM dba_datapump_jobs ;
Step 3. 파일을 수동으로 삭제하는 장애 발생
SQL> !rm -fr /data/dp_a/*
SQL> !rm -fr /data/dp_b/*
SQL> !rm -fr /data/dp_c/*
SQL> exit
Step 4. 중지되었던 job 에 다시 접속 시도 – 에러 발생함
$ expdp system/oracle attach=dp_a
( 지면 관계상 중간 내용은 생략합니다 )
ORA-31640: unable to open dump file "/data/dp_a/full01.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
$ expdp system/oracle attach=dp_b
(지면 관계상 중간 내용은 생략합니다)
ORA-39002: invalid operation
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/data/dp_b/full02.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
$ expdp system/oracle attach=dp_c
(지면 관계상 중간 내용은 생략합니다)
ORA-39002: invalid operation
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/data/dp_c/full03.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Step 5. 위 문제의 정보를 가진 마스터 테이블 확인 후 삭제
SYS> set line 200
SYS> col owner.object for a15
SYS> SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"
2 FROM dba_objects o, dba_datapump_jobs j
3 WHERE o.owner=j.owner_name
4 AND o.object_name=j.job_name
5 AND j.job_name NOT LIKE 'BIN$%'
6 ORDER BY 4,2;
SYS> drop table system.dp_a;
SYS> drop table system.dp_b;
SYS> drop table system.dp_c;
SYS> SELECT owner_name , job_name, operation, job_mode, state
2 FROM dba_datapump_jobs ;
* 실습 19. 여러 사용자의 테이블 한꺼번에 expdp 받기
$ expdp system/oracle directory=datapump dumpfile=scott16.dmp \
> tables=scott.emp ,hr.departments
*실습 20. 병렬로 expdp 작업 하기
$ expdp system/oracle full=y directory=datapump dumpfile=full04.dmp \
> job_name=a parallel=4
*실습 21. 병렬로 위치 분산하여 expdp 작업하기
$ expdp system/oracle full=y parallel=4 \
> dumpfile=DATADIR1:full1%U.dat, \
> DATADIR2:full2%U.dat, \
> DATADIR3:full3%U.dat, \
> DATADIR4:full4%U.dat \
> filesize=100M
*실습 22. 파라미터 파일 사용해서 expdp 수행 – 여러 개의 파일로 분할
$ vi expdp_pump.par
userid=system/oracle
directory=datapump
job_name=datapump
logfile=expdp.log
dumpfile=expdp_%U.dmp
filesize=100M
full=y
:wq!
$ expdp parfile=expdp_pump.par
* 실습 23. parameter 파일 이용해서 impdp 작업하기
$ vi impdp.par
userid=system/oracle
directory=datapump
job_name=datapump
logfile=impdp_pump.log
dumpfile=expdp_%U.dmp
full=y
table_exists_action=append
$ impdp parfile=impdp.par
* 실습 24. Impdp 병렬 작업하기
$ impdp system/oracle parallel=4 \
> dumpfile= DATADIR1:full1%U.dat, \
> DATADIR2:full2%U.dat, \
> DATADIR3:full3%U.dat, \
> DATADIR4:full4%U.dat \
> Table_exists_action=append
* 실습 25. Import 수행하지 않고 DDL 문장만 추출하기
$ impdp system/oracle directory=datapump \
> dumpfile=expdp_%U.dmp sqlfile=datapump.dat
* 실습 26. 설정된 Directory 경로 확인하기
SQL> set line 200
SQL> col owner for a10
SQL> col directory_name for a25
SQL> col directory_path for a60
SQL> select * from dba_directories ;
* 실습 27. 일자 별 schema 별로 자동 백업 받는 스크립트
이번 실습은 현업에서 많이 사용하는 것으로 마이그레이션 작업 과 같은 일을 할 때
AS-IS 서버(원본서버)에서 모든 schema 별로 datapump를 수행해서 To-Be (신규서버)로 옮기게 됩니다.
그때 AS-IS 서버에 schema 가 너무 많을 경우 일일이 수동으로 찾아서 하기는 너무 힘들기 때문에
아래에서 소개하는 스크립트를 사용하면 작업이 많이 수월해 질 것입니다.
아래 스크립트는 특정 백업 디렉토리(여기서는 /data/backup/expdp) 아래에
백업을 수행하는 년-월-일-시간 형식으로 디렉토리를 자동으로 만들고
그 아래에 Schema 별로 dmp 파일과 log 파일을 생성하게 만들어져 있습니다.
Expdp 에 많은 옵션이 있어서 기본적인 옵션만 주고 만들었지만
원하는 옵션이 있으시다면 스크립트를 조금만 수정해서 사용하시면 될 것입니다.
$ vi expdp_script.sh
export LANG=C
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11g
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=testdb
sqlplus /nolog << EOF3
conn / as sysdba
set head off
set time off
set timing off
set feedback off
set echo off
set line 200
col name for a100
spool /app/oracle/expdp.tmp
SELECT '!mkdir -p /data/backup/expdp/'||to_char(sysdate,'YYYY_MM_DD_HH24') FROM dual;
-- 아래 줄은 화면상 줄 바꿈 했지만 실제로는 ; 까지 한 줄로 이어서 쓰세요
SELECT 'create or replace directory datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||
' as '||''''||'/data/backup/expdp/'||to_char(sysdate,'YYYY_MM_DD_HH24')||''''||';' FROM dual;
-- 아래 줄은 화면상 줄 바꿈 했지만 실제로는 ; 까지 한 줄로 이어서 쓰세요
SELECT distinct 'grant read , write on directory '||' datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||' to '||
lower(owner) FROM dba_tables WHERE owner not
in('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','PM','EXFSYS','CTXSYS','OLAPSYS','MDSYS','SYSMAN','LOADER','XDB',
'ORDSYS','OUTLN','TSMSYS','DMSYS');
-- 아래 줄은 화면상 줄 바꿈 했지만 실제로는 ; 까지 한 줄로 이어서 쓰세요
SELECT distinct '!expdp system/oracle'||' schemas='||lower(owner)||
job_name='||lower(owner)||'_datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||' directory =
datapump_'||to_char(sysdate,'YYYY_MM_DD_HH24')||' dumpfile='||lower(owner)||'_%U.dmp'||' logfile='||
lower(owner)||'.log'||' filesize=100m ' FROM dba_tables WHERE owner not
in('SYS','DBSNMP','WMSYS','IX','SYSTEM','OE','PM','EXFSYS','CTXSYS','OLAPSYS','MDSYS','SYSMAN','LOADER','XDB',
'ORDSYS','OUTLN','TSMSYS','DMSYS');
spool off
!cat /app/oracle/expdp.tmp | grep -v SQL | grep -v SYS > /app/oracle/expdp.sh
@/app/oracle/expdp.sh
exit
EOF3
:wq!
$ sh expdp_script.sh
SQL> !mkdir -p /data/backup/expdp/2013_02_20_16
$ cd /data/backup/expdp
$ ls
2013_02_20_16
$ cd 2013_02_20_16/
$ ls
hr.log hr_01.dmp scott.log scott_01.dmp sh.log sh_01.dmp
6) Datapump 작업 관리 및 모니터링 하기
DBA_DATAPUMP_JOBS 딕셔너리를 조회하여 현재 작업중인 작업들의 내용을 확인할 수 있습니다.
- owner_name : 작업 계정
- job_name : 작업 명칭
- job_mode : full , table , index , tablespace 등
- state : executing (수행중) , defining , undefined ,not running 등이 있습니다.
7) Datapump 를 사용하여 Tablespace 이동하기 (Transpotable Tablespace : TTS 사용하기)
이 실습을 하기 위해 동일한 OHEL5 + 11g R2 버전을 설치하여 Source DB 의 SID 를 testdb 라고 하고
Target DB 의 SID 를 testdb2 라고 하겠습니다.
아래의 실습은 두 서버가 모두 운영 중이라고 가정하고 진행한 것입니다)
- Source DB(AS-IS) 에서 작업합니다
Step 1. 테스트용 Tablespace 와 Table 을 생성합니다.
$ sqlplus sys/oracle as sysdba;
SYS>select instance_name from v$instance;
SYS>@dd
SYS>create tablespace conv_test1
SYS>create table scott.ctest1 (no number) tablespace conv_test1;
SYS>insert into scott.ctest1 values (1);
SYS>commit;
SYS>select * from scott.ctest1 ;
SYS>exit
Step 2. 이전하기 위해 Datapump 관련 환경설정을 합니다.
$ mkdir /data/con_test1
$ sqlplus sys/oracle as sysdba
SYS>create directory con_test1 as '/data/con_test1';
SYS>grant read,write on directory con_test1 to system;
Step 3. 이전하기 위한 작업을 진행합니다.
SYS>exec dbms_tts.transport_set_check('CONV_TEST1',true);
SYS>select * from transport_set_violations ;
SYS>@dd
SYS>alter tablespace conv_test1 read only; <-- 읽기전용 상태로 변경해야 합니다 !
SYS>exit
$ expdp system/oracle directory=con_test1 transport_tablespaces=conv_test1
file=conv.dmp logfile=conv.log
Step 4. Target DB (TO-BE) 로 파일을 복사합니다.
$ cp /app/oracle/oradata/testdb/conv_test01.dbf /data/testdb2/
$ cp /data/con_test1/* /data/conv_test2/
$ sqlplus / as sysdba;
SYS>alter tablespace conv_test1 read write ;
SYS>exit
여기까지 Source DB (AS-IS) 쪽의 작업은 완료되었습니다.
- 여기서부터는 Target DB (testdb2)에서 작업합니다.
Step 5. Source DB에서 사용하던 계정을 동일하게 생성 한 후 Datapump 관련 작업을 수행합니다.
SYS>select instance_name from v$instance;
INSTANCE_NAME
----------------
testdb2 <-- Target DB 입니다.
SYS>!
$ mkdir /data/conv_test2
$ exit
SYS>create directory conv_test1 as '/data/conv_test2';
SYS>grant read,write on directory conv_test1 to system;
SYS>!
Step 6. impdp 를 수행합니다.
$ cd /data/conv_test2/
$ ls
conv.dmp conv.log
$ impdp system/oracle directory=conv_test1 dumpfile=conv.dmp
logfile=conv2.log transport_datafiles='/data/testdb2/conv_test01.dbf'
Step 7. 확인합니다.
$ sqlplus / as sysdba;
SYS>select instance_name from v$instance;
INSTANCE_NAME
----------------
testdb2
SYS>@dd
TABLESPACE MB FILE_NAME
----------------- ---------- ---------------------------------------------
EXAMPLE 345.625 /data/testdb2/example01.dbf
USERS 10 /data/testdb2/users01.dbf
UNDOTBS1 10 /data/testdb2/undotbs01.dbf
SYSAUX 620 /data/testdb2/sysaux01.dbf
SYSTEM 720 /data/testdb2/system01.dbf
CONV_TEST1 10 /data/testdb2/conv_test01.dbf <-- 이전 완료되었습니다.
6 rows selected.
SYS>select * from scott.ctest1;
SYS>insert into scott.ctest1 values (2);
insert into scott.ctest1 values (2)
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/data/testdb2/conv_test01.dbf'
SYS>alter tablespace conv_test1 read write;
SYS>insert into scott.ctest1 values (2);
SYS>select * from scott.ctest1;
NO
-------------
2
1
8) 12c Datapump New Feature
(1) LOGTIME 지원
Test 1) LOGTIME 사용 전
$ expdp scott/tiger directory=datapump dumpfile=ex1.dmp schemas=scott
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Test 2) LOGTIME 사용 후
[oracle@dbserver ~]$ expdp scott/tiger directory=datapump dumpfile=ex2.dmp schemas=scott \
> logtime=a
21-JUL-13 00:29:26.670: Estimate in progress using BLOCKS method...
21-JUL-13 00:29:28.031: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
21-JUL-13 00:29:28.195: Total estimation using BLOCKS method: 1.312 MB
21-JUL-13 00:29:28.795: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/
위와 같이 시간이 함께 기록되는 기능이 12c NF 로 등장합니다.
logtime 에 사용할 수 있는 옵션은 아래와 같습니다.
* * * * NONE NONE NONE -- 시간 정보를 남기지 않습니다 (기본값입니다)
* * * * STATUS STATUS STATUS -- 시간 정보를 현재 화면에만 남깁니다.
* * * * LOGFILE LOGFILE LOGFILE -- 시간 정보를 로그 파일에만 남기고 현재 화면에는 보여주지 않습니다.
* * * * ALL ALLALL ALL -- 시간 정보를 현재 화면에도 남기고 로그파일에도 남깁니다.