[Oracle] Oracle DB DUMP 및 PUMP 방법

// Oracle Pump import / export 방법
작성자 : KEI

// 1.Pump 디렉토리 생성 및 권한 부여
SQL>CONN TESTUSER/*****
SQL>CREATE DIRECTORY DATA_PUMP_DIR AS 'D:\oracle\admin\orcl\dpdump';
SQL>CONN sys/*******
SQL>GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO 권한줄계정;

// 2.Pump export
$ EXPDP 구계정/***** DIRECTORY=DATA_PUMP_DIR DUMPFILE=파일명.dmp

// 3. Pump import  
두개의 테이블스페이스를 사용하는 구계정 -->하나의 테이블스페이스를 사용하는 신계정

$ IMPDP 신계정/**** DIRECTORY=DATA_PUMP_DIR DUMPFILE=파일명.DMP REMAP_SCHEMA=구계정:신계정 REMAP_TABLESPACE=A테이블스페이스:신테이블스페이스 REMAP_TABLESPACE=B테이블스페이스:신테이블스페이스 LOG=로그파일명.LOG

-- 설명
1.Create a SQLFILE to include the relevant DDL command(s): impdp system/** directory=test_dp DUMPFILE=export_schemas.dmp remap_schema=u1:u2 sqlfile=script.sql
2.Extract the affected DDL from the written SQLFILE and correct the schema reference. Then execute the command manually.

// tables - table 하나씩 export하기
$ EXPDP 구계정/***** DIRECTORY=DATA_PUMP_DIR DUMPFILE=파일명.dmp TABLES=테이블명 JOB_NAME=T1

// pump는 export oracle version에 민감 - 상위버전에서 EXPORT한 DUMP파일 하위버전에 IMPORT할때
$ EXPDP 구계정/***** DIRECTORY=DATA_PUMP_DIR DUMPFILE=파일명.dmp VERSION="11.1.0.6.0"
$ IMPDP 신계정/**** DIRECTORY=DATA_PUMP_DIR DUMPFILE=파일명.DMP REMAP_SCHEMA=구계정:신계정 REMAP_TABLESPACE=A테이블스페이스:신테이블스페이스 VERSION="11.1.0.6.0"

 

// Oracle DB DUMP 방법

// 일반 dump는(oracle 9i때 사용하던 방식) import/export는 pump에 비해 느림

// 권한 부여
-- 권한주기
SQL>GRANT IMP_FULL_DATABASE TO 계정;
-- 권한회수
SQL>REVOKE IMP_FULL_DATABASE FROM 계정;

// Oracle DB import
// tablespace만들기

$ IMP 신계정/**** FILE=파일명.DMP FROMUSER=구계정 TOUSER= 신계정 LOG=로그파일명.log
-- import할 계정과 테이블스페이스 만들기

// Oracle DB export
$ EXP 구계정/**** FILE=파일명.DMP

### 시스템계정으로 Export한 덤프파일 import시 에러날 경우 (IMP-00024 에러코드)
IMP-00024: Only one mode (TABLES, FROMUSER, or FULL) can be specified
Cause: Parameters were specified that conflict with the import specification FULL=Y.

// 공통 import할 tablespace만들기

SQL>CREATE TABLESPACE 테이블스페이스명 DATAFILE
'D:/oracle/oradata/물리파일명.dbf' SIZE 최초사이즈 AUTOEXTEND ON NEXT 자동증가사이즈 MAXSIZE UNLIMITED;
SQL>CREATE USER 신계정 IDENTIFIED BY 패스워드 DEFAULT TABLESPACE 테이블스페이스명;
-- 모든 권한을 줄 경우
SQL>GRANT ALL PRIVILEGE TO 신계정;

// 테이블스페이스는 Default 테이블만 사용
SQL>REVOKE UNLIMITED TABLESPACE FROM 계정명;
SQL>ALTER USER 계정명 QUOTA UNLIMITED ON 테이블스페이스명;

// 테이블스페이스 삭제 (물리파일까지 삭제)
SQL>DROP TABLESPACE 테이블스페이스명 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

 

// trubleshooting

// 시스템계정으로 Export한 덤프파일 import시 에러날 경우 (IMP-00024 에러코드)
IMP-00024: Only one mode (TABLES, FROMUSER, or FULL) can be specified
Cause: Parameters were specified that conflict with the import specification FULL=Y.

 

// DB Pump export시 ORA-39126에러 발생
Oracle RDBMS version 11.2.0.2. When attempting DataPump export (expdp) the following errors occur:
#################################################################################
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CUBE_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 1245
ORA-06550: line 1, column 8:
PLS-00201: identifier 'SYS.DBMS_CUBE_EXP' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8353
----- PL/SQL Call Stack -----
object line object
handle number name
0x139b48e78 19208 package body SYS.KUPW$WORKER
0x139b48e78 8385 package body SYS.KUPW$WORKER
0x139b48e78 12748 package body SYS.KUPW$WORKER
0x139b48e78 2546 package body SYS.KUPW$WORKER
0x139b48e78 9054 package body SYS.KUPW$WORKER
0x139b48e78 1688 package body SYS.KUPW$WORKER
0x13da077b0 2 anonymous block
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_CUBE_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 1245
ORA-06550: line 1, column 8:
PLS-00201: identifier 'SYS.DBMS_CUBE_EXP' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8353
----- PL/SQL Call Stack -----
object line object
handle number name
0x137cadbc8 19208 package body SYS.KUPW$WORKER
0x137cadbc8 8385 package body SYS.KUPW$WORKER
0x137cadbc8 12748 package body SYS.KUPW$WORKER
0x137cadbc8 2546 package body SYS.KUPW$WORKER
0x137cadbc8 9054 package body SYS.KUPW$WORKER
0x137cadbc8 1688 package body SYS.KUPW$WORKER
0x1574b1700 2 anonymous block
Job "SYS"."SYS_EXPORT_SCHEMA_06" stopped due to fatal error at 16:33:33
##########################################################################
Cause
OLAP objects remain existing in data dictionary while OLAP is not installed or was de-installed. Verify with:
# DBA권한으로 Connect한다.
connect / as sysdba
Solution
Perform the following:
# DBA권한으로 Connect한다.
connect / as sysdba
-- backup the table SYS.EXPPKGACT$ before deleting the row
SQL>CREATE TABLE SYS.EXPPKGACT$_BACKUP AS SELECT * FROM SYS.EXPPKGACT$;
-- delete the DBMS_CUBE_EXP from the SYS.EXPPKGACT$
SQL>DELETE FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA = 'SYS';
SQL>COMMIT;
-- 확인
SQL>SELECT * FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP';
Run EXPDP command again.
References
NOTE:1109045.1 - Full and Schema DataPump Exports Fail With Errors ORA-39125 ORA-31642 During DBMS_SCHED_EXPORT_CALLOUTS.SCHEMA_CALLOUT
NOTE:445291.1 - EXPDP FAILS WITH ORA-39125 ORA-31642 DBMS_SCHED_EXPORT_CALLOUTS

등록일시
2012.01.26 13:58 (업로드 2012.01.26 13:58)
공개권한
전체공개
태그
#Oracle