Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2026140
  • 博文数量: 508
  • 博客积分: 10716
  • 博客等级: 上将
  • 技术积分: 7266
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-01 22:35
文章分类

全部博文(508)

文章存档

2018年(4)

2017年(36)

2016年(22)

2015年(1)

2013年(10)

2012年(19)

2011年(15)

2010年(15)

2009年(238)

2008年(148)

分类: Oracle

2009-01-16 13:47:23

Oracle10g跨平台传输表空间 – eygle

1.准备工作:
查询源数据库平台信息

SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Solaris[tm] OE (64-bit) Big

查询目标数据库平台信息

SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
3 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 64
-bit for AMD                     Little


         16 Apple Mac OS                             Big


         12 Microsoft Windows 64
-bit for AMD         Little




2.创建一个独立的自包含表空间

用于测试.

代码:


 


$ sqlplus "/ as sysdba"




SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:04:08 2004




Copyright
(c) 1982, 2004, Oracle.  All rights reserved.






Connected to:


Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production


With the Partitioning
, OLAP and Data Mining options




SQL
> select name from v$datafile;




NAME


--------------------------------------------------------------------------------


/
opt/oracle/oradata/eygle/system01.dbf


/opt/oracle/oradata/eygle/undotbs01.dbf


/opt/oracle/oradata/eygle/sysaux01.dbf


/opt/oracle/oradata/eygle/users01.dbf


/data1/oradata/systemfile/eygle01.dbf


/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf


/opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf




7 rows selected
.




SQL> create tablespace trans


  2  datafile
'/data1/oradata/systemfile/trans01.dbf'


  
3  size 10M;




Tablespace created.




SQL> create user trans identified by trans


  2  
default tablespace trans;




User created.




SQL> grant connect,resource to trans;




Grant succeeded.




SQL> connect trans/trans


Connected
.




SQL> create table test as select * from user_objects;




Table created.




SQL> select  count(*) from test;




  
COUNT(*)


----------


         
1




SQL
> select * from test;




OBJECT_NAME


--------------------------------------------------------------------------------


SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE


------------------------------ ---------- -------------- -------------------


CREATED      LAST_DDL_TIM TIMESTAMP           STATUS  T G S


------------ ------------ ------------------- ------- - - -


TEST


                                    15604          15604 TABLE


27
-APR-04    27-APR-04    2004-04-27:14:05:42 VALID   N N N






SQL
> exit


Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production


With the Partitioning
, OLAP and Dat


3.导出要传输的表空间
$ pwd
/opt/oracle
$ cd dpdata
$ ls
$ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_tablespace=trans
LRM-00101: unknown parameter name 'transport_tablespace'

$ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans

Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:07

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace 'TRANS' is not read only

Job "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 14:08

注意:传输表空间必须置为只读状态

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 27 14:08:13 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter tablespace trans read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


$ expdp eygle/eygle dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans

Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 14:08

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata TRANSPORT_TABLESPACES=trans
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EYGLE.SYS_EXPORT_TRANSPORTABLE_01 is:
/opt/oracle/dpdata/trans.dmp
Job "EYGLE"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:09

4.使用rman转换文件格式

$ rman target /

Recovery Manager: Release 10.1.0.2.0 - 64bit Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: EYGLE (DBID=1337390772)

RMAN> convert tablespace trans
2>
to platform 'Microsoft Windows IA (32-bit)'
3> Format '/tmp/%U';

Starting backup at 27-APR-04
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00008 name=/data1/oradata/systemfile/trans01.dbf
converted datafile=/tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 27-APR-04

RMAN> exit


Recovery Manager complete.

5.确认导出文件已生成.

$ ls /tmp/data*
/tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ ls -l /tmp/data*
-rw-r----- 1 oracle dba 10493952 Apr 27 14:12 /tmp/data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg
$ chmod 777 /tmp/data*
$ chmod 777 /opt/oracle/dpdata/*

6.
通过ftp传输文件至目标主机


220 billing-center.hurray.com.cn FTP server (SunOS 5.8) ready.
User (172.16.33.32 none)): gqgai
331 Password required for gqgai.
Password:
230 User gqgai logged in.
ftp> bin
200 Type set to I.
ftp> cd /tmp
250 CWD command successful.
ftp> mget data*
200 Type set to I.
mget data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg? y
200 PORT command successful.
150 Binary data connection for data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg (172.16.32.65,2885) (10493952 bytes).
226 Binary Transfer complete.
ftp: 10493952 bytes received in 15.90Seconds 659.87Kbytes/sec.
ftp> cd /opt/oracle/dpdata
250 CWD command successful.
ftp> ls
200 PORT command successful.
150 ASCII data connection for /bin/ls (172.16.32.65,2889) (0 bytes).
export.log
trans.dmp
226 ASCII Transfer complete.
ftp: 23 bytes received in 0.01Seconds 2.30Kbytes/sec.
ftp> bin
200 Type set to I.
ftp> mget trans.dmp
200 Type set to I.
mget trans.dmp? y
200 PORT command successful.
150 Binary data connection for trans.dmp (172.16.32.65,2893) (73728 bytes).
226 Binary Transfer complete.
ftp: 73728 bytes received in 0.03Seconds 2457.60Kbytes/sec.
ftp> bye
221 Goodbye.

 

7.使用rman在目标数据库转换文件

E:\Oracle\oradata\vilen\dpdata>rman target /

恢复管理器: 版本10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

连接到目标数据库: VILEN (DBID=1587222708)


RMAN>
CONVERT DATAFILE 'E:\Oracle\oradata\vilen\dpdata\data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg'
2> DB_FILE_NAME_CONVERT
3> 'E:\Oracle\oradata\vilen\dpdata\data_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01fk92hg','E:\Oracle\oradata\vilen\VILEN\DATAFILE\trans01.dbf';

启动 backup 27-4 -04
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件转换
输出文件名=E:\ORACLE\ORADATA\VILEN\DPDATA\DATA_D-EYGLE_I-1337390772_TS-TRANS_FNO-8_01FK92HG
已转换的数据文件 = E:\ORACLE\ORADATA\VILEN\VILEN\DATAFILE\TRANS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04
完成 backup 27-4 -04

RMAN>

8.在目标数据库plugin数据文件

注意目标数据库中的目标用户必须存在
否则会报错.

E:\Oracle\oradata\vilen\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
ata\vilen\VILEN\DATAFILE\TRANS01.DBF'

Import: Release 10.1.0.2.0 - Production on
星期二, 27 4, 2004 15:02

Copyright (c) 2003, Oracle. All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\
Oracle\oradata\vilen\VILEN\DATAFILE\TRANS01.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123:
数据泵可传输的表空间作业中止
ORA-29342: 数据库中不存在用户 TRANS

作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 15:02 停止


E:\Oracle\oradata\vilen\dpdata>sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on
星期二 4 27 15:03:03 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user trans identified by trans;

用户已创建。

SQL> grant connect,resource to trans;

授权成功。

SQL> exit
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
断开

E:\Oracle\oradata\vilen\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
ata\vilen\VILEN\DATAFILE\TRANS01.DBF'

Import: Release 10.1.0.2.0 - Production on
星期二, 27 4, 2004 15:03

Copyright (c) 2003, Oracle. All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01": eygle/******** dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\
Oracle\oradata\vilen\VILEN\DATAFILE\TRANS01.DBF'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
作业 "EYGLE"."SYS_IMPORT_TRANSPORTABLE_01" 已于 15:03 成功完成

9.检查数据

E:\Oracle\oradata\vilen\dpdata>sqlplus trans/trans

SQL*Plus: Release 10.1.0.2.0 - Production on
星期二 4 27 15:03:50 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from test;

COUNT(*)
----------
1

SQL> exit
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
断开

E:\Oracle\oradata\vilen\dpdata>

可以选择把表空间更改为读写

SQL> alter tablespace trans read write;

表空间已更改。

10.总结

10g
的表空间跨平台迁移,较9i就是增加了一个使用Rman进行的文件格式转换的过程.
实际上也就是转换了数据文件头的格式信息而已.

阅读(1055) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册