유니원아이앤씨 DB 기술지원팀 2015 년 09 월 09 일 문서정보 프로젝트명 TTS (Transportable Tablespace) 서브시스템명 버전 1.0 문서명 TTS (Transportable Tablespace) 작성일 2015-08-16 작성자 김성한 최종수정일 2015-09-09 문서번호 UNIONE-201509091051-KSH 재개정이력 일자내용수정인버전 문서배포이력 발신자수신자배포목적일자비고.
Table of Contents 1 TTS (Transportable Tablespace) 란... 3 2 TTS 제약사항... 3 3 TEST 1- 동기종 platform... 4 3.1 TEST 환경... 4 3.2 TEST... 4 3.2.1 Source DB 설정... 4 3.2.2 Export... 5 3.2.3 Target DB 설정... 7 3.2.4 Import... 8 3.2.5 TEST 확인... 9 4 TEST 2 - 이기종 Platform...12 4.1 TEST 환경...12 4.2 TEST...13 4.2.1 Source DB 확인...13 4.2.2 Source DB 설정...13 4.2.3 Export...15 4.2.4 Target DB 정보확인...17 4.2.5 RMAN - dumpfile convert...18 4.2.6 Target DB 설정...19 4.2.7 Import...20 4.2.8 TEST 확인...23.
1 TTS (Transportable Tablespace) 란 Transportable Tablespace 는서로다른 DB 간에대량의데이터를 Tablespace 단위로 datafile 를이동하여 옮기는가장빠르고효율적인기능이다. migration 할 tablespace의 metadata만 export 한후 datafile를 source db에서 target db로복사한후 medata만 import 하면바로사용이가능하다. 2 TTS 제약사항 source 와 target db 간의 character set 이동일해야한다 system, sysaux, undo, temp tablespace 는지원불가능 10g 이전버전은 source 와 target db 가같은플랫폼일때만지원 10g 이후버전은 rman 변환작업을통하여지원가능 Standard Edition 은지원불가능 Target db 는 Source db 버전과같거나더높아야한다 같은이름을갖는테이블스페이스가이미존재하는타겟데이타베이스에테이블스페이스를이전할수없다. 그러나, 트랜스포트수행전에트랜스포트될테이블스페이스나 destination 테이블스페이스를 rename할수있다. 만일테이블스페이스객체의 owner가타겟데이타베이스에존재하지않으면, 트랜스포터블테이블스페이스 import 전에 username은수동으로생성해야한다. 3/23
3 TEST 1- 동기종 platform 3.1 TEST 환경 동일한 platform, character : KO16MSWIN949 동일 source db oracle EE 11.2.0.3 on linux 6.3 target db oracle EE 11.2.0.3 on linux 6.3 3.2 TEST 3.2.1 Source DB 설정 ## transport 할 tablespace 생성 SQL> create tablespace tts_ts datafile '/oradata/orcl/tts_ts01.dbf' size 10m; SQL> select tablespace_name, bytes/1024/1024, file_name from dba_data_files; TABLESPACE_NAME BYTES/1024/1024 FILE_NAME ------------------------------ --------------- ---------------------------------- USERS 5 /oradata/orcl/users01.dbf UNDOTBS1 75 /oradata/orcl/undotbs01.dbf SYSAUX 710 /oradata/orcl/sysaux01.dbf SYSTEM 720 /oradata/orcl/system01.dbf TTS_TS 10 /oradata/orcl/tts_ts01.dbf ## user 생성 SQL> create user tts_user 2 identified by tts 3 default tablespace tts_ts ; User created. SQL> grant resource, connect to tts_user; 4/23
## table 생성및 Data inset SQL> create table tts_user.tts_table (no number); Table created. SQL> insert into tts_user.tts_table values (1); 1 row created. SQL> insert into tts_user.tts_table values (2); 1 row created. SQL> commit; Commit complete. SQL> select * from tts_user.tts_table; NO ---------- 1 2 3.2.2 Export ## Datapump 설정 ## export 할 directory 생성 root@source~]# mkdir -p /data/pump root@ source ~]# chown -R oracle.dba /data/pump root@ source ~]# su - oracle oracle@ source ~]$ sqlplus "/as sysdba" SQL> create directory tts_dir as '/data/pump' ; SQL> grant read,write on directory tts_dir to public; 5/23
## TTS check - tablespace 가이동가능한지체크 Transport tablespace set 이 self-contained 인지점검한다. Transport 하고자하는 tablespace set은 self-contained 이어야만한다. 대상이되는 tablespace set 내에 partitioned table이존재한다면해당 table의모든 partition들이이들 tablespace 내에존재해야하며, 비슷하게 LOB column의 data들과함께이들 tablespace 내에존재해야하는데, 이렇게서로관련된 object들이 tablespace set 내에모두존재하는것을 self-contained라고한다. tablespace set이 self-contained하지않다면 transport 할수없다. SQL> exec dbms_tts.transport_set_check('tts_ts',true); PL/SQL procedure successfully completed. ## 관련 view 확인 transport tablespace set이 self-contained 인지에대한정보를 TRANSPORT_SET_VIOLATIONS view에기록해준다. SQL > select * from tranport_set_violations; no rows selected ## export 전에 tablespace 를 read only mode 로변경 SQL> alter tablespace tts_ts read only ; Tablespace altered. SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ ------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS TTS_TS READ ONLY ## datapump export 실행 [oracle@source~]$ expdp system/oracle directory=tts_dir transport_tablespaces=tts_ts dumpfile=tts.dmp 6/23
logfile=tts.log Export: Release 11.2.0.3.0 - Production on Wed Apr 30 10:28:04 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "file=tts.dmp" Location: Command Line, Replaced with: "dumpfile=tts.dmp" Legacy Mode has set reuse_dumpfiles=true parameter. Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=tts_dir transport_tablespaces=tts_ts dumpfile=tts.dmp logfile=tts.log reuse_dumpfiles=true Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /data/pump/tts.dmp ****************************************************************************** Datafiles required for transportable tablespace TTS_TS: /oradata/orcl/tts_ts01.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:28:51... [oracle@source~]$ pwd /data/pump [oracle@ source~]$ ls tts.dmp tts.log 3.2.3 Target DB 설정 ## source DB에서사용하던계정을동일하게생성후 datapump 관련작업을수행 7/23
oracle@target~]# sqlplus "/as sysdba" SQL> create user tts_user identified by tts User created. SQL> grant resource, connect to tts_user; SQL> exit oracle@target~]# exit root@ target ~]# mkdir -p /data/pump root@ target ~]# chown -R oracle.dba /data/pump root@ target ~]# su - oracle oracle@ target ~]$ sqlplus "/as sysdba" SQL> create directory tts_dir as '/data/pump' ; SQL> grant read,write on directory tts_dir to public; 3.2.4 Import ## export 받은 dmp 파일을 target DB /data/pump 디렉토리로복사 oracle@ source~]# scp tts.dmp 192.168.91.156:/data/pump The authenticity of host '192.168.91.156 (192.168.91.156)' can't be established. RSA key fingerprint is a3:64:78:86:1e:ff:dd:c8:b3:73:a8:9c:06:65:f0:38. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.91.156' (RSA) to the list of known hosts. root@192.168.91.156's password: tts.dmp 100% 88KB 88.0KB/s 00:00 ## tts_ts01.dbf 를 target DB로복사 ( 경로는 /oradata/target/ ) oracle@ source /oradata/ocrl]# scp tts_ts01.dbf 192.168.91.156:/oradata/TARGET/ The authenticity of host '192.168.91.156 (192.168.91.156)' can't be established. RSA key fingerprint is a3:64:78:86:1e:ff:dd:c8:b3:73:a8:9c:06:65:f0:38. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.91.156' (RSA) to the list of known hosts. oracle@192.168.91.156's password: 8/23
tts_ts01.dbf 100% 10MB 10.0MB/s 00:00 ## target DB에서 import 수행 oracle@ target ~]# cd /data/pump oracle@ target ~]# ls /data/pump tts.dmp [oracle@dbsrv11 ~]$ impdp system/oracle directory=tts_dir dumpfile=tts.dmp logfile=tts.log transport_datafiles='/oradata/target/tts_ts01.dbf' Import: Release 11.2.0.3.0 - Production on Thu Apr 17 22:26:43 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=tts_dir dumpfile=tts.dmp logfile=tts.log transport_datafiles=/oradata/target/tts_ts01.dbf Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:26:46 3.2.5 TEST 확인 ## Target DB 접속 [oracle@target ~]$ sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 17 22:30:29 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. 9/23
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ## tablespace, datafile 확인 SQL> select file_name, tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME --------------------------------------------- ------------------------------ /oradata/orcl/users01.dbf USERS /oradata/orcl/undotbs01.dbf UNDOTBS1 /oradata/orcl/sysaux01.dbf SYSAUX /oradata/orcl/system01.dbf SYSTEM /oradata/target/tts_ts01.dbf TTS_TS ## user 확인 SQL> select username from dba_users; USERNAME ------------------------------ SYS SYSTEM TTS_USER OUTLN MGMT_VIEW FLOWS_FILES MDSYS ORDSYS EXFSYS DBSNMP WMSYS APPQOSSYS 10/23
## table 확인 SQL> select * from tts_user.tts_table; NO ---------- 1 2 ## ## TTS_TS 테이블스페이스 read write mode로변경 ( source,target 서버동일 ) SQL> alter tablespace tts_ts read write ; SQL> select tablespace_name, status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ ------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS TTS_TS 11/23
4 TEST 2 - 이기종 Platform OS 나 Oracle 버젼이다를경우 RMAN의 convert 명령어를사용하여 Cross-Platform 데이터이전을할수있다 4.1 TEST 환경 이기종 platform, character : KO16MSWIN949 동일 source db oracle EE 11.2.0.3 on linux 6.3 target db oracle EE 11.2.0.2 on Window server 2003 지원가능한 Platform SQL>set pagesize 50 SQL >col PLATFORM_ID for 99999999999999 SQL >col PLATFORM_NAME for a40 SQL >col ENDIAN_FORMAT for a10 SQL >select * from v$transportable_platform SQL> select * from v$transportable_platform order by 1; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ------------------ ------------------------------------------ -------------- 1 Solaris[tm] OE (32-bit) Big 2 Solaris[tm] OE (64-bit) Big 3 HP-UX (64-bit) Big 4 HP-UX IA (64-bit) Big 5 HP Tru64 UNIX Little 6 AIX-Based Systems (64-bit) Big 7 Microsoft Windows IA (32-bit) Little 8 Microsoft Windows IA (64-bit) Little 9 IBM zseries Based Linux Big 10 Linux IA (32-bit) Little 11 Linux IA (64-bit) Little 12 Microsoft Windows x86 64-bit Little 13 Linux x86 64-bit Little 15 HP Open VMS Little 12/23
16 Apple Mac OS Big 17 Solaris Operating System (x86) Little 18 IBM Power Based Linux Big 19 HP IA Open VMS Little 20 Solaris Operating System (x86-64) Little 21 Apple Mac OS (x86-64) Little 4.2 TEST 4.2.1 Source DB 확인 [oracle@dbsrv11 /]$ sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.3.0 Production on Thu May 15 10:21:57 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> col platform_name for a20 SQL> col endian_format for a20 SQL> select a.platfrom_name,endian_format SQL> select d.platform_name, endian_format 2 from v$transportable_platform tp, v$database d 3 where tp.platform_name = d.platform_name ; PLATFORM_NAME -------------------- -------------------- Linux x86 64-bit Little ENDIAN_FORMAT 4.2.2 Source DB 설정 ## 현재 datafile 조회 SQL> select file_name, tablespace_name, bytes/1024/1024 MB from dba_data_files; 13/23
FILE_NAME TABLESPACE_NAME MB --------------------------------------------- ------------------------------ ---------- /oradata/orcl/users01.dbf USERS 5 /oradata/orcl/undotbs01.dbf UNDOTBS1 75 /oradata/orcl/sysaux01.dbf SYSAUX 790 /oradata/orcl/system01.dbf SYSTEM 720 /oradata/orcl/tts_ts01.dbf TTS_TS 10 ## TEST tablespace 생성 SQL> create tablespace tts_ts2 datafile '/oradata/orcl/tts_ts02.dbf' size 10m; Tablespace created. ## TEST 유저생성 SQL> create user tts_user 2 identified by tts 3 default tablespace tts_ts2 ; User created. ## TEST Table 생성 SQL> create table tts_user.test02(no number) ; Table created. ## data insert SQL> insert into tts_user.test02 values(1); 1 row created. SQL> insert into tts_user.test02 values(22); 1 row created. SQL> commit; Commit complete. ## Table 조회 SQL> select * from tts_user.test02 ; 14/23
NO ---------- 1 22 4.2.3 Export ## datapump 설정 ## export 할 directory 생성 root@source~]# mkdir -p /data/pump root@ source ~]# chown -R oracle.dba /data/pump root@ source ~]# su - oracle oracle@ source ~]$ sqlplus "/as sysdba" SQL> create directory tts_dir as '/data/pump' ; SQL> grant read,write on directory tts_dir to public; ## TTS check - tablespace 가이동가능한지체크 SQL> exec dbms_tts.transport_set_check('tts_ts2',true); PL/SQL procedure successfully completed. SQL> select * from transport_set_violations; no rows selected ## 이관할 Tablespace 를 read only 로변경 SQL> alter tablespace TTS_TS2 read only ; SQL> select tablespace_name, status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM 15/23
SYSAUX UNDOTBS1 TEMP USERS TEMP2 TTS_TS2 READ ONLY ## Export 수행 1. meta data 먼저 export 실행 [oracle@source pump]$ expdp system/oracle dumpfile=tts_meta.dmp directory=tts_dir full=y include=user,role,role_grant,profile content=metadata_only Export: Release 11.2.0.3.0 - Production on Thu May 15 12:37:47 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=tts_meta.dmp directory=tts_dir full=y include=user,role,role_grant,profile content=metadata_only Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/ROLE Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is: /data/pump/tts_meta.dmp Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 12:37:54 2. 이관할 data를 Export 실행 [oracle@source pump]$ expdp system/oracle dumpfile=tts_test2.dmp directory=tts_dir transport_tablespaces=tts_ts2 logfile=test02.log 16/23
Export: Release 11.2.0.3.0 - Production on Thu May 15 11:20:35 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=tts_test2.dmp directory=tts_dir transport_tablespaces=tts_ts2 logfile=test02.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /data/pump/tts_test2.dmp ****************************************************************************** Datafiles required for transportable tablespace TTS_TS2: /oradata/orcl/tts_ts02.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:21:07 [oracle@source pump]$ ls -lrt 합계 96 -rw-r-----. 1 oracle dba 90112 2014-05-15 11:21 tts_test2.dmp -rw-r--r--. 1 oracle dba 1144 2014-05-15 11:21 test02.log 4.2.4 Target DB 정보확인 rman으로 convert 하기전 target db의오라클버전과 os를체크 ## Target 오라클버젼과 os 확인 C:\Documents and Settings\Administrator>sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.2.0 Production on 수 9월 3 15:38:09 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. 17/23
다음에접속됨 : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select d.platform_name, endian_format from v$transportable_platform tp, v$ database d where tp.platform_name = d.platform_name ; PLATFORM_NAME ENDIAN_FOR -------------------------------------------- ---------- Microsoft Windows x86 64-bit Little 4.2.5 RMAN - dumpfile convert ## source DB 에서 RMAN 접속하여 TTS_TS2 테이블스페이스 convert 작업수행 [oracle@source pump]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 15 11:35:00 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1372771007) RMAN> convert tablespace 'TTS_TS2' to platform 'Microsoft Windows x86 64-bit' 2> format '/data/pump/%u'; -- TTS_TS2 테이블스페이스를 'Microsoft Windows x86 64-bit' platform 에서사용할수있게 convert 작업 Starting conversion at source at 15-MAY-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=42 device type=disk channel ORA_DISK_1: starting datafile conversion input datafile file number=00006 name=/oradata/orcl/tts_ts02.dbf converted datafile=/data/pump/data_d-orcl_i-1372771007_ts-tts_ts2_fno-6_01p8bfie channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Finished conversion at source at 15-MAY-14 18/23
[oracle@dbsrv11 pump]$ ls -lrt 합계 10360 -rw-r-----.1 oracle dba 6_01p8bfie 10493952 2014-05-15 11:36 data_d-orcl_i-1372771007_ts-tts_ts2_fno- 4.2.6 Target DB 설정 ## datapump 디렉토리생성및권한부여 C:\ >sqlplus "/as sysdba" SQL> create directory tts_dir as 'C:\oracle\datapump'; 디렉토리가생성되었습니다. SQL> grant read,write on directory tts_dir to public; 권한이부여되었습니다. ## TEST 유저생성 ( source 서버와동일하게생성 ) SQL> create user tts_user identified by tts ; SQL> grant resource, connect to tts_user ## 변환된파일과 export 받은 dump 을 target으로이동 xftp 로 3개의파일을 target 서버의 c:\oracle\datatpump 디렉토리로옮김. tts_meta.dmp ---- meta data만 export 받은 dumpfile tts_test2.dmp ---- 이관할 tablespace를 transportalbe 옵션을주어 export 받은 dumpfile data_d-orcl_i-1372771007_ts-tts_ts2_fno-6_01p8bfie ----- rman convert platform 변환 ## 변환된파일을 target 에서한번더 convert 작업수행 - 이작업으로 Source DB에서사용하던파일을 Target DB 파일로변환및재생성함 C:\ > rman target / 복구관리자 : Release 11.2.0.2.0 - Production on 수 9월 3 16:20:59 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 대상데이터베이스에접속합니다 : UNIONE (DBID=314790755) 19/23
RMAN> convert datafile 'C:\oracle\datapump/data_D-ORCL_I-1372771007_TS-TTS_TS2_FNO-6_01p8bfie' format 'C:\ORACLE\ORADATA\TTS_TS02.DBF' ; conversion at target을 ( 를 ) 14/09/03에서시작중복구카탈로그대신대상데이터베이스제어파일을사용하고있습니다. 채널을할당했습니다 : ORA_DISK_1 ORA_DISK_1 채널 : SID=73 장치유형 =DISK ORA_DISK_1 채널 : 데이터파일변환시작중입력파일이름 =C:\ORACLE\DATAPUMP\DATA_D-ORCL_I-1372771007_TS-TTS_TS2_FNO-6_01P8 BFIE 변환된데이터파일 =C:\ORACLE\ORADATA\TTS_TS02.DBF ORA_DISK_1 채널 : 데이터파일변환완료. 경과시간 : 00:00:01 conversion at target을 ( 를 ) 14/09/03에서완료 4.2.7 Import ## Target 서버에서 meta data 및실제 data를 import 실행 1. meta data import 실행 C:\>impdp system/oracle directory=tts_dir dumpfile=tts_meta.dmp full=y Import: Release 11.2.0.2.0 - Production on 수 9 월 3 17:07:50 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights rese 접속대상 : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-6 duction With the Partitioning, OLAP, Data Mining and Real Application Testing op 마스터테이블 "SYSTEM"."SYS_IMPORT_FULL_01" 이 ( 가 ) 성공적으로로드됨 / 로드 "SYSTEM"."SYS_IMPORT_FULL_01" 시작중 : system/******** directory=tts_dir e=tts_meta.dmp full=y 객체유형 DATABASE_EXPORT/PROFILE 처리중 ORA-31684: 객체유형 PROFILE:"MONITORING_PROFILE" 이 ( 가 ) 존재함객체유형 DATABASE_EXPORT/SYS_USER/USER 처리중객체유형 DATABASE_EXPORT/SCHEMA/USER 처리중 20/23
ORA-31684: 객체유형 USER:"OUTLN" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"ORDDATA" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"OLAPSYS" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"MDDATA" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"SPATIAL_WFS_ADMIN_USR" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"SPATIAL_CSW_ADMIN_USR" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"SYSMAN" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"MGMT_VIEW" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"FLOWS_FILES" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"APEX_PUBLIC_USER" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"APEX_030200" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"OWBSYS" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"OWBSYS_AUDIT" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"SCOTT" 이 ( 가 ) 존재함 ORA-31684: 객체유형 USER:"TTS_USER" 이 ( 가 ) 존재함객체유형 DATABASE_EXPORT/ROLE 처리중 ORA-31684: 객체유형 ROLE:"SELECT_CATALOG_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"EXECUTE_CATALOG_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"DELETE_CATALOG_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"DBFS_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"AQ_ADMINISTRATOR_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"AQ_USER_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"ADM_PARALLEL_EXECUTE_TASK" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"GATHER_SYSTEM_STATISTICS" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"RECOVERY_CATALOG_OWNER" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"SCHEDULER_ADMIN" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"HS_ADMIN_SELECT_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"HS_ADMIN_EXECUTE_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"HS_ADMIN_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"GLOBAL_AQ_USER_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"OEM_ADVISOR" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"OEM_MONITOR" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"WM_ADMIN_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"JAVAUSERPRIV" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"JAVAIDPRIV" 이 ( 가 ) 존재함 21/23
ORA-31684: 객체유형 ROLE:"JAVASYSPRIV" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"JAVADEBUGPRIV" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"EJBCLIENT" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"JMXSERVER" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"JAVA_ADMIN" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"JAVA_DEPLOY" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"CTXAPP" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"XDBADMIN" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"XDB_SET_INVOKER" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"AUTHENTICATEDUSER" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"XDB_WEBSERVICES" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"XDB_WEBSERVICES_WITH_PUBLIC" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"XDB_WEBSERVICES_OVER_HTTP" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"ORDADMIN" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"OLAP_XS_ADMIN" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"OLAP_DBA" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"CWM_USER" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"OLAP_USER" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"SPATIAL_WFS_ADMIN" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"WFS_USR_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"SPATIAL_CSW_ADMIN" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"CSW_USR_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"MGMT_USER" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"APEX_ADMINISTRATOR_ROLE" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"OWB$CLIENT" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"OWB_DESIGNCENTER_VIEW" 이 ( 가 ) 존재함 ORA-31684: 객체유형 ROLE:"OWB_USER" 이 ( 가 ) 존재함객체유형 DATABASE_EXPORT/SCHEMA/ROLE_GRANT 처리중 "SYSTEM"."SYS_IMPORT_FULL_01" 작업이 62 오류와함께 17:07:52에서완료됨 ## 데이터 import 실행 C:\> impdp system/oracle directory=tts_dir dumpfile=tts_test2.dmp transport_dataf iles='c:\oracle\oradata\tts_ts02.dbf' Import: Release 11.2.0.2.0 - Production on 수 9 월 3 17:16:13 2014 22/23
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 접속대상 : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Pro duction With the Partitioning, OLAP, Data Mining and Real Application Testing options 마스터테이블 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 이 ( 가 ) 성공적으로로드됨 / 로드취소됨 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 시작중 : system/******** directory=tts_di r dumpfile=tts_test2.dmp transport_datafiles='c:\oracle\oradata\tts_ts02.dbf' 객체유형 TRANSPORTABLE_EXPORT/PLUGTS_BLK 처리중객체유형 TRANSPORTABLE_EXPORT/TABLE 처리중객체유형 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 처리중 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 작업이 17:16:15에서성공적으로완료됨 4.2.8 TEST 확인 ## Tablespace 확인 FILE_NAME TABLESPACE_NAME --------------------------------------------- ----------------------------- C:\ORACLE\ORADATA\UNIONE\SYSTEM01.DBF SYSTEM C:\ORACLE\ORADATA\UNIONE\SYSAUX01.DBF SYSAUX C:\ORACLE\ORADATA\UNIONE\UNDOTBS01.DBF UNDOTBS1 C:\ORACLE\ORADATA\UNIONE\USERS01.DBF USERS C:\ORACLE\ORADATA\TTS_TS02.DBF TTS_TS2 SQL> select * from tts_user.test02; NO ---------- 1 22 ## TTS_TS2 read write mode 로변경 ( source,target 서버동일 ) SQL> alter tablespace tts_ts2 read write ; 23/23