跨平台传输表空间

最新推荐文章于 2024-09-09 09:47:53 发布
cpgs76737
最新推荐文章于 2024-09-09 09:47:53 发布
阅读量128
点赞数
文章标签：数据库操作系统
跨平台传输表空间

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

查询Oracle11g支持的平台转换

	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 

	4 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

3.导出要传输的表空间

	$ pwd
	/opt/oracle
	$ cd dpdata
	$ ls
	$ expdp scott/tiger dumpfile=trans.dmp directory=dpdata transport_tablespace=trans
	LRM-00101: unknown parameter name 'transport_tablespace'

	$ expdp scott/tiger 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 "scott"."SYS_EXPORT_TRANSPORTABLE_01": scott/******** 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 scott/tiger 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 "SCOTT"."SYS_EXPORT_TRANSPORTABLE_01": scott/******** 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 "SCOTT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
	******************************************************************************
	Dump file set for SCOTT.SYS_EXPORT_TRANSPORTABLE_01 is:
	/opt/oracle/dpdata/trans.dmp
	Job "SCOTT"."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.确认导出文件已生成

6.通过ftp传输文件(expdp文件及转换后的数据文件)至目标主机

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

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

	恢复管理器: 版本10.1.0.2.0 - Production

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

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


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

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

	RMAN>

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

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

	E:\Oracle\oradata\eygle\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
	ata\eygle\EYGLE\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\eygle\EYGLE\DATAFILE\TRANS01.DBF'
	处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
	ORA-39123: 数据泵可传输的表空间作业中止
	ORA-29342: 数据库中不存在用户 TRANS

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


	E:\Oracle\oradata\eygle\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\eygle\dpdata>impdp eygle/eygle dumpfile=trans.dmp directory=dpdata transport_datafiles='E:\Oracle\orad
	ata\eygle\EYGLE\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\eygle\EYGLE\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\eygle\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\eygle\dpdata>

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

	SQL> alter tablespace trans read write;

	表空间已更改。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/203348/viewspace-1281236/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/203348/viewspace-1281236/