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

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

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

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

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博客

10g的表空间传输特性

最新文章