传输表空间

最新推荐文章于 2019-12-04 13:34:27 发布

朝闻道-夕死可矣

最新推荐文章于 2019-12-04 13:34:27 发布

阅读量716

点赞数

分类专栏： -- Ora12c&13c ORACLE

本文链接：https://blog.csdn.net/jc_benben/article/details/78647626

版权

ORACLE 同时被 2 个专栏收录

79 篇文章 0 订阅

订阅专栏

-- Ora12c&13c

15 篇文章 0 订阅

订阅专栏

用途：数据迁移
演示场景：
原库：linux x86_64 oracle 11.2.0.4

目标：linux x86_64 oracle 12.2.0.1

过程

0, 创建试验环境

SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/PROD/tbs1.dbf' size 10m autoextend on maxsize 100M;

Tablespace created.

SQL> create user u1 identified by u1 default tablespace tbs1;

User created.

SQL> grant connect,resource to u1;

Grant succeeded.

SQL> conn u1/u1;
Connected.
SQL> create table t1(id int,name varchar2(20));

Table created.

SQL> insert into t1 (id,name) values(1,'china');

1 row created.

SQL> commit;

Commit complete.

1，检查原库，目标库支持的平台、本地平台类型和端序

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 t2.PLATFORM_NAME,ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM t1,V$DATABASE t2 WHERE t1.PLATFORM_NAME = t2.PLATFORM_NAME;

PLATFORM_NAME                            ENDIAN_FORMAT
---------------------------------------- --------------
Linux x86 64-bit                         Little

目标端：

SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT      CON_ID
----------- ------------------------------ -------------- ----------
          1 Solaris[tm] OE (32-bit)        Big                     0
          2 Solaris[tm] OE (64-bit)        Big                     0
          7 Microsoft Windows IA (32-bit)  Little                  0
         10 Linux IA (32-bit)              Little                  0
          6 AIX-Based Systems (64-bit)     Big                     0
          3 HP-UX (64-bit)                 Big                     0
          5 HP Tru64 UNIX                  Little                  0
          4 HP-UX IA (64-bit)              Big                     0
         11 Linux IA (64-bit)              Little                  0
         15 HP Open VMS                    Little                  0
          8 Microsoft Windows IA (64-bit)  Little                  0
          9 IBM zSeries Based Linux        Big                     0
         13 Linux x86 64-bit               Little                  0
         16 Apple Mac OS                   Big                     0
         12 Microsoft Windows x86 64-bit   Little                  0
         17 Solaris Operating System (x86) Little                  0
         18 IBM Power Based Linux          Big                     0
         19 HP IA Open VMS                 Little                  0
         20 Solaris Operating System (x86- Little                  0
            64)

         21 Apple Mac OS (x86-64)          Little                  0

20 rows selected.

SQL>  SELECT t2.PLATFORM_NAME,ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM t1,V$DATABASE t2 WHERE t1.PLATFORM_NAME = t2.PLATFORM_NAME;

PLATFORM_NAME                  ENDIAN_FORMAT
------------------------------ --------------
Linux x86 64-bit               Little

2，检查是否满足条件，如果满足导出元数据

另外需要确定的是，字符集

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs1', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> alter tablespace tbs1 read only;

Tablespace altered.

-- 导出元数据

[oracle@prod ~]$ expdp \'/ as sysdba \' directory=DMP_DIR dumpfile=metaexpdat.dmp transport_tablespaces=tbs1

Export: Release 11.2.0.4.0 - Production on Mon Nov 27 16:54:34 2017

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 "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" directory=DMP_DIR dumpfile=metaexpdat.dmp transport_tablespaces=tbs1 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/bak/metaexpdat.dmp
******************************************************************************
Datafiles required for transportable tablespace TBS1:
  /u01/app/oracle/oradata/PROD/tbs1.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Nov 27 16:55:10 2017 elapsed 0 00:00:35

将这两个文件'/u01/bak/metaexpdat.dmp','/u01/app/oracle/oradata/PROD/tbs1.dbf',传输到目标端指定路径,如果大的话可以压缩下
这里也可以使用exp导出元数据，比如：
exp userid=\’/ as sysdba\’ tablespaces=tbs1 file=/tmp/tbs1.dmp transport_tablespace=y
相应导入imp userid=\’/ as sysdba\’ tablespaces=tbs1 file=/tmp/tbs1.dmp transport_tablespace=y datafiles=XXX/tbs01.dbf, xxx/tbs02.dbf fromuser=u1 touser=u2

3，转换源端数据文件格式匹配目标端

（此步骤不用，因为第一步骤中检查源目标一致）

如不一致，比如源端是'Microsoft Windows x86 64-bit',在目标端进入RMAN，进行转换
RMAN> convert datafile '/home/oracle/transport_tbs/tbs1.dbf' FROM PLATFORM 'Microsoft Windows x86 64-bit' FORMAT '/u01/app/oracle/oradata/cdb1/pdb1/bak/tbs1.dbf';

4，导入元数据和数据文件

4.1创建待导入的用户

SQL> create user u2 identified by u2 ;

User created.

SQL> grant connect,resource to u2;

Grant succeeded.

4.2，导入元数据和数据文件

如果多个文件逗号隔开即可

[oracle@centos7 ~]$ impdp system/oracle@pdb2 directory=dp_dir dumpfile=metaexpdat.dmp datafiles='/u01/app/oracle/oradata/cdb1/tbs1.dbf' remap_schema=u1:u2

Import: Release 12.2.0.1.0 - Production on Mon Nov 27 17:53:53 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "datafiles=/u01/app/oracle/oradata/cdb1/tbs1.dbf" Location: Command Line, Replaced with: "transport_datafiles=/u01/app/oracle/oradata/cdb1/tbs1.dbf"
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@pdb2 directory=dp_dir dumpfile=metaexpdat.dmp transport_datafiles=/u01/app/oracle/oradata/cdb1/tbs1.dbf remap_schema=u1:u2 
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 Mon Nov 27 17:54:05 2017 elapsed 0 00:00:09

5，查看结果

SQL> conn u2/u2@pdb2
Connected.
SQL> select * from t1;

        ID NAME
---------- --------------------
         1 china
SQL> insert into t1 values(2,'USA');
insert into t1 values(2,'USA')
            *
ERROR at line 1:
ORA-00372: file 17 cannot be modified at this time
ORA-01110: data file 17: '/u01/app/oracle/oradata/cdb1/tbs1.dbf'


SQL> conn system/oracle@pdb2
Connected.
SQL> alter tablespace tbs1 read write;

Tablespace altered.

SQL> conn u2/u2@pdb2
Connected.
SQL> insert into t1 values(2,'USA');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 china
         2 USA

朝闻道-夕死可矣

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
传输表空间

用途：数据迁移演示场景：原库：linux x86_64 oracle 11.2.0.4目标：linux x86_64 oracle 12.2.0.1过程0, 创建试验环境SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/PROD/tbs1.dbf' size 10m autoextend on m
复制链接

扫一扫