#28_140722_WSHOP2_EXPORT/IMPORT

# EXPORT/IMPORT

1.     export/import 하는 과정에서 전달해야할 2가지 파일

- 해당 테이블 스패이스의 data file

- 해당 테이블 스패이스의 메타정보가 들어있는 덤프 파일

2.     tablespace level로 export/import를 왜 해야하는가?

- 구장비에서 신장비로 데이터 이행을 할 때 가장 빠르게 이행할 수 있는 방법

l tablespace export 테스트 순서

1.     SQL> create tablespace ts200

DATAFILE '/home/oracle/ts200.dbf' SIZE 5m;

2.     SQL> alter tablespace ts200 read only;

3.     $ exp transport_tablespace=ts200 tablespaces=ts200 file=ts200.dmp

- username : sys as sysdba

- password : oracle

4.     짝꿍에게 2개의 파일을 전달해줘야 한다

5.     ts200.dmp : ts200 테이블 스패이스의 메타정보

6.     ts200.dbf : data가 들어있는 data file

7.     짝꿍에게 전달 받은 두개의 파일을 /home/oracle에 두고

8.     $ imp transport_tablespace=ts200 file=ts200.dmp datafiles=/home/oracle/ts200.dbf

9.     SQL> alter tablespace ts200 read write;

10.   SQL> select tablespace_name from dba_tablespaces;

SQL> create tablespace ts200 DATAFILE '/home/oracle/ts200.dbf' SIZE 5m; SQL> SQL> alter tablespace ts200 read only; [dbdb:dbdb]$ exp transport_tablespace=ts200 tablespaces=ts200 file=ts200.dmp Export: Release 11.2.0.1.0 - Production on Tue Jul 22 14:43:22 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Username: sys as sysdba Password: ………………………………………………………………………… Export terminated successfully without warnings. [dbdb:~]$ imp transport_tablespace=y file=ts7777.dmp datafiles=/home/oracle/ts7777.dbf Import: Release 11.2.0.1.0 - Production on Tue Jul 22 15:21:59 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path About to import transportable tablespace(s) metadata... import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set import server uses KO16MSWIN949 character set (possible charset conversion) export client uses US7ASCII character set (possible charset conversion) . importing SYS's objects into SYS . importing SYS's objects into SYS IMP-00017: following statement failed with ORACLE error 29345:  "BEGIN   sys.dbms_plugts.beginImport ('11.2.0.1.0',178,'2000',10,'Linux IA ("  "32-bit)',74679,1561451,1,0,0,0); END;" IMP-00003: ORACLE error 29345 encountered ORA-29345: cannot plug a tablespace into a database using an incompatible character set - 위오류는 characterset이 맞지 않아 발생하는 오류다 - NLS_CHARACTERSET 이 export 유저와 import 유저가 같아야지 가능하다(SELECT * FROM DATABASE_PROPERTIES;) - 만약 다르다면 옮기는 것을 불가능하다 ORA-06512: at "SYS.DBMS_PLUGTS", line 2561 ORA-06512: at "SYS.DBMS_PLUGTS", line 2070 ORA-06512: at line 1 IMP-00000: Import terminated unsuccessfully