签到成功

知道了

CNDBA社区CNDBA社区

Oracle 11g 单实例 TTS(异构平台传输表空间) 操作示例

2021-02-05 15:10 165 0 原创 Oracle 19c
作者: Dave

本片我们演示Oracle 11.2.0.4 的单实例,从Linux 平台使用传输表空间(TTS)迁移到 Windows 平台的步骤。 迁移进行之前,目标端的Windows 实例要存在,字符集也要保持一致,可以理解成一个空库,通过TTS 直接把表空间附加上去。

TTS在使用时是有一些限制条件的,比如IOT对象,系统表空间和SYS对象等,具体参考我之前的博客:

Oracle 传输表空间(TTS) 的限制与局限性
https://www.cndba.cn/dave/article/4390

1 准备源库测试环境

[dave@www.cndba.cn ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 5 12:04:20 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,dbid from v$database;

NAME                     DBID
------------------ ----------
cndba               1591449381

SQL> col file_name for a50
SQL> set lines 120
SQL> col tablespace_name for a20
SQL> select file_id,file_name,tablespace_name from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         4 /u01/app/oracle/oradata/cndba/users01.dbf           USERS
         3 /u01/app/oracle/oradata/cndba/undotbs01.dbf         UNDOTBS1
         2 /u01/app/oracle/oradata/cndba/sysaux01.dbf          SYSAUX
         1 /u01/app/oracle/oradata/cndba/system01.dbf          SYSTEM
         5 /u01/app/oracle/oradata/cndba/example01.dbf         EXAMPLE

创建测试表空间:

SQL> create tablespace cndba datafile '/u01/app/oracle/oradata/cndba/dave01.dbf' size 20M,'/u01/app/oracle/oradata/cndba/dave02.dbf' size 20M;

Tablespace created.

SQL> select file_id,file_name,tablespace_name from dba_data_files;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         4 /u01/app/oracle/oradata/cndba/users01.dbf           USERS
         3 /u01/app/oracle/oradata/cndba/undotbs01.dbf         UNDOTBS1
         2 /u01/app/oracle/oradata/cndba/sysaux01.dbf          SYSAUX
         1 /u01/app/oracle/oradata/cndba/system01.dbf          SYSTEM
         5 /u01/app/oracle/oradata/cndba/example01.dbf         EXAMPLE
         6 /u01/app/oracle/oradata/cndba/dave01.dbf            CNDBA
         7 /u01/app/oracle/oradata/cndba/dave02.dbf            CNDBA

7 rows selected.
SQL> create user cndba identified by cndba default tablespace cndba;

User created.

SQL> grant connect,resource,dba to cndba;

Grant succeeded.

SQL> conn cndba/cndba
Connected.

SQL> create table dave tablespace cndba as select * from dba_objects;

Table created.

SQL> insert into dave select * from dba_objects;

86961 rows created.

SQL> commit;

Commit complete.

2 查看平台 endian format (byte ordering)和字符集

SQL> set pages 200
SQL> COLUMN PLATFORM_NAME FORMAT A40
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
          9 IBM zSeries Based Linux                  Big
         13 Linux x86 64-bit                         Little
         16 Apple Mac OS                             Big
         12 Microsoft Windows x86 64-bit             Little
         17 Solaris Operating System (x86)           Little
         18 IBM Power Based Linux                    Big
         19 HP IA Open VMS                           Little
         20 Solaris Operating System (x86-64)        Little
         21 Apple Mac OS (x86-64)                    Little

20 rows selected.

--也可以直接查询当前平台:
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 x86 64-bit                         Little


SQL> select userenv('LANGUAGE') FROM DUAL;

USERENV('LANGUAGE')
--------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

Linux 和Windows 都是Little,Unix 都是Big。

3 检查表空间自包含

在执行传输之前必须确保这一步通过,否则无法正常进行传输表空间。

https://www.cndba.cn/dave/article/4392

SQL>  execute sys.dbms_tts.transport_set_check('CNDBA,USERS', true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

我们这里只对CNDBA 这个表空间进行迁移,但是我们看到对USERS 也是可以进行的。

4 导出元数据

在执行导出之前,必须先将表空间设置为只读模式:

SQL> alter tablespace cndba read only;
Tablespace altered.

可以使用exp/imp 或者 expdp/impdp 进行导出元数据,我们这里选择expdp/impdp 进行:https://www.cndba.cn/dave/article/4392

[dave@www.cndba.cn ~]$ mkdir /tmp/cndba

SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/cndba' ;

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

Grant succeeded.

[dave@www.cndba.cn ~]$ expdp system/oracle DUMPFILE=cndba.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES= CNDBA TRANSPORT_FULL_CHECK=Y

Export: Release 11.2.0.4.0 - Production on Fri Feb 5 13:01:27 2021

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** DUMPFILE=cndba.dmp directory=dpump_dir transport_tablespaces= TRANSPORT_FULL_CHECK=Y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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:
  /tmp/cndba/cndba.dmp
******************************************************************************
Datafiles required for transportable tablespace CNDBA:
  /u01/app/oracle/oradata/cndba/dave01.dbf
  /u01/app/oracle/oradata/cndba/dave02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Feb 5 13:02:06 2021 elapsed 0 00:00:38

[dave@www.cndba.cn ~]$

5 在源端转换数据文件(RMAN)

这一步是从源端转换成目标端的ENDIAN_FORMAT,如果2端相同,那么可以省略这一步。 我们这里是从Linux 迁移到Windows,ENDIAN_FORMAT都是Little,转换操作如下。

https://www.cndba.cn/dave/article/4392

PS C:/WINDOWS/system32> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期五 2月 5 13:37:40 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
  2  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME
--------------------------------------------------------------------------------
ENDIAN_FORMAT
----------------------------
Microsoft Windows x86 64-bit
Little


SQL>

[dave@www.cndba.cn ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 5 13:41:26 2021

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

connected to target database: cndba (DBID=1591449381)

[dave@www.cndba.cn cndba]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 5 14:03:31 2021

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

connected to target database: cndba (DBID=1591449381)

RMAN> convert tablespace CNDBA to platform="Microsoft Windows x86 64-bit" FORMAT '/tmp/cndba/dave%s.dbf';

Starting conversion at source at 05-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=/u01/app/oracle/oradata/cndba/dave01.dbf
converted datafile=/tmp/cndba/dave5.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00007 name=/u01/app/oracle/oradata/cndba/dave02.dbf
converted datafile=/tmp/cndba/dave6.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 05-FEB-21

6 传输文件

将/tmp/cndba下的dump 文件和转换后的datafile 复制到目标端,我们这里复制到Windows 系统中。 这里要注意,因为我们这里都是文件系统的复制,不涉及ASM 磁盘组,所以数据库文件我们是直接复制到windows 实例对应的DATA 目录下,在impdp 之后,会直接使用这个路径。 https://www.cndba.cn/dave/article/4392https://www.cndba.cn/dave/article/4392https://www.cndba.cn/dave/article/4392

7 导入元数据

同样适用data pump 进行操作。

https://www.cndba.cn/dave/article/4392

SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS 'G:/tmp';

目录已创建。

SQL> GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

授权成功。

SQL>

导入之前要确保用户是存在的,否则就需要在impdp时使用REMAP_SCHEMA=(source:target)。
SQL> create user cndba identified by cndba;

用户已创建。

SQL> grant connect,resource,dba to cndba;

授权成功。

PS G:/tmp> impdp system/oracle DUMPFILE=cndba.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='D:/app/zhixin/oradata/cndba/dave5.dbf','D:/app/zhixin/oradata/cndba/dave6.dbf'

Import: Release 11.2.0.4.0 - Production on 星期五 2月 5 14:41:03 2021

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

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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/******** DUMPFILE=cndba.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES=D:/app/zhixin/oradata/cndba/dave5.dbf,D:/app/zhixin/oradata/cndba/dave6.dbf
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 星期五 2月 5 14:41:04 2021 elapsed 0 00:00:01 成功完成

8 将目标端表空间设置为读写并验证数据

SQL> alter tablespace cndba read write;

表空间已更改。

SQL> select file_id,file_name,tablespace_name from dba_data_files;

   FILE_ID FILE_NAME                                                              TABLESPACE_NAME
---------- ---------------------------------------------------------------------- ------------------------------------------------------------
         4 D:/APP/ZHIXIN/ORADATA/CNDBA/USERS01.DBF                                USERS
         3 D:/APP/ZHIXIN/ORADATA/CNDBA/UNDOTBS01.DBF                              UNDOTBS1
         2 D:/APP/ZHIXIN/ORADATA/CNDBA/SYSAUX01.DBF                               SYSAUX
         1 D:/APP/ZHIXIN/ORADATA/CNDBA/SYSTEM01.DBF                               SYSTEM
         5 D:/APP/ZHIXIN/ORADATA/CNDBA/EXAMPLE01.DBF                              EXAMPLE
         6 D:/APP/ZHIXIN/ORADATA/CNDBA/DAVE6.DBF                                  CNDBA
         7 D:/APP/ZHIXIN/ORADATA/CNDBA/DAVE5.DBF                                  CNDBA

已选择7行。

SQL> select count(1) from cndba.dave;

  COUNT(1)
----------
    173925

SQL>

9 注意事项(DBMS_FILE_TRANSFER 说明)

从Oracle 11.2.0.4 开始默认会进行DBMS_FILE_TRANSFER的操作。 即直接使用数据文件存放的位置,这也是我们为什么将数据文件直接放到windows 实例数据目录的原因。

https://www.cndba.cn/dave/article/4392

如果是低于11.2.0.4的版本,或者使用了ASM 的情况,那么在目标端进行impd 之前还是必须先使用DBMS_FILE_TRANSFER进行一个路径的转换。https://www.cndba.cn/dave/article/4392

如果是文件系统,语法如下:https://www.cndba.cn/dave/article/4392

RMAN> CONVERT DATAFILE
      '/path/tbs_31.f',
      '/path/tbs_32.f',
      '/path/tbs_41.f'
      TO PLATFORM="Solaris[tm] OE (32-bit)"
      FROM PLATFORM="HP TRu64 UNIX"
      DB_FILE_NAME_CONVERT= "/path_source/", "/path_dest/"
      PARALLELISM=5;

如果是+ASM 磁盘组,语法如下:

RMAN> CONVERT DATAFILE
      '/path/tbs_31.f',
      '/path/tbs_32.f',
      '/path/tbs_41.f'
      TO PLATFORM="Solaris[tm] OE (32-bit)"
      FROM PLATFORM="HP TRu64 UNIX"
      DB_FILE_NAME_CONVERT="/path_source/", "+diskgroup"
      PARALLELISM=5;

关于磁盘组的操作,我们在后面的操作再单独演示。

参考:

How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN (Doc ID 371556.1)
Information Center: Transportable Tablespaces (TTS) for Oracle Database (Doc ID 1461278.2)
10g+: Transportable Tablespaces Across Different Platforms (Doc ID 243304.1)

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1832
    原创
  • 2
    翻译
  • 417
    转载
  • 119
    评论
  • 访问:3164717次
  • 积分:2888
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群