DB기술지원/유지보수/DB컨설팅

DATAPUMP 기능을 사용하기 위해서는 첫째 DIRECTORY가 설정되어 있어야한다.

* 필요 권한 

* Directory  조회

* Directory 생성 및 권한

 - DUMP = 사용하고자 하는 DIRECTORY 이름지정

 - '/oracle/dumpfile' = DIRECTORY가 사용할 물리적인 경로

 - READ 권한은 Import Pump를 수행하기 위해 필요

 - WRITE 권한은 Export Pump를 수행하기 위해 필요

 - 일반유저로 오브젝트를 생성하기 위해서는 CREATE ANY DIRECTORY 권한을 가지고 있어야 한다.

* Directory 삭제 

* Default Directory 설정 

* Export

 -  expdp 옵션

  * system/oracle - 데이터베이스 유저 및 비밀번호

  * DIRECTORY - 데이터 펌프 파일을 저장하거나 또는 저장되어 있는 디렉토리

  * LOGFILE - 로그가 저장될 파일 이름

  * FULL - 데이터베이스 전체에 데이터 펌프 적용

  * SCHEMAS - 설정된 데이터베이스 유저가 소유한 오브젝트에 대해 데이터 추출

  * TABLES - 명시된 테이블에 대해서만 데이터 추출

  * TABLESPACES - 명시된 테이블스페이스에 저장된 오브젝트에 대해서만 데이터 추출

  * PARALLEL - 데이터 펌프 작업시 병렬 프로세스의 개수를 지정하는 옵션

  * 필터링 옵션

    - EXCLUDE - 데이터 펌프 작업에서 제외될 오브젝트의 종류와 이름을 명시하는 옵션

                        (EXCLUDE=OBJECT_TYPE[:'object_name'],  INCLUDE=TABLE:="TEST"    TEST 테이블만 포함

    - INCLUDE - 데이터 펌프 작업에 포함될 오브젝트의 종류와 이름을 명시하는 옵션

                        (INCLUDE=OBJECT_TYPE[:'object_name'],   

                        EXCLUDE=TABLE:="TEST"     TEST테이블은 제외

                        EXCLUDE=INDEX:\"=EMP%'\"   EMP 테이블의 인덱스 제외

    - PARFILE - 지정된 파일에 원하는 옵션을 설정한 후 해당 파일에 설정된 옵션을 적용하여 적제를 수행하는 옵션

  * 추출옵션

    - CONTENTS = [ALL | DATA_ONLY | METADATA_ONLY}

  * 오브젝트 정의변경 REMAP

    - REMAP_SCHEMA : 적재가 수행되는 데이터베이스에서 오브젝트 소유자를 변경

      (REMAP_SCHEMA='SCOTT' : 'MIKE')  SCOTT유저에서 MIKE유저로 적재

    - REMAP_TABLESPACE : 적재가 수행되는 데이터베이스에서 오브젝트가 저장될 테이블스페이스를 변경

      (REMAP_TABLESPACE='USERS' : 'TOOLS') USER테이블스페이스에서 TOOLS테이블스페이스로 변경

    - REMAP_DATAFILES : 적재가 수행되는 데이터베이스의 테이블스페이스에서 데이터파일의 위치를 변경

      (REMAP_DATAFILE='/data/ts1.dbf' : '/oracle/ts1.dbf') datafile 재정의

* Import

 * 데이터 적재에만 사용되는 옵션 (TABLE_EXITS_ACTION)

  : export/import 기능에서 import의 ignore기능이 datapump에서는 TABLE_EXITS_ACTION 옵션이다.

  SKIP - 동일 이름의 테이블이 존재할 경우 해당 테이블에 대한 데이터 적재 작업 생략

  APPEND - 동일 이름의 테이블이 전재할 경우 데이터를 해당 테이블에 추가로 적재

  REPLACE - 동일 이름의 테이블이 존재할 경우 해당 테이블을 제거한 후 재생성하여 데이터 적재

  TRUNCATE - 동일 이름의 테이블이 존재할 경우 해당 테이블의 데이터를 DELETE한 후 데이터 적재

ATTACH 옵션

 - ADD_FILE : 추출 파일(DUMPFILE) 추가

 - CONTINUE_CLIENT : 데이터 펌프 작업에 대한 진행 로그 확인

 - EXIT_CLIENT : 데이터 펌프의 클라이언트 관리 세션 종료

 - FILESIZE : ADD_FILE 명령으로 추가되는 파일의 기본 크기 설정

 - KILL_JOB : 데이터 펌프 작업을 삭제 (작업 재시작 불가능)

 - PARALLEL : 병렬 프로세싱 옵션 지정

 - START_JOB : 데이터 펌프 작업 재시작

 - STOP_JOB : 수행중인 데이터 펌프 작업 중단(작업 재시작 가능)

출처: http://boeok.tistory.com/entry/Data-Pump-사용방법 [Secret]

Export와 Import 툴을 사용할 수 있게 되어 있지 않다면, catexp.sql과 catalog.sql 을 실행합니다. 이것은 한번만 실행해야 합니다. 이 스크립트가 수행하는 작업은 다음과 같습니다.

- 데이터 딕셔너리에 exp와 imp에서 필요한 뷰를 생성합니다.

- EXP_FULL_DATABASE 역할을 생성합니다.

- EXP_FULL_DATABASE와 IMP_FULL_DATABASE역할에 모든 필요한 권한을 할당합니다.

- EXP_FULL_DATABASE와 IMP_FULL_DATABASE를 DBA 역할에 할당합니다.

- 설치된 catexp.sql의 버전을 기록합니다.

exp했을때 데이터를 저장할 충분한 디스크 공간이 없다면 오류가 발생할 것입니다. exp 될 데이터의 양이 얼마나 될지 다음 쿼리로 대충 예측할 수 있습니다. 여기서 계산된 양에 LOG 데이터와 VARRAY 데이터의 양은 포함되지 않습니다.

SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';

Export와 Import 툴을 사용하기 위한 권한

사용자에게 CREATE SESSION 권한이 있어야 합니다. 이 권한은 CONNECT 롤에 포함되어 있습니다. 다른 사용자가 소유한 테이블을 exp 하려면 EXP_FULL_DATABASE 역할이 사용 가능해야 합니다. 이 역할은 모든 데이터베이스 관리자(DBA)에게 부여되어 있습니다.

Export와 Import 명령의 실행

명령의 실행을 위해서는 필요한 파라미터를 인자로 주어 실행을 합니다. 파라미터를 주는 방법은 다음과 같습니다.

- 명령행에 인자를 붙여서 실행합니다.

- 파라미터 파일을 만들어서 파일을 인자로 실행합니다.

- 대화식 모드로 실행합니다.

Export 파라미터

- BUFFER : row 패치에 사용되는 버퍼의 크기로 기본값은 운영체제에 의존적입니다. 이 파라미터는 패치된 배열내에 들어갈 최대 행의 수를 결정합니다. 다음식으로 버퍼 사이즈를 계산할 수 있습니다.

buffer_size = rows_in_array * maximum_row_size

이 값이 0 이면 한번에 하나의 행을 페치 합니다. 계산 예를 보겠습니다. 다음과 같은 테이블이 있을때,

CREATE TABLE sample (name varchar(30), weight number);

name 컬럼의 크기는 30 + 2( 크기 + 지시자) 입니다. weight 클럼의 크기는 22 + 2 (크기 + 지시자) 입니다. 그러므로 한번에 100 행을 페치한다면, BUFFER_SIZE는 (30+2+22+2) * 100 = 5600이 되겠습니다.

보통은 이 파라미터를 기본값으로 사용을 합니다. 하지만 데이터량이 많을때 적절한 사이즈를 지정해주면 덤프를 빠르게 수행할 수 있습니다.

- COMPRESS : 이 파라미터는 테이블 데이터를 위한 초기 확장(initial extent)을 어떻게 관리할지를 지정합니다. 기본값을 y 입니다. 이 파라미터를 데이터를 압축하는 파라미터가 아닙니다. Oracle은 초기 extent가 꽉차게 되면 다음 extent를 next extent = next * (1 + PCTINCREASE / 100) 로 계산하여 확장합니다. PCTINCREASE가 파라미터에 의해 다음 확장은 초기보다 더 커지게 됩니다. 이 상황 그대로 export 한 후, 다른 컴퓨터에서 import할 때 충분한 디스크 용량이 없으면 extent 도중에 "Unable to extent next extent" 오류를 만날 수 있습니다. COMPRESS=y 를 사용하면 현재 전체 extent 크기를 하나의 초기 extent로 만들어 줍니다. COMPRESS=n을 사용하면 현재 테이블과 동일한 extent 값을 만들어 줍니다.

- CONSISTENT : SET TRANSACTION READ ONLY 문장을 사용하여 exp가 실행되는 동안 일관성이 유지되도록 합니다. 기본값은 n 입니다.

- CONSTRINTS : 테이블 제약사항을 내보낼지 지정합니다. 기본값은 y 입니다.

- DIRECT : SQL 문장 처리 층을 거치지 않고 export합니다. 기본값은 n 입니다.

- FEEDBACK : 지정된 행수를 내보낼때마다 . 을 출력합니다. 기본값은 0입니다.

- FILE : 덤프 파일명을 지정합니다. 기본값은 expdat.dmp 입니다.

- FILESIZE : 덤프 파일 크기를 지정합니다. 지정된 크기가 넘으면 다음 파일에 저장되고, 더이상 지정된 파일이 없으면 새파일 이름을 입력하라는 메세지가 나옵니다. 지정하지 않으면 하나의 파일이 덤프 됩니다.

exp scott/tiger FILE=dat1.dmp, dat2.dmp, dat3.dmp FILESIZE=2048

- FLASHBACK_SCN : SCN(System Change Number)를 사용하여 flashback를 활성화 하고, export 동작은 지정된 scn으로 일관된 데이터를 수행합니다. 기본값은 없습니다.(none)

※ 참고 

FLASH BACK 은 commit 해서 rollback 할 수 없는 데이터에 대해서 조회하거나 복구할 수 있는 기능 입니다. SCN(System Change Number) 타임스탬프를 사용하여 복구 지점을 선택할 수 있습니다.

- FLASHBACK_TIME : 타임스탬프를 사용하여 flashback 지점을 지정하여 export 합니다. 기본값은 none 입니다.

- FULL : 전체 데이터베이스를 export 합니다. 전체 데이터베이스를 복제할 수 있는 좋은 방법입니다. 하지만 오류를 방지하기 위해 주의가 필요합니다. 기본값은 n 입니다.

모든 테이블 공간과 데이터 파일 목록을 만들어 두면 좋습니다. 모든 테이블 스페이스가 작성된 것이 확실하지 않으면 둘 이상의 데이터베이스가 있는 시스템에서 전체 import 를 수행하지 마십시오. 전체 가져오기는 export한 데이터베이스와 동일한 데이터 파일명을 사용하여 정의되지 않은 모든 테이블 공간을 작성합니다. 이로 인해 충돌이 발생할 수 있습니다.

- GRANTS : 권한 정보를 export 할지 지정합니다. 사용자 모드에서는 사용자가 부여한 권한 정보만 export 됩니다. 기본값은 y 입니다.

- HELP : 도움말을 출력합니다. 기본값은 none 입니다.

- INDEXES : 인덱스를 내보낼지 지정합니다. 기본값은 y 입니다.

- LOG : 메세지를 로그 파일에 기록합니다.  (예, export.log)

- OBJEXT_CONSISTENT : 개개의 객체를 read only 트랜잭션으로 export 합니다. CONSiSTENT 옵션은 하나의 read only 트랜잭션만 있습니다. 기본값은 n 입니다.

- OWNER : 사용자 모드를 지정합니다. 지정된 사용자만 백업됩니다. 기본값은 none 입니다.

- PARFILE : 파라미터 파일을 지정합니다.

- QUERY : 테이블 데이터중 조건에 맞는 일부말 백업할 수 있습니다.

exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"

exp scott/tiger TABLES=emp,bonus QUERY=\"WHERE job=\'SALESMAN\' and sal\<1600\"

- RECORDLENGTH : 파일 레코드의 길이를 지정합니다. 기본값은 운영체제에 따라 다릅니다.

- RESUMABLE : 공간 할당에 resumable 을 사용할 것인지 표시합니다. y 로 설정하게되면  RESUMABLE_NAME과 RESUMABLE_TIMEOUT을 지정해야 합니다. 기본 값은 n 입니다.

※ 참고 

resumable 은 데이터 입력작업중 공간이 부족할 경우 rollback 하지 않고 공간이

확보될때까지 기다리게 하는 옵션 입니다. 데이터 입력 작업에서 resumable 로 작업하면

alter session enable resumable;

오류로 멈추지 않고 데이터 파일을 추가 등의 작업으로 공간을 확보하고 계속 진행할 수 있습니다.

- RESUMABLE_NAME : resume 가능한 문장을 식별합니다. 기본값 'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID' 입니다.

- RESUMABLE_TIMEOUT : 재개 가능한 시간을 지정합니다. 지정된 시간안에 오류가 수정되지 않으면 실행이 종료됩니다. 기본값은 7200초 (2 시간)입니다.

- ROWS : 테이블 데이터의 행을 export 할지 지정합니다. 기본값은 y 입니다.

- STATISTICS : import 할때 옵티마이저 통계를 생성할 방식을 지정합니다. ESTIMATE, COMPUTE, NONE 중에 하나를 지정합니다. 기본값은 ESTIMATE 입니다.

- TABLES : export할 테이블을 지정할 수 있습니다. 기본값은 none 입니다.

- TABLESPACES : 지정된 테이블 스페이스의 모든 테이블이 export 됩니다. 기본값은 none 입니다.

- TRANSPORT_TABLESPACE : Transportable Tablespace메타데이터를 export합니다. 기본값은 n 입니다.

※ Transportable Tablespace란 서로 다른 DB간에 Tablespace단위로 Datafile을 이동해서 바로 사용할 수 있는 기능입니다. exp로 메타데이터만 옮기면 데이터파일은 복사해 넣으면 됩니다.

- TRIGGERS : 트리거를 export합니다. 기본값은 y 입니다.

- USERID (username/password) : export를 사용할 사용자의 아이디와 비밀번호를 입력합니다. 비밀번호를 빼면 프롬프트를 보여줍니다.

Import 파라미터

- BUFFER : row 패치에 사용되는 버퍼의 크기로 기본값은 운영체제에 의존적입니다. export 파라미터 참조

- COMMIT : y로 지정하면 각 배열을 입력한 후에 commit 됩니다. 기본값은 n 으로 테이블 단위로 commit 합니다.

- COMPILE : 패키지, 프로시저, 함수를 컴파일할지 지정합니다. 기본값은 y 입니다.

- CONSTRAINTS : 제약조건을 가져올지 지정합니다. 기본값은 y 입니다.

- DATAFILES : Transportable Tablespace를 사용할 때 데이터 파일을 지정합니다.

- DESTORY : 기존 데이터파일을 재사용할지를 지정합니다. y를 지정하면 기존의 정보로 데이터파일을 생성합니다. 기본값은 n 입니다.

- FEEDBACK : 지정된 행 만큼 입력시 마다 . 표시 합니다.

- FILE : 덤프 파일 을 지정합니다. 기본값은 expdata.dmp 입니다.

- FILESIZE : 다중 export 파일을 지정합니다. 운영체제에 의존적입니다.

- FROMUSER : import 할 스키마를 지정합니다. 기본값을 none 입니다.

- FULL : 덤프 파일 전체를 import 합니다. 기본값은 y 입니다.

- GRANTS : 권한을 import 합니다. 기본값은 y 입니다.

- HELP : 도움말을 보여줍니다.

- IGNORE : 오류를 보여줄지 말지 지정합니다. n은 오류를 표시하고, y는 무시합니다. 기본값은 n입니다.

- INDEXES : 인덱스를 가져올지 여부를 지정합니다. 기본값은 y입니다.

- INDEXFILE : 지정되면 인덱스 생성문이 지정된 파일에 저장되고 실제 인덱스가 생성되지는 않습니다.

- LOG : 로고 메세지를 저장할 파일을 지정합니다.

- PARFILE : 파라미터 파일을 지정합니다.

- RECORDLENGTH : 파일 레코드의 길이를 지정합니다. 기본값은 운영체제에 의존적입니다.

- RESUMABLE : 공간 할당을 재시작 가능한지 지정합니다. 기본값은 n 입니다.

- RESUMABLE_NAME : resume 가능한 문장을 식별합니다.

- RESUMAGLE_TIMEOUT : 재개 가능한 시간을 지정합니다.

- ROWS : 테이블 데이터를 import 합니다. 기본값은 y 입니다. 

- SHOW : 덤프파일 내용을 보여주고, 실제 import하지는 않습니다. 기본값은 n 입니다.

- SKIP_UNUSABLE_INDEXES : 색인 사용 불가능 상태 (시스템 또는 사용자에 의해)로 설정된 색인을 건너 뜁니다. 이전에 색인 사용 불가능으로 설정되지 않은 다른 색인은 행이 삽입 될 때 계속 갱신됩니다.

- STATISTICS : import 시 옵티마이저 통계에 사용될 모드를 지정합니다.(ALWAYS, NONE, SAFE, RECALCULATE) 기본값은 ALWAYS 입니다.

- STREAMS_CONFIGUREATION : 스트림 메타데이터를 import 할지 지정합니다.(복제 관련) 기본값은 n입니다.

- STREAMS_INSTANTIATION : 스트림 인스턴스 메타데이터를 import 할지 지정합니다.(복제 관련) 기본값은 n입니다.

- TABLES : 지정된 테이블을 import 합니다.

- TABLSPACES : 지정된 테이블 스페이스만 import 합니다.

- TOUSER : import할 target 유저를 지정합니다. 유저는 미리 존재해야 합니다.

- TRANSPORT_TABLESPACE : Transportable tablespace 메타메이터를 import 합니다.

- TTS_OWNERS : Transportable tablespace를 소요한 사용자를 지정합니다.

- USERID(username/password) : import를 수행할 사용자의 아이디와 비밀번호를 지정합니다.

출처: http://boeok.tistory.com/entry/펌-exp-imp-명령어?category=564741 [Secret]

이 패키지를 설정하게 되면 SNP 백그라운드 프로세스가 이 패키지에 설정되어 있는 작업을 수행하게 된다.

SNP 백그라운드 프로세스는 선택적인 프로세스로 초기화 파라미터 파일에 아래의 항목을 설정 한 후 재 시작을 하면 실행 된다.

 - job_queue_processes = 1     <-- SNP 프로세스의 개수를 지정한다.

 - job_queue_interval = 60         <-- SNP 프로세스가 Sleep 상태에서 깨어나는 간격을 초로 지정한다.

이 패키지는 아래와 같은 다양한 프로시저들을 가지고 있다.

1. Job 관리하기

이 프로시저는 다양한 파라미터의 항목을 가지고 설정 할 수 있다.

 - 기본문법

 DBMS.JOB.submit (

  job out binary_interger,

 what in varchar2,

 next_date in date default sysdate,

 interval in varchar2 default 'null'

 no_parse in boolean default false

 )

 - 주요 파라미터의 의미 

 (1) 새로운 job 등록 테스트

 위에서 살펴본 프로시저들을 사용해서 새로운 job을 등록하는 테스트를 하겠다. 테스트 내용은 job_test01이라느 테이블을 만든 후 1분에 1번씩 해당 테이블에 데이터를 insert 하는 것이다.

 step 1. 테스트용 테이블과 sequencem procedure를 생성한다. 

 step2. job을 등록할 프로시저를 생성한다. 

 * 참고 : 위 5행의 시간 설정 방법 * 

 step3. job을 등록한다.

 step4. 수행되고 있는 job 내용을 확인한다.

 step5. 실제 테이블에 데이터가 입력되는지 확인한다.

(2) 등록되어 있는 job 삭제하기

 (3) 등록되어 있는 job 수정하기

 수행시간을 1분마다에서 5분마다 1회 수행으로 변경함.

$ORACLE_HOME/network/admin/listener.ora 에서 다음 명령어를 추가해준다.

맨 마지막줄

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON

DIAG_ADR_ENABLED_LISTENER=OFF

LOG_FILE_LISTENER=listener_etc.log    <<--- LOG로 남길 파일

LOG_DIRECTORY_LISTENER=/tmp        <<--- LOG가 저장될 위치

wq!

lsnrctl reload

원복방법

위의 3줄 삭제후 저장