ITPub博客

首页 > 数据库 > Oracle > oracle10G 异构方式迁移表空间之一

oracle10G 异构方式迁移表空间之一

原创 Oracle 作者:西门吹牛 时间:2011-02-16 15:10:48 0 删除 编辑

西门说明:本次试验成功,但是是按照标准流程,把源库表空间设置为只读状态进行的,实际上在生产库,一般是不允许这样操作的。

源库:WinXP + Oracle10.2.0.1
目的库:Linux + ASM + RAC + Oracle10.2.0.1


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

SQL> col platform_name for a40
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

查询目标数据库平台信息

SQL> col platform_name for a40
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

发现两个平台的endian格式不一致,需要转换

查询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
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big

已选择17行。

SQL>
SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
-----------------------------------------------------------------------------
ADMIN_DIR
C:ADEaime_10.2_nt_pushoracle/md/admin

DATA_PUMP_DIR
C:oracleproduct10.2.0adminorcldpdump

WORK_DIR
C:ADEaime_10.2_nt_pushoracle/work


已选择3行。

SQL>

2.创建一个独立的自包含表空间,用于测试

SQL> select name from v$datafile;

NAME
------------------------------------------------------
C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF
C:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF
C:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF
C:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF

create tablespace trans datafile 'C:ORACLEPRODUCT10.2.0ORADATAORCLtrans01.dbf' size 2M;

create user trans identified by trans default tablespace trans;

grant connect,resource to trans;

connect trans/trans

create table test as select * from all_objects where rownum < 1000;

commit;

确认该表空间是可以传输的
SQL>EXECUTE

3.导出要传输的表空间
注意:传输表空间必须置为只读状态

$ 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.


C:> expdp trans/trans dumpfile='trans.dmp' directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES=trans


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: 作业不存在
ORA-31633: 无法创建主表 "TRANS.SYS_EXPORT_TRANSPORTABLE_05"
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPV$FT", line 863
ORA-01647: 表空间 'TRANS' 是只读, 无法在其中分配空间


原因:expdp导出需要创建一张Master Table,users表空间只读,无法创建。
可以用别的用户来执行导出即可!

C:> expdp system/tagal dumpfile='trans.dmp' directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES=trans

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile='trans.dm
p' directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES=trans
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
C:ORACLEPRODUCT10.2.0ADMINORCLDPDUMPTRANS.DMP
作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 17:09:44 成功完成


C:>

上面文件生成,C:oracleproduct10.2.0adminorcldpdumptrans.dmp 文件只有80K,而TRANSS表空间数据文件2M。

4.使用rman转换文件格式
相当于生成的文件是Linux IA (32-bit)格式的数据文件

$ 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 'Linux IA (32-bit)'
3> Format 'C:oracleproduct10.2.0adminorcldpdump%U';

启动 backup 于 15-2月 -11
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=146 devtype=DISK
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00005 name=C:ORACLEPRODUCT10.2.0ORADATAORCLTRANS01.DBF
已转换的数据文件 = C:ORACLEPRODUCT10.2.0ADMINORCLDPDUMPDATA_D-ORCL_I-1266
117835_TS-TRANS_FNO-5_02M4O9QD
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
完成 backup 于 15-2月 -11

RMAN>

5.确认导出文件已生成并且在源库把表空间变成可写
下面文件生成,大小有2M,和TRANS表空间文件一样大
C:oracleproduct10.2.0adminorcldpdumpDATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD

在源库把表空间变成可写
SQL> alter tablespace trans read write;

6.通过ftp传输文件至目标主机
注意用bin方式传输

把上面步骤生成的文件复制到 /home/oracle/目录下

7.使用rman在目标数据库转换文件
我理解这步其实没有必要,因为这个文件其实已经是Linux IA (32-bit)格式的文件了
我理解这步的意义只不过是把名字改了一下而已。

连接到目标数据库:
rman target /

RMAN> CONVERT DATAFILE '/home/oracle/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD'
2> DB_FILE_NAME_CONVERT
3> '/home/oracle/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD','/home/oracle/trans01.dbf';

Starting backup at 16-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 instance=RACDB1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD
converted datafile=/home/oracle/trans01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 16-FEB-11

RMAN>


[oracle@node1 ~]$ pwd
/home/oracle
[oracle@node1 ~]$ ls -ltr
total 4300
-rw-r--r-- 1 oracle oinstall 81920 Feb 15 17:09 TRANS.DMP
-rw-r--r-- 1 oracle oinstall 2105344 Feb 15 17:11 DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD
-rw-r----- 1 oracle oinstall 2105344 Feb 16 01:19 trans01.dbf
[oracle@node1 ~]$

可见这两个文件大小和格式都一样。

然后把文件convert到ASM中

RMAN> convert datafile '/home/oracle/trans01.dbf' format '+RAC_DISK/racdb/datafile/trans01.dbf';

Starting backup at 16-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 instance=RACDB1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/trans01.dbf
converted datafile=+RAC_DISK/racdb/datafile/trans01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 16-FEB-11

RMAN>

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

必须保证目标库没有同名的表空间,而且目标库有同样的用户

SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DATA_PUMP_DIR
/opt/ora10g/product/10.2.0/db_1/rdbms/log/

ADMIN_DIR
/opt/ora10g/product/10.2.0/db_1/md/admin

SD
+RAC_DISK/racdb/datafile


DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
WORK_DIR
/opt/ora10g/product/10.2.0/db_1/work

DD
/home/oracle


SQL>

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

directory是数据库建立的目录,如果没有,需要通过 create directory DD as '/home/oracle' ; 建立,这个目录我们我们之前的实验已经建立了。
dumpfile 参数是该目录下的导出文件
transport_datafiles 是绝对路径名称,是指ASM中需要关联的数据文件。

[oracle@node1 ~]$ impdp system/tagal dumpfile=TRANS.DMP directory=DD transport_datafiles='+RAC_DISK/racdb/datafile/trans01.dbf'

Import: Release 10.2.0.1.0 - Production on Wednesday, 16 February, 2011 1:23:13

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, Real Application Clusters, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TRANS.DMP directory=DD transport_datafiles=+RAC_DISK/racdb/datafile/trans01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user TRANS does not exist in the database

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 01:23:41

[oracle@node1 ~]$

原来是用户trans不存在,没有关系,我们建立相关的用户就可以了。其实如果我们之前查询一下,哪些用户的默认表空间是这个表空间就好了。
这步可以节约不少时间

[oracle@node1 linux]$ impdp system/tagal dumpfile=TRANS.DMP directory=DD transport_datafiles='+RAC_DISK/racdb/datafile/trans01.dbf'

Import: Release 10.2.0.1.0 - Production on Wednesday, 16 February, 2011 23:00:19

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, Real Application Clusters, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TRANS.DMP directory=DD transport_datafiles=+RAC_DISK/racdb/datafile/trans01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 23:01:27

[oracle@node1 linux]$

成功了


9.检查数据

sqlplus trans/trans


SQL> select count(*) from test;

COUNT(*)
----------
999

SQL> exit


成功了

注意,上面第八步的时候,可以指定impdp的参数remap_schema=trans:other
把源库上面trans用户的所有对象转移到目标库other用户下面

[@more@]

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

请登录后发表评论 登录
全部评论

注册时间:2013-06-13

  • 博文量
    18
  • 访问量
    122931