오라클 9i 에서 오라클 19c로 정통 export, import 마이그레이션

OS환경 : Oracle Linux 4.8, 7.6 (64bit)

 

DB 환경 : Oracle Database 9.2.0.4, 19.11.0.0

 

방법 : 오라클 9i 에서 오라클 19c로 정통 export, import 데이터 마이그레이션

오라클 10g 이상의 버전이라면 datapump를 사용해서 마이그레이션 할 수 있지만 9i에서는 datapump가 없기 때문에 정통 export/import를 이용해서 이관을 해야함

 

 

오라클 9i db에서 데이터 생성 후 오라클 19c db로 export/import를 통해 데이터를 마이그레이션 하는 방법을 설명함

기존에 생성되어있던 imsi 라는 계정을 이용해 테스트함

imsi 계정이 없다면 생성 후 진행(9i)

SQL>
create user imsi identified by imsi account unlock default tablespace users quota unlimited on users;
grant resource, connect to imsi;

 

 

테이블 생성(9i)

SQL>
conn imsi/imsi
drop table exptest;
create table exptest(cola varchar2(20), colb number, colc number, cold varchar2(30));
create index idx_colb on exptest(colb); 

 

 

데이터 생성(9i)

SQL>
DECLARE
TYPE tbl_ins IS TABLE OF EXPTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=999;
   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
END LOOP;
   FORALL i in 1..100000 INSERT INTO EXPTEST VALUES w_ins(i);
   COMMIT;
END;
/

 

 

데이터 확인(9i)

SQL> select count(*) from exptest;
 
  COUNT(*)
----------
    100000

10만건의 데이터가 삽입됨

 

 

인덱스 생성 확인

SQL>
col table_name for a10
col index_name for a10
col column_name for a10
select a.table_name
      , a.index_name
      , a.column_name
from all_ind_columns a
where a.table_name = 'EXPTEST'
order by a.index_name, a.column_position;
 
TABLE_NAME INDEX_NAME COLUMN_NAM
---------- ---------- ----------
EXPTEST    IDX_COLB   COLB

인덱스도 정상적으로 생성되어있음

 

 

데이터 용량 확인(9i)

SQL> conn / as sysdba
select segment_name, sum(bytes)/1024/1024 as MB
from dba_segments
where segment_name='EXPTEST'
GROUP BY segment_name
 
SEGMENT_NAME      MB
-----------    ----------
EXPTEST       4

 

 

export 용 폴더 생성 후 유저별 export(9i)

$ mkdir -p /home/oracle/dump
$ cd /home/oracle/dump
$ exp system/oracle file=exp_full211124.dmp log=exp_full211124.log direct=y owner=imsi
 
Export: Release 9.2.0.4.0 - Production on Thu Nov 25 02:30:29 2021
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16MSWIN949 character set (possible charset conversion)
 
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user IMSI 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user IMSI 
About to export IMSI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export IMSI's tables via Direct Path ...
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                        EXPTEST     100000 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                     PLAN_TABLE          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
You have new mail in /var/spool/mail/oracle

 

 

덤프파일 복제(9i -> 19c)

$ scp exp_full211124.dmp 10.10.10.60:/home/oracle/dump/
The authenticity of host '10.10.10.60 (10.10.10.60)' can't be established.
RSA key fingerprint is a6:30:17:4d:af:96:85:12:08:88:a7:19:af:d2:4c:39.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.10.10.60' (RSA) to the list of known hosts.
oracle@10.10.10.60's password: 
You must specify direct recipients with -s, -c, or -b.
exp_full211124.dmp                                                  100% 3328KB   3.3MB/s   00:00  

 

 

유저 생성(19c)

SQL>
create user imsi identified by imsi account unlock default tablespace users quota unlimited on users;
grant resource, connect to imsi;

 

 

유저별 import(19c)

$ cd /home/oracle/dump
$ imp system/oracle file=exp_full211124.dmp log=imp_full211124.log buffer=10247680 fromuser=imsi touser=imsi
 
Import: Release 19.0.0.0.0 - Production on Wed Nov 24 17:42:14 2021
Version 19.11.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.11.0.0.0
 
Export file created by EXPORT:V09.02.00 via direct path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
IMP-00403: 
 
Warning: This import generated a separate SQL file "imp_full211124_sys.sql" which contains DDL that failed due to a privilege issue.
 
. importing IMSI's objects into IMSI
. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"         14 rows imported
. . importing table                      "EXPTEST"     100000 rows imported
. . importing table                   "PLAN_TABLE"          0 rows imported
About to enable constraints...
Import terminated successfully with warnings.

 

 

데이터 확인(19c)

SQL> conn imsi/imsiSQL> select count(*) from exptest;
  COUNT(*)
----------
    100000

10만건의 데이터가 정상적으로 확인됨

 

 

인덱스 생성 확인(19c)

SQL>
col table_name for a10
col index_name for a10
col column_name for a10
select a.table_name
      , a.index_name
      , a.column_name
from all_ind_columns a
where a.table_name = 'EXPTEST'
order by a.index_name, a.column_position;
 
TABLE_NAME INDEX_NAME COLUMN_NAM
---------- ---------- ----------
EXPTEST    IDX_COLB   COLB

인덱스도 정상적으로 생성되어있음

 

 

9i에서 19c 데이터베이스로 export/import를 이용해 정상적으로 마이그레이션이 가능함

단 케릭터셋을 잘 확인하고 넘겨야함

+ 테이블, 데이터가 정상적으로 넘어갔더라도 application 소스단의 검증작업은 필수임

대표적으로 group by 정렬순서를 보장하지 않는 문제(9i에서도 공식적으론 정렬순서를 보장하진않았음) 같은

이슈가 있을수 있으니 충분한 검증 후 이관작업을 진행해야함

 

 

참조 : 1903870.1, 553337.1, 61949.1, 132904.1

https://positivemh.tistory.com/449

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

https://blog.naver.com/crossb/117817151

https://www.oracle.com/a/tech/docs/twp-upgrade-oracle-database-19c.pdf