最近做数据迁移，之前有一篇迁移思路思考的文章，这里继续做具体的测试，主题问表空间传输。
一、源服务器上导出表空间
源服务器: 10.1.122.55 目标服务器：10.1.122.54
0.设置字符集
注意，这里不设置字符集在导入的时候会报错，详细情况见文章的最后。 suse11sp2:~ # export LANG=AMERICAN_AMERICA.AL32UTF8 suse11sp2:~> export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 suse11sp2:~> sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 24 14:45:47 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved.
1.准备需要传输的表空间
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> create tablespace aaa datafile '/oracle/oradata/aa.dbf' size 100M ; Tablespace created. SQL> CREATE USER aaa IDENTIFIED BY aaa DEFAULT TABLESPACE aaa TEMPORARY TABLESPACE temp; 2 3 User created. SQL> GRANT CONNECT,RESOURCE TO aaa; Grant succeeded. SQL> REVOKE UNLIMITED TABLESPACE FROM aaa; Revoke succeeded. SQL> ALTER USER aaa QUOTA UNLIMITED ON aaa; User altered. SQL> conn aaa/aaa; Connected. SQL> create table a1(id varchar2(10),name varchar2(20)); Table created. SQL> insert into a1 values('01','lurou'); 1 row created. SQL> insert into a1 values('02','hello,DBA!'); 1 row created. SQL> COMMIT; Commit complete. SQL> select * from a1; ID NAME ---------- -------------------- 01 lurou 02 hello,DBA! SQL> SQL> SQL>
2.做传输前检查
SQL> conn / as sysdba Connected. SQL> SQL> execute sys.dbms_tts.transport_set_check('aaa',true); PL/SQL procedure successfully completed. SQL> SQL> select * from sys.transport_set_violations; no rows selected SQL>
3.设置表空间为只读
SQL> SQL> alter tablespace aaa read only; Tablespace altered. SQL> SQL> commit; Commit complete. SQL>
4.导出表空间
1）遇到个小插曲，必须使用sys dba suse11sp2:~> exp system/its7888$ tablespaces=aaa file=/tmp/aaatts.dmp transport_tablespace=y Export: Release 11.2.0.3.0 - Production on Wed Jul 24 14:58:19 2013 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 EXP-00044: must be connected 'AS SYSDBA' to do Point-in-time Recovery or Transportable Tablespace import EXP-00000: Export terminated unsuccessfully2）导出成功 suse11sp2:~> exp \'sys/its7888$ as sysdba\' tablespaces=aaa file=/tmp/aaatts.dmp transport_tablespace=y Export: Release 11.2.0.3.0 - Production on Wed Jul 24 14:59:13 2013 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 Export done in AL32UTF8 character set and UTF8 NCHAR character set Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace AAA ... . exporting cluster definitions . exporting table definitions . . exporting table A1 . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings. suse11sp2:~>
二、目标服务器上导入表空间
5.将导出的dmp文件和数据文件拷贝到目标服务器
scp /oracle/oradata/aa.dbf oracle@10.1.122.54:/oracle/oradata scp /tmp/aaatts.dmp oracle@10.1.122.54:/tmp
6.创建用户
SQL> create user bbb identified by bbb; User created. SQL> grant connect,resource to bbb; Grant succeeded. SQL> commit; Commit complete.
7.导入表空间
imp \'sys/its7888$ as sysdba\' tablespaces=aaa transport_tablespace=y file=/tmp/aaatts.dmp datafiles=/oracle/oradata/aa.dbf fromuser=aaa touser=bbb SQL> conn aaa/aaa Connected. SQL> select * from a1 2 ; ID NAME ---------- -------------------- 01 lurou 02 hello,DBA! SQL> SQL> SQL> conn / as sysdba Connected. SQL> drop tablespace aaa including contents and datafiles; Tablespace dropped. ——————————————————————————
三、之前遭遇的报错
未设置字符集的操作过程中，遭遇报错 suse11sp2:~> exp \'sys/its7888$ as sysdba\' tablespaces=aaa file=/tmp/aaatts.dmp transport_tablespace=y Export: Release 11.2.0.3.0 - Production on Wed Jul 24 15:31:57 2013 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 Export done in US7ASCII character set and UTF8 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace AAA ... . exporting cluster definitions . exporting table definitions . . exporting table A1 . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings. suse11sp2:~> imp userid=system/its7888$ tablespaces=aaa transport_tablespace=y file=/tmp/aaatts.dmp datafiles=/oracle/oradata/aa.dbf Import: Release 11.2.0.3.0 - Production on Wed Jul 24 09:22:57 2013 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 Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) IMP-00053: Import mode incompatible with Export dump file
IMP-00000: Import terminated unsuccessfully
___________________________________________________________________________________
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
Author: laven54 (lurou)
Email: laven54@163.com
Blog: