oracle 10g 跨平台dg,跨平台传输表空间

测试了10G，跨平台传输表空间，数据迁移又多了种方法！测试平台是windows32bit 10.201 传输表空间至linux x64 RAC 10.204

1 准备工作

检查目的数据库平台 LINUX平台

SQL> select * from v$version;

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi

PL/SQL Release 10.2.0.4.0 – Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 – Production

NLSRTL Version 10.2.0.4.0 – Production

SQL> 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

—————————————- ————–

Linux x86 64-bit Little

检查源数据库平台 windows平台

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

PL/SQL Release 10.2.0.1.0 – Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 – Production

NLSRTL Version 10.2.0.1.0 – Production

SYSTEM@CATDB.REGRESS.RDBMS.DEV.US.ORACLE.COM> col platform_name for a40

SQL> 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

查询Oracle10g支持的平台转换

SQL> 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

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

———– —————————————- ————–

9 IBM zSeries Based Linux Big

13 Linux x86 64-bit Little

16 Apple Mac OS Big

12 Microsoft Windows x86 64-bit Little

17 Solaris Operating System (x86) Little

18 IBM Power Based Linux Big

20 Solaris Operating System (x86-64) Little

19 HP IA Open VMS Little

19 rows selected.

检查源、目的数据库的字符集，要相同！

SQL> select * from sys.props$ where name=’NLS_CHARACTERSET’;

2 源数据库建立测试表空间、用户、以及数据

略

3 源库的表空间是只读，并且是自包含

select STATUS

FROM DBA_TABLESPACES

WHERE TABLESPACE_NAME =’TEST’;

–修改表空间为只读

alter tablespace test read only;

—–监测用的表空间是自包含的，若自建的可以省略掉这步—–

SQL> exec sys.dbms_tts.transport_set_check(’TEST’);

未选定行

—–no rows selected means 是自包含的。

4 导出传输的表空间 表空间必须是只读

create OR REPLACE DIRECTORY exp_dir as ‘e:\expdb\’;

grant read on directory exp_dir to test;

expdp system/aaa dumpfile=test.dmp directory=exp_dir TRANSPORT_TABLESPACES=test

5 在源数据库上，使用rman转换文件格式

E:\expdb>rman target /

恢复管理器: Release 10.2.0.1.0 – Production on 星期二 6月 16 12:16:23 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到目标数据库: CATDB (DBID=2235366936)

RMAN> convert tablespace ‘test’ to platform. ‘Linux 64-bit for AMD’ format ‘e:/expdb/%U’;

启动 backup 于 16-6月 -09

使用目标数据库控制文件替代恢复目录

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: sid=1644 devtype=DISK

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: backup 命令 (在 06/16/2009 12:16:28 上) 失败

RMAN-20202: tablespace not found in the recovery catalog

RMAN-06019: 无法转换表空间名称”test”

注意Windows平台表空间名称居然区分大小写，没有想到

RMAN> convert tablespace ‘TEST’ to platform. ‘Linux 64-bit for AMD’ format ‘e:/ex

pdb/%U’;

启动 backup 于 16-6月 -09

使用通道 ORA_DISK_1

通道 ORA_DISK_1: 启动数据文件转换

输入数据文件 fno=00005 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\CATDB\DATAFILE\TEST

已转换的数据文件 = E:\EXPDB\DATA_D-CATDB_I-2235366936_TS-TEST_FNO-5_01KHNKDK

通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:07

完成 backup 于 16-6月 -09

E:\expdb>FTP 192.168.2.1

Connected to 192.168.2.1.

220 (vsFTPd 2.0.1)

User (192.168.2.1:(none)): oracle

331 Please specify the password.

Password:

230 Login successful.

ftp> bin

200 Switching to Binary mode.

ftp> cd /bak/orabak

250 Directory successfully changed.

ftp> ldir

Invalid command.

ftp> put DATA_D-CATDB_I-2235366936_TS-TEST_FNO-5_01KHNKDK

200 PORT command successful. Consider using PASV.

150 Ok to send data.

226 File receive OK.

ftp: 104865792 bytes sent in 1.27Seconds 82832.38Kbytes/sec.

ftp> bye

221 Goodbye.

E:\expdb>FTP 192.168.2.1

Connected to 192.168.2.1.

220 (vsFTPd 2.0.1)

User (192.168.2.1:(none)): oracle

331 Please specify the password.

Password:

230 Login successful.

ftp> cd/bak/orabak

Invalid command.

ftp> bin

200 Switching to Binary mode.

ftp> put TEST.DMP

200 PORT command successful. Consider using PASV.

150 Ok to send data.

226 File receive OK.

ftp: 77824 bytes sent in 0.00Seconds 77824000.00Kbytes/sec.

ftp> bye

221 Goodbye.

6 文件上传目标服务器

7 使用rman在目标数据库转换文件

RMAN> convert datafile ‘/bak/orabak/DATA_D-CATDB_I-2235366936_TS-TEST_FNO-5_01KHNKDK’ db_file_name_convert ‘/bak/orabak/DATA_D-CATDB_I-2235366936_TS-TEST_FNO-5_01KHNKDK’,'+ORADATA_DG/’;

Starting backup at 16-JUN-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=835 instance=newsdb1 devtype=DISK

channel ORA_DISK_1: starting datafile conversion

input filename=/bak/orabak/DATA_D-CATDB_I-2235366936_TS-TEST_FNO-5_01KHNKDK

converted datafile=+ORADATA_DG/newsdb/datafile/test.316.689694787

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02

Finished backup at 16-JUN-09

8 在目标数据库加载数据文件

SQL> conn system/aaa;

Connected.

SQL> create directory exp_dir as ‘/bak/orabak/’;

Directory created.

SQL> create user test identified by test;

User created.

[oracle@newsdb1 orabak]$ impdp system/aaa dumpfile=TEST.DMP directory=exp_dir transport_datafiles=’+ORADATA_DG/newsdb/datafile/test.316.689694787′

Import: Release 10.2.0.4.0 – 64bit Production on Tuesday, 16 June, 2009 13:55:36

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Master table “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded

Starting “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″: system/******** dumpfile=TEST.DMP directory=exp_dir transport_datafiles=+ORADATA_DG/newsdb/datafile/test.316.689694787

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job “SYSTEM”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at 13:55:40

SQL> select file_name from dba_data_files;

FILE_NAME

——————————————————————————–

+ORADATA_DG/newsdb/datafile/users.315.689009831

+ORADATA_DG/newsdb/datafile/sysaux.257.688649323

+ORADATA_DG/newsdb/datafile/undotbs1.258.688649323

+ORADATA_DG/newsdb/datafile/system.256.688649323

+ORADATA_DG/newsdb/datafile/undotbs2.264.688649407

FILE_NAME

+ORADATA_DG/newsdb/datafile/test.316.689694787

SQL> select count(*) from test.test;

COUNT(*)

———-

49745

SQL> alter tablespace test read write;

Tablespace altered.