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

 

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

방법 : oracle 30gb 정통 export import 테스트, 시간 측정

데이터 펌프를 사용하지 않고 정통 exp/imp를 사용해서 속도를 측정해봄

데이터의 크기는 30GB로 단일 테이블임

 

 

하드웨어 정보 : 램 10GB 할당, cpu 1, 데이터파일 경로 60gb 할당, dump 파일 경로 40gb 할당

 

 

테스트

디스크 용량확인

 

# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2       9.5G  9.3G     0 100% /
tmpfs           4.9G     0  4.9G   0% /dev/shm
/dev/sda3        60G  8.3G   49G  15% /app
/dev/sdb1        40G   48M   38G   1% /dump

 

 

/dump 경로 권한 부여

# chown -R oracle:oinstall /dump
# chmod -R 755 /dump

 

 

sga_target 4gb로 설정

# su - oracle
$ cd $ORACLE_HOME/dbs
$ vi initorcl.ora 
*.sga_target=4g

 

 

db기동

$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 15 04:55:51 2019
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area 4275781632 bytes
Fixed Size            2260088 bytes
Variable Size          335545224 bytes
Database Buffers     3925868544 bytes
Redo Buffers           12107776 bytes
Database mounted.
Database opened.

 

 

기존 데이터파일 경로 확인

SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/orcl/users01.dbf
/app/oracle/oradata/orcl/undotbs01.dbf
/app/oracle/oradata/orcl/sysaux01.dbf
/app/oracle/oradata/orcl/system01.dbf

 

 

테이블 스페이스 생성(30gb 할당)

SQL> create tablespace samsib datafile '/app/oracle/oradata/orcl/samsib01.dbf' size 30g;
 
Tablespace created.

 

 

유저 생성 및 권한 부여

SQL> create user expman identified by expman account unlock default tablespace samsib;
 
User created.
 
SQL> grant resource, connect to expman;

 

 

유저 접속

SQL> conn expman/expman

 

 

테이블 및 인덱스 생성

SQL>
CREATE TABLE EXPTEST(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.
 
SQL> CREATE INDEX IDX_COLB ON EXPTEST(COLB); 
 
Index created.

 

 

샘플 데이터 삽입(DECLARE문 1번당 197mb) 총 153번 반복 => 약 30gb

(약 25분정도 소요됨)

SQL>
set serveroutput on;
DECLARE
TYPE tbl_ins IS TABLE OF EXPTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
for d in 1..153 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 EXPTEST VALUES w_ins(i);
   COMMIT;
DBMS_OUTPUT.PUT_LINE(d);
end loop;
END;
/

(DBMS_OUTPUT.PUT_LINE(d);를 이용해

반복된 횟수가 나오게끔 하고싶었지만 잘나오지않음;)

 

 

테이블 rows 수 확인

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

 

 

테이블 용량확인(약 30gb)

SQL> 
set lines 200
col owner for a10
col segment_name for a20
select segment_name, sum(bytes)/1024/1024 as MB
from dba_segments
where segment_name='EXPTEST'
GROUP BY segment_name;
 
SEGMENT_NAME     MB
---------- ----------
EXPTEST        30656

 

 

export 할 경로로 이동

$ cd /dump

 

 

system 계정으로 export 실행(약 20분 소요)

$ exp system/oracle file=full191014.dmp log=full191014.log direct=y full=y
 
Export: Release 11.2.0.4.0 - Production on Tue Oct 15 06:13:56 2019
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
 
EXP-00056: ORACLE error 28002 encountered
ORA-28002: the password will expire within 7 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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses KO16MSWIN949 character set (possible charset conversion)
 
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
.
.
.
.
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.

 

 

import를 위해 기존 테이블 삭제

SQL> conn expman/expman
Connected.
SQL> drop table exptest purge;
 
Table dropped.

 

 

테이블 삭제되었는지 확인

SQL> 
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='EXPMAN'
GROUP BY owner;
 
no rows selected

 

 

유저 삭제

SQL> conn / as sysdba
Connected.
SQL> drop user expman cascade;
 
User dropped.

 

 

index 생성 스크립트 뽑기(테스트 작업 시나리오상 안해도됨 참고용)

 

index 생성 스크립트 뽑기

$ imp system/oracle file=full191014.dmp log=full191014idx.log full=y indexfile=index constraints=y  
Import: Release 11.2.0.4.0 - Production on Tue Oct 15 06:46:07 2019
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
 
IMP-00058: ORACLE error 28002 encountered
ORA-28002: the password will expire within 7 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
 
Export file created by EXPORT:V11.02.00 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses KO16MSWIN949 character set (possible charset conversion)
. . skipping table "DEF$_AQCALL"                      
 
. . skipping table "DEF$_AQERROR"                     
 
. . skipping table "DEF$_CALLDEST"                    
 
. . skipping table "DEF$_DEFAULTDEST"     
.
.

 

index.sql 파일이 생성됨

 

 

 

system 계정으로 import 실행(약 50분 소요)

$ imp system/oracle file=full191014.dmp log=fullimp191014.log full=y buffer=10247680 commit=n
 
Import: Release 11.2.0.4.0 - Production on Tue Oct 15 07:02:24 2019
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
 
IMP-00058: ORACLE error 28002 encountered
ORA-28002: the password will expire within 7 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
 
Export file created by EXPORT:V11.02.00 via direct path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses KO16MSWIN949 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
.
.
.
.
. importing SYSTEM's objects into SYSTEM
. importing ORDDATA's objects into ORDDATA
. importing SYSMAN's objects into SYSMAN
. importing EXPMAN's objects into EXPMAN
. importing ORDDATA's objects into ORDDATA
About to enable constraints...
. importing APEX_030200's objects into APEX_030200
Import terminated successfully with warnings

 

 

테이블 rows 수 확인

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

 

 

테이블 용량확인(약 30gb)

SQL> 
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='EXPMAN'
GROUP BY owner;
 
OWNER           MB
---------- ----------
EXPMAN        30528

 

 

 

결과 : 데이터 30gb에 export 시간 약 20분, import 시간 약 50분

내 테스트 결과는 이렇지만 용량이 테이블 단위로 많이 

나눠져있다면 조금 더걸리지 않을까 하는 생각이 듬

 

 

 

 

 

참조 :