행위

Oracle 데이터펌프

DB CAFE

thumb_up 추천메뉴 바로가기

1 DATAPUMP 데이터펌프[편집]

  1. 권한 생성
  2. 디렉토리 권한 부여
  3. 디비링크 생성 후 디비링크를 통한 데이터 펌프 실행


assignment * 데이터 펌프(DATAPUMP) 사용 절차

1) 사용자 생성 

SQL> create user new_scott identified by tiger;


2) 권한 추가 

SQL> grant connect, resource to new_scott;


3) 디렉토리 권한 추가 

SQL> grant read, write on directory MY_DMP_DIR to new_scott;
Grant succeeded.


4) 디비링크 생성권한(DBLINK 이용시) 

SQL> grant create database link to new_scott;
SQL> connect new_scott/tiger
SQL> create database link OLD_DB connect to scott identified by tiger  using 'olddb.krenger.ch';

5) 디비링크 생성후 impdp 수행

  • 스키마를 변경(scott-> new_scott) 하여 import
impdp new_scott/tiger directory=MY_DMP_DIR LOGFILE=dblink_transfer.log network_link=OLD_DB remap_schema=scott:new_scott



2 IMPDP (Import Datapump) 사용법[편집]

2.1 IMPORT 사용 예시[편집]

2.2 DBLINK로 IMPORT PUMP 처리 방법[편집]

2.3 1.DB 링크 생성[편집]

2.4 2.IMPDP 파라미터 파일로 실행[편집]

2.5 IMPORT 파라미터 샘플[편집]

2.6 테이블만 IMPORT(테이블 존재시 TRUNCATE)[편집]

2.7 Package, Function, Procedure 만 import 하기[편집]

2.8 IMPORT DP 파라미터[편집]

2.9 통계정보를 제외[편집]

2.10 스키마 선택[편집]

2.11 IMPORT DP 상세 파라미터[편집]

2.12 IMPORT API[편집]

2.13 IMPORT 스키마/테이블 API[편집]

2.14 IMPORT 개별 TABLE (복구시)[편집]

3 EXPDP (Export Datapump) 사용법[편집]

3.1 EXPORT 파라미터 작성[편집]

3.2 병렬처리 parallel 파라미터[편집]

3.3 tables 파라미터[편집]

3.4 QUERY 파라미터[편집]

3.5 sqlfile 파라미터[편집]

3.6 Package, Function, Procedure 만 EXPORT 하기[편집]

3.7 EXPORT DP 파라미터[편집]

3.8 2개 디렉토리 export 하기(1개의 디스크로 충분하지 않을때)[편집]

3.9 ASM환경에서 EXPDP 실행 방법[편집]

4 데이터펌프 작업 관리 및 모니터링[편집]

assignment : 주요사항
현재 작업중인 datapump 작업들의 내용을 dba_datapump_jobs 딕셔너리를 통해 확인 가능



4.1 ExportDP JOB 확인[편집]

4.1.1 터미널에서 JOB Attach 모드[편집]

expdp 아이디/비밀번호@db명 attach=JOB이름
expdp>status

4.1.2 SQL로 확인[편집]

  • dba_datapump_jobs
SELECT owner_name, job_name, operation, job_mode, state 
  FROM dba_datapump_jobs;
  • sys.v_$sqlarea
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name
     , rows_processed
     , round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
     , trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
  from sys.v_$sqlarea
 where sql_text like 'INSERT %INTO "%'
   and command_type = 2
   and open_versions > 0;

4.2 Datapump 작업 진행사항[편집]

SELECT X.JOB_NAME
     , B.STATE
     , B.JOB_MODE
     , B.DEGREE
     , X.OWNER_NAME
     , Z.SQL_TEXT
     , P.MESSAGE
     , P.TOTALWORK
     , P.SOFAR
     , ROUND ( (P.SOFAR / P.TOTALWORK) * 100, 2) DONE
     , P.TIME_REMAINING
  FROM DBA_DATAPUMP_JOBS B
  LEFT JOIN DBA_DATAPUMP_SESSIONS X ON (X.JOB_NAME = B.JOB_NAME)
  LEFT JOIN V$SESSION Y ON (Y.SADDR = X.SADDR)
  LEFT JOIN V$SQL Z ON (Y.SQL_ID = Z.SQL_ID)
  LEFT JOIN V$SESSION_LONGOPS P ON (P.SQL_ID = Y.SQL_ID)
-- WHERE Y.MODULE = 'Data Pump Worker' AND P.TIME_REMAINING > 0
;

5 데이터펌프(DATAPUMP) JOB 중지[편집]

SELECT * FROM DBA_DATAPUMP_JOBS;

5.1 IMPDP ATTACH=JOB 접속 후[편집]

$>IMPDP SCOTT/TIGER@DB ATTACH=JOB명 
KILL_JOB
STOP_JOB #STOP_JOB = IMMEDIATE 즉시 종료

5.2 API 이용 정지[편집]

  • 주의) 즉시 정지 하지 않음
DECLARE
h1 NUMBER;
BEGIN
h1:=DBMS_DATAPUMP.ATTACH(JOB_NAME => '"MIG_CHANGED_TABLE"', JOB_OWNER => 'RTIS_MIG');
DBMS_DATAPUMP.STOP_JOB(h1,1,0);
END;

6 EXPORT API[편집]

6.1 스키마 EXPORT[편집]

DECLARE
    hndl NUMBER;
    TAG_NAME  VARCHAR2(30) := 'EXP_MIG_TEST01';
BEGIN
    hndl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT'
                              , job_mode => 'SCHEMA' -- FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE
                              , job_name=>'JOB_'||TAG_NAME
                              --, remote_link => 'DBLINK_NAME', version => 'LATEST'
                              );
    DBMS_DATAPUMP.ADD_FILE( handle => hndl, filename => TAG_NAME||'.dmp', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE, reusefile=>1);
    DBMS_DATAPUMP.ADD_FILE( handle => hndl, filename => TAG_NAME||'.log', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
    
    -- DBMS_DATAPUMP.SET_PARAMETER(handle=> hndl, name=> 'INCLUDE_METADATA', value=> 1); -- META 포함여부
    DBMS_DATAPUMP.DATA_FILTER(handle=> hndl, name=> 'INCLUDE_ROWS', value=> 0);  -- DATA 포함 여부 0, 
    DBMS_DATAPUMP.METADATA_FILTER(handle=> hndl, name=> 'SCHEMA_EXPR', value=>'IN (''FED40'',''TTT'')');
    -- DBMS_DATAPUMP.METADATA_FILTER(handle=> hndl, name=> 'NAME_EXPR', value=>'IN (''FED40'', ''TEST'')');
    -- DBMS_DATAPUMP.METADATA_FILTER(handle=> hndl, name=> 'NAME_LIST', value=>'''EMP'',''DEPT''');

    
    DBMS_DATAPUMP.START_JOB(hndl);
END;

6.2 테이블 EXPORT API[편집]

-- 1.테이블 DUMP EXPORT 스크립트 

DECLARE
hdnl NUMBER;

BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>'JOB_EXP_TB_RC_JEJU_14');

DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'INIT_BK_TB_RC_JEJU_14_20190910.EXP', directory => 'DATAPUMP2', filetype => dbms_datapump.ku$_file_type_dump_file ,reusefile=>1);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'INIT_BK_TB_RC_JEJU_14_20190910.LOG', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file  ,reusefile=>1);

-- 스키마 정보 
 DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl,
                               name   => 'SCHEMA_EXPR',
                               value  => ' IN (''유저명'')'
                               );
-- 테이블 정보  
DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl
                             ,name   => 'NAME_EXPR'
                             ,value  => ' IN (''테이블명'')'
                             ,object_type => 'TABLE'
                             );

DBMS_DATAPUMP.START_JOB(hdnl);
END;

7 데이터펌프 로그파일 읽기 API[편집]

DECLARE 
V1 VARCHAR2(200); --32767
F1 UTL_FILE.FILE_TYPE; 

BEGIN 
    F1 := UTL_FILE.FOPEN('DATA_PUMP_DIR','INIT_BK_TB_RC_JEJU_12_20190910.LOG','R');	
Loop
	BEGIN
		UTL_FILE.GET_LINE(F1,V1); 
		dbms_output.put_line(V1);
		EXCEPTION WHEN No_Data_Found THEN EXIT; 
	END;
end loop;

IF UTL_FILE.IS_OPEN(F1) THEN
	dbms_output.put_line('File is Open');
end if;

UTL_FILE.FCLOSE(F1);	
END;

8 파일 복사(ASM등)[편집]

 attach_file OS상의 파일복사 명령를 DB에서 직접 수행이 가능.

  • DBMS_FILE_TRANSFER.COPY_FILE 패키지/함수
  • 소스 디렉토리에서 파일을 읽고 대상 디렉토리에 복사
  • ASM 과 로컬 디스크간 복사
  • 소스 및 대상 디렉토리는 로컬 파일 시스템에 있거나 ASM (Automatic Storage Management) 디스크 그룹에 있거나

로컬 파일 시스템과 ASM간에 어느 방향 으로든 복사 할 수 있음. 

BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
  source_directory_object      =>  'SOURCEDIR' -- 소스 디렉토리 
, source_file_name             => 't_xdbtmp.f' -- 소스 파일
, destination_directory_object => 'DGROUP'     -- 타켓 디렉토리
, destination_file_name        =>'t_xdbtmp.f'  -- 타겟 파일
);
END;
/

8.1 DB LINK를 이용한 파일 복사(밀어 넣기)[편집]

 attach_file DBMS_FILE_TRANSFER.PUT_FILE 패키지/함수

  • 소스 에서 프로시져 실행 해야 함.
  • 소스 => 타겟으로 복사
  • 로컬 파일 또는 ASM을 읽고 원격 데이터베이스에 접속하여 원격 파일 시스템에 파일 사본을 작성합니다.
  • DB링크 간의 DUMP파일 복사가 가능(.log파일은 복사 안됨,12c)

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object       => 'DATA_PUMP_DIR',    -- 패키지 실행하는 DB
source_file_name              => 'sample.dmp',       -- 패키지 실행하는 DIRECTORY의 덤프파일명
destination_directory_object  => 'DATA_PUMP_DIR2',   -- DB링크 원격지 디렉토리
destination_file_name         => 'sample_copied.dmp',-- DB링크 원격지에 저장될 파일명
destination_database          => '디비링크명' 
);
END;
/

8.2 파일 복사(가져오기)[편집]

 attach_file DBMS_FILE_TRANSFER.GET_FILE

  • 원격 데이터베이스에 접속하여 원격 파일을 로컬 파일 시스템 또는 ASM에 파일 복사.
  • 절차가 성공적으로 완료 될 때까지 대상 파일이 닫히지 않음.

BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object       => 'DATA_PUMP_DIR',
source_file_name              => 'sample.dmp',
source_database               => '디비링크명',
destination_directory_object  => 'DATA_PUMP_DIR2', 
destination_file_name         => 'sample_copied.dmp' 
);
END;
/

9 DATAPUMP API 상세 정보[편집]

https://www.morganslibrary.org/reference/pkgs/dbms_datapump.html

10 DB LINK를 이용한 파일 복사[편집]

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object       => 'DATA_PUMP_DIR',
source_file_name              => 'sample.dmp',
destination_directory_object  => 'DATA_PUMP_DIR',
destination_file_name         => 'sample_copied.dmp', 
destination_database          => 'to_rds' 
);
END;
/
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=Oracle_데이터펌프&oldid=12325"