文档课题：运用传输表空间完成oracle异构平台迁移.
1、环境介绍
源平台：rhel6.5 64位 + oracle 11.2.0.3.0 64位 + asm单实例 + ORACLE_SID=orclasm
目标平台：windows 2008 server r2 64位 + oracle 11.2.0.1.0 64位 + ORACLE_SID=orcl
测试目标：将自定义的应用程序表空间app1tbs,app2tbs,idxtbs从源平台迁移到目标平台.
2、环境准备
2.1、将表空间
在源库上创建3个表空间.
[oracle@liujun ~]$ sas
SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 20 20:47:02 2022
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
> create tablespace app1tbs datafile '+data' size 20m;
> create tablespace app2tbs datafile '+data' size 20m;
> create tablespace idxtbs datafile '+data' size 20m;
> select 'datafile' file_type,file#,name FILE_NAME,status,enabled from v$datafile;
FILE_TYP FILE# FILE_NAME STATUS ENABLED
-------- ---------- ------------------------------------------------------------ ------- ----------
datafile 1 +DATA/orclasm/datafile/system.256.1115920627 SYSTEM READ WRITE
datafile 2 +DATA/orclasm/datafile/sysaux.257.1115920627 ONLINE READ WRITE
datafile 3 +DATA/orclasm/datafile/undotbs1.258.1115920627 ONLINE READ WRITE
datafile 4 +DATA/orclasm/datafile/users.259.1115920627 ONLINE READ WRITE
datafile 5 +DATA/orclasm/datafile/app1tbs.266.1115930901 ONLINE READ WRITE
datafile 6 +DATA/orclasm/datafile/app2tbs.267.1115930917 ONLINE READ WRITE
datafile 7 +DATA/orclasm/datafile/idxtbs.268.1115930935 ONLINE READ WRITE
7 rows selected.
2.2、创建表和索引
在相应的表空间创建表和索引.
> alter user scott account unlock;
> alter user scott identified by tiger;
> create table scott.app1_tab tablespace app1tbs as select * from scott.emp;
> create table scott.app2_tab tablespace app2tbs as select * from scott.dept;
> create index scott.idx_emp_ename on scott.app1_tab(ename) tablespace idxtbs;
> create index scott.idx_dept_dname on scott.app2_tab(dname) tablespace idxtbs;
2.3、字节序
传输表空间集到不同平台，需确定源和目标平台表空间是否被支持，也要确定每个平台的字节序，如果平台具有相同的字节序，则不需要转化，否则必须在源端或目标端做表空间集转化.
源平台查询
> select d.platform_name,tp.endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Linux x86 64-bit Little
说明：当前系统平台支持跨平台表空间传输(因以上查询有记录返回).
目标平台查询
> col platform_name for a40
> select d.platform_name,tp.endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- ----------------------------
Microsoft Windows x86 64-bit Little
说明：当前系统平台支持跨平台表空间传输，且源平台和目标平台的Endian_format 相同(均为Little)，不需要进行表空间集转换.
2.4、自包含
传输表空间前需先确认是否自包含，假设此处迁移app1tbs和idxtbs表空间.
> execute dbms_tts.transport_set_check('app1tbs,idxtbs',true);
> col violations for a70
> select * from transport_set_violations;
VIOLATIONS
----------------------------------------------------------------------
ORA-39907: Index SCOTT.IDX_DEPT_DNAME in tablespace IDXTBS points to table SCOTT.APP2_TAB in tablespace APP2TBS.
说明： 在idxtbs表空间中IDX_DEPT_DNAME索引指向了表空间集外的scott.APP2_TAB表，所以需选择app1tabs,app2tabs,idxtbs作为新的表空间集再次进行检查.
> execute dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);
> select * from transport_set_violations;
no rows selected
说明：此时该表空间集已不在违反自包含条件，可确定为一个可传输表空间集.
2.5、创建传输表空间集
2.5.1、设置只读
将自包含表空间集中的所有表空间设置为只读.
> alter tablespace app1tbs read only;
> alter tablespace app2tbs read only;
> alter tablespace idxtbs read only;
2.5.2、expdp
使用数据泵导出工具，导出要传输的各个表空间元数据.
> set line 9999
> col directory_name for a25
> col directory_path for a100
> select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
------------------------- -----------------------------------------------
XMLDIR /u01/app/oracle/product/11.2.0/db_1/rdbms/xml
DATA_PUMP_DIR /u01/app/oracle/admin/orclasm/dpdump/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state
开始导出
$ expdp system/oracle_4U@orclasm dumpfile=expdat.dmp directory=data_pump_dir transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log
Export: Release 11.2.0.3.0 - Production on Tue Sep 20 21:11:30 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@orclasm dumpfile=expdat.dmp directory=data_pump_dir transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/orclasm/dpdump/expdat.dmp
******************************************************************************
Datafiles required for transportable tablespace APP1TBS:
+DATA/orclasm/datafile/app1tbs.266.1115930901
Datafiles required for transportable tablespace APP2TBS:
+DATA/orclasm/datafile/app2tbs.267.1115930917
Datafiles required for transportable tablespace IDXTBS:
+DATA/orclasm/datafile/idxtbs.268.1115930935
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 21:11:48
2.5.3、查看文件
$ ll /u01/app/oracle/admin/orclasm/dpdump/
total 116
-rw-r----- 1 oracle asmadmin 116 Sep 20 17:58 dp.log
-rw-r----- 1 oracle asmadmin 110592 Sep 20 21:11 expdat.dmp
-rw-r--r-- 1 oracle asmadmin 1571 Sep 20 21:11 tts_export.log
ASMCMD> pwd
+data/orclasm/datafile
ASMCMD> ls
APP1TBS.266.1115930901
APP2TBS.267.1115930917
IDXTBS.268.1115930935
SYSAUX.257.1115920627
SYSTEM.256.1115920627
UNDOTBS1.258.1115920627
USERS.259.1115920627
ASMCMD> cp APP1TBS.266.1115930901 /home/grid
ASMCMD> cp APP2TBS.267.1115930917 /home/grid
ASMCMD> cp IDXTBS.268.1115930935 /home/grid
2.6、恢复源库表空间
恢复源库表空间为读写状态.
> alter tablespace app1tbs read write;
> alter tablespace app2tbs read write;
> alter tablespace idxtbs read write;
2.7、传输文件
将/home/grid目录下的转储元文件APP1TBS.DBF、APP2TBS.DBF、IDXTBS.DBF以及/u01/app/oracle/admin/orclasm/dpdump目录下的dmp文件EXPDAT.DMP拷贝到目标库.
sftp> lcd C:\Users\Administrator\Desktop\file
sftp> cd /home/grid
sftp> get APP1TBS.266.1115930901
sftp> get APP2TBS.267.1115930917
sftp> get IDXTBS.268.1115930935
sftp> cd /u01/app/oracle/admin/orclasm/dpdump
sftp> get expdat.dmp
说明：至此源库操作结束.
3、目标库恢复
3.1、上传文件
查看目标库数据文件位置和导入目录.
> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
> set line 9999
> col directory_name for a25
> col directory_path for a70
> select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
------------------------- ------------------------------------------------------
XMLDIR c:\ade\aime_dadvfh0169\oracle\rdbms\xml
DATA_PUMP_DIR D:\app\Administrator\admin\orcl\dpdump\
ORACLE_OCM_CONFIG_DIR D:\app\Administrator\product\11.2.0\dbhome_1\ccr\state
将表空间文件拷贝到D:\APP\ADMINISTRATOR\ORADATA\ORCL\目录下，expdat.dmp文件拷贝到D:\app\Administrator\admin\orcl\dpdump\
3.2、impdp
3.2.1、parfile文件
编写parfile文件par.f，内容如下:
DUMPFILE=expdat.dmp
DIRECTORY=DATA_PUMP_DIR
TRANSPORT_DATAFILES=
D:\app\Administrator\oradata\orcl\APP1TBS.266.1115930901,
D:\app\Administrator\oradata\orcl\APP2TBS.267.1115930917,
D:\app\Administrator\oradata\orcl\IDXTBS.268.1115930935
LOGFILE=tts_import.log
说明：par.f文件属性需为.bat类型.
3.2.2、开始导入
C:\Users\Administrator>impdp system/oracle_4U parfile='D:\app\Administrator\admin\orcl\dpdump\par.f'
Import: Release 11.2.0.1.0 - Production on 星期二 9月 20 23:36:12 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile='D:\app\Administrator\admin\orcl\dpdump\par.f'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 23:36:15 成功完成
3.2.3、导入后处理
查看目标平台信息.
> col tablespace_name for a20
> select tablespace_name,status from dba_tablespaces
TABLESPACE_NAME STATUS
-------------------- ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
APP1TBS READ ONLY
APP2TBS READ ONLY
IDXTBS READ ONLY
已选择8行.
4、修改文件名
修改表空间对应的文件名.
SQL> select a.name tablespace_name,b.name from v$tablespace a,v$datafile b where a.ts#=b.ts#
TABLESPACE_NAME NAME
-------------------- -----------------------------------------------------------------------
SYSTEM D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
SYSAUX D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
UNDOTBS1 D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
USERS D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
APP1TBS D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP1TBS.266.1115930901
APP2TBS D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP2TBS.267.1115930917
IDXTBS D:\APP\ADMINISTRATOR\ORADATA\ORCL\IDXTBS.268.1115930935
已选择7行.
> create directory asmsrc as 'D:\APP\ADMINISTRATOR\ORADATA\ORCL';
> begin
dbms_file_transfer.copy_file('ASMSRC','APP1TBS.266.1115930901','ASMSRC','APP1TBS.DBF');
dbms_file_transfer.copy_file('ASMSRC','APP2TBS.267.1115930917','ASMSRC','APP2TBS.DBF');
dbms_file_transfer.copy_file('ASMSRC','IDXTBS.268.1115930935','ASMSRC','IDXTBS.DBF');
end;
/
PL/SQL 过程已成功完成.
> alter database rename file 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP1TBS.266.1115930901' to 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP1TBS.DBF';
> alter database rename file 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP2TBS.267.1115930917' to 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP2TBS.DBF';
> alter database rename file 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\IDXTBS.268.1115930935' to 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\IDXTBS.DBF';
> alter tablespace app1tbs online;
> alter tablespace app2tbs online;
> alter tablespace idxtbs online;
5、数据确认
> select a.name tablespace_name,b.name from v$tablespace a,v$datafile b where a.ts#=b.ts#;
TABLESPACE_NAME NAME
-------------------- --------------------------------------------------------------------------------
SYSTEM D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
SYSAUX D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
UNDOTBS1 D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
USERS D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
APP1TBS D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP1TBS.DBF
APP2TBS D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP2TBS.DBF
IDXTBS D:\APP\ADMINISTRATOR\ORADATA\ORCL\IDXTBS.DBF
已选择7行.
> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
APP1TBS ONLINE
APP2TBS ONLINE
IDXTBS ONLINE
已选择8行.
参考网址：http://blog.itpub.net/26736162/viewspace-1375260/
说明：文档基本根据参考网址完成测试，其中表空间以及表名等均未做修改.
运用传输表空间完成oracle异构平台迁移(二)
原创
©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任
提问和评论都可以，用心的回复会被更多人看到
评论
发布评论
相关文章
-
运用传输表空间完成oracle异构平台迁移(一)
运用传输表空间完成oracle异构平台迁移
oracle 表空间 对象类型 -
Oracle在不同平台间表空间迁移
平台环境： 1、源库: Windows Service 2003 SP2 x86 Oracl
oracle 平台 dictionary system database -
Innodb 表空间传输迁移大表
innodb表的迁移就显得很
表空间传输 mysql svn 表空间 -
oracle 可传输表空间
可传输表空间的导出使用transport_tablespacescanshu
oracke 传输表空间 表空间 xml sed
