(Free Tool)Oracle Database Table 데이터를 Oracle용 또는 PostgreSQL용 Insert문으로 출력하기

업무용 시스템 개발 때 데이터베이스 데이터를 파일로 출력하거나 다른 데이터베이스로 옮기기 위한 기존 소프트웨어 Tool을 직접 작성해 보았습니다. 이 글에서는 Data Export/Import 툴 (Exp2Dml.exe)의 다운로드, 설치, 사용 방법 등에 대해서 소개합니다.

Exp2Dml.exe에 대해서…

“기존 Oracle Server에서 신규 Oracle Server로”, “기존 Oracle Server에서 신규 PostgreSQL Server로” 데이터를 복제하기 위한 Exp2Dm.exe 툴을 만들어 보았습니다.

다음과 같이 기존 서버에서 다른 신규 서버로 데이터를 복사하기 위해 기존 Oracle Server의 데이터를 SQL Insert문으로 출력해주는 소프트웨어입니다.

• 기존 Oracle Server -> Oracle DML(SQL Insert문) -> 신규 Oracle Server
• 기존 Oracle Server -> PostgreSQL DML(SQL Insert문) -> 신규 PostgreSQL Server

이 툴은 기존 Oracle Server의 모든 Table 데이터를 SQL Insert문으로 출력할 수 있습니다. 출력 파일은 환경 파일 설정 값에 따라 신규 Oracle Server에 다시 로드할 수 있는 Oracle DML(SQL Insert문)과 신규 PostgreSQL Server에 로드할 수 있는 PostgreSQL DML(SQL Insert문)로 만들어집니다.

Exp2Dml.exe 소프트웨어 다운로드

1. 아래의 [지금 다운로드] 버튼을 눌러 exp_ora2dml_until_YYYYMMDD.zi_를 다운로드 합니다. 파일은 github에 등록되어 있습니다. YYYYMMDD는 사용기한을 의미합니다. 사용기한이 지난 경우는 다시 최신버전을 다운로드 합니다.

설치하기

2. exp_ora2dml_until_YYYYMDD.zi_를 exp_ora2dml_until_YYYYMMDD.zip으로 확장자를 변경합니다.
3. exp_ora2dml_until_YYYYMMDD.zip 압축 파일에 대해 다음과 같이 압축 풀기를 실행합니다.

[파일 선택 -> 오른쪽 마우스 버튼 클릭 -> 컨텍스트 메뉴(context menu)의 압축 풀기(T)…]를 실행합니다.

다음과 같이 압축 풀기 다이얼로그 팝업창을 표시합니다. 그리고 저장 폴더를 C:\exp_ora2dml 또는 D:\exp_ora2dml 로 변경한 후 [압축 풀기(E)]를 선택합니다. 만약 D드라이브가 존재한다면 다음과 같이 D:\exp_ora2dml폴더에 배치하는 것을 추천합니다.

4. D:\exp_ora2dml 폴더에 프로그램이 배치되었음을 확인합니다.

D:\exp_ora2dml
├─bin
│      exp2dml.exe
│      exp2dml.ini
├─instantclient_12_2
│  │  adrci.exe
│  │  adrci.sym
... 중간 생략 ...
│  │  xstreams.jar
│  └─vc14
│          oraocci12.dll
│          oraocci12.sym
│          oraocci12d.dll
│          oraocci12d.sym
├─oradml
│      01make.bat
│      02load.bat
│      exp2dml.ini
│      setenv.bat
├─output
│      yyyymmdd_hhmiss_dml_output.sql
└─pgdml
        01make.bat
        02load.bat
        exp2dml.ini
        setenv.bat

주의) 설치 직후에는 instantclient_12_2 디렉토리가 존재하지 않습니다. 아래의 Oracle 다운로드 사이트에 방문해서 instantclient-basic-windows.x64-12.2.0.1.0.zip을 다운로드 해서 압축을 풉니다. 그리고 압축을 푼 instantclient_12_2 디렉토리를 위의 D:\exp_ora2dml 디렉토리 안에 배치합니다.

Instant Client for Microsoft Windows (x64) 64-bit

Instant Client downloads for Microsoft Windows (x64) 64-bit

www.oracle.com

이 글에서는 다음 버전의 instantclient를 다운로드 했습니다.

주의) The 12.2 Basic Light package requires the Microsoft Visual Studio 2013 Redistributable.

실행하기(데이터 출력하기)

5. 우선 Oracle DML을 만드는 환경 파일 또는 PostgreSQL DML을 만드는 환경 파일을 수정합니다. 설치 시의 기본값은 다음과 같습니다.

Oracle DML을 만드는 환경파일 D:\exp_ora2dml\oradml\exp2dml.ini

[DEFAULT]
oracle_instantclient_dir = ..\instantclient_12_2
dbuser = scott
dbpass = tiger
dbconn = 192.168.220.145/orcl
#filter_where = and object_name like 'E%%'
filter_where =
# 1:Oracle, 2:PostgreSQL
output_dml_dbkind = 1

PostgreSQL DML을 만드는 환경 파일 D:\exp_ora2dml\pgdml\exp2dml.ini

[DEFAULT]
oracle_instantclient_dir = ..\instantclient_12_2
dbuser = scott
dbpass = tiger
dbconn = 192.168.220.145/orcl
#filter_where = and object_name like 'E%%'
filter_where =
# 1:Oracle, 2:PostgreSQL
output_dml_dbkind = 2

각 환경 변수의 설명을 다음과 같습니다.

환경	설명
oracle_instantclient_dir	makedml.exe은 Oracle instantclient의 라이브러리 파일을 필요로 합니다. Oracle 사이트에서 다운로드한 instantclient-basic-windows.x64-12.2.0.1.0.zip에서 압축 해제한 instantclient_12_2 디렉토리의 패스를 지정합니다.
dbuser	Oracle 접속 스키마(유저) 정보를 지정합니다.
dbpass	Oracle 접속 패스워드를 지정합니다.
dbconn	Oracle 접속 서버 IP Address와 SID 정보를 지정합니다.
filter_where	지정하지 않으면 스키마(유저)의 모든 테이블이 대상이 됩니다. 대상을 줄이고 싶다면 and로 시작하는 조건문을 지정합니다.
output_dml_dbkind	1을 지정하면 Oracle Database에서 실행할 수 있는 형태로 출력합니다. 2를 지정하면 PostgreSQL Database에서 실행할 수 있는 형태로 출력합니다.

6. 명령 프롬프트에서 다음 내용의 Oracle DML 또는 PostgreSQL DML을 만드는 배치 파일을 실행합니다. 실행 시의 log 결과는 01make_yyyymmdd_hhmiss.log로 출력됩니다.

Oracle SQL문으로 출력	D:\exp_ora2dml\oradml\01make.bat
PostgreSQL문으로 출력	D:\exp_ora2dml\pgdml\01make.bat

배치 파일 내용은 양쪽 모두 다음과 같습니다.

@echo off
cd %~dp0
set CUR_PATH=%~dp0
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
set FMTDATE=%date:~0,4%%date:~5,2%%date:~8,2%_%CUR_HH%%time:~3,2%%time:~6,2%
copy .\exp2dml.ini ..\bin\exp2dml.ini
cd ..\bin
echo "make dml processing..."
exp2dml.exe > %CUR_PATH%01make_%FMTDATE%.log 2>&1
notepad %CUR_PATH%01make_%FMTDATE%.log

다음과 같은 메시지가 표시되면 [추가 정보]를 누릅니다.

다음과 같이 앱 정보와 [실행] 버튼이 표시되면 [실행] 버튼을 누릅니다.

exp2dml.exe이 실행되면 D:\exp_ora2dml\output\ 디렉토리에 다음과 같은 pgdml_output.sql 파일 또는 oradml_output.sql 파일이 출력 됩니다. 이 파일은 위 환경 파일 exp2dml.ini에서 지정한 output_dml_dbkind 값에 따라 Oracle버전 sql문 또는 PostgreSQL버전 sql문으로 출력합니다.

-- ******************************************************
-- read [exp2dml.ini] file and display value
-- ******************************************************
-- oracle_instantclient_dir = ..\instantclient_12_2
-- dbuser = scott
-- dbpass = tiger
-- dbconn = 192.168.220.145/orcl
-- filter_where = 
-- output_dml_dbkind = 2

-- *****************************************************
-- Successfully connected to Oracle Database
-- *****************************************************
-- select object_name from user_objects where object_type = 'TABLE' 


-- select * from DEPT
--     DEPTNO                                   NOT NULL NUMBER(2,0) 
--     DNAME                                             VARCHAR(14) 
--     LOC                                               VARCHAR(13) 

begin;
truncate table DEPT;
end;

begin;
insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
commit;
... 이하 생략 ...

참고로 주황색의 begin; end; 명령이 존재하면 PostgreSQL버전의 sql문으로 출력 했음을 알 수 있습니다.

실행하기(데이터 로드하기)

7. 다음과 같은 setenv.bat 환경 파일을 열어 데이터 로드 대상의 데이터베이스 서버 정보를 수정합니다.

데이터 로드 대상이 Oracle Server

D:\exp_ora2dml\oradml\setenv.bat

@echo off
set NLS_LANG=American_America.AL32UTF8
REM set NLS_LANG=KOREAN_KOREA.KO16MSWIN949
set PATH=C:\app\oracle\product\12.2.0\dbhome_1\bin;%PATH%
set ORACLE_HOME=C:\app\oracle\product\12.2.0\dbhome_1
set ORAHOST=localhost
set ORAPORT=1521
set ORACLE_SID=ORCL
set ORAUSER=scott
set ORAPASS=tiger

데이터 로드 대상이 PostgreSQL Server

D:\exp_ora2dml\pgdml\setenv.bat

@echo off
set PGCLIENTENCODING=SJIS
REM set PGCLIENTENCODING=UTF-8
set PATH=C:\PostgreSQL\12\bin;%PATH%
set PGDATA=C:\PostgreSQL\12\data
set PGHOST=localhost
set PGPORT=5432
set PGDATABASE=postgres
set PGUSER=postgres
set PGPASS=password

8. 02load.bat에 지정된 yyyymmdd_hhmiss_dml_output.sql 파일명을 의 6. 에서 출력한 데이터의 파일명으로 변경하고 명령 프롬프트에서 다음 내용의 02load.bat를 실행합니다. 실행 시의 log결과는 02load_yyyymmdd_hhmiss.log 파일로 출력합니다. setenv.bat에서 지정한 서버에 로드할 수 있습니다.

데이터 로드 대상이 Oracle Server

D:\exp_ora2dml\oradml\02load.bat

@echo off
cd %~dp0
set CUR_PATH=%~dp0
call setenv.bat
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
set FMTDATE=%date:~0,4%%date:~5,2%%date:~8,2%_%CUR_HH%%time:~3,2%%time:~6,2%
echo "load dml processing..."
sqlplus %ORAUSER%/%ORAPASS%@%ORAHOST%/%ORACLE_SID% @..\output\oradml_output.sql >  %CUR_PATH%02load_%FMTDATE%.log 2>&1
notepad  %CUR_PATH%02load_%FMTDATE%.log

데이터 로드 대상이 PostgreSQL Server

D:\exp_ora2dml\pgdml\02load.bat

@echo off
cd %~dp0
set CUR_PATH=%~dp0
call setenv.bat
set CUR_HH=%time:~0,2%
if %CUR_HH% lss 10 (set CUR_HH=0%time:~1,1%)
set FMTDATE=%date:~0,4%%date:~5,2%%date:~8,2%_%CUR_HH%%time:~3,2%%time:~6,2%
echo "load dml processing..."
psql.exe -f ..\output\pgdml_output.sql >  %CUR_PATH%02load_%FMTDATE%.log 2>&1
notepad  %CUR_PATH%02load_%FMTDATE%.log

주의 사항) 데이터 로드 대상 데이터베이스에는 oradml_output.sql 출력 때 사용했던 동일한 구조의 Table이 존재해야 합니다. 로드는 Truncate /Insert 방식으로 수행하므로 기존 데이터는 삭제 됩니다.

오류 대응

01make.bat 실행 log에 다음과 같은 오류가 표시된다면 D:\exp_ora2dml\instantclient_12_2에 dll 파일이 존재하는지를 확인 하거나 exp2dml.ini 의 oracle_instantclient_dir 키의 값 ..\instantclient_12_2와 실제 디렉토리 명이 같은지를 확인합니다. 그리고 The 12.2 Basic packages는 Microsoft Visual Studio 2013 Redistributable.가 인스톨되어 있어야합니다.

Traceback (most recent call last):
  File "exp2dml.py", line 100, in <module>
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help
[12912] Failed to execute script 'exp2dml' due to unhandled exception!

02load.bat 실행 log에 다음과 같은 오류가 표시된다면 setenv.bat의 특수 환경 변수 PATH에 psql.exe 실행파일 경로가 존재하는지 확인하고, 없다면 추가합니다.

'psql.exe'은(는) 내부 또는 외부 명령, 실행할 수 있는 프로그램, 또는
배치 파일이 아닙니다.

TODO 작업

외부 키를 가지고 있는 테이블은 Truncate에서 오류가 발생합니다. 임시로 데이터를 로드하기 전에 외래키를 지우고 다시 설정하는 방법을 적용해야 합니다.

실습용 PostgreSQL Database 클라이언트/서버 설치하기

psql 클라이언트 설치에 대해서는 다음 글을 참조 하십시오.

• PostgreSQL Windows에서 psql클라이언트가 포함된 오직 “Command Line Tools”만 설치하기

PostgreSQL 서버 설치에 대해서는 다음 글을 참조 하십시오.

• Linux PostgreSQL version13설치하기
• Linux7 CentOS7에서 PostgreSQL 12 설치하기
• Linux8 CentOS8에서 PostgreSQL 12 설치하기
• Windows Server 2012 R2에 PostgreSQL 14 Windows x64 버전 설치하기(zip archive 사용)
• Windows Server 2019에서 PostgreSQL 13.5-1 Windows x64 버전 설치하기(인스톨러 사용)

실습용 Oracle Database 클라이언트/서버 설치하기

Oracle 클라이언트 설치에 대해서는 다음 글을 참조 하십시오.

• Oracle Client 12.2.0.1.0 for Microsoft Windows x64 설치 하기 (Instant Client)

Oracle 서버 설치에 대해서는 다음 글을 참조 하십시오.

• Windows Server 2012 R2에서 Oracle Database 12.2.0.1.0 for Microsoft Windows x64 설치 하기
• Windows10 Pro에서 무상으로 이용가능한 Oracle Database Express Edition 18c 설치하기
• Windows Server 2019 에서 Oracle Database 19c for Microsoft Windows x64 설치 하기