当前位置:数据库 > Oracle >>

oracle传输表空间功能测试(含详细过程)

oracle传输表空间功能测试(含详细过程)
 
一、源服务器上导出表空间
源服务器:   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 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.
 
 
 
 
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,