Oracle 데이터베이스 REMAP 기능 활용하기

글쓴이 /

오늘은 데이터 펌프 옵션 중 하나인 REMAP에 대해 이야기해보려고 합니다. 이 내용이 여러분의 업무에 도움이 되길 바랍니다!

개념

-DataPump 에서 사용되는 옵션입니다.

  • remap_schema
    • import 될 데이터들의 object user 변경
  • remap_table
    • import 될 테이블명을 새로 바꿉니다.
  • remap_tablespace
    • 오브젝트의 테이블스페이스를 변경할 경우 사용됩니다.

DataPump 가져오기가 매우 느린경우 와 ERROR ?

  • oracle 10.2.0.1에서 버그가 있어서 느렸으며, 11.0 버전에서 해결됨
  • 혹은10.2.0.5 패치 적용하기

  • 11.0 이상에서도 DataPump로 스키마 임포트 시 REMAP_SCHEMA 파라미터를 사용하면 “TABLE/STATISTICS/TABLE_STATISTICS” 단계에서 무한정 멈추는 것처럼 보임
    • 원인: DataPump 임포트는 내부적으로 XML 문서를 사용함
    • 테이블 수가 많을 경우(예: 2000개 이상)와 테이블 통계(예: 70만개)가 많을 때 XML 문서에서 이름을 변경하고, DDL로 변환하여 실행하는 데 많은 시간이 소요됨
  • 해결방안: 통계를 제외하고 DataPump 내보내기/가져오기 실행 (EXCLUDE=STATISTICS 사용)

==============================

실습 방향

*DB 2개가 있다는 가정 하에 진행합니다.

1.각 DB에 서로 다른 사용자 생성 2.서로 다른 테이블스페이스 생성 3.동일한 테이블 생성 4.개발DB 데이터 입력

==============================

Test

# ORA19C DB에 사용자 생성 부터 테이블까지 만듬 
SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
ORA19C

CREATE TABLESPACE prd
DATAFILE '/u01/app/oracle/oradata/ORA19C/prd_tbs01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 2M MAXSIZE 200M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER userp
IDENTIFIED BY oracle
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 10M ON users
ACCOUNT UNLOCK;

#세션 접속을 위하여 권한 부여 
GRANT create session TO userp;
#테이블 생성권한 부여
GRANT CREATE TABLE TO userp;
#테이블스페이스 사용 권한
GRANT UNLIMITED TABLESPACE TO userp;  

#권한 확인 
SQL> SELECT *
FROM dba_sys_privs
WHERE grantee = 'USERP';

GRANT PRIVILEGE                                ADM COM INH
----- ---------------------------------------- --- --- ---
USERP CREATE TABLE                             NO  NO  NO
USERP UNLIMITED TABLESPACE                     NO  NO  NO
USERP CREATE SESSION                           NO  NO  NO
==
ADM: 권한을 다른 사용자에게 부여할 수 있는지 여부 (NO = 불가능)
COM: 권한이 WITH GRANT OPTION으로 부여되었는지 여부 (NO = 아님)
INH: 권한이 상속되었는지 여부 (NO = 직접 부여된 권한)
==

SQL> conn userp/oracle

CREATE TABLE newp (id NUMBER, day TIMESTAMP) TABLESPACE prd;
#데이터 까지 집어넣음   
SQL>  SELECT NAME FROM V$DATABASE;

NAME
---------
CLONE

CREATE TABLESPACE dev
DATAFILE '/home/oracle/clone/dev_tbs01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 2M MAXSIZE 200M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER devp
IDENTIFIED BY oracle
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 10M ON users
ACCOUNT UNLOCK;

#세션 접속을 위하여 권한 부여 
GRANT create session TO devp;
#테이블 생성권한 부여
GRANT CREATE TABLE TO devp;
#테이블스페이스 사용 권한
GRANT UNLIMITED TABLESPACE TO devp;  

#권한 확인 
SQL> SELECT *
FROM dba_sys_privs
WHERE grantee = 'DEVP';
GRAN PRIVILEGE                                ADM COM INH
---- ---------------------------------------- --- --- ---
DEVP CREATE TABLE                             NO  NO  NO
DEVP UNLIMITED TABLESPACE                     NO  NO  NO
DEVP CREATE SESSION                           NO  NO  NO

SQL> conn devp/oracle

CREATE TABLE newd (id NUMBER, day TIMESTAMP) TABLESPACE dev;
INSERT INTO newd (id, day) VALUES (1, SYSTIMESTAMP);
SQL> commit;
SQL> select * from newd;

ID    | DAY                       
------|---------------------------
1     | 05-FEB-25 02:57:29.357078 PM

#export

#위치확인(CLONE진행)
SQL> SELECT * FROM DBA_DIRECTORIES;
[oracle@oracle19c ~]$ expdp system/oracle  directory=pump_dir dumpfile=dev_data.dmp schemas=devp

-- 1. directory=pump_dir : 덤프 파일이 생성될 위치
--    - pump_dir : Oracle 내에 생성된 디렉토리 객체 이름
--    - 실제 물리적 경로와 매핑된 논리적 이름
--    - CREATE DIRECTORY 명령으로 미리 생성되어 있어야 함

-- 2. dumpfile=dev_data.dmp : 생성될 덤프 파일 정보
--    - dev_data.dmp : 생성될 덤프 파일의 이름
--    - 이 파일에 실제 추출된 데이터가 저장됨
--    - pump_dir 디렉토리 내에 생성됨

-- 3. schemas=devp : 추출할 대상 정보
--    - schemas : 스키마 단위로 추출하겠다는 의미
--    - devp : 추출할 스키마(사용자) 이름
--    - devp 스키마의 모든 객체가 추출됨 (테이블, 인덱스, 시퀀스 등)
#=>덤프 파일 (dev_data.dmp) : 실제 데이터와 메타데이터 추출

==

#ERROR 발생 및 해결

  • ORA-39002: invalid operation
  • ORA-39070: Unable to open the log file.
  • ORA-39087: directory name PUMP_DIR is invalid
#pump_dir 에 대한 권한이 없거나 디렉터리가 없어서 발생함 
!mkdir -p /u01/app/oracle/admin/CLONE/dpdump
CREATE OR REPLACE DIRECTORY pump_dir AS '/u01/app/oracle/admin/CLONE/dpdump';
GRANT READ, WRITE ON DIRECTORY pump_dir TO devp;

#확인
OWNER | DIRECTORY_NAME | DIRECTORY_PATH                     | ORIGIN_CON_ID
------|----------------|------------------------------------|----------------
SYS   | PUMP_DIR      | /u01/app/oracle/admin/CLONE/dpdump  | 0
  • ORA-25153: Temporary Tablespace is Empty
  • ORA-06512: at “SYS.DBMS_LOB”, line 741
  • ORA-06512: at “SYS.DBMS_DATAPUMP”, line 5420
#템프파일 재사용한다 (파일은 있기때문에)
alter tablespace temp add tempfile '/home/oracle/clone/temp01.dbf' size 100M reuse autoextend on;

==

  • 각 DB마다 pump_dir 다르니 cp 해주자
[oracle@oracle19c dpdump]$ cp -av dev_data.dmp /u01/app/oracle/admin/ORA19C/dpdump/
‘dev_data.dmp’ -> ‘/u01/app/oracle/admin/ORA19C/dpdump/dev_data.dmp’
  • import
impdp system/oracle directory=pump_dir dumpfile=dev_data.dmp tables=DEVP.NEWD remap_schema=DEVP:USERP remap_table=DEVP.NEWD:NEW remap_tablespace=DEV:PRD table_exists_action=REPLACE nologfile=y

==

#Error 발생 및 해결

  • pump_dir 경로가 없어서 발생함

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name PUMP_DIR is invalid

#경로없음 확인
SQL> SELECT * FROM dba_directories WHERE directory_name = 'PUMP_DIR';

no rows selected

#지정하자 (디렉터리는 있음)
CREATE OR REPLACE DIRECTORY pump_dir AS '/u01/app/oracle/admin/ORA19C/dpdump';
GRANT READ, WRITE ON DIRECTORY pump_dir TO userp;

==

  • 확인
SQL> SELECT * FROM newp;

ID    | DAY                       
------|---------------------------
1     | 05-FEB-25 02:57:29.357078 PM

SQL> SELECT * FROM newd;
SELECT * FROM newd
               *
ERROR at line 1:
ORA-00942: table or view does not exist

==

이런 식으로 나오면(주의)?

“successfully completed at Wed Feb 5 17:10:21 2025 elapsed 0 00:00:22” 문구가 없음으로 실패한거다


Import: Release 19.0.0.0.0 - Production on Wed Feb 5 16:28:01 2025
Version 19.3.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_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=pump_dir dumpfile=dev_data.dmp remap_schema=DEVP:USERP remap_tablespace=DEV:PRD remap_table=DEVP.NEWD:USERP.NEWP exclude=trigger
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"USERP" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USERP"."USERP"                               5.5 KB       1 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Feb 5 16:28:45 2025 elapsed 0 00:00:40

==

위로 스크롤