学堂 学堂 学堂公众号手机端
首页 技术正文

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

lewis 5年前 (2020-12-10) 阅读数 6 #技术 
文档课题：运用传输表空间完成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

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

版权声明

本文仅代表作者观点，不代表博信信息网立场。

上一篇：如何解决centos不能ping域名 下一篇：NameSilo：注册局[Verisign]宣布2021年9月1日起.com域名涨价,此前按现价续费最多十年
相关文章
lewis

lewis

作者文章
浏览更多
热门
最新文章
标签列表
Copyright boxinxx.com.Some Rights Reserved.