오라클 11g R2 에서 오라클 19c Datapump 마이그레이션 방법

OS환경 : Oracle Linux 6.8, 8.1 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4, 19.3.0.0

 

방법 : 오라클 11g R2 에서 오라클 19c Datapump 마이그레이션 방법

Oracle의 기능 중 하나인 Datapump를 이용해 11g R2의 데이터를 19c로 마이그레이션 하는 방법을 설명함

11gR2 dumpuser 유저의 dumptable 테이블을 19c dumpuser2 유저로 넣을 예정

테스트1. expdp 로 dump 파일 생성 후 scp 전송 후 impdp 로 dump 파일 import

테스트2. expdp 없이 impdp network_link 파라미터를 이용해 네트워크 import

이렇게 두가지 방법으로 테스트를 진행함, Datapump 사용시 세부 파라미터는 최하단 참조 부분 링크를 확인해야함

 

 

서버 정보

db 버전 / 호스트네임 / IP / sid

11g R2 / ora11 / 192.168.137.11 / orcl

19c     / ora19 / 192.168.137.19 / oracle19

 

 

사전 작업

/etc/hosts에 각 서버 IP, HOSTNAME 삽입

11gR2
# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.137.11 ora11
192.168.137.19 ora19
 
19c
# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.137.19 ora19
192.168.137.11 ora11

 

 

datapump용 directory, os directory 생성(11g,19c 모두)

$ mkdir -p /app/oracle/datapump
 
SQL> create directory migdir as '/app/oracle/datapump';
 
Directory created.

 

 

방화벽 해제(11g,19c 모두)

11gR2
# /etc/init.d/iptables stop
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]
 
19c
# systemctl stop firewalld
# systemctl disable firewalld

 

 

샘플 유저 및 데이터 생성(11g)

테이블스페이스 생성
SQL> create tablespace dumpts datafile '/app/oracle/oradata/orcl/dumpts01.dbf' size 5g;
 
Tablespace created.
 
 
유저생성 및 권한 부여
SQL> create user dumpuser identified by dumpuser account unlock default tablespace dumpts quota unlimited on dumpts;
 
User created.
 
SQL> grant resource, connect, dba to dumpuser;
 
Grant succeeded.
 
 
테이블 생성
SQL> conn dumpuser/dumpuser
create table dumptable(cola varchar2(20), colb number, colc number, 
cold varchar2(30), cole varchar2(30), colf varchar2(30), 
colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30));
 
Table created.
 
 
샘플 데이터 삽입(DECLARE문 1번당 197mb) 총 11번 반복 => 약 2gb
SQL>
set serveroutput on;
DECLARE
TYPE tbl_ins IS TABLE OF DUMPTABLE%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
for d in 1..11 loop
FOR i IN 1..1000000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=999;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII';
   w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA';
END LOOP;
   FORALL i in 1..1000000 INSERT INTO DUMPTABLE VALUES w_ins(i);
   COMMIT;
DBMS_OUTPUT.PUT_LINE(d);
end loop;
END;
/
 
PL/SQL procedure successfully completed.
 
 
용량확인(sys)
SQL>
conn / as sysdba
set lines 200
col owner for a10
col segment_name for a20
select owner, sum(bytes)/1024/1024 as MB
from dba_segments
where owner='DUMPUSER'
GROUP BY owner;
 
OWNER           MB
---------- ----------
DUMPUSER     1920

 

 

샘플 유저 및 데이터 생성(19c)

테이블스페이스 생성
SQL> create tablespace dumpts datafile '/ORA19/app/oracle/oradata/ORACLE19/dumpts01.dbf' size 5g;
Tablespace created.
 
 
유저생성 및 권한 부여
SQL> create user dumpuser2 identified by dumpuser2 account unlock default tablespace dumpts quota unlimited on dumpts;
 
User created.
 
SQL> grant resource, connect, dba to dumpuser2;
 
Grant succeeded.

 

network_link 파라미터 용 tns 및 db 링크 설정(19c)

$ cat $ORACLE_HOME/network/admin/tnsnames.ora
oralink =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
SQL> create database link ora11g connect to dumpuser identified by dumpuser using 'oralink';
 
Database link created.
 
SQL> select version from v$instance@ora11g;
 
VERSION
-----------------
11.2.0.4.0

11g db로 정상 접속됨

 

 

테스트1. expdp 로 dump 파일 생성 후 scp 전송 후 impdp 로 dump 파일 import

11gR2 db에서 expdp 실행

$ expdp system/oracle directory=migdir dumpfile=test1_exp.dmp logfile=test1_exp.log tables=dumpuser.dumptable
 
Export: Release 11.2.0.4.0 - Production on Thu Jun 10 00:42:39 2021
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 6 days
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/****** directory=migdir dumpfile=test1_exp.dmp logfile=test1_exp.log tables=dumpuser.dumptable 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.875 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DUMPUSER"."DUMPTABLE"                      1.637 GB 11000000 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /app/oracle/datapump/test1_exp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jun 10 00:42:51 2021 elapsed 0 00:00:10

 

 

덤프파일 확인 및 scp 전송

$ cd /app/oracle/datapump/
$ ls -al
total 1717676
drwxr-xr-x.  2 oracle oinstall       4096 Jun 10 00:42 .
drwxr-xr-x. 11 oracle oinstall       4096 Jun  9 22:08 ..
-rw-r-----.  1 oracle oinstall 1758883840 Jun 10 00:42 test1_exp.dmp
-rw-r--r--.  1 oracle oinstall       1088 Jun 10 00:42 test1_exp.log
$ scp test1_exp.dmp ora19:/app/oracle/datapump/
The authenticity of host 'ora19 (192.168.137.19)' can't be established.
RSA key fingerprint is ec:f5:99:2d:4d:ab:2a:3d:10:d3:6d:b4:c2:5c:0e:67.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ora19,192.168.137.19' (RSA) to the list of known hosts.
oracle@ora19's password: 
test1_exp.dmp                                                                                                              100% 1677MB 139.8MB/s   00:12 

 

 

19c db에서 impdp 실행

$ cd /app/oracle/datapump/
$ ls -al
total 1717660
drwxr-xr-x 2 oracle oinstall         27 Jun  9 15:46 .
drwxr-xr-x 3 oracle oinstall         22 Jun  9 13:08 ..
-rw-r----- 1 oracle oinstall 1758883840 Jun  9 15:46 test1_exp.dmp
 
$ impdp system/oracle directory=migdir dumpfile=test1_exp.dmp logfile=test1_imp.log remap_schema=dumpuser:dumpuser2
 
Import: Release 19.0.0.0.0 - Production on Wed Jun 9 15:48:59 2021
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
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/*****  directory=migdir dumpfile=test1_exp.dmp logfile=test1_imp.log remap_schema=dumpuser:dumpuser2 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DUMPUSER2"."DUMPTABLE"                     1.637 GB 11000000 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Jun 9 15:49:20 2021 elapsed 0 00:00:13

 

 

데이터 확인

SQL> conn dumpuser2/dumpuser2
SQL> select * from tab;
 
TNAME        TABTYPE        CLUSTERID
--------------- ------------- ----------
DUMPTABLE    TABLE
 
SQL> conn / as sysdba
set lines 200
col owner for a10
col segment_name for a20
select owner, sum(bytes)/1024/1024 as MB
from dba_segments
where owner='DUMPUSER2'
GROUP BY owner;
 
OWNER           MB
---------- ----------
DUMPUSER2     1984

정상적으로 마이그레이션됨

 

 

테스트1 소요 시간 : 2GB expdp 시간 : 10초 / scp 이동 시간 : 12초 / 2GB impdp 시간 : 13초 = 총 35초

 

 

테스트2. expdp 없이 impdp network_link 파라미터를 이용해 네트워크 import

network_link 테스트를 진행하기 전 oracle docs 를 보다가 아래 내용을 확인함

문서상으로는 두 버전 이하만 달라야하는데 11gR2 to 19c 는 세 버전이 다름(11g, 12c, 18c, 19c)

일단은 이점을 인지하고 테스트를 진행함

 

 

network_link 제한사항

네트워크 링크를 통해 작동 할 때 Data Pump는 소스 및 대상 데이터베이스가 두 버전 이하 만 달라야함

예를 들어 한 데이터베이스가 Oracle Database 12c 인 경우 다른 데이터베이스는 12c, 11g 또는 10g 여야함

Oracle Data Pump는 특정 릴리스 번호 (예 : 12.1, 12.2, 11.1, 11.2, 10.1 또는 10.2)가 아닌 주 버전 번호 (예 : 10g, 11g, 12c) 만 확인함

 

테스트를 위해 19c에서 dumptable 및 dump파일 삭제

SQL> drop table dumpuser2.dumptable purge;
 
Table dropped.
 
SQL> !rm /app/oracle/datapump/test1_exp.dmp

 

 

19c db에서 impdp(network_link) 실행

impdp 시도
$ impdp system/oracle directory=migdir logfile=test1_imp2.log network_link=ora11g tables=dumptable remap_schema=dumpuser:dumpuser2
 
Import: Release 19.0.0.0.0 - Production on Wed Jun 9 17:10:34 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39200: Link name "ora11g" is invalid.
ORA-02019: connection description for remote database not found
 
@ 붙여서 시도
$ impdp system/oracle@oralink directory=migdir logfile=test1_imp2.log network_link=ora11g tables=dumptable remap_schema=dumpuser:dumpuser2
 
Import: Release 19.0.0.0.0 - Production on Wed Jun 9 17:13:47 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 6 days
UDI-00018: Data Pump client is incompatible with database version 11.2.0.4.0

두가지 방법 모두 실행되지 않음

network_link 제한사항 때문에 되지 않는듯함

network_link 를 이용해 impdp를 사용할 경우 두 버전 이하 만 달라야함(12c to 19c 는 가능할듯함)

다른 버전의 db에서 다시 테스트를 진행해보겠음

 

 

참조 : 

https://blog.goodusdata.com/61

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html#GUID-D11E340E-14C6-43B8-AB09-6335F0C1F71B

https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html#GUID-0871E56B-07EB-43B3-91DA-D1F457CF6182

https://www.oracle.com/a/tech/docs/oracle-database-utilities-data-pump-bp-2019.pdf

https://positivemh.tistory.com/449

오라클 30gb 정통 export import 테스트, 시간 측정

https://andwiz.tistory.com/entry/Data-Pump-2-NETWORKMODE-DB-link