ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Ora10G跨平台传输表空间

Ora10G跨平台传输表空间

原创 Oracle 作者：Steven1981 时间：2008-03-18 15:05:15 0 删除 编辑
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

三,查看两个平台之间是否可以传输(big<->big ,little<->little)
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.

oracle@linux:~> 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

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

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.

oracle@linux:~> 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

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

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.

oracle@linux:~> 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

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

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

oracle@linux:~> 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

--- 确认导出文件已生成.
oracle@linux:~> 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

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

连接到目标数据库: 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

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

连接到: 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;

表空间已更改。

十二,结束
没有感言. 但有收获. 最后,做一个数据库的备份!

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

0
0
上一篇： 关于oracle 10G for suse 9 的rman: can't open target
下一篇： Ora10G 用ASH生成性能报告
请登录后发表评论 登录
全部评论
Steven1981
  • 博文量
    127
  • 访问量
    855003

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593