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

最新推荐文章于 2023-08-17 11:55:43 发布

congrang4031

最新推荐文章于 2023-08-17 11:55:43 发布

阅读量91

点赞数

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

源库：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

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

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

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10771/viewspace-1046070/

congrang4031

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle10G 异构方式迁移表空间之一

西门说明：本次试验成功，但是是按照标准流程，把源库表空间设置为只读状态进行的，实际上在生产库，一般是不允许这样操作的。源库：WinXP + Oracle10.2.0.1目的库：Linux + ASM + RAC + Oracle...
复制链接

扫一扫