oracle传输表空间功能测试(含详细过程)
来源:互联网 发布:淘宝中国质造申请条件 编辑:程序博客网 时间:2024/05/20 22:37
最近做数据迁移,之前有一篇迁移思路思考的文章,这里继续做具体的测试,主题问表空间传输。
一、源服务器上导出表空间
源服务器: 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 sysdbaConnected.
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 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/oradatascp /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=bbbSQL> 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: http://blog.csdn.net/laven54
QQ群: 164734649 可以到群里来提问,Oracle相关的问题我都很感兴趣
- oracle传输表空间功能测试(含详细过程)
- oracle表空间传输
- oracle 传输表空间
- oracle 传输表空间
- oracle 传输表空间总结。
- oracle 可传输表空间
- oracle 11g 传输表空间(数据迁移)
- Oracle中跨平台传输表空间
- 使用exp/imp传输表空间(Oracle)
- Oracle Data Guard_ 主备库传输表空间
- oracle 传输表空间一例
- oracle表空间大小测试
- oracle 表空间与数据文件详细(一)
- mysql使用传输表空间迁移大表测试
- 使用 dbms_file_transfer 方式测试增量传输表空间XTTS
- 七步详细分析Oracle表空间
- oracle 创建表空间详细介绍
- oracle 创建表空间详细介绍
- Sqlite使用手册
- Hud 3466 Proud Merchantsp[0-1背包变形]
- 集合(下)
- 求个为大神看看dwr与hibernate整合
- java socket
- oracle传输表空间功能测试(含详细过程)
- ArcEngine中同时闪烁多个要素
- UVa 10954 - Add All
- Qt for WINCE5.0在windowsXP的VS2005平台上的搭建
- 更新至Ubuntu12.10后fontsconfig出现警告的解决办法
- 读取txt文件实现搜索替换功能
- 希尔排序小结
- 把ATmega128开发板转为Arduino
- 收藏,Linus Torvalds删除链表节点的NB代码。