10G新特性[@more@]

一,檢視源資料庫平臺資訊
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 IA (32-bit) Little

二,檢視目標機平臺資訊
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

三,檢視兩個平臺之間是否可以傳輸(bigbig ,littlelittle)
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.

四,在源資料庫建立需要傳輸的表空間

SQL> conn hyf/hyf
Connected.

SQL> create tablespace data01 datafile '/oradata/ora10/data01.dbf' size 10M;

Tablespace created.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
DATA01

6 rows selected.

SQL> ! ls -l /oradata/ora10/
total 999780
-rw-r----- 1 oracle oinstall 7061504 2008-03-18 10:05 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 2008-03-18 10:05 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 2008-03-18 10:05 control03.ctl
-rw-r----- 1 oracle oinstall 10493952 2008-03-18 10:00 data01.dbf
-rw-r----- 1 oracle oinstall 52429312 2008-03-18 06:00 redo01.log
-rw-r----- 1 oracle oinstall 52429312 2008-03-18 10:05 redo02.log
-rw-r----- 1 oracle oinstall 52429312 2008-03-17 11:00 redo03.log
-rw-r----- 1 oracle oinstall 293609472 2008-03-18 10:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 2008-03-18 10:05 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 2008-03-17 22:01 temp01.dbf
-rw-r----- 1 oracle oinstall 26222592 2008-03-18 10:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 2008-03-18 06:05 users01.dbf

SQL> alter user hyf default tablespace data01;

User altered.

SQL> create table test as select username,user_id,password from dba_users
2 ;

Table created.

SQL> select count(*) from test;

COUNT(*)
----------
22

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

TABLESPACE_NAME
------------------------------
DATA01

五,匯出要傳輸的表空間:

SQL> create directory expdp as '/home/oracle';

Directory created.

:~> expdp hyf/hyf dumpfile=data01.dmp directory=expdp transport_tablespaces=data01;

Export: Release 10.2.0.1.0 - Production on Tuesday, 18 March, 2008 10:17:54

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HYF"."SYS_EXPORT_TRANSPORTABLE_01": hyf/******** dumpfile=data01.dmp directory=expdp transport_tablespaces=data01
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace 'DATA01' is not read only

Job "HYF"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:18:16

----注意這裡提示要匯出的表空間必須為只讀

SQL> alter tablespace data01 read only;

Tablespace altered.

:~> expdp hyf/hyf dumpfile=data01.dmp directory=expdp transport_tablespaces=data01;

Export: Release 10.2.0.1.0 - Production on Tuesday, 18 March, 2008 10:20:17

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "HYF.SYS_EXPORT_TRANSPORTABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01647: tablespace 'DATA01' is read only, cannot allocate space in it

--在這裡提示DATA01為只讀,不能分配空間. 由於hyf這個使用者的預設表空間就是data01.下面將它改為USERS

SQL> alter user hyf default tablespace users;

User altered.

:~> expdp hyf/hyf dumpfile=data01.dmp directory=expdp transport_tablespaces=data01;

Export: Release 10.2.0.1.0 - Production on Tuesday, 18 March, 2008 10:22:07

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "HYF"."SYS_EXPORT_TRANSPORTABLE_01": hyf/******** dumpfile=data01.dmp directory=expdp transport_tablespaces=data01
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "HYF"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HYF.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/data01.dmp
Job "HYF"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:23:12

:~> ll
total 80
-rw-r----- 1 oracle oinstall 73728 2008-03-18 10:23 data01.dmp
-rw-r--r-- 1 oracle oinstall 892 2008-03-18 10:23 export.log

成功匯出表空間.

六, 在源資料庫上使用rman進行轉換檔案格式RMAN> convert tablespace data01 to platform 'Microsoft Windows IA (32-bit)' format '/home/oracle/data01_rman.dmp';

Starting backup at 18-MAR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/oradata/ora10/data01.dbf
converted datafile=/home/oracle/data01_rman.dmp
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 18-MAR-08

RMAN> exit

--- 確認匯出檔案已生成.
:~> ls -l data*
-rw-r----- 1 oracle oinstall 73728 2008-03-18 10:23 data01.dmp
-rw-r----- 1 oracle oinstall 10493952 2008-03-18 10:41 data01_rman.dmp

七.ftp 到目標資料庫主機.(data01.dmp,data01_rman.dmp)

八.使用rman在目標資料庫轉換檔案

SQL> select username from dba_users where username='HYF';

USERNAME
------------------------------
HYF

注意目標資料庫中的目標使用者必須存在，否則會報錯。
如果沒有就建立帳戶
create user hyf identified by hyf default tablespace users;
grant resource to hyf;
grant connect to hyf;

C:Documents and SettingsCCAV>rman target /

恢復管理器: Release 10.2.0.1.0 - Production on 星期二 3月 18 14:03:38 2008

連線到目標資料庫: ORA10W (DBID=2110870445)

RMAN> convert datafile 'd:ora10tmpdata01_rman.dmp' db_file_name_convert
'd:ora10tmpdata01_rman.dmp','d:ora10oradataora10wdata01.dbf';

啟動 backup 於 18-3月 -08
使用目標資料庫控制檔案替代恢復目錄
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=154 devtype=DISK
通道 ORA_DISK_1: 啟動資料檔案轉換
輸出檔名=D:ORA10TMPDATA01_RMAN.DMP
已轉換的資料檔案 = D:ORA10ORADATAORA10WDATA01.DBF
通道 ORA_DISK_1: 資料檔案轉換完畢, 經過時間: 00:00:02
完成 backup 於 18-3月 -08

RMAN> exit

九,在目標庫匯入資料

C:Documents and SettingsCCAV>impdp hyf/hyf dumpfile=data01.dmp directory=expdp
transport_datafiles='d:ora10oradataora10wdata01.dbf'

Import: Release 10.2.0.1.0 - Production on 星期二, 18 3月, 2008 14:08:01

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功載入/解除安裝了主表 "HYF"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "HYF"."SYS_IMPORT_TRANSPORTABLE_01": hyf/******** dumpfile=data01.dmp dire
ctory=expdp transport_datafiles='d:ora10oradataora10wdata01.dbf'
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作業 "HYF"."SYS_IMPORT_TRANSPORTABLE_01" 已於 14:08:06 成功完成

十,檢驗資料

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
DATA01

已選擇7行。

SQL> select count(*) from test;
COUNT(*)
----------
22

十一,置源主機和目標主機上,置表空間為可讀寫

SQL> alter tablespace data01 read write;

表空間已更改。

十二,結束
沒有感言. 但有收穫. 最後,做一個資料庫的備份!

Ora10G跨平臺傳輸表空間

相關文章