안녕하세요
Surmin 입니다.
오늘은 PDB 환경에서 EXPORT 및 IMPORT 방법에 대해 알아보겠습니다. 참고 부탁드립니다.
TEST
#CDB유저 생성 및 권한부여
CREATE USER C##PDB_TEST IDENTIFIED BY 1234;
GRANT RESOURCE, CONNECT TO C##PDB_TEST;테이블스페이스 생성
CREATE TABLESPACE TEST_TBS
DATAFILE '/app/oracle/oradata/ORCL/test_tbs01.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
--쿼터할당
ALTER USER C##PDB_TEST QUOTA UNLIMITED ON TEST_TBS;
--테이블 생성
CREATE TABLE C##PDB_TEST.emp_test1 (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
dept VARCHAR2(30)
) TABLESPACE TEST_TBS;
--데이터 입력
INSERT INTO C##PDB_TEST.emp_test1 VALUES (1, 'hone', 'sdae');
INSERT INTO C##PDB_TEST.emp_test1 VALUES (2, 'dnge', 'donq');
commit;
--테이터 확인
select * from C##PDB_TEST.emp_test1;
EMP_ID EMP_NAME
---------- --------------------------------------------------
DEPT
------------------------------
1 hone
sdae
2 dnge
donq아카이브 설정
[oracle@ORA19C ~]$ mkdir clone
[oracle@ORA19C clone]$ mkdir archive
alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile;
alter system set log_archive_dest = '' scope = spfile;
alter system set log_archive_dest_1 = 'location=/home/oracle/clone/archive' scope = spfile;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
SQL>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/clone/archive
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19clone 절차
#컨트롤파일 생성문 백업
alter database backup controlfile to trace as '/home/oracle/re.sql';
#USERS는 필요없음 왜냐하면 TEST 테이블 복원을 할려고 하는것이기 때문에
select distinct 'alter tablespace '||tablespace_name||' begin backup;' from dba_data_files;
==
'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'
-------------------------------------------------------------
alter tablespace SYSAUX begin backup;
alter tablespace SYSTEM begin backup;
alter tablespace TEST_TBS begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS begin backup;
==
!cp -av /app/oracle/oradata/ORCL/*.dbf /home/oracle/clone/
select distinct 'alter tablespace '||tablespace_name||' end backup;' from dba_data_files;
==
'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;'
-----------------------------------------------------------
alter tablespace TEST_TBS end backup;
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace USERS end backup;
alter tablespace SYSTEM end backup;
==
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
--아카이브 clone db복사
[oracle@ORA19C clone]$ cp -av archive archive_clone
'archive' -> 'archive_clone'
'archive/1_19_1216590216.arc' -> 'archive_clone/1_19_1216590216.arc'
'archive/1_20_1216590216.arc' -> 'archive_clone/1_20_1216590216.arc'
'archive/1_21_1216590216.arc' -> 'archive_clone/1_21_1216590216.arc'
#init 파일 없으면 spfile 이용하여 만들기
[oracle@ORA19C dbs]$ cd $ORACLE_HOME/dbs
[oracle@ORA19C dbs]$ cp -av initorcl.ora initclone.ora
'initorcl.ora' -> 'initclone.ora'
#SID수정하기
##필요한 디렉터리 만들어주기
[oracle@ORA19C dbs]$ vi initclone.ora
===
*.audit_file_dest='/app/oracle/admin/clone/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/app/oracle/oradata/clone/control01.ctl'
*.db_block_size=8192
*.db_name='clone'
*.db_recovery_file_dest='/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=17271m
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_clone'
*.log_archive_dest=''
*.log_archive_dest_1='location=/home/oracle/clone/archive_clone' <-아카이브 경로 수정하기
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=387m <-PAG 값 크면 최소 값으로 수정하기
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1158m <-SGA 값 크면 최소 값으로 수정하기
*.undo_tablespace='UNDOTBS1'
===- re.sql 파일 수정
- 해당 부분에서 PDB부분은 다 제거한다.
- 경로 변경 해준다.
vi /home/oracle/re.sql
==
CREATE CONTROLFILE SET DATABASE "clone" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/oracle/clone/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/home/oracle/clone/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/home/oracle/clone/redo03.log' SIZE 200M BLOCKSIZE 512
DATAFILE
'/home/oracle/clone/system01.dbf',
'/home/oracle/clone/sysaux01.dbf',
'/home/oracle/clone/undotbs01.dbf',
'/home/oracle/clone/test_tbs01.dbf'
CHARACTER SET KO16KSC5601
;
==
#프롬프트창 하나 더 띄움
export ORACLE_SID=clone
[oracle@oracle19c ~]$ sqlplus / as sysdba
SQL> startup nomount
--컨트롤 위치 확인
SQL> show parameter control
==
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/app/oracle/oradata/clone/cont
rol01.ctl
control_management_pack_access string
DIAGNOSTIC+TUNING
==
--sql실행
SQL> @re.sql
==
Control file created.
==
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1485802 generated at 11/12/2025 01:19:36 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/archive_clone/1_19_1216590216.arc
ORA-00280: change 1485802 for thread 1 is in sequence #19
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1485952 generated at 11/12/2025 01:22:45 needed for thread 1
ORA-00289: suggestion : /home/oracle/clone/archive_clone/1_20_1216590216.arc
ORA-00280: change 1485952 for thread 1 is in sequence #20
ORA-00278: log file '/home/oracle/clone/archive_clone/1_19_1216590216.arc' no
longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
⇒ 여기서 중요한 점은 hot backup은 꼭 한개 의 아카이브를 가지고 있어야 한다 없을 경우 현재의 redo 라도 있어야 한다 없으면 recover 실패한다.
(히든 파라미터를 사용하면 조금 다를 수 있음)
데이터 조회
SQL> alter database open resetlogs;
#Test 테이터 조회
set linesize 120
set pagesize 20
column emp_id format 99999
column emp_name format a20
column dept format a15
SELECT * FROM C##PDB_TEST.emp_test1;SQL> SQL> SQL> SQL> SQL>
EMP_ID EMP_NAME DEPT
------ -------------------- ---------------
1 hone sdae
2 dnge donq
users TBS 없어도 잘보임 그리고 데이터 조회에 문제없음을 확인
SQL> SET LINESIZE 200
SQL> SET PAGESIZE 100
SQL> COL FILE_NAME FORMAT A60
SQL> COL TABLESPACE_NAME FORMAT A20
SQL> COL STATUS FORMAT A10
COL AUTOEXTENSIBLE FORMAT A5
SQL> SQL> COL ONLINE_STATUS FORMAT A10
SQL>
SELECT FILE_ID,
SQL> 2 FILE_NAME,
3 TABLESPACE_NAME,
BYTES/1024/1024 SIZE_MB,
4 5 MAXBYTES/1024/1024/1024 MAX_GB,
6 AUTOEXTENSIBLE AUTO,
STATUS,
7 8 ONLINE_STATUS
9 FROM DBA_DATA_FILES
ORDER BY FILE_ID; 10
FILE_ID FILE_NAME TABLESPACE_NAME SIZE_MB MAX_GB AUT STATUS ONLINE_STA
---------- ------------------------------------------------------------ -------------------- ---------- ---------- --- ---------- ----------
1 /home/oracle/clone/system01.dbf SYSTEM 760 31.9999847 YES AVAILABLE SYSTEM
3 /home/oracle/clone/sysaux01.dbf SYSAUX 550 31.9999847 YES AVAILABLE ONLINE
5 /home/oracle/clone/undotbs01.dbf UNDOTBS1 340 31.9999847 YES AVAILABLE ONLINE
7 /app/oracle/product/19c/dbs/MISSING00007 USERS AVAILABLE RECOVER
12 /home/oracle/clone/test_tbs01.dbf TEST_TBS 100 .48828125 YES AVAILABLE ONLINE
temp 할당
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/clone/temp01.dbf' REUSE;
SET LINESIZE 200
SET PAGESIZE 50
COL FILE_NAME FORMAT A60
COL TABLESPACE_NAME FORMAT A20
COL AUTOEXTENSIBLE FORMAT A5
SELECT FILE_NAME,
TABLESPACE_NAME,
BYTES/1024/1024 SIZE_MB,
MAXBYTES/1024/1024/1024 MAX_GB,
AUTOEXTENSIBLE AUTO
FROM DBA_TEMP_FILES;
#auto 가 no일경우 ON 설정
ALTER DATABASE TEMPFILE '/home/oracle/clone/temp01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
FILE_NAME TABLESPACE_NAME SIZE_MB MAX_GB AUT
------------------------------------------------------------ -------------------- ---------- ---------- ---
/home/oracle/clone/temp01.dbf TEMP 20 0 NO
#확인
SQL> SELECT FILE_NAME,
TABLESPACE_NAME,
BYTES/1024/1024 SIZE_MB,
MAXBYTES/1024/1024/1024 MAX_GB,
AUTOEXTENSIBLE AUTO
FROM DBA_TEMP_FILES; 2 3 4 5 6
FILE_NAME TABLESPACE_NAME SIZE_MB MAX_GB AUT
------------------------------------------------------------ -------------------- ---------- ---------- ---
/home/oracle/clone/temp01.dbf TEMP 20 31.9999847 YES
clone에서 export
--디렉토리 확인
SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM DBA_DIRECTORIES;
expdp system/oracle schemas=C##PDB_TEST directory=DATA_PUMP_DIR logfile=expdp_test.log dumpfile=expdp_test.dmp
==
[oracle@ORA19C ~]$ expdp system/oracle schemas=C##PDB_TEST directory=DATA_PUMP_DIR logfile=expdp_test.log dumpfile=expdp_test.dmp
Export: Release 19.0.0.0.0 - Production on Wed Nov 12 02:17:41 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
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. (CDB$ROOT에서 expdp/impdp는 일반적이지 않음)
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=C##PDB_TEST directory=DATA_PUMP_DIR logfile=expdp_test.log dumpfile=expdp_test.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/ROLE_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/CONSTRAINT/CONSTRAINT
. . exported "C##PDB_TEST"."EMP_TEST1" 5.945 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/app/oracle/admin/orcl/dpdump/expdp_test.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 12 02:18:00 2025 elapsed 0 00:00:19
==- import 작업
- 임시 계정 하나 만든 뒤 remap_schema 사용
CREATE USER C##PDB_TESTT IDENTIFIED BY 1234;
GRANT RESOURCE, CONNECT TO C##PDB_TESTT;import 하는데 remap_schema 사용
impdp system/oracle schemas=C##PDB_TEST directory=DATA_PUMP_DIR logfile=impdp_test.log dumpfile=expdp_test.dmp remap_schema=C##PDB_TEST:C##PDB_TESTT
==
Import: Release 19.0.0.0.0 - Production on Wed Nov 12 02:23:52 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
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas=C##PDB_TEST directory=DATA_PUMP_DIR logfile=impdp_test.log dumpfile=expdp_test.dmp remap_schema=C##PDB_TEST:C##PDB_TESTT
Processing object type SCHEMA_EXPORT/ROLE_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 "C##PDB_TESTT"."EMP_TEST1" 5.945 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Nov 12 02:24:08 2025 elapsed 0 00:00:14
==조회 시 사용자(스키마) 만 다르고 데이터는 같음을 확인
SQL> select * from C##PDB_TESTT.EMP_TEST1;
EMP_ID EMP_NAME DEPT
------ -------------------- ---------------
1 hone sdae
2 dnge donq