运用传输表空间完成oracle异构平台迁移

文档课题:运用传输表空间完成oracle异构平台迁移.
1、环境介绍
源平台:windows 2008 server r2 64位 + oracle 11.2.0.1.0 64位 + ORACLE_SID=orcl
目标平台:rhel6.5 64位 + oracle 11.2.0.1.0 64位 + asm + ORACLE_SID=orclasm
测试目标:将自定义的应用程序表空间app1tbs,app2tbs,idxtbs从源平台迁移到目标平台.
2、环境准备
2.1 创建表空间
在源库上创建3个表空间.
> select * from v$version

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

> 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
> create tablespace app1tbs datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP1TBS.DBF' size 10m;
> create tablespace app2tbs datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP2TBS.DBF' size 10m;
> create tablespace idxtbs datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\IDXTBS.DBF' size 10m;
> select tablespace_name,autoextensible,bytes/1024/1024,file_name from dba_data_files

TABLESPACE_NAME AUTOEX BYTES/1024/1024 FILE_NAME
--------------- ------ --------------- ------------------------------------------------------------
USERS YES 5 D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
UNDOTBS1 YES 70 D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
SYSAUX YES 490 D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
SYSTEM YES 680 D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
APP1TBS NO 10 D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP1TBS.DBF
APP2TBS NO 10 D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP2TBS.DBF
IDXTBS NO 10 D:\APP\ADMINISTRATOR\ORADATA\ORCL\IDXTBS.DBF

已选择7行.
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_ename 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
---------------------------------------- ----------------------------
Microsoft Windows x86 64-bit Little
说明:当前系统平台支持跨平台表空间传输(因以上查询有记录返回).
目标平台查询
> 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
说明:当前系统平台支持跨平台表空间传输,且源平台和目标平台的Endian_format 相同(均为Little),不需要进行表空间集转换.
2.4、自包含
传输表空间前需先确认是否自包含,假设此处迁移app1tbs和idxtbs表空间.
> execute sys.dbms_tts.transport_set_check('app1tbs,idxtbs',true);
> select * from sys.transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
ORA-39907: 索引 SCOTT.IDX_DEPT_ENAME (在表空间 IDXTBS 中) 指向表 SCOTT.APP2_TAB
(在表空间 APP2TBS 中).
说明: 在idxtbs表空间中IDX_DEPT_DNAME索引指向了表空间集外的scott.APP2_TAB表,所以需选择app1tabs,app2tabs,idxtbs作为新的表空间集再次进行检查.
> execute sys.dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);
> select * from transport_set_violations;

未选定行
说明:此时该表空间集已不在违反自包含条件,可确定为一个可传输表空间集.
2.5、创建传输表空间集
2.5.1、设置只读
将自包含表空间集中的所有表空间设置为只读.
> alter tablespace app1tbs read only;
> alter tablespace app2tbs read only;
> alter tablespace idxtbs read only;
2.5.2、expdp
使用数据泵导出工具,导出要传输的各个表空间元数据.
SQL> 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
开始导出.
C:\Users\Administrator>expdp system/oracle_4U@orcl dumpfile=expdat.dmp directory=data_pump_dir transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log

Export: Release 11.2.0.1.0 - Production on 星期六 9月 17 10:26:42 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_EXPORT_TRANSPORTABLE_01": system/********@orcl dumpfile=expdat.dmp directory=data_pump_dir transport
_tablespaces=app1tbs,app2tbs,idxtbs logfile=tts_export.log
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
D:\APP\ADMINISTRATOR\ADMIN\ORCL\DPDUMP\EXPDAT.DMP
******************************************************************************
可传输表空间 APP1TBS 所需的数据文件:
D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP1TBS.DBF
可传输表空间 APP2TBS 所需的数据文件:
D:\APP\ADMINISTRATOR\ORADATA\ORCL\APP2TBS.DBF
可传输表空间 IDXTBS 所需的数据文件:
D:\APP\ADMINISTRATOR\ORADATA\ORCL\IDXTBS.DBF
作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 10:26:58 成功完成
2.6、恢复源库表空间
恢复源库表空间为读写状态.
> alter tablespace app1tbs read write;
> alter tablespace app2tbs read write;
> alter tablespace idxtbs read write;
2.7、传输文件
将D:\APP\ADMINISTRATOR\ORADATA\ORCL\目录下的转储元文件APP1TBS.DBF、APP2TBS.DBF、IDXTBS.DBF以及D:\APP\ADMINISTRATOR\ADMIN\ORCL\DPDUMP\目录下的dmp文件EXPDAT.DMP拷贝到目标库.
说明:至此源库操作结束.
3、目标库恢复
3.1、文件位置
查看目标库数据文件位置和导入目录.
> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/orclasm/datafile/system.256.1115660791
+DATA/orclasm/datafile/sysaux.257.1115660791
+DATA/orclasm/datafile/undotbs1.258.1115660791
+DATA/orclasm/datafile/users.259.1115660791
> select directory_name,directory_path from dba_directories

DIRECTORY_NAME DIRECTORY_PATH
------------------------------ --------------------------------------------------
XMLDIR /ade/b/2125410156/oracle/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
3.2、上传文件
sftp> cd /u01/app/oracle/admin/orclasm/dpdump
sftp> lcd C:\Users\Administrator\Desktop\dumpfile
sftp> put *
[root@liujun dpdump]# ll
total 30864
-rw-r--r-- 1 root root 10493952 Sep 17 10:23 APP1TBS.DBF
-rw-r--r-- 1 root root 10493952 Sep 17 10:23 APP2TBS.DBF
-rw-r----- 1 oracle oinstall 116 Sep 17 09:44 dp.log
-rw-r--r-- 1 root root 114688 Sep 17 10:26 EXPDAT.DMP
-rw-r--r-- 1 root root 10493952 Sep 17 10:23 IDXTBS.DBF
-rw-r--r-- 1 root root 1567 Sep 17 10:26 tts_export.log
3.3、属性修改
修改文件属性.
[root@liujun dpdump]# chown -R oracle:oinstall APP1TBS.DBF
[root@liujun dpdump]# chown -R oracle:oinstall APP2TBS.DBF
[root@liujun dpdump]# chown -R oracle:oinstall IDXTBS.DBF
[root@liujun dpdump]# chown -R oracle:oinstall EXPDAT.DMP
[root@liujun dpdump]# ll
total 30860
-rw-r--r-- 1 oracle oinstall 10493952 Sep 17 10:23 APP1TBS.DBF
-rw-r--r-- 1 oracle oinstall 10493952 Sep 17 10:23 APP2TBS.DBF
-rw-r--r-- 1 oracle oinstall 114688 Sep 17 10:26 EXPDAT.DMP
-rw-r--r-- 1 oracle oinstall 10493952 Sep 17 10:23 IDXTBS.DBF
-rw-r--r-- 1 root root 1567 Sep 17 10:26 tts_export.log
3.4、impdp
3.4.1、parfile文件
编写parfile文件,内容如下:
[root@liujun dpdump]# vi par.f
[root@liujun dpdump]# cat par.f
DUMPFILE=EXPDAT.DMP
DIRECTORY=DATA_PUMP_DIR
TRANSPORT_DATAFILES=
/u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF,
/u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF,
/u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF
LOGFILE=tts_import.log
3.4.2、开始导入
[oracle@liujun ~]$ impdp system/oracle_4U parfile='/u01/app/oracle/admin/orclasm/dpdump/par.f'

Import: Release 11.2.0.1.0 - Production on Sat Sep 17 18:00:17 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=/u01/app/oracle/admin/orclasm/dpdump/par.f
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 18:00:20
3.4.3、导入后处理
查看目标平台信息
[oracle@liujun ~]$ sqlplus / as sysdba
SQL> 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 rows selected.

> alter tablespace app1tbs read write;
> alter tablespace app2tbs read write;
> alter tablespace idxtbs read write;
> select d.owner,d.index_name,d.tablespace_name from dba_indexes d where d.table_name in ('APP1_TAB','APP2_TAB');

OWNER INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT IDX_EMP_ENAME IDXTBS
SCOTT IDX_DEPT_ENAME IDXTBS

7 rows selected.
4、修改文件路径
修改表空间对应的文件路径
> select a.name tablespace_name,b.name from v$tablespace a,v$datafile b where a.ts#=b.ts#;

TABLESPACE_NAME NAME
--------------- --------------------------------------------------
SYSTEM +DATA/orclasm/datafile/system.256.1115660791
SYSAUX +DATA/orclasm/datafile/sysaux.257.1115660791
UNDOTBS1 +DATA/orclasm/datafile/undotbs1.258.1115660791
USERS +DATA/orclasm/datafile/users.259.1115660791
APP1TBS /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF
APP2TBS /u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF
IDXTBS /u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF

> archive log list;
Database log mode No Archive Mode
> shutdown immediate;
> startup mount;
$ rman target /
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** +DATA/orclasm/datafile/system.256.1115660791
2 480 SYSAUX *** +DATA/orclasm/datafile/sysaux.257.1115660791
3 75 UNDOTBS1 *** +DATA/orclasm/datafile/undotbs1.258.1115660791
4 5 USERS *** +DATA/orclasm/datafile/users.259.1115660791
5 10 APP1TBS *** /u01/app/oracle/admin/orclasm/dpdump/APP1TBS.DBF
6 10 APP2TBS *** /u01/app/oracle/admin/orclasm/dpdump/APP2TBS.DBF
7 10 IDXTBS *** /u01/app/oracle/admin/orclasm/dpdump/IDXTBS.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 +DATA/orclasm/tempfile/temp.264.1115660849
RMAN> backup as copy datafile 5 format '+DATA';
RMAN> backup as copy datafile 6 format '+DATA';
RMAN> backup as copy datafile 7 format '+DATA';
RMAN> switch tablespace app1tbs to copy;

datafile 5 switched to datafile copy "+DATA/orclasm/datafile/app1tbs.266.1115662357"

RMAN> switch tablespace app2tbs to copy;
RMAN> switch tablespace idxtbs to copy;
RMAN> alter database open;
查看结果
RMAN> report schema;

Report of database schema for database with db_unique_name ORCLASM

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** +DATA/orclasm/datafile/system.256.1115660791
2 480 SYSAUX *** +DATA/orclasm/datafile/sysaux.257.1115660791
3 75 UNDOTBS1 *** +DATA/orclasm/datafile/undotbs1.258.1115660791
4 5 USERS *** +DATA/orclasm/datafile/users.259.1115660791
5 10 APP1TBS *** +DATA/orclasm/datafile/app1tbs.266.1115662357
6 10 APP2TBS *** +DATA/orclasm/datafile/app2tbs.267.1115662381
7 10 IDXTBS *** +DATA/orclasm/datafile/idxtbs.268.1115662397

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 +DATA/orclasm/tempfile/temp.264.1115660849

说明:文档基本根据参考网址完成测试,其中表空间以及表名等均未做修改.