[Oracle] USER, PUBLIC SYNONYM, PROFILE 이관시 DATAPUMP 사용법

오라클 개발 서버에 운영과 동일한 환경을 세팅하기 위한 작업 진행 중

public synonym과 profile 정보도 함께 가지고 오고자 작업 진행함.

 

USER, PUBLIC SYNONYM, PROFILE, ROLE 등 특정 스키마에 포함된 object 가 아닌 경우에는

expdp full = y include = public_synonym, profile을 조건 사용한다.

 

full=y : 데이터베이스의 모든 스키마 대상

INCLUDE =  추출에 포함할 데이터베이스 개체 유형

 

PROFILE 테스트

-- test profile 생성
create profile TEST_PROFILE LIMIT
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_CALL  UNLIMITED
PRIVATE_SGA UNLIMITED
CONNECT_TIME  UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
SESSIONS_PER_USER UNLIMITED
COMPOSITE_LIMIT UNLIMITED
CPU_PER_CALL  UNLIMITED
CPU_PER_SESSION UNLIMITED
PASSWORD_LOCK_TIME  1
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX  UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME  UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED;  


--생성 확인
SELECT *
  FROM DBA_PROFILES
 where profile = 'TEST_PROFILE';

 

 

profile expdp 진행 

expdp 'dbe/#roqkf0909' directory=DIR_BACKUP dumpfile=dpfile_profile_%U.dmp full = Y INCLUDE = PROFILE

[oracle@devoracle01 orabackup]$ expdp 'dbe/#roqkf0909' directory=DIR_BACKUP dumpfile=dpfile_profile_%U.dmp full = Y INCLUDE = PROFILE

Export: Release 11.2.0.2.0 - Production on Thu Dec 28 17:30:34 2023

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "dbe"."SYS_EXPORT_FULL_01":  dbenuri/******** directory=DIR_BACKUP dumpfile=dpfile_profile_%U.dmp full=Y INCLUDE = PROFILE
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/PROFILE
Master table "dbe"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DBENURI.SYS_EXPORT_FULL_01 is:
  /orabackup/dpfile_profile_01.dmp
Job "dbe"."SYS_EXPORT_FULL_01" successfully completed at 17:30:35

 

 

--프로파일 삭제
drop profile TEST_PROFILE;

 

 

impdp 진행

[oracle@devoracle01 orabackup]$ impdp 'dbe/#roqkf0909' directory=DIR_BACKUP dumpfile=dpfile_profile_01.dmp

Import: Release 11.2.0.2.0 - Production on Thu Dec 28 17:35:08 2023

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "dbe"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "dbe"."SYS_IMPORT_FULL_01":  dbenuri/******** directory=DIR_BACKUP dumpfile=dpfile_profile_01.dmp
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Job "dbe"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 17:35:09

 

 

-- 생성 확인
SELECT *
  FROM DBA_PROFILES
 where profile = 'TEST_PROFILE';

삭제한 프로파일이 복원 후 다시 생성됐음을 확인할 수 있다.

 

퍼블릭시노님도 동일하게 확인 테스트 가능 

 

 

참고) 

https://www.enterprisedb.com/docs/migration_portal/latest/04_mp_migrating_database/01_mp_schema_extraction/01_data_pump_utility/