跨平台传输表空间

测试了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.

Tags: RMAN EXPDP CONVERT TABLESPACE

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/611609/viewspace-614702/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/611609/viewspace-614702/