ORACLE

Oracle Character Set 변환(8): 6. 사용자 구현 Character Set 변환 방법 (1)

게시됨 2022년 10월 03일 · 업데이트됨 2022년 10월 05일

사용자 구현 Character Set 변환 방법 에 대해 살펴본다. Oracle Utitlity인 Data Pump Export, Import를 활용하여 KO16MSWIN949, US7ASCII에서 AL32UTF8로 변환하는 방법을 확인할 수 있다.

별도 구현이 필요한 방법은 Oracle에서 변환을 보장하지 않고 구현한 사용자가 변환을 보장해야 하므로 기술 검증과 다양한 케이스에 대하여 테스트를 거쳐 정확성을 확인해야 한다.

이전 글에서 이어지는 내용이다.

Oracle Character Set 변환(7): 5.4. KO16MSWIN949 환경 CSSCAN 실행 결과

<<목차>>

6. 사용자 구현 Character Set 변환 방법

6.1. Third party tool 적용 방법 – ETL 등

시장에 잘 알려진 ETL tool(예: Informatica PowerCenter, IBM DataStage 등)을 활용하여 Character Set 변환을 구현하는 방법이다.

내게는 직접 경험이 없는 방법이고 가능하다는 것만 알고 있어 자세한 방법을 기술하지는 않는다.

다만, 아래 고려사항에 대해서만 검토하기 바란다.

각 제품은 구매에 따른 비용이 발생하거나, 기 구매되어 있더라도 개발 인건비가 추가적으로 필요함
Open Source인 TOS(Talend Open Studio)-DI, Pentaho Kettle 등도 고려해 볼 수 있음
Source DB의 Data를 file로 write하고 Target DB에 file을 읽어서 insert하는 방식일 경우, column 구분자와 row 구분자가 데이터 value에 포함되어 한 column씩 밀리거나 한 row가 여러 row로 잘못 저장되는 등의 오류 처리가 반드시 필요함.
초기 Tool 선정시 대용량 테이블을 대상으로 pilot 테스트를 진행하여 적합한 Tool을 선정해야 하며, 이와 함께 경험이 많은 ETL 개발 인력 확보가 중요함

6.2. Oracle Utility 활용 방법

Oracle 기능과 Utility를 활용하여 Character Set 변환을 구현하는 세 가지 방법을 살펴본다

1. Data Pump Export/Import 활용

강제로 Character Set 변경: ALTER DATABASE CHARACTER SET INTERNAL
Export/Import 실행: Target Character Set과 동일한 NLS_LANG 적용

2. SQL*Loader 활용

Source DB에서 Data unload: SQL*Loader 에서 사용 가능한 포맷, PL/SQL, Pro*C, Java등으로 개발 필요
Target DB에 SQL*Loader로 데이터 적재

3. DB Link & CTAS & UTL_RAW 활용
(별도 글 참조: Oracle Character Set 변환(9): 6. 사용자 구현 Character Set 변환 방법 (2))

DB Link 생성: Target에서 Source를 가리키는 DB Link 생성
View 생성: Source에서 CHAR, VARCHAR2컬럼을 UTL_RAW.CAST_TO_RAW로 변환한 View 생성
CTAS로 데이터 적재: Target에서 DB Link로 CTAS(Create Table As Select)를 실행하면서 UTL_RAW.CAST_TO_VARCHAR2와 CONVERT 함수 적용

각 방법의 장점과 단점에 대해 요약하면 다음과 같다.

방법	장점	단점
1. Data Pump Export/Import	– 병렬처리 가능 – 개행문자, 특수문자, 널문자 등의 예외처리 불필요	– 데이터 unload 시간 필요 – 원본 DB가 US7ASCII인 경우 . Character Set 변경 필요 (원본 보존 필요) . CLOB type 데이터의 추가변환 필요
2. SQL*Loader	– 병렬처리 가능	– Data file을 생성(unload)하는 기능 개발 필요 – unload/load의 File 처리시 발생하는 예외/오류처리 필요 (개행문자, 특수문자, 널문자 등) – 데이터 unload 시간 필요
3. DB Link & CTAS & UTL_RAW	– 데이터 unload 시간 불필요	– Parallel hint로 병렬 처리 어려움 (데이터 범위를 직접 분할하여 처리해야 함) – 2000 byte를 초과하는 데이터에는 적용할 수 없음(RAW type의 최대길이가 2000 byte임)

이 방법들을 검토하면서 다음의 고려사항도 함께 검토하기 바란다.

Data Pump Export/Import 방법을 활용하여 US7ASCII to AL32UTF8로의 변환 시, <ALTER DATABASE CHARACTER SET> DDL로 원본 DB의 Character Set을 변경해야 함
- 원본 DB를 직접 변경하는 것은 권고하지 않음. 잘못되는 경우 돌이키기가 매우 어려울 수 있음.
- 장애복구용 DB가 이미 구성되어 있다면 이를 활용하거나 또는 별도 복제 DB를 구성하여 변경하는 것이 바람직함
- 또는 원본 DB의 백업 데이터를 별도의 서버에 복구하여 이를 이용하는 방법도 가능함.
- 이외에도 복제DB를 구성하는 여러가지 방법(Disk 복제 등)을 검토하고, 비용과 시간을 고려하여 방법을 선택하는 것이 바람직함.
- 복제 DB를 구성하는 것이 어려울 경우, Full backup이후 진행할 수 있으나 복구 필요시 데이터 양에 따라 서비스 Down-Time이 길어질 수 있음
서비스 중지상태가 아닌 이상 어떤 방법을 적용하더라도 데이터 정합성 확보를 위하여 원본 DB로부터의 추출시점을 일치시켜야 함(FLASHBACK_SCN, FLASHBACK_TIME 활용)
또한, 추출시점 이후의 변경 데이터 반영 방법을 미리 테스트하고 준비해야 함 (CDC 활용 등)

6.2.1. 방법1) Data Pump Export/Import

준비: 다음 script로 Data Pump Export/Import 실행시 필요한 Directory 개체 생성과 권한을 부여한다.

테스트 대상 Oracle instance인 ORAUS7, ORAMSWIN949, ORAUTF 각각에 대하여 실행한다.

▼ ORAUS7 instance에 권한 부여

-- ORAUS7 instance에 권한 부여
title ORAUS7
set NLS_LANG=AMERICAN_AMERICA.US7ASCII
sqlplus sys/________@oraus7 as sysdba
CREATE OR REPLACE DIRECTORY PUMP_DIR AS 'D:\Temp\datapump';
GRANT READ, WRITE ON DIRECTORY PUMP_DIR TO LEG;
EXIT

▼ ORAMSWIN949 instance에 권한 부여

-- ORAMSWIN949 instance에 권한 부여
title ORAMSWIN949
set NLS_LANG=AMERICAN_AMERICA.KO16MSWIN949
sqlplus sys/________@oramswin949 as sysdba
CREATE OR REPLACE DIRECTORY PUMP_DIR AS 'D:\Temp\datapump';
GRANT READ, WRITE ON DIRECTORY PUMP_DIR TO LEG;
EXIT

▼ ORAUTF instance에 권한 부여

-- ORAUTF instance에 권한 부여
title ORAUTF
set NLS_LANG=AMERICAN_AMERICA.KO16MSWIN949
sqlplus sys/________@orautf as sysdba
CREATE OR REPLACE DIRECTORY PUMP_DIR AS 'D:\Temp\datapump';
GRANT READ, WRITE ON DIRECTORY PUMP_DIR TO LEG;
EXIT

다음 세 가지 경우에 대하여 각각 살펴본다.

KO16MSWIN949 to AL32UTF8
US7ASCII to AL32UTF8 (2 path)
US7ASCII to AL32UTF8 (1 path)

참고로, US7ASCII to AL32UTF8 변환의 경우는 CLOB type 컬럼이 정상적으로 변환되지 않아 별도 처리가 필요하다.

다음 글에서 설명 예정이다.

1. KO16MSWIN949 to AL32UTF8

이 경우는 CHAR, VARCHAR2, CLOB type이 모두 정상적으로 변환된다.

각 단계별 실행 스크립트는 다음과 같다.

--------------------------------------------------------------------
-- 단계1
-- 실행 Instance: ORAMSWIN949
-- NLS_LANG: KO16MSWIN949
-- 작업명: Datapump Export
-- Tool: expdp

set NLS_LANG=AMERICAN_AMERICA.KO16MSWIN949
expdp leg/leg@oramswin949 directory=PUMP_DIR dumpfile=oramswin949_leg.dmp logfile=oramswin949_leg_exp.log schemas=leg


--------------------------------------------------------------------
-- 단계2
-- 실행 Instance: ORAUTF
-- NLS_LANG: KO16MSWIN949
-- 작업명: Table 생성
-- Tool: SQL*Plus

-- Target table을 미리 생성
-- LINE_NUM, SUB_STA_NM 컬럼 길이 증가
-- COMMT 컬럼 데이터 타입 변경(VARCHAR2 -> CLOB)
CREATE TABLE SUB_MON_STAT 
(
     USE_MON            VARCHAR2(8),
     LINE_NUM           VARCHAR2(50),  -- 14 -> 50
     SUB_STA_ID         VARCHAR2(4),
     SUB_STA_NM         VARCHAR2(100), -- 20 -> 100
     RIDE_PASGR_NUM     NUMBER(10),
     ALIGHT_PASGR_NUM   NUMBER(10),
     WORK_DT            VARCHAR2(8),
     COMMT              CLOB,          -- VARCHAR2 -> CLOB
     REF_DES            CLOB,
     REF_IMG            BLOB
);


--------------------------------------------------------------------
-- 단계3
-- 실행 Instance: ORAUTF
-- NLS_LANG: KO16MSWIN949
-- 작업명: Datapump Import
-- Tool: impdp
set NLS_LANG=AMERICAN_AMERICA.KO16MSWIN949
impdp leg/leg@orautf directory=PUMP_DIR dumpfile= oramswin949_leg.dmp logfile=orautf_leg_imp.log content=data_only

2. US7ASCII to AL32UTF8 (2 path)

다음 내용을 참고한다.

임시 복제 DB서버는 As-Is 운영 DB를 원본과 동일한 형태로 복제한 DB임.
테스트 환경에서는 ORAMSWIN949 instance를 임시 복제 DB로 사용했고, Data Pump로 데이터를 복제하는데, 이 경우 복제하기 전에 미리 Character Set을 US7ASCII로 변경해 두어야 함
실 적용시는 임시 복제 DB서버를 US7ASCII Character Set으로 별도 구성하여 사용하는 것이 바람직함.

이 방법은 ORACLE에서 권장하는 방법이 아니고 CLOB type의 데이터는 정상 변환되지 않기 때문에 별도 변환 처리가 필요하다.

각 단계별 실행 스크립트는 다음과 같다.

--------------------------------------------------------------------
-- 단계1-1
-- 실행 Instance: ORAUS7
-- NLS_LANG: US7ASCII
-- 작업명: Datapump Export
-- Tool: expdp
set NLS_LANG=US7ASCII
expdp leg/leg@oraus7 directory=MIG_DIR dumpfile=oraus7_leg_exp.dmp logfile=oraus7_leg_exp.log schemas=leg


--------------------------------------------------------------------
-- 단계1-2
-- 실행 Instance: ORAMSWIN949
-- NLS_LANG: US7ASCII
-- 작업명: Datapump Import
-- Tool: impdp
set NLS_LANG=US7ASCII
impdp leg/leg@oramswin949 directory= MIG_DIR dumpfile=oramswin949_leg_exp.dmp logfile=oramswin949_leg_imp.log


--------------------------------------------------------------------
-- 단계2
-- 실행 Instance: ORAMSWIN949
-- NLS_LANG: US7ASCII
-- 작업명: Character Set 변경
-- Tool: SQL*Plus
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE KO16MSWIN949;
SHUTDOWN IMMEDIATE;
STARTUP;


--------------------------------------------------------------------
-- 단계3
-- 실행 Instance: ORAMSWIN949
-- NLS_LANG: KO16MSWIN949
-- 작업명: Datapump Export
-- Tool: expdp
-- sql*plus에서 한글 표시 되는지 확인 후 다음 실행
set NLS_LANG=KO16MSWIN949
expdp leg/leg@oramswin949 directory=MIG_DIR dumpfile=oramswin949_leg_exp.dmp logfile=oramswin949_leg_exp.log schemas=leg


--------------------------------------------------------------------
-- 단계4
-- 실행 Instance: ORAUTF
-- NLS_LANG: AL32UTF8
-- 작업명: Table 생성
-- Tool: SQL*Plus

-- Target table을 미리 생성
-- LINE_NUM, SUB_STA_NM 컬럼 길이 증가
-- COMMT 컬럼 데이터 타입 변경(VARCHAR2 -> CLOB)
CREATE TABLE SUB_MON_STAT 
(
     USE_MON            VARCHAR2(8),
     LINE_NUM           VARCHAR2(50),  -- 14 -> 50
     SUB_STA_ID         VARCHAR2(4),
     SUB_STA_NM         VARCHAR2(100), -- 20 -> 100
     RIDE_PASGR_NUM     NUMBER(10),
     ALIGHT_PASGR_NUM   NUMBER(10),
     WORK_DT            VARCHAR2(8),
     COMMT              CLOB,          -- VARCHAR2 -> CLOB
     REF_DES            CLOB,
     REF_IMG            BLOB
);


--------------------------------------------------------------------
-- 단계5
-- 실행 Instance: ORAUTF
-- NLS_LANG: AL32UTF8
-- 작업명: Datapump Import
-- Tool: impdp
impdp leg/leg@orautf directory=MIG_DIR dumpfile=oramswin949_leg_exp.dmp logfile=oramswin949_leg_imp.log content=data_only

3. US7ASCII to AL32UTF8 (1 path)

이 방법은 ORACLE에서 권장하는 방법이 아니고 CLOB type의 데이터는 정상 변환되지 않기 때문에 별도 변환 처리가 필요하다.

또한, 운영 DB의 Character Set을 변경하므로 원본 데이터를 보존할 수 없어서 잘못되는 경우에 Roll-back이 어려우니, 가급적 운영환경에 적용하지 않아야 한다.

부득이하게 이 방법을 사용하는 경우 테스트 환경에서 충분히 검증후 운영환경에 적용하기 바란다.

각 단계별 실행 스크립트는 다음과 같다.

--------------------------------------------------------------------
-- 단계1
-- 실행 Instance: ORAUS7
-- NLS_LANG: US7ASCII
-- 작업명: Character Set 변경
-- Tool: SQL*Plus
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE KO16MSWIN949;
SHUTDOWN IMMEDIATE;
STARTUP;


--------------------------------------------------------------------
-- 단계2
-- 실행 Instance: ORAMSWIN949(=ORAUS7)
-- NLS_LANG: KO16MSWIN949
-- 작업명: Datapump Export
-- Tool: expdp
-- sql*plus에서 한글 표시 되는지 확인 후 다음 실행
set NLS_LANG=KO16MSWIN949
expdp leg/leg@oramswin949 directory=MIG_DIR dumpfile=oramswin949_leg_exp.dmp logfile=oramswin949_leg_exp.log schemas=leg


--------------------------------------------------------------------
-- 단계3
-- 실행 Instance: ORAUTF
-- NLS_LANG: AL32UTF8
-- 작업명: Table 생성
-- Tool: SQL*Plus

-- Target table을 미리 생성
-- LINE_NUM, SUB_STA_NM 컬럼 길이 증가
-- COMMT 컬럼 데이터 타입 변경(VARCHAR2 -> CLOB)
CREATE TABLE SUB_MON_STAT 
(
     USE_MON            VARCHAR2(8),
     LINE_NUM           VARCHAR2(50),  -- 14 -> 50
     SUB_STA_ID         VARCHAR2(4),
     SUB_STA_NM         VARCHAR2(100), -- 20 -> 100
     RIDE_PASGR_NUM     NUMBER(10),
     ALIGHT_PASGR_NUM   NUMBER(10),
     WORK_DT            VARCHAR2(8),
     COMMT              CLOB,          -- VARCHAR2 -> CLOB
     REF_DES            CLOB,
     REF_IMG            BLOB
);


--------------------------------------------------------------------
-- 단계4
-- 실행 Instance: ORAUTF
-- NLS_LANG: AL32UTF8
-- 작업명: Datapump Import
-- Tool: impdp
impdp leg/leg@orautf directory=MIG_DIR dumpfile=oramswin949_leg_exp.dmp logfile=oramswin949_leg_imp.log content=data_only

6.2.2. 방법2) SQL*Loader

SQL*Loader는 control file과 data file이 필요하다. 이 중에서 data file이 실제 데이터를 포함한다.

이 data file을 생성하는 방법은 PL/SQL, Pro*C, Java 등 여러 Tool을 사용하여 직접 만들거나, 동일한 기능을 이미 구현해 놓은 소스코드를 참조하여 그대로 쓰거나 약간 확장하는 방법이 있다.

여기서는 직접 만드는 방법은 소개하지 않고, 구현해 놓은 소스코드의 URL만 소개한다.

Effective Oracle의 저자인 Tom Kyte 블로그: https://asktom.oracle.com/Misc/httpasktomoraclecomtkyteflat.html

이외에도, 구글에서 검색하면 참조할 수 있는 소스코드를 구할 수 있을 것이다.

테이블 단위나 파티션 단위로 data file을 생성한 다음, sqlldr를 다수의 그룹으로 동시에 실행하여 병렬로 적재하여 적정 수준의 부하로 실행시간을 최소화할 수 있도록 한다.

태그: Export Import AL32UTF8 ORACLE Character Set 변환 Character Set Conversion US7ASCII KO16MSWIN949 Data Pump

Oracle Character Set 변환(8): 6. 사용자 구현 Character Set 변환 방법 (1)

6. 사용자 구현 Character Set 변환 방법

6.1. Third party tool 적용 방법 – ETL 등

6.2. Oracle Utility 활용 방법

6.2.1. 방법1) Data Pump Export/Import

6.2.2. 방법2) SQL*Loader

답글 남기기 응답 취소

🔔 카테고리

📌 최근 글

⭐ 인기글/댓글/태그

Oracle Character Set 변환(8): 6. 사용자 구현 Character Set 변환 방법 (1)

6. 사용자 구현 Character Set 변환 방법

6.1. Third party tool 적용 방법 – ETL 등

6.2. Oracle Utility 활용 방법

6.2.1. 방법1) Data Pump Export/Import

6.2.2. 방법2) SQL*Loader

관련 글:

답글 남기기 응답 취소

🔔 카테고리

📌 최근 글

⭐ 인기글/댓글/태그