[오라클] [Data Pump] 서로 다른 디렉토리에서 작업 (관련 실습 스크립트 포함)

[문제]

impdp할때 expdp directory와 dumpfile 일치해야 하는지???

[답] 

서로 다른 디렉토리에서 백업받고 로드가능하다. 확인 사항: directory 파라미터 expdp에 로드한 값이랑 동일 여부 / dumpfile 파라미터 서로 다른 디렉토리에서 받을 경우 dumpfile=[directory_name:]dump_file로 지정 / remap_schema=source_schema:target_schema 형식으로 설정 / logfile 파라미터에서는 %t, %U등 대체 변수 사용 불가 

사전에 dpump_dir1, dpump_dir2 hr4유저로 생성 

 

0. exp_full_database 및 imp_full_database 롤에 어떤 시스템 권한 들어있는지 확인
--> create session은 exp_full_database 및 imp_full_database에 모두 포함,
--  create any directory는 imp_full_database에 부여됨.
--> 아래처럼 확인

SQL> COL GRANTEE FOR A30
SQL> COL PRIVILEGE FOR A35
SQL> select grantee,privilege from dba_sys_privs where grantee like 'EXP_FULL_DATABASE';
SQL> select grantee,privilege from dba_sys_privs where grantee like 'IMP_FULL_DATABASE';

 

0.1 privilege에 create any directory 가지는 grantee(롤 또는 유저) 조회 

SQL> select grantee,privilege from dba_sys_privs where privilege = 'CREATE ANY DIRECTORY';

 

1. system으로 hr4에게 create any directory 권한 및 exp_full_database 부여 (hr4 기본 테이블스페이스 따로 생성해서 지정, 유저 이미 생성한 경우 지정 테이블스페이스로 변경)

일반 테이블스페이스 생성:

SQL> conn system/pw
Connected.
SQL> grant create tablespace to hr4;

Grant succeeded.

SQL> conn hr4/hr4
Connected.
SQL> create tablespace hr4_tbs_01 datafile 'hr4_tbs_f1.dbf' size 20M autoextend on online;

Tablespace created.

SQL> conn system/pw
Connected.
SQL> alter user hr4 default tablespace hr4_tbs_01;

User altered.
SQL> grant create session to hr4 identified by "hr4";
SQL> grant create any directory to hr4;
SQL> grant exp_full_database to hr4;

 

테이블스페이스 참고 사항: 

autoextend절 - datafile, tempfile에 유효, redo log file에는 불가능. AUTOEXTEND 생략하고, SIZE 지정시 AUTOEXTEND DISABLE. SIZE 지정과 명시적으로 AUTOEXTEND ON도 가능. SIZE 생략(미지정) 및 AUTOEXTEND도 생략 시 자동으로 AUTOEXTEND 활성화된 100M 파일 생성 /
 online - 테이블스페이스에 대한 액세스 권한 부여된 사용자가 생성 직후 테이블스페이스를 사용할 수 있게함. ONLINE이 기본값. CREATE TABLESPACE 명령 시 처음에는 READ/WRITE만 되고 이후에 ALTER로 ONLINE/OFFLINE등 설정 가능. temporary tablespace에 대해 not valid. 데이터 딕셔너리 뷰 DBA_TABLESPACES는 각 테이블스페이스가 온라인/오프라인인지 나타낸다.

 

1.1. 권한 부여 확인 

SQL> set line 200
SQL> set pages 500
SQL> col grantee for a30
SQL> col granted_role for a25
SQL> select grantee, privilege from dba_sys_privs where grantee='HR4';
SQL> select grantee,granted_role from dba_role_privs where grantee='HR4';

 

- 참고: datapump_exp_full_database롤은 exp_full_database롤을 부여(포함)받는다. datapump_imp_full_database도 동일.

select grantee,granted_role from dba_role_privs where grantee='DATAPUMP_EXP_FULL_DATABASE';

GRANTEE                        GRANTED_ROLE
------------------------------ -------------------------
DATAPUMP_EXP_FULL_DATABASE     EXP_FULL_DATABASE

select grantee,granted_role from dba_role_privs where grantee='EXP_FULL_DATABASE';

GRANTEE                        GRANTED_ROLE
------------------------------ -------------------------
EXP_FULL_DATABASE              SELECT_CATALOG_ROLE
EXP_FULL_DATABASE              EXECUTE_CATALOG_ROLE

select grantee,granted_role from dba_role_privs where grantee='SELECT_CATALOG_ROLE';

GRANTEE                        GRANTED_ROLE
------------------------------ -------------------------
SELECT_CATALOG_ROLE            HS_ADMIN_SELECT_ROLE

select grantee,granted_role from dba_role_privs where grantee='HS_ADMIN_SELECT_ROLE';

no rows selected

-- exp_full_database를 부여받아서 해당 롤의 권한까지 가능하다. 
-- 롤에 부여된 권한 확인
SQL> select grantee, privilege from dba_sys_privs where grantee='DATAPUMP_EXP_FULL_DATABASE';

GRANTEE                        PRIVILEGE
------------------------------ -----------------------------------
DATAPUMP_EXP_FULL_DATABASE     CREATE TABLE
DATAPUMP_EXP_FULL_DATABASE     CREATE SESSION

select grantee, privilege from dba_sys_privs where grantee='EXP_FULL_DATABASE';

GRANTEE                        PRIVILEGE
------------------------------ -----------------------------------
EXP_FULL_DATABASE              EXEMPT REDACTION POLICY
EXP_FULL_DATABASE              CREATE TABLE
EXP_FULL_DATABASE              SELECT ANY SEQUENCE
EXP_FULL_DATABASE              READ ANY FILE GROUP
EXP_FULL_DATABASE              EXECUTE ANY PROCEDURE
EXP_FULL_DATABASE              FLASHBACK ANY TABLE
EXP_FULL_DATABASE              ADMINISTER RESOURCE MANAGER
EXP_FULL_DATABASE              SELECT ANY TABLE
EXP_FULL_DATABASE              BACKUP ANY TABLE
EXP_FULL_DATABASE              ADMINISTER SQL MANAGEMENT OBJECT
EXP_FULL_DATABASE              RESUMABLE
EXP_FULL_DATABASE              CREATE SESSION
EXP_FULL_DATABASE              ANALYZE ANY
EXP_FULL_DATABASE              EXECUTE ANY TYPE

select grantee, privilege from dba_sys_privs where grantee='SELECT_CATALOG_ROLE';

no rows selected

select grantee, privilege from dba_sys_privs where grantee='EXECUTE_CATALOG_ROLE';

no rows selected

select grantee, privilege from dba_sys_privs where grantee='HS_ADMIN_SELECT_ROLE';

no rows selected

 

2. 실제 dpump_dir1, dpump_dir2 디렉토리 생성 및 hr4으로 dpump_dir1, dpump_dir2 디렉토리 오브젝트 생성 (기본 data pump 디렉토리인 data_pump_dir의 /u01/app/oracle/admin/orcl/dpdump 아래에 생성)

- 참고: 어떤 유저로 디렉토리 생성하든 owner는 sys이다. 

[oracle@enp0s3 ~]$ mkdir /u01/app/oracle/admin/orcl/dpdump/test_dir_1, /u01/app/oracle/admin/orcl/dpdump/test_dir_2
SQL> conn hr4/hr4
SQL> create or replace directory dpump_dir1 as '/u01/app/oracle/admin/orcl/dpdump/test_dir_1';
SQL> create or replace directory dpump_dir2 as '/u01/app/oracle/admin/orcl/dpdump/test_dir_2';

 

3. hr5 유저 생성 및 imp_full_database 권한 부여

SQL> conn system/pw
Connected.
SQL> create tablespace hr5_tbs_01 datafile 'hr5_tbs_f1.dbf' size 20M autoextend on online;

Tablespace created.

SQL> create user hr5 identified by "hr5" default tablespace hr5_tbs_01;

User created.

SQL> grant create session to hr5;

Grant succeeded.

 

4. hr4에 샘플 스키마 오브젝트(employees 테이블 등 추가) 

SQL> conn system/pw
SQL> grant resource to hr4;
SQL> grant unlimited tablespace to hr4;
create table hr4.countries as select * from hr.countries;
create table hr4.departments as select * from hr.departments;
create table hr4.employees as select * from hr.employees;
create table hr4.jobs as select * from hr.jobs;
create table hr4.job_history as select * from hr.job_history;
create table hr4.locations as select * from hr.locations;
create table hr4.regions as select * from hr.regions;

 

5. hr4 유저로 expdp 작업 수행 및 hr5 유저로 impdp 작업 수행 

expdp hr4 directory=DPUMP_DIR1 dumpfile=exp_hr_schema_%t_%U.dmp logfile=exp_hr_schema_20220216_01.log

-- quota 관련 오류 발생 시 해결방법: 유저에게 space quota 제공 
alter user <user_name> quota unlimited on <tablespace_name>;
SQL> select * from dba_ts_quotas;

TABLESPACE_NAME                USERNAME        BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---------- ---
SYSAUX                         AUDSYS        1376256         -1        168         -1 NO
SYSTEM                         OUTLN          589824         -1         72         -1 NO
SYSAUX                         GSMADMIN_I     917504         -1        112         -1 NO
                               NTERNAL

SYSAUX                         DBSFWUSER           0         -1          0         -1 NO
SYSAUX                         APPQOSSYS           0         -1          0         -1 NO
SYSAUX                         GGSYS               0         -1          0         -1 NO
SYSAUX                         MDSYS       210894848         -1      25744         -1 NO
SYSTEM                         MDSYS               0         -1          0         -1 NO
SYSAUX                         OLAPSYS             0         -1          0         -1 NO
SYSTEM                         LBACSYS        327680         -1         40         -1 NO
SYSAUX                         HR            4063232         -1        496         -1 NO

SQL> select * from dba_ts_quotas;

TABLESPACE_NAME                USERNAME        BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---------- ---
SYSAUX                         AUDSYS        1376256         -1        168         -1 NO
SYSTEM                         OUTLN          589824         -1         72         -1 NO
SYSAUX                         GSMADMIN_I     917504         -1        112         -1 NO
                               NTERNAL

SYSAUX                         DBSFWUSER           0         -1          0         -1 NO
SYSAUX                         APPQOSSYS           0         -1          0         -1 NO
SYSAUX                         GGSYS               0         -1          0         -1 NO
SYSAUX                         MDSYS       210894848         -1      25744         -1 NO
SYSTEM                         MDSYS               0         -1          0         -1 NO
SYSAUX                         OLAPSYS             0         -1          0         -1 NO
SYSTEM                         LBACSYS        327680         -1         40         -1 NO
SYSAUX                         HR            4063232         -1        496         -1 NO
HR5_TBS_01                     HR5                 0         -1          0         -1 NO

-- hr5에게 imp_full_database 롤 부여

 

 

참고사항 

- 보내고 받을 스키마가 다를 경우 impdp 명령 시 remap_schema 파라미터 지정하거나 expdp 명령 시 exclude 파라미터를 추가해줘야한다.
- logfile 파라미터에는 대체 변수(%t, %U등) 유효하지 않고 오직 datafile, dump file에서만 유효하다. 
 import 성공

try8:

[oracle@enp0s3 ~]$ impdp hr5 directory=dpump_dir2 dumpfile=dpump_dir1:exp_hr_schema_20220216_01.dmp remap_schema=hr4:hr5 logfile=imp_hr_schema_%t_%U.log

Import: Release 19.0.0.0.0 - Production on Wed Feb 16 10:51:56 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "HR5"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR5"."SYS_IMPORT_FULL_01":  hr5/pw directory=dpump_dir2 dumpfile=dpump_dir1:exp_hr_schema_20220216_01.dmp remap_schema=hr4:hr5 logfile=imp_hr_schema_%t_%U.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HR5" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
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 "HR5"."EMPLOYEES"                           17.09 KB     107 rows
. . imported "HR5"."LOCATIONS"                           8.437 KB      23 rows
. . imported "HR5"."JOB_HISTORY"                         7.195 KB      10 rows
. . imported "HR5"."JOBS"                                7.109 KB      19 rows
. . imported "HR5"."DEPARTMENTS"                         7.125 KB      27 rows
. . imported "HR5"."COUNTRIES"                           6.375 KB      25 rows
. . imported "HR5"."REGIONS"                             5.546 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "HR5"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Feb 16 10:52:14 2022 elapsed 0 00:00:16

-- 위에서 1 error는 타겟 스키마인 hr5 유저가 존재해서 표시되는 것이며, 대상 데이터베이스에 대상 스키마가 이미 있는 경우 ORA-31684를 무시할 수 있습니다. 오류를 피하기 위해 CREATE USER 문을 제외할 수 있습니다. 그 방법은 아래와 같습니다:

$ impdp hr5 directory=dpump_dir2 dumpfile=dpump_dir1:exp_hr_schema_20220216_01.dmp table_exists_action=skip remap_schema=hr4:hr5 exclude=USER:\"=\'HR4\'\" logfile=imp_hr_schema_20220216_02.log

위의 exclude 파라미터값은 expdp때 사용한 유저명을 입력해야 합니다.