14 Datapump 와 Migration

1. Datapump 의 장점

1) 일시 중단 시켰다가 다시 시작 가능

2) 필요한 디스크 공간 예측 가능

3) 원격지에 있는 데이터베이스에 expdp / impdp 작업 가능

4) remapping 기능 지원

5) dump 작업하면서 압축 동시 진행

6) 빨라진 작업 속도

2. 사용 전 환경설정

$ mkdir /data/datapump

SQL> create or replace directory datapump as '/data/datapump' ;

SQL> grant read,write on directory datapump to scott ;

SQL> grant create any directory to scott ;

export 는 expdp, import 는 impdp 명령 사용

3. expdp 실행 모드

1) Full 파라미터 사용 데이터베이스 전체 export 가능

    DBA 나 export_full_database 권한 있어야 함

2) Schemas 파라미터 사용 특정 스키마의 전체 export 가능

    Export 에서 OWNER 파라미터와 동일

3) Tablespaces 파라미터 사용 해당 테이블스페이스에 속한 모든 테이블 export 가능

4) Tables 파라미터를 사용하여 여러개의 테이블을 export 받으려면 , 로 구분

4. expdp 파라미터 정리

1) directory - 디렉토리 오브젝트를 지정하여 덤프 파일의 위치로 로그 파일의 위치를 지정할 수 있다.

directory=directory_object_name

2) dumpfile - 덤프파일의 이름을 지정해 주는 파라미터

dumpfile=dumpfile_%U

3) filesize - 한개 파일의 최대 크기 지정

filesize=N[bytes | kilobytes | megabytes | gigabytes]

4) parfile - 각종 파라미터 저장해 놓고 참조해서 사용

parfile=file.par 확장자는 상관없음

5) logfile /nologfile - 작업내용을 저장할 로그파일명을 지정

logfile=logfile

6) compression - 해당 테이블에 대한 메타 데이터를 압축해서 덤프 파일 내에 저장

compression=all

7) transport_full_check - 테이블 스페이스 내에 존재하는 테이블과 인덱스의 의존성 검사 유무 결정

y - 반드시 테이블과 인덱스가 같은 테이블 스페이스에 존재해야 성공

n - 테이블과 인덱스는 다른 테이블 스페이스에 존재해야 작업 성공

8) content - 어떤 내용을 포함시킬 것인가 결정

all - 테이블과 메타데이터를 포함한 모든 것

data_only - 테이블 데이터만 포함

metadata_only - 메타데이터만 포함

9) exclude / include - 원하는 오브젝트만 선택해서 작업

emp 테이블만 제외하고 exclude

exclude=table:"\='EMP'"

emp, dept 테이블만 제외하고

exclude=table:\"IN\(\'EMP\',\'DEPT\'\)\"

emp 테이블의 인덱스 idx_emp_ename 만 제외

exclude=index:"\=\'idx_emp_ename\'\"

10) query - 특정 조건에 맞는 데이터만 수행

query=emp:\"'where sal>1000'\"

11) sample - 데이터가 너무 많을 경우 특정 퍼센트를 지정해서 추출

sample=scott.emp:20

12) network_link - 원격지에 있는 데이터 베이스에 접근

network_link=emp@second_db

13) encryption_password - 특정 encryption 되어 있을 경우 암호 설정

단, 이 옵션은 Advanced Security Option 이라는 추가 라이선스를 구입해야 함

encryption_password=a1234

14) job_name - 작업명 설정

15) status - 작업시 갱신된 내용을 status 에 설정된 시간 간격으로 진행상태 보여줌

16) parallel - 작업 수행시 프로세스 몇개를 사용할 것인가 지정 (4개 이상이어야 효과봄)

parallel=4

17) attach - 일시 중단된 작업에 다시 접속할 때 사용

attach scott/tiger job_name=dp1

18) reuse_dumpfiles - 저장하고자 하는 덤프파일명이 있을 경우 해당 내용을 덮어 씀

reuse_dumpfiles=Y

temporary tablespace 설정이 올바로 되어있어야만 된다

없거나 크기가 작으면 temporary tablespace is empty. 라는 경고가 나옴

SYS> create temporary tablespace temp100

  2     tempfile '/app/oracle/oradata/testdb/temp100.dbf' size 100m ;

SYS> alter user scott temporary tablespace temp100 ;

또는

SYS> alter database default temporary tablespace temp100 ;

실습1 scott 계정의 emp, dept 테이블만 백업받기

$ expdp scott/tiger tables=emp,dept directory=datapump job_name=t1 dumpfile=emp_dept.dmp

실습2 scott schema 전부 백업받기

$ expdp scott/tiger schemas=scott directory=datapump dumpfile=scott01.dmp

실습3 DB 전체를 백업받기

$ expdp system/oracle full=y directory=datapump dumpfile=full01.dmp job_name=a

실습4 일시 중단 후 다시 작업하기

$ expdp system/oracle full=y directory=datapump dumpfile=full02.dmp job_name=a

CTRL+C 로 취소

상태 확인

Export> status

일시 중단 시킴

Export> stop_job

엔터

일시 중단된 작업에 재접속

$ expdp system/oracle attach=system.a

다시 시작

Export> start_job

Export> exit

Export> 상태에서 해당 작업을 완전히 취소하고 싶으면 kill_job 을 입력

실습5 비정상적으로 종료된 job 취소하기

step1 사전환경 설정

$ mkdir /data/dp1

$ mkdir /data/dp2

$ mkdir /data/dp3

$ mkdir /data/dp4

$ sqlplus / as sysdba ;

SQL> startup

SQL> create directory dp1 as '/data/dp1' ;

SQL> create directory dp2 as '/data/dp2' ;

SQL> create directory dp3 as '/data/dp3' ;

SQL> create directory dp4 as '/data/dp4' ;

SQL> grant read,write on directory dp1 to scott ;

SQL> grant read,write on directory dp2 to scott ;

SQL> grant read,write on directory dp3 to scott ;

SQL> grant read,write on directory dp4 to scott ;

step2 Daatapump 수행 중 비정상 종료

SQL> exit

$ expdp system/oracle full=y directory=dp1 dumpfile=full01.dmp job_name=dp1

CTRL+C

Export> stop

yes

$ expdp system/oracle full=y directory=dp2 dumpfile=full02.dmp job_name=dp2

CTRL+C

Export> stop

yes

$ expdp system/oracle full=y directory=dp3 dumpfile=full03.dmp job_name=dp3

CTRL+C

Export> stop

yes

$ sqlplus / as sysdba ;

SQL> SELECT owner_name, job_name, operation, job_mode, state

  2     FROM dba_datapump_jobs ;

step3 파일을 수동으로 삭제하는 장애 발생

SQL> !rm -fr /data/dp1/*

SQL> !rm -fr /data/dp2/*

SQL> !rm -fr /data/dp3/*

SQL> exit

step4 중지되었던 job에 다시 접속 시도 - 에러발생

$ expdp system/oracle attach=dp1

$ expdp system/oracle attach=dp2

$ expdp system/oracle attach=dp3

step5 문제의 정보를 가진 마스터 테이블 확인 후 

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.dp1 ;

SYS> drop table system.dp2 ;

SYS> drop table system.dp3 ;

SYS> select owner_name, job_name, operation, job_mode, state

  2      from dba_datapump_jobs ;

실습6 여러 사용자의 테이블 한꺼번에 expdp 받기

$ expdp system/oracle directory=datapump dumpfile=scott16.dmp tables=scott.emp, hr.departments

실습7 병렬로 expdp 작업하기

$ expdp system/oracle full=y directory=datapump dumpfile=full04.dmp job_name=a parallel=4

창 하나 더 열어서 top -c 명령어로 현재 수행 중인 프로세스를 조회하면 확인할 수 있다.

실습8 파일 위치 다르게 병렬로 expdp 작업하기

$ expdp system/oracle full=y parallel=4 dumpfile=DATADIR1:full1%U.dat, 

  DATADIR1:full2%U.dat, 

  DATADIR1:full3%U.dat, 

  DATADIR1:full4%U.dat filesize=100m

DATADIR1-4 까지 미리 만들어져 있어야 한다.

실습9 파라미터 파일 사용해서 expdp 수행 - 여러개의 파일로 분할 expdp

$ vi expdp_pump.par

userid=system/oracle

directory=datapump

job_name=datapump

logfile=expdp.log

dumpfile=expdp_%U.dmp

filesize=100m

full=y

$ expdp parfile=expdp_pump.par

5. impdp 관련 파라미터

1) Content

2) Include 특정테이블만 포함

emp 테이블만 impdp 

include=table:\"\=\'emp\'\"

emp, dept 만 impdp

include=table:\"\in\(\'emp\',\'dept\'\)\"

3) Exclude 특정 테이블만 빼고

4) Table_exists_action 테이블 명은 같지만 데이터는 다를 경우

skip 같은 테이블을 만나면 건너뜀

append 같은 테이블을 만나면기존 내용에 데이터를 추가

truncate 같은 테이블을 만나면 기존 테이블을 truncate 하고 새로 impdp

drop 기존 테이블을 drop 하고 새로 만들어서 impdp

5) Remap_schema 다른 유저로 impdp

remap_schema=scott:hr

export/import 에서 from scott to hr 와 같음

6) Remap_datafile 기존 서버의 데이터파일 정보를 이전 후 서버의 데이터 파일로 매핑

Remap_datafile='/data1/users01.dbf':'/data2/users01.dbf','/data1/test.dbf':'/data2/test.dbf'

7) Remap_tablespace 기존 테이블스페이스에서 다른 테이블 스페이스로

Remap_tablespace='users':'example'

8) Remap_table 기존 테이블 이름을 impdp 하면서 변경

Remap_table=emp:emp100

9) Network_link 원격지에 impdp

10) Partition_options 파티션되어 있던 테이블을 단일 테이블로 impdp

partition_options=merge

11) Data_options=skip_constrait_errors 제약조건 위배시 에러 무시하고 진행

실습10 parameter file 이용해서 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

실습11 impdp 병렬 작업하기

$ impdp system/oracle parallel=4 dumpfile=DATADIR1:full1%U.dat,

   DATADIR1:full2%U.dat,

   DATADIR1:full3%U.dat,

   DATADIR1:full4%U.dat

   table_exists_action=append

실습12 import 수행하지 않고 ddl 문장만 추출

$ impdp system/manager directory=datapump dumpfile=expdp_%U.dmp sqlfile=datapump.dat

실습13 작업 예상시간 추출하기

$ sqlplus / as sysdba

SYS> select sid, serial#, sofar, totalwork

  2      from v$session_longops

  3      where opname='DATAPUMP2'         -- job_name 을 대문자로 입력

  4      and sofar!=totalwork ;

실습14 데이터 펌프 재 설치 (10.2 이상)

SQL> @$ORACLE_HOME/rdbms/admin/catdph.sql

SQL> @$ORACLE_HOME/rdbms/admin/prvtdtde.plb

SQL> @$ORACLE_HOME/rdbms/admin/catdpb.sql

SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

실습15 datapump 수행 시 암호화 작업 - 11g new feature

$ expdp system/oracle full=y directory=datapump dumpfile=full04.dump job_name=abc ENCRYPTION=data_only ENCRYPTION_PASSWORD=epasswd

all 모든 데이터와 메타데이터 암호화

data_only 덤프 파일에 저장될 데이터만 암호화

encrypted_columns_only 특정 컬럼만 암호화

metadata_only 메타데이터만 암호화

none 암호화 사용안함

실습16 설정된 directory 경로 확인하기

SQL> select * from dba_directories ;

실습17 일자별 schema 별로 자동 백업받는 스크립트

$ vi expdp_script.sh

export LANG=C

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10g

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)||'_%.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

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 사용하기)

8. 통계정보 이동하기

통계정보란 특정 세그먼트에 대한 자세한 정보로 딕셔너리에 저장되어 있으면서 여러 가지 튜닝이나 실행 계획 생성 등에

영향을 주는 아주 중요한 정보

DBMS_STATS 패키지 사용

DBMS_STATS.GATHER_TABLE_STATS - 특정 테이블의 통계정보 수집

DBMS_STATS.GATHER_SCHEMA_STATS - 특정 스키마의 통계정보 수집

DBMS_STATS.GATHER_INDEX_STATS - 특정 인덱스의 통계정보 수집

DBMS_STATS.GATHER_DATABASE_STATS - 데이터베이스 전체의 통계정보 수집

DBMS_STATS.GENERATE_STATS - B*TREE 인덱스, BITMAP 인덱스의 통계정보 수집

ANALYZE 는 특정 테이블이나 인덱스만 지정해서 하나씩 통계정보를 생성해야 하는 단점이 있음

step1 통계정보 생성

SCOTT> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT') ;

step2 통계정보를 저장할 테이블 생성

SCOTT> EXEC DBMS_STATS.CREATE_STAT_TABLE('SCOTT', 'SCOTT_STATS') ;

step3 통계정보를 위 step2 에서 만든 테이블로 exp 수행

SCOTT> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('SCOTT', 'SCOTT_STATS') ;

step4 위 step3 에서 통계정보를 저장하고 있는 테이블을 exp 한다

SCOTT> ! exp scott/tiger tables=scott_stats file=scott_stats.dmp

step5 위 step 4 에서 수행한 scott.stat.dmp 파일을 신규 서버로 이동

step6 step 5 에서 이동한 파일을 imp  수행

SCOTT> drop table scott_stats ;

SCOTT> !imp scott/tiger file=scott_stats.dmp ignore=y

step7 step 6 단계에서 imp 된 통계정보를 딕셔너리로 입력

SCOTT> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('SCOTT', 'SCOTT_STATS') ;

9. Migration 과 Character 에 대해

(1) Character set 종류와 NLS_LANG 변수

KO16KSC5601 - 한글 완성형 코드, 최신 네티즌 용어 없음

KO16MSWIN949 - Ms Windows codepage 949번, 최신 네티즌 용어 다 표현

UTF8/AL32UTF8 - 유니코드를 구현한 가변길이 인코등 방식, 영어1, 한글2, 중어3byte (AL32UTF8 은 6byte)

현재 서버의 Character Set 정보는

SQL> select * from v$nls_parameters ;

주요 파라미터

NLS_TERRITORY - 영역설정

NLS_LANGUAGE - 언어설정

NLS_LANG - 언어 , 영역, Character set 설정

NLS_COMP - SQL 에서 비교방식 설정

NLS_SORT - 문자열의 정렬방법 설정

NLS_LANG 파라미터 - 데이터베이스 내부에 데이터를 입력할 때 입력될 데이터의 유형을 알려주는 역할

NLS_SORT 파라미터 - 데이터베이스를 조회할 때 정렬을 함

(2) Unicode 

Character Set 마다 각각의 독특한 특징을 최대한 쉽게 처리하기 위해 만들어 놓은 코드

Coded character set (CCS) - 각각의 문자에 대하여 비트로 표현할 수 있는 정수 값에 각각의 문자 하나 하나씩을 할당

Character Encoding Schema (CES) - 각각의 문자에 대하여 16진수에 하나씩 할당

10. Character Set Scan (CSSCAN) Utility

변경하려는 Character set 으로 변경했을 경우 Data 손실 여부를 미리 테스트

필요한 정보를 미리 생성해주는 스크립트

SQL> $ORACLE_HOME/rdbms/admin/csminst.sql

테스트 실행

$ csscan system/oracle full=y tochar=UTF8

엔터

엔터

3개의 파일 생성

$ ls scan*

가장 일반적인 정보는 scan.txt 에 저장

11. Migration 작업 순서 및 스크립트 파일 예제

step1 이전 대상 원본 (AS-IS) DB Object 개수 확인

적당한 경로와 이름으로 스크립트 만들고 수행

set line 200

set long 2000

set pagesize 2000

select owner ow,

sum(decode(object_type, 'TABLE', 1, 0)) ta,

sum(decode(object_type, 'INDEX', 1, 0)) ind,

sum(decode(object_type, 'SYNONYM', 1, 0)) sy,

sum(decode(object_type, 'SEQUENCE', 1, 0)) se,

sum(decode(object_type, 'VIEW', 1, 0)) ve,

sum(decode(object_type, 'CLUSTER', 1, 0)) clu

from dba_objects

group by owner

having owner not in ('SYS', 'SYSTEM', 'SCOTT', 'DBSNMP', 'OUTLN', 'WKPROXY', 'WMSYS', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'XDB', 'ANONYMOUS', 'OWNER', 'WKSYS', 'ODM_MTR', 'ODM', 'OLAPSYS', 'HR', 'OE', 'PM', 'SH', 'QS_ADM', 'QS', 'QS_WS', 'QS_ES', 'QS_OS', 'QS_CBADM', 'QS_CB', 'QS_CS', 'PERFSTAT', 'EXFSYS')

ORDER BY 1 ;