내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
OS 환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.21.0.0
방법 : 오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 datapump 테스트
오라클 19c 환경의 스탠다드 에디션에서 엔터프라이즈 에디션으로 datapump 이관이 제대로 동작하는지 확인해봄
에디션별 기능 차이 및 cold backup 테스트는 아래 게시물 참고하길 바람
오라클 19c 스탠다드 에디션, 엔터프라이즈 에디션 기능 차이 ( https://positivemh.tistory.com/1050 )
오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 백업복구(cold backup) 테스트 ( https://positivemh.tistory.com/1067 )
테스트
스탠다드 에디션 확인
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200
col product for a50
col version for a15
col version_full for a15
col status for a15
select * from product_component_version;
PRODUCT VERSION VERSION_FULL STATUS
-------------------------------------------------- --------------- --------------- ---------------
Oracle Database 19c Standard Edition 2 19.0.0.0.0 19.21.0.0.0 Production
|
Standard Edition 2 에디션에 19.21 DB임
스탠다드 db에서 샘플 유저, TS, 테이블 생성, 데이터 삽입
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
SQL>
-- 유저 생성
create user imsi identified by imsi account unlock default tablespace users quota unlimited on users;
grant resource, connect, dba to imsi;
-- TS 생성
drop tablespace imsits including contents and datafiles;
create tablespace imsits datafile '/ORA19/app/oracle/oradata/ORACLE19/imsits01.dbf' size 1g autoextend off;
alter user imsi default tablespace imsits quota unlimited on imsits;
-- 유저 접속 및 테이블 생성
conn imsi/imsi
drop table sample_t purge;
create table sample_t
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
nologging;
-- 데이터 삽입
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..1000000 INSERT INTO sample_t VALUES w_ins(i);
COMMIT;
END;
/
|
샘플 테이블 건수 확인
1
2
3
4
5
|
SQL> select count(*) from sample_t;
COUNT(*)
----------
1000000
|
100만건 존재
샘플 테이블 용량 확인
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 200 pages 1000
col segment_name for a10
select segment_name, round(bytes/1024/1024) mb from dba_segments
where segment_name = 'SAMPLE_T';
SEGMENT_NA MB
---------- ----------
SAMPLE_T 96
|
96MB임
데이터 펌프용 디렉토리 생성
1
2
3
4
|
$ mkdir -p /home/oracle/datapump
SQL> create directory stdent as '/home/oracle/datapump';
Directory created.
|
expdp 실행
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
$ expdp system/oracle directory=stdent dumpfile=stddump.dmp logfile=stddump_exp.log tables=imsi.sample_t
Export: Release 19.0.0.0.0 - Production on Sat Mar 30 01:14:01 2024
Version 19.21.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=stdent dumpfile=stddump.dmp logfile=stddump.log tables=imsi.sample_t
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "IMSI"."SAMPLE_T" 79.90 MB 1000000 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/datapump/stddump.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sat Mar 30 01:14:16 2024 elapsed 0 00:00:14
|
덤프파일 엔터프라이즈 에디션이 설치되어있는 서버로 복사(대상서버에서 /home/oracle/datapump 폴더 생성 필요)
1
2
|
$ cd /home/oracle/datapump
$ scp stddump.dmp oracle@192.168.137.101:/home/oracle/datapump
|
엔터프라이즈 db에서 샘플 유저, TS 생성
1
2
3
4
5
6
7
8
9
|
SQL>
-- 유저 생성
create user imsi identified by imsi account unlock default tablespace users quota unlimited on users;
grant resource, connect, dba to imsi;
-- TS 생성
drop tablespace imsits including contents and datafiles;
create tablespace imsits datafile '/ORA19/app/oracle/oradata/ORACLE19/imsits01.dbf' size 1g autoextend off;
alter user imsi default tablespace imsits quota unlimited on imsits;
|
데이터 펌프용 디렉토리 생성
1
2
3
|
SQL> create directory stdent as '/home/oracle/datapump';
Directory created.
|
impdp 실행
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
$ impdp system/oracle directory=stdent dumpfile=stddump.dmp logfile=entdump_imp.log tables=imsi.sample_t
Import: Release 19.0.0.0.0 - Production on Sat Mar 30 02:12:36 2024
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 "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=stdent dumpfile=stddump.dmp logfile=entdump_imp.log tables=imsi.sample_t
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "IMSI"."SAMPLE_T" 79.90 MB 1000000 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sat Mar 30 02:13:00 2024 elapsed 0 00:00:22
|
import 완료
샘플 테이블 건수 확인
1
2
3
4
5
6
7
|
SQL>
conn imsi/imsi
select count(*) from sample_t;
COUNT(*)
----------
1000000
|
100만건 존재
정상적으로 import 됨
샘플 테이블 용량 확인
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 200 pages 1000
col segment_name for a10
select segment_name, round(bytes/1024/1024) mb from dba_segments
where segment_name = 'SAMPLE_T';
SEGMENT_NA MB
---------- ----------
SAMPLE_T 96
|
96MB임
정상적으로 import 됨
결론 :
스탠다드 에디션 db에서 expdp를 이용해 테이블을 export 한 뒤 엔터프라이즈 에디션 db에 impdp를 이용해 테이블을 import 하면 정상적으로 데이터가 들어감
참조 :
2089230.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/migrating-from-standard-to-enterprise--database.html#GUID-294262A1-9E53-4C76-8FFA-086BD6CC4EE1
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/migrating-from-enterprise-to-standard-database.html#GUID-FD2DB65B-0E54-4ECE-8DF3-8E8F12FBD005
https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87
https://positivemh.tistory.com/440
https://positivemh.tistory.com/808
https://positivemh.tistory.com/810
https://positivemh.tistory.com/1017
https://positivemh.tistory.com/1050
https://positivemh.tistory.com/1067
'ORACLE > Backup&Recover' 카테고리의 다른 글
오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트2(정상) (0) | 2024.09.19 |
---|---|
오라클 19c 인덱스 nologging 옵션 생성후 복구 테스트1(에러발생) (0) | 2024.09.16 |
오라클 19c 스탠다드 에디션에서 엔터프라이즈 에디션으로 백업복구(cold backup) 테스트 (0) | 2024.04.01 |
오라클 19c DUL 이용 Truncate 된 데이터 복구(비공식 툴) (0) | 2024.03.28 |
오라클 19c rman 백업 압축 알고리즘별 압축률 비교 (0) | 2024.01.18 |