使用rman在不同平台之间传送oracle asm表空间（transport tablespace)

源：Linux ORACLE 10.2.0.1 RAC 数据文件存在于 ASM
目标：AIX 5.3 ORACLE 10.2.0.3，数据文件在文件系统

参考：metalink node.371556.1

总体步骤：

1、准备工作：创建测试表空间、测试用户
2、迁移表空间

详细过程：

[@more@]

1、准备工作

1.1、在源数据库上创建测试表空间

[oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 19 18:20:02 2010 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, Real Application Clusters, OLAP and Data Mining options SQL> create tablespace test_tbs1; Tablespace created.

1.2、在源数据库上创建测试用户

SQL> create user test01 identified by oracle default tablespace test_tbs1; User created. SQL> grant dba to test01; Grant succeeded.

1.3、为测试用户准备数据

SQL> create table test as select * from dba_objects; Table created. SQL> select count(1) from test; COUNT(1) ---------- 50477

2、迁移过程

2.1、在源数据库上检查是否可以迁移到AIX平台

SQL> conn / as sysdba Connected. SQL> COLUMN PLATFORM_NAME FORMAT A32 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 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.

2.2、准备在源数据库上准备导出表空间

SQL> execute sys.dbms_tts.transport_set_check('test_tbs1',true); PL/SQL procedure successfully completed. SQL> select * from sys.transport_set_violations; no rows selected SQL> ALTER TABLESPACE test_tbs1 read only; Tablespace altered. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options

2.3、在源数据库上导出metadata

[oracle@rac1 ~]$ exp userid='sys/sys as sysdba' file=tbs_exp.dmp log=tba_exp.log transport_tablespace=y tablespaces=test_tbs1; Export: Release 10.2.0.1.0 - Production on Tue Jan 19 20:11:06 2010 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, Real Application Clusters, OLAP and Data Mining options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses ZHS16CGB231280 character set (possible charset conversion) Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace TEST_TBS1 ... . exporting cluster definitions . exporting table definitions . . exporting table TEST . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings. [oracle@rac1 ~]$ ls -l *dmp -rw-r--r-- 1 oracle oinstall 16384 Jan 19 20:11 tbs_exp.dmp

2.4、在源数据库上从asm中生成目的数据库格式的文件

[oracle@rac1 ~]$ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jan 19 20:15:27 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: CNHTM (DBID=1435973924) RMAN> CONVERT TABLESPACE test_tbs1 TO PLATFORM 'AIX-Based Systems (64-bit)' FORMAT '/home/oracle/%U'; Starting backup at 19-JAN-10 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile conversion input datafile fno=00007 name=+DATA/cnhtm/datafile/test_tbs1.274.708718829 converted datafile=/home/oracle/data_D-CNHTM_I-1435973924_TS-TEST_TBS1_FNO-7_01l3sj2b channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07 Finished backup at 19-JAN-10 RMAN> exit Recovery Manager complete. [oracle@rac1 ~]$ ls -l total 102532 -rw-r----- 1 oracle oinstall 104865792 Jan 19 20:17 data_D-CNHTM_I-1435973924_TS-TEST_TBS1_FNO-7_01l3sj2b -rw-r--r-- 1 oracle oinstall 717 Jan 19 20:11 tba_exp.log -rw-r--r-- 1 oracle oinstall 16384 Jan 19 20:11 tbs_exp.dmp

2.5、将tbs_exp.dmp文件和data_D-CNHTM_I-1435973924_TS-TEST_TBS1_FNO-7_01l3sj2b两个文件ftp到目标数据库的主机上的/tmp目录

2.6、在目标数据库上准备用户

ibm@oracle[/home/oracle]> sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 19 12:08:42 2010 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> create user test01 identified by oracle; User created. SQL> grant dba to test01; Grant succeeded. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options

2.7、在目标数据库上导入表空间

ibm@oracle[/home/oracle]> cd /tmp ibm@oracle[/tmp]> ls -l total 333976 -rw-r----- 1 oracle dba 104865792 Jan 19 12:08 data_D-CNHTM_I-1435973924_TS-TEST_TBS1_FNO-7_01l3sj2b -rw-r----- 1 oracle dba 16384 Jan 19 11:35 tbs_exp.dmp ...... ibm@oracle[/tmp]> imp userid='sys/oracle as sysdba' > file=tbs_exp.dmp log=tba_imp.log > transport_tablespace=y > datafiles='/tmp/data_D-CNHTM_I-1435973924_TS-TEST_TBS1_FNO-7_01l3sj2b' Import: Release 10.2.0.3.0 - Production on Tue Jan 19 12:13:35 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.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 US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16CGB231280 character set (possible charset conversion) . importing SYS's objects into SYS . importing SYS's objects into SYS . importing TEST01's objects into TEST01 . . importing table "TEST" . importing SYS's objects into SYS Import terminated successfully without warnings. ibm@oracle[/tmp]>

2.8、检查导入的表空间

ibm@oracle[/tmp]> sqlplus test01/oracle SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 19 12:13:47 2010 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> select count(1) from test; COUNT(1) ---------- 50477

2.9、额外工作、修改数据文件名

使用如下命令检查数据文件名

SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oracle/oradata/test/system01.dbf /oracle/oradata/test/undotbs01.dbf /oracle/oradata/test/sysaux01.dbf /oracle/oradata/test/users01.dbf /oracle/oradata/test/example01.dbf /tmp/data_D-CNHTM_I-1435973924_TS-TEST_TBS1_FNO-7_01l3sj2b 6 rows selected.

发现新导入的数据文件名比较难看，使用如下方法进行修改

SQL> alter tablespace test_tbs1 offline; Tablespace altered. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ibm@oracle[/tmp]> ibm@oracle[/tmp]> mv data_D-CNHTM_I-1435973924_TS-TEST_TBS1_FNO-7_01l3sj2b > /oracle/oradata/test/test_tbs1_d01.dbf ibm@oracle[/tmp]> sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 19 12:21:35 2010 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> alter database rename file '/tmp/data_D-CNHTM_I-1435973924_TS-TEST_TBS1_FNO-7_01l3sj2b' to '/oracle/oradata/test/test_tbs1_d01.dbf'; Database altered. SQL> select file#,name from v$datafile; FILE# ---------- NAME -------------------------------------------------------------------------------- 1 /oracle/oradata/test/system01.dbf 2 /oracle/oradata/test/undotbs01.dbf 3 /oracle/oradata/test/sysaux01.dbf FILE# ---------- NAME -------------------------------------------------------------------------------- 4 /oracle/oradata/test/users01.dbf 5 /oracle/oradata/test/example01.dbf 6 /oracle/oradata/test/test_tbs1_d01.dbf 6 rows selected. SQL> alter tablespace test_tbs1 online; Tablespace altered.

--end--

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

转载于:http://blog.itpub.net/22049049/viewspace-1030703/