오라클 11g R2 에서 오라클 19c datapump schemas 옵션 이관시 주의사항

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4, 19.11.0.0

 

방법 : 오라클 11g R2 to 19c datapump schemas 옵션 이관시 주의사항

datapump를 이용해 oracle 11gR2에서 full=y로 expdp로 dump 파일을 만든뒤

impdp schemas 옵션으로 19c나 다른 db로 이관할 때 주의사항을 설명함

 

 

expdp 옵션인 full=y 로 dump 파일을 뽑은 경우 데이터베이스의 모든 오브젝트가 다 들어있음

하지만 impdp 옵션인 schemas 옵션으로 넣을 경우 일부 오브젝트가 빠지게됨

public synonym과 role, profile, public db link 등이 빠지게됨

 

 

테스트를 통해 확인해보도록함

 

 

11gR2 db에서 샘플 유저 생성

SQL> 
create user test identified by test account unlock default tablespace users quota unlimited on users;
grant resource, connect, dba to test;
 
SQL> 
conn test/test
create table test_tab as select * from dba_objects;

 

 

11gR2 db에서 샘플 오브젝트 생성

시노님, 퍼블릭 시노님 생성
SQL> 
conn test/test
create synonym test_tab_syno for test.test_tab;
conn / as sysdba
create public synonym test_tab_psyno for test.test_tab;
 
role 생성 및 부여
SQL> 
conn / as sysdba
create role test_role;
grant create user,alter user, drop user to test_role;
grant test_role to test;
 
db 링크, 퍼블릭 db 링크 생성
SQL> 
conn test/test
create database link test_link connect to system identified by oracle using 'orcl19';
conn / as sysdba
create public database link test_plink connect to system identified by oracle using 'orcl19';
 
프로파일 생성 및 적용
SQL>
conn / as sysdba
create profile test_profile limit
idle_time 10
failed_login_attempts 6
password_life_time 90
password_reuse_time unlimited
password_reuse_max 4
password_verify_function NULL
password_lock_time 1
password_grace_time 7;
alter user test profile test_profile ;

 

 

11gR2 db에서 디덱토리 생성 후 full=y 옵션을 이용해 expdp 진행

datapump용 폴더 생성
$ mkdir -p /home/oracle/pump
 
datapump directory 생성
SQL> create directory test_pump as '/home/oracle/pump';
 
Directory created.
 
expdp 진행
$ expdp system/oracle directory=test_pump dumpfile=2022_pump_test.dmp logfile=2022_pump_test_imp.log full=y
 
Export: Release 11.2.0.4.0 - Production on Thu Feb 24 00:07:41 2022
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 6 days
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/*** directory=test_pump dumpfile=2022_pump_test.dmp logfile=2022_pump_test_imp.log full=y 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 584.4 MB
Processing object type DATABASE_EXPORT/TABLESPACE
.
.
. . exported "TEST"."TEST_TAB"                           1.278 MB   14192 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /home/oracle/pump/2022_pump_test.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Thu Feb 24 00:07:51 2022 elapsed 0 00:00:10

 

 

덤프파일 확인 및 전송

덤프파일 확인
$ cd /home/oracle/pump/
$ ls
2022_pump_test.dmp  2022_pump_test_imp.log
 
덤프파일 전송
$ scp 2022_pump_test.dmp oracle@10.10.10.60:/home/oracle/pump/
oracle@10.10.10.60's password: 
2022_pump_test.dmp            100%  355MB 177.6MB/s   00:02 

 

 

19c에 미리 유저 생성

SQL> 
create user test identified by test account unlock default tablespace users quota unlimited on users;

 

 

19c db에서 디덱토리 생성 후 schemas=test 옵션을 이용해 impdp 진행

datapump용 폴더 생성
$ mkdir -p /home/oracle/pump
 
datapump directory 생성
SQL> create directory test_pump as '/home/oracle/pump';
 
Directory created.
 
impdp 진행
$ impdp system/oracle directory=test_pump dumpfile=2022_pump_test.dmp logfile=2022_pump_test_imp.log schemas=test
 
Import: Release 19.0.0.0.0 - Production on Wed Feb 24 15:12:26 2022
Version 19.11.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_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/ directory=test_pump dumpfile=2022_pump_test.dmp logfile=2022_pump_test_imp.log schemas=test 
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-39083: Object type USER:"TEST" failed to create with error:
ORA-02380: profile TEST_PROFILE does not exist
 
Failing sql is:
 CREATE USER "TEST" IDENTIFIED BY VALUES 'S:21901359CF28DD6528D23BA4DD1BED7D56E29E8CB7FF31A52557EEF84255;7A0F2B316C212D67' 
DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE "TEST_PROFILE"
 
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01919: role 'TEST_ROLE' does not exist
 
Failing sql is:
 GRANT "TEST_ROLE" TO "TEST"
 
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "TEST"."TEST_TAB"                           1.278 MB   14192 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Wed Feb 23 15:12:30 2022 elapsed 0 00:00:03

먼저 로그에서도 알수있듯이

21번째 줄에 PROFILE이 없어서 에러가 발생함

30번째 줄에서는 ROLE이 없어서 에러가 발생함

 

 

이외에 어떤 오브젝트가 빠졌는지 확인

impdp 옵션 중 하나인 sqlfile을 이용해 impdp 내용을 스크립트로 확인

$ impdp system/oracle directory=test_pump dumpfile=2022_pump_test.dmp logfile=2022_pump_test_imp.log schemas=test sqlfile=test_sqlfile. 
sql
 
Import: Release 19.0.0.0.0 - Production on Wed Feb 24 15:36:08 2022
Version 19.11.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_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01":  system/ directory=test_pump dumpfile=2022_pump_test.dmp logfile=2022_pump_test_imp.log schemas=test sqlfile=test_sqlfile.sql 
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Wed Feb 24 15:36:10 2022 elapsed 0 00:00:02

 

 

파일 확인

$ vi test_sqlfile.sql 
-- CONNECT SYSTEM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/USER
 CREATE USER "TEST" IDENTIFIED BY VALUES 'S:21901359CF28DD6528D23BA4DD1BED7D56E29E8CB7FF31A52557EEF84255;7A0F2B316C212D67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP2"
      PROFILE "TEST_PROFILE";
-- new object type path: DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
GRANT UNLIMITED TABLESPACE TO "TEST";
-- new object type path: DATABASE_EXPORT/SCHEMA/ROLE_GRANT
 GRANT "TEST_ROLE" TO "TEST";
 GRANT "CONNECT" TO "TEST";
 GRANT "DBA" TO "TEST";
 GRANT "RESOURCE" TO "TEST";
-- new object type path: DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
 ALTER USER "TEST" DEFAULT ROLE ALL;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
DECLARE 
  TEMP_COUNT NUMBER; 
  SQLSTR VARCHAR2(200); 
BEGIN 
  SQLSTR := 'ALTER USER "TEST" QUOTA UNLIMITED ON "USERS"';
  EXECUTE IMMEDIATE SQLSTR;
EXCEPTION 
  WHEN OTHERS THEN
    IF SQLCODE = -30041 THEN 
      SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES 
              WHERE TABLESPACE_NAME = ''USERS'' AND CONTENTS = ''TEMPORARY''';
      EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
      IF TEMP_COUNT = 1 THEN RETURN; 
      ELSE RAISE; 
      END IF;
    ELSE
      RAISE;
    END IF;
END;
/
-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
-- CONNECT TEST
CREATE DATABASE LINK "TEST_LINK"
   CONNECT TO "SYSTEM" IDENTIFIED BY VALUES ':1'
   USING 'orcl19';
-- new object type path: DATABASE_EXPORT/SCHEMA/SYNONYM
-- CONNECT SYSTEM
CREATE SYNONYM "TEST"."TEST_TAB_SYNO" FOR "TEST"."TEST_TAB";
-- new object type path: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
-- CONNECT TEST
 
BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'ORCL11', inst_scn=>'40690363');
COMMIT; 
END; 
/ 
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/TABLE
-- CONNECT SYSTEM
CREATE TABLE "TEST"."TEST_TAB" 
   (    "OWNER" VARCHAR2(30 BYTE), 
    "OBJECT_NAME" VARCHAR2(128 BYTE), 
    "SUBOBJECT_NAME" VARCHAR2(30 BYTE), 
    "OBJECT_ID" NUMBER, 
    "DATA_OBJECT_ID" NUMBER, 
    "OBJECT_TYPE" VARCHAR2(19 BYTE), 
    "CREATED" DATE, 
    "LAST_DDL_TIME" DATE, 
    "TIMESTAMP" VARCHAR2(19 BYTE), 
    "STATUS" VARCHAR2(7 BYTE), 
    "TEMPORARY" VARCHAR2(1 BYTE), 
    "GENERATED" VARCHAR2(1 BYTE), 
    "SECONDARY" VARCHAR2(1 BYTE), 
    "NAMESPACE" NUMBER, 
    "EDITION_NAME" VARCHAR2(30 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- fixup virtual columns... 
-- done fixup virtual columns 

10번째 줄에 TEST 유저를 생성하는 구문은 존재하지만
TEST_PROFILE을 생성하는 CREATE 문은 보이지 않음
17번째 줄에 ROLE 을 부여하는 구문은 존재하지만 
TEST_ROLE라는 ROLE를 만드는 CREATE 문은 보이지 않음
46번째 줄에 DB 링크를 생성하는 구문이 존재하고
51번째 줄에 시노님을 생서하는 구문이 존재하지만
PUBLIC SYNONYM과 PUBLIC DB 링크는 보이지 않음

 

 

이 경우 퍼블릭 시노님, 퍼블릭 db 링크와 role, profile을 수동으로 마이그레이션 해줘야함

 

 

퍼블릭 시노님 생성 구문

SQL>
select 'create public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';' as "create" from dba_synonyms
where owner = 'PUBLIC'
and table_owner not in ('SYS','SYSTEM');
 
create
-----------------------------------------------------------------------------------------------------------------------
create public synonym TEST_TAB_PSYNO for TEST.TEST_TAB;

해당 내용 19c에서 실행

 

 

퍼블릭 db 링크 생성 구문

SQL>
select 'create public database link '||db_link||' connect to '||username||' identified by '||'"PW"'||' using '''||host||''';' as "link" from dba_db_links
where owner = 'PUBLIC';
 
link
---------------------------------------------------------------------------------------------
create public database link TEST_PLINK connect to SYSTEM identified by "PW" using 'orcl19';

퍼블릭 시노님과는 다르게 db 링크는 패스워드를 입력 해줘야해서 이부분은 따로 확인 후 넣어줘야함

해당 내용 19c에서 실행

 

 

profile 생성 구문

SQL>
select 'create profile '||profile||' limit ' as "profile" from dba_profiles
where profile = 'TEST_PROFILE'
and rownum<= 1
union all
select resource_name||' '||limit as "profile" from dba_profiles
where profile = 'TEST_PROFILE'
union all
select ';' from dual;
 
profile
-------------------------------------------------------------------------
create profile TEST_PROFILE limit
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME 10
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 6
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX 4
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
;
 
18 rows selected.
 
profile 생성 후 적용(19c)
SQL> alter user test profile test_profile;
 
User altered.

프로파일 지정 후 구문 출력

해당 내용 19c에서 실행

 

 

role 확인

SQL> 
select * from dba_role_privs
where grantee = 'TEST';
 
GRANTEE                GRANTED_ROLE              ADM DEF
------------------------------ ------------------------------ --- ---
TEST                   RESOURCE               NO  YES
TEST                   DBA                  NO  YES
TEST                   TEST_ROLE              NO  YES
TEST                   CONNECT                  NO  YES
 
SQL> select * from dba_sys_privs where grantee='TEST_ROLE';
 
GRANTEE                PRIVILEGE                ADM
------------------------------ ---------------------------------------- ---
TEST_ROLE               ALTER USER                NO
TEST_ROLE               DROP USER                NO
TEST_ROLE               CREATE USER                NO

 

 

role 생성 구문

SQL> 
select 'create role '||grantee||';' as "role" from dba_sys_privs 
where grantee='TEST_ROLE'
and rownum <= 1
union all
select 'grant '||privilege||' to '||grantee||';' as "role"
from dba_sys_privs 
where grantee='TEST_ROLE';
 
role
---------------------------------------------------------------------------------
create role TEST_ROLE;
grant ALTER USER to TEST_ROLE;
grant DROP USER to TEST_ROLE;
grant CREATE USER to TEST_ROLE;
 
실패한 role 권한 재부여
SQL> grant test_role to test;

 

 

위와 같이 datapump 를 이용해서 schema별로 이관할때 주의사항을 잘 확인하고 진행해야함

 

 

참조 : 

https://neo-orcl.tistory.com/112

https://gent.tistory.com/397

https://seungzzang5811.tistory.com/29