前一篇簡單介紹了10g的新特性，RMAN的CONVERT命令，不過由於篇幅，沒有給出一個具體的例子，這篇利用CONVERT在SOLARIS平臺和LINUX平臺的10.2.0.3版本資料庫之前實現表空間的遷移。

Oracle10g新增CONVERT語法：http://yangtingkun.itpub.net/post/468/483871

首先檢查源資料庫的版本，並檢查表空間是否滿足遷移要求：

$ uname -a
SunOS racnode1 5.8 Generic_117350-46 sun4u sparc SUNW,Sun-Fire-480R
$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 3月 5 23:31:24 2009

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> show parameter compati

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      10.2.0.1.0
plsql_v2_compatibility               boolean     FALSE
SQL> show parameter block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select tablespace_name, block_size from dba_tablespaces
2 where tablespace_name = 'TEST';

TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
TEST 8192

SQL> select property_name, property_value
2 from database_properties
3 where property_name like 'NLS%CHARACTERSET';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               ZHS16GBK

SQL> select distinct owner from dba_segments
2 where tablespace_name = 'TEST';

OWNER
------------------------------
TEST

SQL> select table_name from dba_tables
2 where tablespace_name = 'TEST';

TABLE_NAME
------------------------------
T_P
T_C

SQL> exec dbms_tts.transport_set_check('TEST', true)

PL/SQL 過程已成功完成。

SQL> select * from transport_set_violations;

未選定行

檢查目標資料庫是否滿足表空間遷移要求，

[oracle@yanttest ~]$ uname -a
Linux yanttest 2.6.18-8.el5 #1 SMP Tue Jun 5 23:25:19 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
[oracle@yanttest ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 5月 5 15:43:17 2009

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> show parameter compati

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      10.2.0.1.0
plsql_v2_compatibility               boolean     FALSE
SQL> show parameter block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select property_name, property_value
2 from database_properties
3 where property_name like 'NLS%CHARACTERSET';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               ZHS16GBK
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
DRSYS
INDX
TOOLS
USERS
XDB
LT_INDEX_TS
NDINDEX
NDMAIN
QUEST
SYSAUX
SHIYQ
STEST

已選擇15行。

SQL> select username from dba_users
2 where username = 'TEST';

USERNAME
------------------------------
TEST

SQL> select object_name from dba_objects
2 where wner = 'TEST'
3 and object_name in ('T_P', 'T_C');

未選定行

目標資料庫的版本和源資料庫一致，相容性設定也一樣。資料庫字符集和國家字符集設定一致。預設的資料塊大小也一樣，且TEST表空間在目標資料庫不存在。TEST使用者在目標資料庫存在，不過遷移表空間操作所要匯入的表在目標資料庫TEST使用者中不存在。

下面執行匯出操作：

SQL> alter tablespace test read only;

表空間已更改。

SQL> host
$ expdp system directory=d_output dumpfile=trans_test.dp transport_tablespaces=test transport_full_check=y

Export: Release 10.2.0.3.0 - 64bit Production on 星期五, 06 3月, 2009 0:26:55

Copyright (c) 2003, 2005, Oracle. All rights reserved.
口令:

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
啟動 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=d_output dumpfile=trans_test.dp transport_tablespaces=test transport_full_check=y
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/INDEX
處理物件型別 TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
處理物件型別 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
處理物件型別 TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
處理物件型別 TRANSPORTABLE_EXPORT/TABLE_STATISTICS
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功載入/解除安裝了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的轉儲檔案集為:
/export/home/oracle/trans_test.dp
作業 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已於 00:28:00 成功完成

$ rman target /

恢復管理器: Release 10.2.0.3.0 - Production on 星期五 3月 6 00:31:16 2009

連線到目標資料庫: TESTRAC (DBID=4291216984)

RMAN> convert tablespace 'TEST'
2> to platform. 'Linux 64-bit for AMD'
3> format '/data1/backup/test01.dbf';

啟動 backup 於 06-3月 -09
使用目標資料庫控制檔案替代恢復目錄
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=289 例項=testrac1 devtype=DISK
分配的通道: ORA_DISK_2
通道 ORA_DISK_2: sid=316 例項=testrac2 devtype=DISK
通道 ORA_DISK_1: 啟動資料檔案轉換
輸入資料檔案 fno=00008 name=+DISK/testrac/datafile/test01.dbf
已轉換的資料檔案 = /data1/backup/test01.dbf
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:07
完成 backup 於 06-3月 -09

RMAN> exit

恢復管理器完成。
$ exit

SQL> alter tablespace test read write;

表空間已更改。

將表空間置於READ ONLY狀態，隨後利用EXPDP執行傳輸表空間的源資料匯出，然後利用RMAN的CONVERT命令將SOLARIS平臺的資料檔案轉化為Linux X86 64格式。由於資料檔案的複製已經完成，這時已經可以將源資料庫的表空間修改為可寫狀態。

將匯出的源資料和資料檔案ftp到目標資料庫伺服器上：

SQL> select directory_path from dba_directories
2 where directory_name = 'D_TEST';

DIRECTORY_PATH
-----------------------------------------
/data

SQL> host
[oracle@yanttest ~]$ cd /data
[oracle@yanttest data]$ ftp 172.25.198.222
Connected to 172.25.198.222.
220 racnode1 FTP server (SunOS 5.8) ready.
500 'AUTH GSSAPI': command not understood.
500 'AUTH KERBEROS_V4': command not understood.
KERBEROS_V4 rejected as an authentication type
Name (172.25.198.222:oracle): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Type set to I.
ftp> cd /export/home/oracle
250 CWD command successful.
ftp> prompt
Interactive mode off.
ftp> mget tra*.dp
local: trans_test.dp remote: trans_test.dp
227 Entering Passive Mode (172,25,198,222,251,243)
150 Binary data connection for trans_test.dp (172.25.13.231,50373) (110592 bytes).
226 Binary Transfer complete.
110592 bytes received in 0.0083 seconds (1.3e+04 Kbytes/s)
ftp> cd /data1/backup
250 CWD command successful.
ftp> lcd /data/oradata/testzj/
Local directory now /data/oradata/testzj
ftp> mget test01.dbf
local: test01.dbf remote: test01.dbf
227 Entering Passive Mode (172,25,198,222,251,253)
150 Binary data connection for test01.dbf (172.25.13.231,46996) (104865792 bytes).
226 Binary Transfer complete.
104865792 bytes received in 8.9 seconds (1.1e+04 Kbytes/s)
ftp> quit
221 Goodbye.

將資料檔案和匯出源資料放到目標資料庫指定位置，就可以執行資料泵的匯入進行表空間的載入了：

[oracle@yanttest data]$ impdp system directory=d_test dumpfile=trans_test.dp transport_datafiles='/data/oradata/testzj/test01.dbf'

Import: Release 10.2.0.3.0 - 64bit Production on 星期二, 05 5月, 2009 16:42:27

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
已成功載入/解除安裝了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=d_test dumpfile=trans_test.dp transport_datafiles=/data/oradata/testzj/test01.dbf
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/INDEX
處理物件型別 TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
處理物件型別 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
處理物件型別 TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
處理物件型別 TRANSPORTABLE_EXPORT/TABLE_STATISTICS
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作業 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已於 16:42:35 成功完成

資料檔案成功匯入，下面回到資料庫中進行檢查：

[oracle@yanttest data]$ exit
exit

SQL> select owner, table_name, tablespace_name from dba_tables
2 where wner = 'TEST'
3 and tablespace_name = 'TEST';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           T_P                            TEST
TEST                           T_C                            TEST

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

COUNT(*)
----------
2

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

COUNT(*)
----------
2

SQL> alter tablespace test read write;

表空間已更改。

檢查無誤後，將表空間置於可寫狀態。

至此利用CONVERT語法，執行的跨平臺的表空間遷移完成。

利用CONVERT實現跨平臺表空間遷移

相關文章