[Oracle] Datapump와 Migration

HYEOB KIM·2022년 12월 1일
oracle
0

Oracle

목록 보기
20/58
post-custom-banner

Datapump와 Migration

Datapump는 export/import의 향상된 유틸리티이고 10g부터 등장했습니다. export/import는 사용하기 편하고 성능도 뛰어나지만 속도가 느리고 작업시간을 예측하기도 어렵습니다. Datapump는 이런 문제점들을 획기적으로 해결하여 매우 뛰어난 속도 향상과 사용자 편의성을 가지고 있습니다.

export/import의 명령어는 exp/imp이며,
Datapump의 명령어는 expdp/impdp입니다.

1. Datapump의 장점

작업 관리의 편의성

exp/imp 방법은 작업이 시작되면 끝날 때까지 제어할 수 없었습니다. 하지만 datapump는 job의 제어가 가능해 훨씬 편리합니다.

필요한 디스크 공간의 예측

exp/imp 방법은 작업 중에 디스크 공간이 부족해서 몇 시간 동안 했던 작업을 취소하고 디스크를 추가한 후 다시 작업을 수행하는 경우가 있었습니다. 하지만 datapump는 ESTIMATE 파라미터를 사용해 해당 작업 시 필요한 디스크 공간을 미리 알 수 있습니다.

원격지 DB에 작업 수행 가능

DB Link 기능을 통해 원격지의 DB에 expdp/impdp 작업을 수행할 수 있습니다.

remapping 기능 지원

스키마 변경이나 Tablespace 변경, Data File 변경까지 가능합니다.

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

용량이 큰 데이터의 경우 압축을 동시에 진행하므로 dump file 용량을 획기적으로 줄일 수 있습니다.

빨라진 평균 작업 속도

exp/imp에 비해 평균적으로 약 20배 이상의 성능 향상을 가져와서 대량의 작업도 아주 빨리 수행할 수 있게 되었습니다.

2. 사용 전 환경 설정하기

Datapump는 직접 OS 파일에 I/O를 할 수 없고 oracle에 directory라는 객체를 통해서 간접으로 접근이 가능합니다. 그래서 미리 directory가 만들어져 있어야 하며 datapump를 수행하는 사용자는 그 directory에 접근할 수 있는 권한이 있어야 합니다.

-- 미리 directory가 만들기
$ mkdir /data/datapump
$ sqlplus / as sysdba

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

-- 사용자에게 directory 접근 권한 부여
SQL> grant read, write on directory datapump to khyup;

SQL> grant create any directory to khyup;

마지막으로 사용자에게 temporary tablespace가 설정되어 있어야 합니다. 만약 없거나 크기가 작은 경우 Temporary tablespace is empty라는 경고가 나옵니다. 이 메시지를 마주했다면 즉시 temporary tablespace를 크게 만든 후 아래와 같이 사용하도록 설정합니다.

SQL> create temporary tablespace temp100
  2  tempfile '/ORA19/app/oracle/oradata/ORACLE19/temp100.dbf' size 100M;
  
SQL> alter user khyup temporary tablespace temp100;

아니면 아예 DB에 default temporary tablespace로 지정해도 됩니다.

SQL> alter database default temporary tablespace temp100;

3. expdp 실행 모드

1. Full 모드

full 파라미터를 사용하며 DB 전체를 export 받을 수 있습니다. DBA 권한 또는 export_full_database 권한을 가지고 있어야 수행할 수 있습니다.

2. schema 모드

Schemas 파라미터를 사용하며 특정 스키마 전체를 export 받을 수 있습니다.

3. Tablespace 모드

tablespaces 파라미터를 사용하며 해당 Tablespace에 속한 모든 테이블을 export 받을 수 있습니다. transport_tablespace 파라미터를 이용하면 Table과 Tablespace의 메타 데이터까지 export 받게 되어 다른 서버로 Tablespace 전체를 이동시킬 때 아주 유용하게 사용할 수 있습니다.

단, transport_tablespace를 사용하려면 양쪽 DB의 OS가 같아야 하고, Block Size와 Characterset도 같아야 합니다.

4. table 모드

tables 파라미터를 사용하며 여러 개의 테이블을 export 받을 수 있습니다. 테이블은 ,로 구분합니다.

4. expdp 파라미터 정리

directory
Expdp를 수행할 때 directory 오브젝트를 지정하여 dump 파일의 위치로 로그 파일의 위치를 지정할 수 있습니다.

dumpfile
파일시스템에 저장될 dump 파일의 이름을 지정해주는 파라미터입니다. Dumpfile=dumpfile_%U와 같이 %U를 붙여주면 01-99까지 자동으로 증가되어 파일을 구분해줍니다. dump 파일의 기본 파일명은 expdat.dmp입니다.

filesize
expdp를 수행할 때 한 개 파일의 최대 크기를 지정하는 파라미터입니다. Filesize=N [bytes | kilobytes | megabytes | gigabytes ] 형식으로 사용합니다.

parfile
expdp 작업할 때 사용하는 각종 파라미터들을 파일에 저장해놓고 expdp 작업을 수행할 때마다 참조해서 사용할 때 이 파라미터를 이용할 수 있습니다. Parfile=file.par 형식으로 사용합니다.

logfile/nologfile
expdp 작업내용을 저장할 로그파일명을 지정하는 파라미터입니다. Logfile=logfile 형식으로 사용하면 됩니다. 파라미터를 지정하지 않으면 expdp를 저장하는 곳에 export.log라는 파일명으로 남기게 됩니다. 로그파일을 남기고 싶지 않을 경우 Nologfile를 사용합니다.

compression
compression 파라미터에는 metadata_only, none 두 개의 옵션이 있고 기본값은 metadata_only 입니다. expdp 작업 시 해당 테이블에 대한 메타 데이터를 압축해서 dump 파일 내에 저장하게 됩니다. 11g에서는 ALL이란 파라미터가 지원되는데 압축률이 아주 좋으므로 꼭 all로 사용하기를 권장합니다.

$ expdp khyup/khyup dumpfile=khyup00.dmp directory=datapump compression=all

transport_full_check
expdp 작업 수행 중 tablespace 내 존재하는 테이블과 인덱스의 의존성 검사 유무를 결정합니다. transport_tablespaces 옵션과 함께 사용됩니다. Y일 경우 반드시 테이블과 인덱스가 같은 Tablespace에 존재해야 작업에 성공합니다.

content
expdp 작업 시 어떤 내용을 포함시킬 것인가를 결정하는 파라미터입니다.

  • All: 테이블과 메타데이터를 포함한 모든 것
  • Data_only: 테이블 데이터만 포함
  • Metadata_only: 메타데이터만 포함
$ expdp khyup/khyup dumpfile=khyup00.dmp directory=datapump content=data_only

Exclude/Include
expdp 작업 수행 시 원하는 오브젝트만 선택해서 작업할 수 있습니다.

예1) khyup schema의 모든 것을 expdp 받되 emp 테이블만 제외하고 싶을 경우

$ expdp khyup/khyup directory=datapump dumpfile=khyup02.dmp schemas=khyup exclude=table:"\='EMP'"

예2) khyup schema의 모든 것을 expdp 받되 emp, dept 테이블만 제외하고 싶을 경우

$ expdp khyup/khyup directory=datapump dumpfile=khyup03.dmp schemas=khyup exclude=table:\"IN \(\'EMP\',\'DEPT\'\)\"

예3) khyup schema의 모든 것을 expdp 받되 emp 테이블의 인덱스 idx_emp_ename만 제외하고 싶을 경우

$ expdp khyup/khyup directory=datapump dumpfile=khyup04.dmp schemas=khyup exclude=index:"\=\'idx_emp_ename\'\"

Query
특정 조건에 맞는 데이터만 expdp를 수행할 경우 사용합니다.

예1) emp 테이블에서 sal이 1000 보다 많은 사람만 expdp 받기

$ expdp khyup/khyup directory=datapump dumpfile=khyup05.dmp tables=emp query=emp:\"WHERE sal\>1000\'\"

예2) emp 테이블에서 job=CLERK이면서 sal > 1000 인 사람만 expdp 받기

$ expdp khyup/khyup directory=datapump dumpfile=khyup06.dmp tables=emp query=emp:\"WHERE sal\> 1000 and job\=\'CLERK\'\"

sample
expdp할 데이터가 너무 많을 경우 특정 퍼센트를 지정해서 추출할 수 있습니다. 입력 가능 범위는 0.000001 ~ 100%입니다.

$ expdp khyup/khyup directory=datapump dumpfile=khyup07.dmp sample=khyup.emp:20

Network_link
원격지의 DB에 접근하여 데이터를 expdp 작업을 할 경우 사용합니다. 이 기능은 DB Link를 이용해 작동하기 때문에 미리 원격 DB에 DB Link가 생성되어 있어야 합니다.

$ expdp khyup/khyup directory=datapump dumpfile=khyup08.dmp network_link=emp@<second_db>

second_db는 원격지에 생성된 DB Link 명입니다.

encryption_password
expdp 받은 테이블 중에 특정 컬럼이 encryption 되어 있을 경우 expdp 작업 시에 암호를 설정할 수 있습니다.

$ expdp khyup/khyup directory=datapump dumpfile=khyup09.dmp encryption_password=a1234

Job_name
expdp 작업을 수행할 때 해당 job에 name을 설정할 수 있습니다.

status
expdp 작업 시에 갱신된 내용을 status에 설정된 시간 간격으로 진행 상태를 보여줍니다. 기본값은 0초로 실시간으로 작업 갱신 내용을 보여줍니다.

parallel
expdp 작업 시 프로세스를 몇 개 사용할 것인가를 지정하는 파라미터입니다. 대량의 데이터를 expdp 해야할 경우 이 옵션을 꼭 사용하길 권장합니다. 사용할 때는 parallel에 지정된 개수만큼 data file을 만들어 주어야 합니다. %U 옵션을 이용하면 자동으로 개수만큼 만들어줍니다.

$ expdp khyup/khyup directory=datapump dumpfile=datapump_%U.dmp parallel=4

Attach
일시 중단된 작업에 다시 접속할 때 사용하는 파라미터입니다.

$ attach khyup/khyup job_name=dp1

이때 사용할 수 있는 옵션들은 아래와 같습니다.

  • add_file: dump 파일을 추가합니다.
  • exit: job의 작업에서 빠져나갑니다.
  • kill_job: 해당 작업을 완전히 삭제합니다. 명령어가 작동하지 않는다면 sqlplus로 해당 사용자로 로그인해서 해당 job_name의 이름을 가진 테이블을 drop합니다.
  • parallel: 현재 작업 중인 프로세스 개수를 조정합니다.
  • start_job: 중단된 작업을 다시 시작할 때 사용합니다.
  • stop_job: 현재 작업을 중단시킵니다.
  • status: 현재 작업상태를 모니터링하는 갱신 시간을 지정합니다.

reuse_dumpfiles
저장하고자 하는 dump file 명이 있을 경우 해당 파일의 내용을 덮어쓰게 만드는 옵션입니다.

$ expdp khyup/khyup directory=datapump dumpfile=datapump_01.dmp reuse_dumpfiles=Y

expdp 실습

1) khyup 계정의 emp, dept 테이블만 백업 받기

OS에서 /data/datapump는 OS 계정의 oracle:oinstall 소유여야 합니다.

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

Export: Release 19.0.0.0.0 - Production on Thu Dec 1 10:37:06 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "KHYUP"."T1":  khyup/******** tables=emp,dept directory=datapump job_name=t1 dumpfile=emp_dept.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "KHYUP"."DEPT"                              5.914 KB       1 rows
. . exported "KHYUP"."EMP"                               5.914 KB       1 rows
Master table "KHYUP"."T1" successfully loaded/unloaded
******************************************************************************
Dump file set for KHYUP.T1 is:
  /data/datapump/emp_dept.dmp
Job "KHYUP"."T1" successfully completed at Thu Dec 1 10:37:18 2022 elapsed 0 00:00:07

2) khyup schema 전부 백업 받기

$ expdp khyup/khyup schemas=khyup directory=datapump dumpfile=khyup01.dmp

Export: Release 19.0.0.0.0 - Production on Thu Dec 1 10:39:53 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "KHYUP"."SYS_EXPORT_SCHEMA_01":  khyup/******** schemas=khyup directory=datapump dumpfile=khyup01.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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/TABLE/INDEX/INDEX
. . exported "KHYUP"."DEPT"                              5.914 KB       1 rows
. . exported "KHYUP"."EMP"                               5.914 KB       1 rows
Master table "KHYUP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for KHYUP.SYS_EXPORT_SCHEMA_01 is:
  /data/datapump/khyup01.dmp
Job "KHYUP"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 1 10:40:15 2022 elapsed 0 00:00:18

3) DB 전체 백업 받기

alter user system identified by <비밀번호> 명령어를 통해 system 스키마의 비밀번호를 변경합니다.

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

Export: Release 19.0.0.0.0 - Production on Thu Dec 1 10:46:46 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."A":  system/******** full=y directory=datapump dumpfile=full01.dmp job_name=a
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.867 KB      24 rows
. . exported "SYSTEM"."REDO_DB"                          25.59 KB       1 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.10 KB       1 rows
. . exported "WMSYS"."WM$HINT_TABLE$"                    9.992 KB      97 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          7.085 KB      11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows
. . exported "SYS"."TSDP_SUBPOL$"                        6.328 KB       1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.382 KB       1 rows
. . exported "WMSYS"."WM$ENV_VARS$"                      6.023 KB       3 rows
. . exported "SYS"."TSDP_PARAMETER$"                     5.953 KB       1 rows
. . exported "SYS"."TSDP_POLICY$"                        5.921 KB       1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.984 KB       1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$"                   5.820 KB      12 rows
. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0"         0 KB       0 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P241"           61.56 KB      27 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P261"           53.85 KB       8 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows
. . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows
. . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows
. . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows
. . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows
. . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.960 KB       2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
. . exported "SYS"."NACL$_ACE_EXP"                           0 KB       0 rows
. . exported "SYS"."NACL$_HOST_EXP"                      6.921 KB       1 rows
. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP"                       0 KB       0 rows
. . exported "SYS"."SQL$_DATAPUMP"                           0 KB       0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP"                 0 KB       0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP"                    0 KB       0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP"                    0 KB       0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP"                        0 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"                   0 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"               0 KB       0 rows
. . exported "WMSYS"."WM$EXP_MAP"                        7.718 KB       3 rows
. . exported "WMSYS"."WM$METADATA_MAP"                       0 KB       0 rows
. . exported "KHYUP"."DEPT"                              5.914 KB       1 rows
. . exported "KHYUP"."EMP"                               5.914 KB       1 rows
Master table "SYSTEM"."A" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.A is:
  /data/datapump/full01.dmp
Job "SYSTEM"."A" successfully completed at Thu Dec 1 10:47:55 2022 elapsed 0 00:01:04

4) 일시 중단 후 다시 작업하기

datapump 작업 관리 및 모니터링
dba_datapump_jobs: 이 딕셔너리를 조회하여 현재 작업 중인 작업들의 내용을 확인할 수 있습니다.

  • owner_name: 작업 계정
  • job_name: 작업 명칭
  • job_mode: full, table, index, tablespace 등
  • state: executing(수행중), defining, undefined, not running 등이 있습니다.
$ expdp system/1234 full=y directory=datapump dumpfile=full02.dmp job_name=a

Export: Release 19.0.0.0.0 - Production on Thu Dec 1 10:49:46 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."A":  system/******** full=y directory=datapump dumpfile=full02.dmp job_name=a
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
^C --> 중간에 작업을 취소합니다.

-- job 상태 확인
Export> status

Job: A
  Operation: EXPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 2
  Dump File: /data/datapump/full02.dmp
    bytes written: 8,192

Worker 1 Status:
  Instance ID: 1
  Instance name: oracle19
  Host name: oel7
  Object start time: Thursday, 01 December, 2022 10:49:59
  Object status at: Thursday, 01 December, 2022 10:49:59
  Process Name: DW00
  State: EXECUTING

-- job을 일시 중지합니다.
Export> stop_job
Are you sure you wish to stop this job ([yes]/no):

-- sqlplus로 접속해 job 목록을 조회해봅니다.
[oracle@oel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 1 10:50:23 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> col owner_name for a10
SQL> col job_name for a10
SQL> col operation for a10
SQL> col job_mode for a10
SQL> col state for a15
SQL> select owner_name, job_name, operation, job_mode, state
  2  from dba_datapump_jobs;

OWNER_NAME JOB_NAME   OPERATION  JOB_MODE   STATE
---------- ---------- ---------- ---------- ----------
SYSTEM     A          EXPORT     FULL       NOT RUNNING

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

-- 일사 중지된 작업에 재접속
[oracle@oel7 ~]$ expdp system/1234 attach=system.a

Export: Release 19.0.0.0.0 - Production on Thu Dec 1 10:51:54 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Job: A
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: EEBB6FA9204A26EBE055020C29D12630
  Start Time: Thursday, 01 December, 2022 10:51:58
  Mode: FULL
  Instance: oracle19
  Max Parallelism: 1
  Timezone: +09:00
  Timezone version: 32
  Endianness: LITTLE
  NLS character set: KO16MSWIN949
  NLS NCHAR character set: AL16UTF16
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** full=y directory=datapump dumpfile=full02.dmp job_name=a
     TRACE                 0
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 4
  Dump File: /data/datapump/full02.dmp
    bytes written: 94,208

Worker 1 Status:
  Instance ID: 1
  Instance name: oracle19
  Host name: oel7
  Object start time: Thursday, 01 December, 2022 10:50:06
  Object status at: Thursday, 01 December, 2022 10:50:19
  Process Name: DW00
  State: UNDEFINED

-- 작업 재개
Export> start_job

Export> exit

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

-- 1. 사전 환경 설정
[oracle@oel7 ~]$ mkdir /data/dp1
[oracle@oel7 ~]$ mkdir /data/dp2
[oracle@oel7 ~]$ mkdir /data/dp3
[oracle@oel7 ~]$ mkdir /data/dp4
[oracle@oel7 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 1 10:58:02 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

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

Directory created.

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

Directory created.

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

Directory created.

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

Directory created.

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

Grant succeeded.

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

Grant succeeded.

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

Grant succeeded.

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

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

-- 2. datapump 수행 중 비정상 종료
[oracle@oel7 ~]$ expdp system/1234 full=y directory=dp1 dumpfile=full03.dmp job_name=dp1

Export: Release 19.0.0.0.0 - Production on Thu Dec 1 11:00:17 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."DP1":  system/******** full=y directory=dp1 dumpfile=full03.dmp job_name=dp1
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
^C --> [Ctrl] + [C] 비정상 종료
Export> stop
Are you sure you wish to stop this job ([yes]/no):

[oracle@oel7 ~]$ expdp system/1234 full=y directory=dp2 dumpfile=full04.dmp job_name=dp2

Export: Release 19.0.0.0.0 - Production on Thu Dec 1 11:00:59 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."DP2":  system/******** full=y directory=dp2 dumpfile=full04.dmp job_name=dp2
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
^C --> [Ctrl] + [C] 비정상 종료
Export> stop
Are you sure you wish to stop this job ([yes]/no):

[oracle@oel7 ~]$ expdp system/1234 full=y directory=dp3 dumpfile=full05.dmp job_name=dp3

Export: Release 19.0.0.0.0 - Production on Thu Dec 1 11:01:39 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."DP3":  system/******** full=y directory=dp3 dumpfile=full05.dmp job_name=dp3
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
^C --> [Ctrl] + [C] 비정상 종료
Export> stop
Are you sure you wish to stop this job ([yes]/no):

[oracle@oel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 1 11:01:54 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> col owner_name for a10
SQL> col job_name for a10
SQL> col operation for a10
SQL> col job_mode for a10
SQL> col state for a10
SQL> select owner_name, job_name, operation, job_mode, state
  2  from dba_datapump_jobs;

OWNER_NAME JOB_NAME   OPERATION  JOB_MODE   STATE
---------- ---------- ---------- ---------- -----------
SYSTEM     DP3        EXPORT     FULL       NOT RUNNING
SYSTEM     DP1        EXPORT     FULL       NOT RUNNING
SYSTEM     DP2        EXPORT     FULL       NOT RUNNING

-- 3. 파일을 수동으로 삭제하는 장애 발생
SQL> !rm -rf /data/dp1/*

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

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

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

-- 4. 중지되었던 job에 다시 접속 시도 -> 에러 발생
$ expdp system/1234 attach=dp1

Export: Release 19.0.0.0.0 - Production on Thu Dec 1 11:06:41 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/data/dp1/full03.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

-- 5. 위 문제의 정보를 가진 마스터 테이블 확인 후 삭제
[oracle@oel7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 1 11:07:30 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set line 200
SQL> col owner.object for a15
SQL> 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;

STATUS   OBJECT_ID OBJECT_TYPE             OWNER.OBJECT
------- ---------- ----------------------- ---------------
VALID        23564 TABLE                   SYSTEM.DP1
VALID        23589 TABLE                   SYSTEM.DP2
VALID        23614 TABLE                   SYSTEM.DP3

SQL> drop table system.dp1;

Table dropped.

SQL> drop table system.dp2;

Table dropped.

SQL> drop table system.dp3;

Table dropped.

SQL> select owner_name, job_name, operation, job_mode, state
  2  from dba_datapump_jobs;

no rows selected

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

$ expdp system/1234 directory=datapump dumpfile=khyup16.dmp tables=khyup.emp,hr.departments

7) 병렬로 expdp 작업하기

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

실제 위와 같이 실행한 후 top -c 명령어를 통해 리눅스의 프로세스 목록을 조회하면 병렬로 4개의 프로세스가 작동하는 것을 확인할 수 있습니다.

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

$ expdp system/1234 full=y parallel=4 \
        dumpfile=DATADIR1:full1%U.dat, \
                 DATADIR2:full2%U.dat, \
                 DATADIR3:full3%U.dat, \
                 DATADIR4:full4%U.dat, \
                 filesize=100M

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

$ vi expdp_pump.par
userid=system/1234
directory=datapump
job_name=datapump
logfile=expdp.log
dumpfile=expdp_%U.dmp
filesize=100M
full=y

:wq!

$ expdp parfile=expdp_pump.par

5. impdp 관련 파라미터

content
expdp의 옵션과 같이 data_only, all, metadata_only 3가지 값을 가지고 있습니다.

include
include=<object_name>:\"\=\'<조건>\'\" 형식으로 사용할 수 있습니다.

예1) khyup user가 여러 개의 테이블을 khyup05.dmp 파일에 expdp 받았을 때 그 중에서 emp 테이블만 impdp하고 싶을 경우

$ impdp khyup/khyup directory=datapump dumpfile=khyup05.dmp include=table:\"\=\'emp\'\"

예2) emp, dept, bonus 3개의 테이블이 expdp 되어있는 khyup13.dmp 파일에서 emp, dept만 impdp 하고 싶을 경우

$ impdp khyup/khyup directory=datapump dumpfile=khyup13.dmp include=table:\"\in\(\'emp\',\'dept\'\)\"

exclude
특정 테이블만 빼고 impdp하고 싶을 때 사용합니다. 사용법은 expdp와 동일합니다.

table_exists_action
impdp에만 있는 파라미터로 동일한 이름의 테이블이 존재할 때 테이블의 데이터가 다른 경우 처리하는 방법을 나타낸 파라미터입니다.

  • skip: 같은 테이블을 만나면 건너뛰고 다음 테이블을 impdp합니다.
  • append: 같은 테이블을 만나면 기존내용에 데이터를 추가합니다.
  • truncate: 같은 테이블을 만나면 기존 테이블을 truncate하고 새로 impdp합니다.
  • drop: 기존 테이블을 drop하고 테이블을 새로 만들어서 새로운 내용을 impdp하니다.

remap_schema
khyup user로 expdp 받은 테이블을 다른 유저로 impdp할 때 사용합니다.

$ impdp system/1234 directory=datapump dumpfile=khyup13.dmp schemas=khyup include=table:\"\=\'emp\'\" remap_schema=khyup:hr

remap_datafile
기존 서버의 data file 정보를 이전한 서버의 data file로 매핑해주는 파라미터입니다. expdp된 dump 파일 안에 data file 정보를 가지고 있는 경우에만 사용할 수 있습니다.

$ impdp system/1234 full=y directory=datapump dumpfile=full01.dmp remap_datafile='/data1/users01.dbf':'/data2/users01.dbf', '/data1/test.dbf':'/data2/test.dbf'

remap_tablespace
기존 tablespace에서 다른 tablespace로 테이블을 impdp 시킬 때 사용하는 파라미터입니다.

$ impdp system/1234 directory=datapump dumpfile=khyup.dmp remap_tablespace='users':'example' schemas=khyup

remap_table
기존 테이블 이름을 impdp하면서 변경할 때 사용하는 파라미터입니다.

$ expdp khyup/khyup directory=datapump tables=emp dumpfile=emp.dmp

$ impdp khyup/khyup directory=datapump dumpfile=emp.dmp tables=emp remap_table=emp:emp100

network_link
expdp와 마찬가지로 원격지 DB에 impdp할 수 있습니다.

partition_options
파티션 되어 있는 테이블을 단일 테이블로 impdp할 때 사용하는 파라미터입니다.

$ impdp khyup/khyup directory=datapump dumpfile=panmae.dmp tables=panmae100 partition_options=merge

data_options=skip_constraint_errors
impdp시 테이블에 설정된 제약조건이 위배되는 에러가 발생했을 경우 그 에러를 무시하고 계속 진행하라는 옵션입니다.

impdp 실습

1) parameter 파일을 이용해서 impdp 작업하기

$ vi impdp.par
userid=system/1234
directory=datapump
job_name=datapump
logfile=impdp_pump.log
dumpfile=expdp_%U.dmp
full=y
table_exists_action=append

$ impdp parfile=impdp.par

2) impdp 병렬 작업하기

$ impdp system/1234 parallel=4 \
            datafile=DATADIR1:full1%U.dat, \
                     DATADIR2:full2%U.dat, \
                     DATADIR3:full3%U.dat, \
                     DATADIR4:full4%U.dat, \
                     table_exists_action=append

3) import 수행하지 않고 DDL 문장만 추출하기

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

4) 작업 예상 시간 추출하기

$ sqlplus / as sysdba

SQL> select sid, serial#, sofar, totalwork
  2  from v$session_longops
  3  where opname='DATAPUMP2'
  4  and sofar != totalwork;

SID        SERIAL#    SOFAR      TOTALWORK
---------- ---------- ---------- ----------
159        69         0          52

5) datapump 재설치하기

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

6) datapump 수행 시 암호화 작업

ENCRYPTION 파라미터의 값은 ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY, NONE이 있습니다.

  • ALL: 모든 데이터와 메타 데이터 암호화
  • DATA_ONLY: 데이터만 암호화
  • ENCRYPTED_COLUMNS_ONLY: 특정 컬럼만 암호화
  • METADATA_ONLY: 메타 데이터만 암호화
  • NONE: 암호화를 사용하지 않습니다(기본값)
$ expdp system/1234 full=y directory=datapump dumpfile=full04.dmp job_name=abc ENCRYPTION=data_only ENCRYPTION_PASSWORD=<epasswd>

7) 설정된 directory 경로 확인하기

SQL> set line 300
SQL> set pages 200
SQL> col owner for a10
SQL> col directory_name for a25
SQL> col directory_path for a60
SQL> /

OWNER      DIRECTORY_NAME            DIRECTORY_PATH                                               ORIGIN_CON_ID
---------- ------------------------- ------------------------------------------------------------ -------------
SYS        DP4                       /data/dp4                                                                0
SYS        DP3                       /data/dp3                                                                0
SYS        DP2                       /data/dp2                                                                0
SYS        DP1                       /data/dp1                                                                0
SYS        DATAPUMP                  /data/datapump                                                           0
SYS        XMLDIR                    /ORA19/app/oracle/product/19.0.0/db_1/rdbms/xml                          0
SYS        XSDDIR                    /ORA19/app/oracle/product/19.0.0/db_1/rdbms/xml/schema                   0
SYS        OPATCH_INST_DIR           /ORA19/app/oracle/product/19.0.0/db_1/OPatch                             0
SYS        ORACLE_OCM_CONFIG_DIR2    /ORA19/app/oracle/product/19.0.0/db_1/ccr/state                          0
SYS        ORACLE_BASE               /ORA19/app/oracle                                                        0
SYS        ORACLE_HOME               /ORA19/app/oracle/product/19.0.0/db_1                                    0
SYS        ORACLE_OCM_CONFIG_DIR     /ORA19/app/oracle/product/19.0.0/db_1/ccr/state                          0
SYS        DATA_PUMP_DIR             /ORA19/app/oracle/admin/oracle19/dpdump/                                 0
SYS        OPATCH_SCRIPT_DIR         /ORA19/app/oracle/product/19.0.0/db_1/QOpatch                            0
SYS        OPATCH_LOG_DIR            /ORA19/app/oracle/product/19.0.0/db_1/rdbms/log                          0
SYS        JAVA$JOX$CUJS$DIRECTORY$  /ORA19/app/oracle/product/19.0.0/db_1/javavm/admin/                      0

16 rows selected.

8) 일자 별 schema 별로 자동 백업 받는 스크립트
현업에서 많이 사용하는 것으로 마이그레이션 작업과 같은 일을 할 때 AS-IS 서버에서 모든 schema 별로 datapump를 수행해서 TO-BE로 옮기게 됩니다. 이때 AS-IS 서버에 schema가 너무 많을 경우 일일이 수동으로 찾아서 하기는 힘들기 때문에 아래에서 소개하는 스크립트를 사용하면 작업이 많이 수월해 집니다.

스크립트의 내용은 다음과 같습니다. 특정 백업 디렉토리(/data/backup/expdp) 아래에 백업을 수행하는 년-월-일-시간 형식으로 디렉토리를 자동으로 만들고 그 아래에 schema 별로 dmp 파일과 log 파일을 생성합니다.

$ vi expdp_script.sh

export LANG=C
export ORACLE_BASE=/home/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 /home/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/1234'||' 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 /home/oracle/expdp.tmp | grep -v SQL | grep -v SYS > /home/oracle/expdp.sh
@/home/oracle/expdp.sh
exit
EOF3

:wq!

$ sh expdp_script.sh

...

$ cd /data/backup/expdp
$ ls

$ cd <생성된 폴더>
$ ls

7. 통계정보 이동하기

통계정보는 특정 segment에 대한 자세한 정보로 딕셔너리에 저장되어 있으면서 튜닝이나 실행계획 생성 등에 영향을 주는 아주 중요한 정보입니다.

실무에서 테이블 등의 데이터를 마이그레이션하면서 통계정보까지 함께 이전하는 경우가 종종 발생하는데, exp/imp에서 직접적으로 통계정보를 이동시킬 수 있는 방법은 없고, 통계정보를 저장할 임시 테이블을 생성한 후 그곳에 통계정보를 저장하고 해당 테이블을 exp/imp하는 방법을 사용하면 됩니다.

통계정보를 수집하기 위해서는 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 인덱스의 통계정보를 수집합니다.
-- 1. 통계정보 생성
SQL> exec DBMS_STATS.GATHER.SCHEMA_STATS('KHYUP');

-- 2. 통계정보를 저장할 임시 테이블 생성
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('KHYUP','KHYUP_STATS');

-- 3. 통계정보를 STEP 2에서 만든 임시 테이블로 EXP 수행
SQL> exec DBMS_STATS.EXPORT_SCHEMA_STATS('KHYUP','KHYUP_STATS');

-- 4. 통계정보를 저장하고 있는 임시 테이블을 EXP
SQL> !exp khyup/khyup tables=khyup_stats file=khyup_stats.dmp

-- 5. khyup_stats.dmp를 신규 서버로 이동

-- 6. 신규 서버에서 khyup_stats.dmp를 imp
SQL> drop table khyup_stats;

SQL> !imp khyup/khyup file=khyup.stats.dmp ignore=y

-- 7. imp된 통계정보를 딕셔너리로 입력
SQL> exec DBMS_STATS.IMPORT_SCHEMA_STATS('KHYUP','KHYUP_STATS');

8. Migration과 Character Set

AS-IS에서 TO-BE로 전환하면서 기껏 마이그레이션을 마쳤더니 글자가 다 깨져서 알아볼 수 없다면 정말 허무하겠죠. 이런 현상은 Character Set이 맞지 않아 발생하는 문제입니다.

Character Set이란 데이터베이스 내부에 데이터가 저장될 때 참조되는 글자의 종류를 적어 놓은 일종의 사전입니다. 사용자가 어떤 데이터를 Insert하면 Server Process는 Character Set을 참고해 글자를 확인하고 입력합니다. 만약 사용자가 입력한 글자가 Character Set에 없다면 Server Process는 무슨 글자인지 모릅니다.

Character Set은 데이터베이스를 생성할 때 지정하게 됩니다. 한글을 사용할 경우 일반적으로 KO16KSC5601, KO16MSWIN949, UTF8/AL32UTF8을 사용합니다. 만약 이 부분을 잘못 설정할 경우 데이터베이스를 다시 만들어야 할 수도 있는 만큼 주의해서 지정해야 합니다.

KO16KSC5601

한글 완성형 코드와 일치하며 가장 많이 사용되는 2350자의 한글, 4888자의 한자와 일본어인 히라가나, 카타카나, 그리고 영문 및 각종 기호들을 포함하고 있습니다. 이 Character Set은 제작된지 오래되었습니다.

KO16MSWIN949

KO16KSC5601을 모두 포함하고 있으며, 추가로 현대 한글 조합으로 표현할 수 있는 모든 글자수에 해당하는 8822자의 한글이 추가되었습니다. 이 Character Set을 사용하길 권장합니다.

UTF8/AL32UTF8

유니코드를 구현했으며 가변길이 인코딩 방식을 택하고 있습니다. 가변 길이를 위해 일종의 플래그 비트를 각 바이트 마다 포함시켜야 하다보니, 한 글자를 표현할 때 필요한 바이트의 길이가 최대 3byte(AL32UTF8의 경우 6byte)까지 늘어날 수 있습니다.

Subset과 Superset

글자를 더 많이 가지고 있는 것을 Superset이라고 합니다. 예를 들어, KO16MSWIN949는 KO16KSC5601의 글자를 모두 포함하고 있으므로, KO16MSWIN949는 Superset, KO16KSC5601는 Subset입니다. Subset -> Superset으로 데이터를 마이그레이션하면 문제가 없는데, 반대의 경우는 문제가 발생할 확률이 있습니다.

AS-ISTO-BE변경 가능 여부
US7ASCIIKO16KSC5601 / KO16MSWIN949 / UTF8 / AL32UTF8가능
KO16KSC5601KO16MSWIN949가능
KO16MSWIN949UTF8불가능
UTF8AL32UTF8가능

현재 서버에 설정되어 있는 Character Set의 정보는 아래와 같이 조회할 수 있습니다.

SQL> set line 200
SQL> set pages 30
SQL> col parameter for a30
SQL> col value for a30
SQL> select * from v$nls_parameters;

PARAMETER                      VALUE                              CON_ID
------------------------------ ------------------------------ ----------
NLS_LANGUAGE                   AMERICAN                                0
NLS_TERRITORY                  AMERICA                                 0
NLS_CURRENCY                   $                                       0
NLS_ISO_CURRENCY               AMERICA                                 0
NLS_NUMERIC_CHARACTERS         .,                                      0
NLS_CALENDAR                   GREGORIAN                               0
NLS_DATE_FORMAT                DD-MON-RR                               0
NLS_DATE_LANGUAGE              AMERICAN                                0
NLS_CHARACTERSET               KO16MSWIN949                            0
NLS_SORT                       BINARY                                  0
NLS_TIME_FORMAT                HH.MI.SSXFF AM                          0
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                0
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                      0
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR            0
NLS_DUAL_CURRENCY              $                                       0
NLS_NCHAR_CHARACTERSET         AL16UTF16                               0
NLS_COMP                       BINARY                                  0
NLS_LENGTH_SEMANTICS           BYTE                                    0
NLS_NCHAR_CONV_EXCP            FALSE                                   0

Unicode에 대해서

Coded Character Set(CCS)는 각각의 문자를 비트로 표현할 수 있는 정수값에 하나씩 할당하는 방식을 말하며 한국에서는 KSC-XXXX 또는 ISO-XXXX로 표현하는 Character Set이 대표적인 예입니다. 즉, 모든 문자에 고유한 정수 값이 할당되어 있다는 뜻입니다.

Character Encoding Schema(CES)는 각각의 문자를 16진수에 하나씩 할당하는 방식을 말하며 CCS 방식보다 더 많은 문자를 표현할 수 있다는 장점이 있습니다.

Unicode는 플랫폼, 프로그램, 언어에 상관없이 모든 문자에 대하여 고유 번호를 할당해서 만들어 놓은 코드를 말합니다. 이 방식은 모든 나라의 언어를 하나의 CCS로 정의해놓고, 이러한 CCS를 표현하는 여러 개의 CES가 존재하는 형태입니다. 모든 나라와 모든 언어를 고유번호로 할당해 놓았기 때문에 영역을 미리 정해놓았습니다. 한글 자모음은 1100~11FF에 240글자가 정의되어 있습니다. 한글 음절은 AC00~D7A3에 11172개의 완성형 한글이 가나다 순으로 정의되어 있습니다. 한자는 CJK 상형문자 영역인 4E00~9FFF에 정의되어 있습니다.

9. Character Set Scan (CSSCAN) Utility

CSSCAN이라는 툴은 현재 사용하고 있는 Character Set을 기준으로 변경하려는 Character Set으로 변경했을 경우를 가정하여 Data 손실 여부를 미리 테스트 할 수 있는 툴입니다.

csscan을 실행하기 전에 csscan이 수행될 때 필요한 정보들을 미리 생성해 주어야 합니다. 이런 작업을 해주는 스크립트가 $ORACLE_HOME/rdbms/admin/csminst.sql입니다.

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

준비가 끝나면 csscan을 수행합니다.

$ csscan system/1234 full=y tochar=UTF8

3개의 파일이 생성됩니다.

$ ls scan*
scan.err	scan.out	scan.txt

scan.txt

각 tablespace 별로 사용량, 크기, 작업 소요 시간 등의 정보를 볼 수 있습니다.
주의깊게 볼 부분은 Expansion 부분입니다. 기존 사이즈보다 변경 후 사이즈가 늘어난다면 Expansion 부분에 추가로 늘어나는 용량을 계산해서 보여줍니다. 만약 Expansion > Free 이라면 관리자가 수동으로 tablespace의 data file의 크기를 늘려줘야 에러가 발생하지 않습니다.

scan.out

csscan을 실행하는 동안의 output 파일이며, error 내용을 포함하고 있습니다.

scan.err

상세한 error 정보를 확인할 수 있습니다.

preconv.sql

관리자의 확인이 필요한 index, trigger, constraints를 disable 또는 삭제하는 SQL문입니다. 이 스크립트를 실행하기 전에 반드시 삭제 대상이 정말 올바른지 확인하고 실행해야 합니다.

postconv.sql

preconv.sql에서 disable된 trigger, constraints를 enable하는 SQL문 입니다. 다만 삭제된 index는 postconv.sql을 통해 재생성할 수 없습니다. 경우에 따라 삭제된 index를 다시 생성해야 할 경우가 있으므로 index 생성 script를 미리 백업 받아두는 것도 좋은 방법입니다.

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

  1. 원본 DB(AS-IS 환경)의 Object 개수를 확인합니다.
  2. 원본 DB에서 현재 invalid 상태인 Object 개수를 확인합니다.
    (마이그레이션 후 invalid 상태와 비교해서 원래 그런 것인지 아니면 마이그레이션이 잘못 되어서 그런 것인지를 판별하기 위해 하는 작업입니다)
  3. tablespace, user, role, system, synonym 스크립트를 생성합니다.
  4. 원본 DB의 Character Set을 확인합니다.
  5. exp/expdp로 마이그레이션할 데이터를 백업합니다.
$ exp system/1234 file=full_exp01.dmp log=full_exp01.log full=y buffer=408200000 recordlength=65535 statistics=none
  1. 신규 DB(TO-BE 환경)를 생성합니다.
    (유의사항 1: Character Set은 AS-IS와 같은 것 또는 Superset으로 설정
    유의사항 2: Standard Edition / Enterprise Edition을 확인합니다. SE -> EE로 넘어가는 부분은 문제가 발생하지 않지만, EE -> SE로 넘어갈 경우 EE 옵션을 사용하는지 확인이 필요합니다)
  2. 신규 DB에 tablespace, user, role, system 딕셔너리 정보를 생성합니다.
    (Step 3의 스크립트를 실행하면 됩니다. 그리고 신규 DB의 Tablespace 생성이 끝나면, 신규 DB의 Data File과 기존 DB의 Data File의 크기를 비교해서 확인해야 합니다)
  3. exp/expdp 받은 데이터를 imp/impdp 합니다.
$ imp system/1234 file=full_exp01.dmp FROMuser=username touser=username buffer=408200000 recordlength=65535 statistics=none log=import.log
  1. utlrp.sql 스크립트를 실행합니다.
    (utlrp.sql: Object 중에서 상태가 invalid(사용 못함) 상태인 것을 찾아서 재컴파일하고 valid(사용 가능) 상태로 만들어주는 역할을 합니다)
  2. 신규 DB의 Object 개수를 확인합니다.
    (Step 1의 스크립트를 이용합니다)
  3. 신규 DB의 invalid 개수를 확인합니다.
    (낮은 버전에서 높은 버전으로 마이그레이션 될 경우 낮은 버전에서 잘 되던 함수나 프로시저가 invalid 되는 경우가 있습니다. 이 경우 재컴파일을 하거나 해당 Object를 다시 생성해야 합니다. Step 2의 스크립트를 이용합니다)

참고

  • <오라클 관리 실무> - 서진수
profile
HYEOB KIM
Devops Engineer
이전 포스트

[Oracle] Oracle Client 설치 방법

다음 포스트

[Oracle] Oracle ASM

post-custom-banner

0개의 댓글