이번 포스팅 주제는 "Importing Dump Files into Oracle Database Using SQL Developer, Starting with Tablespaces and Users"입니다. 제가 얼마 전 SQL 공부를 위해 "평생 필요한 데이터 분석"이라는 제목의 책을 샀는데 실습을 위해서는 Oracle Database를 설치하고 책 저자님이 제공해 주시는 샘플 데이터를 import 해야 했습니다.
테이블스페이스와 사용자 계정을 생성하고 제공받은 덤프 파일을 리눅스에 설치한 Oracle DB에 IMPORT 해보겠습니다.
1.제공받은 dump 파일을 import 해야 할 때 사전 검토 사항
남으로부터 제공받은 dump 파일을 import 하려면 다음의 사항을 사전에 검토해야 합니다.
계정명, 테이블스페이스명, 테이블스페이스 용량
- 계정명은 반드시 일치해야 합니다.
- 테이블스페이스명은 일치하지 않아도 됩니다.
- 테이블스페이스 용량이 제공받은 dump 파일의 용량 이상이어야 합니다.
캐릭터 셋
- dump 파일의 캐릭터 셋과 대상 DB의 캐릭터 셋이 다를 경우 데이터가 깨질 수 있습니다.
- dump 파일의 캐릭터 셋을 사전에 체크하여 대상 DB의 캐릭터 셋으로 변경해야 합니다.
저의 경우, 제공받은 dump 파일의 캐릭터 셋이 AL32UTF이고 대상 DB의 캐릭터셋이 KO16MSWIN949라서 캐릭터셋 변경이 필요합니다. 덤프 파일의 확장자가 .dump이면 구형 유틸리티 exp/imp를 사용하였기 때문에 캐릭터셋이 일치해야 합니다. 확장자가 .dmp이면 DATA PUMP 유틸리티 expdp/impdp를 사용하였기 때문에 캐릭터셋이 일치하지 않아도 "possible data loss"경고만 뜨고 import는 가능합니다.
버추얼박스에서 AL32UTF8 캐릭터셋 DB를 빠르게 설치 후 그 DB에 제공받은 덤프 파일을 import 후 다시 data pump 유틸리티 expdp로 export 후 캐릭터셋이 KO16MSWIN949인 제 DB에 import 해보겠습니다. AL32UTF8 캐릭터 셋은 전 세계의 모든 언어를 표현할 수 있는 대신 한글도 1글자에 3바이트를 소비하여 비효율적입니다. KO16MSWIN949는 한글 1글자에 2바이트가 필요하고 영어도 표현되므로 아랍어, 러시아어, 중국어 같은 문자까지 저장해야 하는 DB가 아니면 대한민국에서는 KO16MSWIN949를 추천합니다.
2. exp/imp 와 expdp/impdp
exp/imp는 Oralce 8i 이전 버전부터 사용된 데이터 백업 및 복원 유틸리티입니다. exp/imp는 단일 프로세스로 데이터를 export 및 import 하므로, 대규모 데이터베이스의 경우 시간이 오래 걸리고 자원 사용량이 많다는 단점이 있습니다.
expdp/impdp는 Oracle 10g부터 도입된 데이터 백업 및 복원 유틸리티입니다. expdp/impdp는 다중 프로세스로 데이터를 export 및 import하므로 exp/imp에 비해 성능이 우수하고 자원 사용량이 적습니다. 특히 expdp/impdp는 데이터베이스 전체를 export 하거나 import 할 수 있으며 데이터 압축 및 암호화 기능을 제공합니다.
3. 테이블스페이스, 계정 생성
제공받은 dump 파일의 계정명은 "MYSTKDB"이고 대상 DB의 "DATA_DIR=/oradata/NOYEORA/"입니다.
다음 참고 화면과 같이 SQL Developer를 통해 DB 서버에 접속합니다.
워크시트에서 아래 스크립트를 참고하여 테이블스페이스와 계정을 생성하고 권한을 부여합니다.
# 테이블 스페이스 생성
create tablespace ts_mystkdb
datafile '/oradata/NOYEORA/mystkdb01.dbf'
size 10G autoextend on next 500M;
# 정상출력: Tablespace TS_MYSTKDB이(가) 생성되었습니다.
# 임시 테이블 스페이스 생성
create temporary tablespace ts_mystkdb_tmp
tempfile '/oradata/NOYEORA/mystkdb_tmp01.dbf'
size 10G autoextend on next 500M;
# 정상출력: Tablespace TS_MYSTKDB_TMP이(가) 생성되었습니다.
# 계정 생성 및 테이블 스페이스 할당
create user mystkdb identified by "mystkdb"
default tablespace ts_mystkdb
temporary tablespace ts_mystkdb_tmp;
# 정상출력: User MYSTKDB이(가) 생성되었습니다.
# 계정 속성 설정
alter user mystkdb default tablespace ts_mystkdb quota unlimited on ts_mystkdb;
grant create view to mystkdb;
alter user mystkdb account unlock;
grant connect, resource to mystkdb;
# 정상출력
# User MYSTKDB이(가) 변경되었습니다.
# Grant을(를) 성공했습니다.
# User MYSTKDB이(가) 변경되었습니다.
# Grant을(를) 성공했습니다.SQL Developer의 메뉴에서 "보기-DBA-접속-저장 영역-테이블스페이스"를 따라가서 테이블스페이스가 정상적으로 생성되었는지 확인할 수 있습니다.
4. legacy export 파일(.dump)을 datapump export 파일(.dmp)로 바꾸기
제공받은 덤프파일의 캐릭터 셋이 AL32UTF8이고 import 대상 DB의 캐릭터셋이 KO16MSWIN949인데 legacy exp로 생성한 덤프 파일이 때문에 impdp를 사용할 수 없으므로 버추얼박스를 활용하여 AL32UTF8 DB를 빠르게 설치 후 expdp로 덤프파일을 재생성합니다.
캐릭터 셋 확인하기
# 서버의 캐릭터셋 확인명령
SQL> select * from nls_database_parameters where parameter like '%CHAR%';
# 출력 중 NLS_CHARACTERSET 값 확인 - AL32UTF8
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE
NLS_NUMERIC_CHARACTERS
.,
NLS_NCHAR_CHARACTERSET
AL16UTF16
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8
SQL>
imp 명령으로 import 하기
imp 명령을 bash 쉘에서 oracle 계정으로 실행합니다.
imp mystkdb/mystkdb@noyeora file=MYSTKDB_20220628_Oracle.dump
# 위 명령 실행시 아래와 같이
# Import terminated successfully without warnings. 나오면 정상
Import: Release 19.0.0.0.0 - Production on Tue Dec 19 11:15:35 2023
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
Version 19.3.0.0.0
Export file created by EXPORT:V19.00.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses KO16MSWIN949 character set (possible charset conversion)
. importing MYSTKDB's objects into MYSTKDB
. . importing table "BASECODE" 6 rows imported
. . importing table "BASECODE_DV" 2 rows imported
. . importing table "FINANCE_Y" 34334 rows imported
. . importing table "HISTORY_DT" 1644248 rows imported
. . importing table "HISTORY_YM" 271003 rows imported
. . importing table "STOCK" 2387 rows imported
Import terminated successfully without warnings.
expdp 명령으로 export 하기
impdp/expdp 명령은 "dba_directories"에서 수행해야 하므로 "dba_directories"를 하나 생성해 줍니다.
bash 쉘에서 "mkdir -p /home/oracle/datapump_dir"를 실행하여 디렉터리 생성 후 SQL 쉘에서 다음 명령을 수행합니다.
# expdp/impdp 용도로 사용할 디렉토리 생성
SQL> create directory datapump_dir as '/home/oracle/datapump_dir';
# 디렉토리 사용할 유저 계정에 읽기/쓰기 권한 부여
SQL> grant read, write on directory datapump_dir to mystkdb;
# 생성된 디렉토리 확인, directory_name은 대문자여야 함
SQL> select directory_name, directory_path from dba_directories
where directory_name='DATAPUMP_DIR';
SQL>
bash 쉘에서 다음의 expdp 구문을 실행합니다.
# 문법 expdp [계정명]/[계정비번] dumpfile=[생성할덤프파일명] directory=[생성한 디렉토리명]
expdp mystkdb/mystkdb dumpfile=mystkdb.dmp directory=datapump_dir
5. impdp로 대상 DB에 import 하기
위 단계에서 생성된 .dmp 파일을 대상 DB 서버에 업로드합니다.
그리고 위 단계와 동일하게 "datapump_dir"을 생성해 둡니다.
저는 파일질라를 사용하여 SFTP로 업로드하였습니다.
impdp 명령을 실행합니다.
[oracle@noyeora ~]$ ls /oracle/app/oracle/oradata/datapump
export.log import.log MYSTKDB_20220628_Oracle.dump mystkdb.dmp
# 문법: impdp [계정명]/[계정비번] dumpfile=[덤프파일명] directory=디렉토리명
[oracle@noyeora ~]$ impdp mystkdb/mystkdb dumpfile=mystkdb.dmp directory=datapump_dir
Import: Release 19.0.0.0.0 - Production on Tue Dec 19 13:14:16 2023
Version 19.21.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
Master table "MYSTKDB"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "MYSTKDB"."SYS_IMPORT_FULL_01": mystkdb/******** dumpfile=mystkdb.dmp directory=prod_dir
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MYSTKDB"."HISTORY_DT" 164.6 MB 1644248 rows
. . imported "MYSTKDB"."HISTORY_YM" 10.38 MB 271003 rows
. . imported "MYSTKDB"."FINANCE_Y" 3.026 MB 34334 rows
. . imported "MYSTKDB"."STOCK" 111.0 KB 2387 rows
. . imported "MYSTKDB"."BASECODE" 6.507 KB 6 rows
. . imported "MYSTKDB"."BASECODE_DV" 5.546 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
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
Job "MYSTKDB"."SYS_IMPORT_FULL_01" successfully completed at Tue Dec 19 13:14:41 2023 elapsed 0 00:00:24
[oracle@noyeora ~]$
impdp 로그를 보면 export 캐릭터 셋이 AL32UTF8이고 import 캐릭터셋이 KO16MSWIN949임을 확인할 수 있으며 캐릭터셋 변환으로 인해 데이터 손실이 발생할 수 있다는 경고문이 있습니다. 어쨌든 성공했으니 실제 데이터를 확인해 보면 됩니다.
테이블 조회 결과 한글이 깨지지 않고 정상적으로 표기되고 있습니다.
이상으로 AL32UTF8 캐릭터셋에서 legacy export 덤프파일을 버추얼박스로 짧은 시간에 구축한 AL32UTF8 DB에 import 후 다시 expdp로 data pump 수행 후 KO16WIN949 환경에 impdp를 수행해 보았습니다.
관련 포스팅 링크를 첨부합니다.
버추얼박스에 Oracle 19c 설치하기:
2023.12.06 - [Database/Oracle] - linux에 oracle 19c 설치
'Database > Oracle' 카테고리의 다른 글
| [SQL] SELECT SQL 예제 (0) | 2023.12.20 |
|---|---|
| [Oracle] 계정 관리 테이블 스페이스 관리 (0) | 2023.12.16 |
| 오라클 데이터베이스 시작 및 종료 방법 (0) | 2023.12.15 |
| SQL Developer 설치 및 기본 설정 (0) | 2023.12.08 |
| oracle OPatch Release Update(RU) Patch 적용 (0) | 2023.12.07 |