分类： Oracle
2008-03-30 13:14:20
| 来源：赛迪网 作者：陈雅诗 |
|
示例：准备两个数据库（ltdb和epras），从ltdb导出tsts表空间（用户ts,包含2个数据文件到epras。
详细的执行步骤：
1.ltdb导出可传输表空间(以sysdba执行导出)到dmp文件。
2.将ltdb的tsts表空间改为只读。
3.用操作系统命令复制数据文件和dmp文件到另一个位置给另一个数据库epras使用。
如果在一台服务器上，dmp文件可以不必复制，复制完成后，将ltdb的tsts表空间改为可读写。
4.在epras数据库创建ts用户（此时不必授权）。
5.导入可传输表空间(以sysdba执行导入)到dmp文件到epras数据库。
6.将epras的tsts表空间改为可读写，给ts用户授权。
好处很明显，和数据exp/imp相比，速度很快，dmp文件只包含表空间元数据10g支持不同平台的表空间传输，支持大头/小头操作系统之间的传输/*
在Oracle以前的版本中,可传输的表空间的特性可以让表空间在相同的体系结构和操作系统之间迁移.
在 Oracle 数据库 10g 中，这个局限消失了：只要操作系统字节顺序相同,您就可以在平台
之间传输表空间。一些操作系统（包括 Windows）在低位内存地址中用最低有效字节存储
多字节二进制数据；因此这种系统被称为低地址低字节序。相反，其它的操作系统
（包括 Solaris）将最高有效字节存储在低位内存地址中，因此这种系统被称为低地址高字节序。
当一个低地址高字节序的系统试图从一个低地址低字节序的系统中读取数据时，需要一个转换过程
— 否则，字节顺序将导致不能正确解释读取的数据。
不过，当在相同字节顺序的平台之间传输表空间时，不需要任何转换。
Oracle 10g的表空间跨平台迁移，与Oracle9i比较就是增加了一个使用Rman进行的文件格式转换的过程。
实际上仅仅是转换了数据文件头的格式信息。
COLUMN PLATFORM_NAME FORMAT A30
检查可转换平台
SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
17 rows selected
检查源平台
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Solaris[tm] OE (64-bit) Big
检查目标平台
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ -------------
Microsoft Windows IA (32-bit) Little
检查待转换表空间
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tswin1,tswin2', TRUE);
PL/SQL procedure successfully completed.
是否有冲突
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
运行rman
rman TARGET /
CONVERT TABLESPACE tswin1,tswin2
TO PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/tmp/rman/%U';
在以上的例子中，转换后的文件名难于辨认并很难与原始文件关联还可以使用其它格式来为数据文件命名。
CONVERT TABLESPACE tswin1,tswin2
TO PLATFORM 'Microsoft Windows IA (32-bit)'
db_file_name_convert '/tmp/LTDB','/tmp/rman'
此处将在转换后保留文件名。
*/
======详细执行结果=============== |
1.ltdb导出
root@t2000 # su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
$ ORACLE_SID=LTDB
$ export ORACLE_SID
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 15 15:54:18 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create tablespace TSTS datafile '/tmp/LTDB/TSTS01.DBF' size 10M ,'/tmp/LTDB/TSTS02.DBF' size 10M;
Tablespace created.
SQL> create user ts identified by ts default tablespace tsts;
User created.
SQL> grant connect to ts;
Grant succeeded.
SQL> grant resource to ts;
Grant succeeded.
SQL> conn ts/ts
Connected.
SQL> create table t1(c1 varchar(255),c2 varchar(255));
Table created.
SQL> insert into t1 select sys_guid(),sys_guid() from dual connect by level <=10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> select * from t1 where rownum<=5;
C1 C2
-------------------------------- --------------------------------
163FD8C45C092993E04400144F025BB6 163FD8C45C0A2993E04400144F025BB6
163FD8C45C0B2993E04400144F025BB6 163FD8C45C0C2993E04400144F025BB6
163FD8C45C0D2993E04400144F025BB6 163FD8C45C0E2993E04400144F025BB6
163FD8C45C0F2993E04400144F025BB6 163FD8C45C102993E04400144F025BB6
163FD8C45C112993E04400144F025BB6 163FD8C45C122993E04400144F025BB6
SQL> conn system/sys
Connected.
SQL> alter tablespace tsts read only
SQL> /
Tablespace altered.
SQL> ho
$ exp userid=\'sys/sys as sysdba\' tablespaces=tsts file=/tmp/tsts.dmp l transport_tablespace=y
Export: Release 10.2.0.1.0 - Production on Thu Jun 15 16:20:29 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TSTS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
2.epras导入
=============================================
root@t2000 # su - oracle
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 15 16:26:11 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> ho
复制数据文件，此时数据库ltdb的tsts表空间还是read only
$ cp /tmp/LTDB/TS*DBF /oracle
建立目标数据库上的用户，如果不建立，导入时需要加touser=参数，否则会出错
SQL> create user ts identified by ts;
User created.
SQL> ho
SQL> revoke connect from ts;
Revoke succeeded.
SQL> revoke resource from ts;
Revoke succeeded.
SQL> ho
$ imp userid=\'sys/sys as sysdba\' tablespaces=tsts file=/tmp/tsts.dmp transport_tablespace=y datafiles=/oracle/TSTS01.DBF, /oracle/TSTS02.DBF
Import: Release 10.2.0.1.0 - Production on Thu Jun 15 18:24:06 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing TS's objects into TS
. . importing table "T1"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
$ exit
SQL> alter tablespace tsts read write;
Tablespace altered.
SQL> select table_name from all_tables where owner='TS';
TABLE_NAME
------------------------------
T1
SQL> grant connect to TS;
Grant succeeded.
SQL> grant resource to TS;
Grant succeeded.
SQL> conn ts/ts
Connected.
SQL> set lines 132
SQL> set pages 9999
SQL> col c1 format a40
SQL> col c2 format a40
SQL> select * from t1 where rownum<=5;
C1 C2
---------------------------------------- ----------------------------------------
163FD8C45C092993E04400144F025BB6 163FD8C45C0A2993E04400144F025BB6
163FD8C45C0B2993E04400144F025BB6 163FD8C45C0C2993E04400144F025BB6
163FD8C45C0D2993E04400144F025BB6 163FD8C45C0E2993E04400144F025BB6
163FD8C45C0F2993E04400144F025BB6 163FD8C45C102993E04400144F025BB6
163FD8C45C112993E04400144F025BB6 163FD8C45C122993E04400144F025BB6
SQL>
