一、源服务器上导出表空间
源服务器: 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 dbasuse11sp2:~> 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 unsuccessfully
2）导出成功
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
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-4-9 16:44:09 |
赞(
操作
