10g的表空间传输特性
10g中支持不同平台之间进行表空间的传输,让我来看看如何实现:
oracle 的官方指导:
1.将被传输的表空间设置为read only(source database)
2.使用exp/expdp将表空间的metadata导出来.(source database)
3.察看source database和target database的endian是否相同.
a. 如果相同,则将对应的bdf和dmp copy到 target database
b. 如果不相同,则将对应的bdf用rman 转换后在和dmp文件一起copy到 target database
4.使用imp将metadata倒入.
5.将表空间设置为read write.
操作如下:
环境 192.168.0.110 (Cent OS 4.5) source database
192.168.1.103 (Windows XP) target database
在110上(source database)
sys@TENG> drop tablespace trans_test including contents and datafiles;
Tablespace dropped.
sys@TENG> create tablespace trans_test datafile '$ORACLE_BASE/oradata/TenG/TenG/trans_test01.bdf' size 5m;
Tablespace created.
sys@TENG> create table eason.test01 as select * from user_objects;
Table created.
sys@TENG> conn eason/eason
Connected.
eason@TENG> select count(*) from test01;
COUNT(*)
----------
2
eason@TENG> conn /as sysdba
Connected.
sys@TENG> alter tablespace trans_test read only;
Tablespace altered.
sys@TENG> exit
[oracle@node2 eason]$ exp \'sys/systeng as sysdba\' file='/home/eason/trans_test.dmp' transport_tablespace=y tablespaces=(trans_test)
Export: Release 10.2.0.1.0 - Production on Mon Jul 28 12:08:59 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TRANS_TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TEST01
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@node2 eason]$
将bdf和dmp文件用ftp或者sftp到target database
在 103上(target database)
D:\>imp "'sys/sysorcl as sysdba'" file='D:\orcl_data\orcl\trans_test.dmp' transport_tablespace=y tablespaces=(trans_test
) datafiles='D:\orcl_data\orcl\trans_test01.dbf'
Import: Release 10.2.0.4.0 - Production on Tue Aug 5 00:06:50 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing EASON's objects into EASON
. . importing table "TEST01"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
D:\>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 5 00:08:15 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(*) from eason.test01;
COUNT(*)
----------
2
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TRANS_TEST READ ONLY
6 rows selected.
SQL> alter tablespace trans_test read write;
Tablespace altered.
SQL> insert into eason.test01 select * from user_objects where rownum<101;
100 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from eason.test01;
COUNT(*)
----------
102
SQL>
至次从linux迁移过来的表空间trans_test能够在windows上正常工作了.
ps:关于第三点endian,可以参看oracle的v$transportable_platform
sys@TENG> select platform_name,endian_format from v$transportable_platform;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux 64-bit for AMD Little
Apple Mac OS Big
Microsoft Windows 64-bit for AMD Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big
17 rows selected.
对于传输表空间,只要endian_format是相同的,那么dbf就不需要rman来转换.
如果endian_format不相同的平台之间传输,那么得用rman来转换.
具体
[oracle@node2 eason]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jul 28 10:49:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: TENG (DBID=1762725495)
RMAN> convert tablespace trans_test to platform. 'Microsoft Windows IA (32-bit)' format '/home/eason/trans_test01.dbf';
Starting backup at 28-JUL-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/u01/oracle/oradata/TenG/TenG/trans_test01.dbf
converted datafile=/home/eason/trans_test01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 28-JUL-08
RMAN> exit
然后把转换出来的bdf和dmp,copy到target database,然后再做imp.
上面的Microsoft Windows IA (32-bit)就是v$transportable_platform里面的PLATFORM_NAME.
对于传输数据库,只能在endian_format相同的平台之间作传输.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9340716/viewspace-418925/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9340716/viewspace-418925/