使用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--