ORACLE-TTS

最新推荐文章于 2022-01-15 19:25:12 发布

huangliang0703

最新推荐文章于 2022-01-15 19:25:12 发布

阅读量362

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/huangliang0703/article/details/118567321

版权

oracle 专栏收录该内容

82 篇文章 0 订阅

订阅专栏

传输表空间的实质是，抽取表空间元数据传输 + 表空间下数据文件传输

1、查看操作系统endianness格式

SQL> col platform_name for a40  
SQL> SELECT *   
  2  FROM V$TRANSPORTABLE_PLATFORM   
  3  ORDER BY PLATFORM_ID;  

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT      CON_ID
----------- ---------------------------------------- -------------- ----------
          1 Solaris[tm] OE (32-bit)                  Big                     0
          2 Solaris[tm] OE (64-bit)                  Big                     0
          3 HP-UX (64-bit)                           Big                     0
          4 HP-UX IA (64-bit)                        Big                     0
          5 HP Tru64 UNIX                            Little                  0
          6 AIX-Based Systems (64-bit)               Big                     0
          7 Microsoft Windows IA (32-bit)            Little                  0
          8 Microsoft Windows IA (64-bit)            Little                  0
          9 IBM zSeries Based Linux                  Big                     0
         10 Linux IA (32-bit)                        Little                  0
         11 Linux IA (64-bit)                        Little                  0

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT      CON_ID
----------- ---------------------------------------- -------------- ----------
         12 Microsoft Windows x86 64-bit             Little                  0
         13 Linux x86 64-bit                         Little                  0
         15 HP Open VMS                              Little                  0
         16 Apple Mac OS                             Big                     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-64)        Little                  0
         21 Apple Mac OS (x86-64)                    Little                  0
         22 Linux OS (S64)                           Big                     0


--分别查看 source 端 和target端操作系统endianness格式 

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

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

--target

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

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

2、选择要传输的表空间（huang）

SQL> select tablespace_name,  
  2      status   
  3  from dba_tablespaces;  

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
HUANG                          ONLINE

3、检查表空间自包含（就是改表空间里的数据没有和其他表空间数据有关联，如果有关联会报错）

SQL> execute dbms_tts.transport_set_check('HUANG', TRUE); 

PL/SQL procedure successfully completed.
--查看自包含验证结果： 

SQL>  select * from transport_set_violations;  

no rows selected
--没有结果说明表空间自包含

4、将表空间TSET设置成read-only

SQL> alter tablespace huang read only; 

Tablespace altered.

SQL> select tablespace_name,      status   from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
HUANG                          READ ONLY

6 rows selected.

5、生成：Transportable Tablespace Set

Transportable Tablespace Set有两部分：

1.expdp 导出的表空间的metadata

2.还有就是表空间对应的数据文件

[oracle@localhost ~]$ expdp \'/ as sysdba\' dumpfile=huang.dmp  transport_tablespaces=huang transport_full_check=y logfile=huang.log  

Export: Release 19.0.0.0.0 - Production on Wed Jul 7 22:21:56 2021
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" dumpfile=huang.dmp transport_tablespaces=huang transport_full_check=y logfile=huang.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
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/app/oracle/admin/orcl19/dpdump/huang.dmp
******************************************************************************
Datafiles required for transportable tablespace HUANG:
  /u01/app/oracle/oradata/ORCL19/datafile/o1_mf_huang_jgdp0pz2_.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Jul 7 22:22:14 2021 elapsed 0 00:00:12

6、将Transportable Tablespace Set 传到目标端

[oracle@localhost ORCL]$ ll
total 3532580
-rw-r-----. 1 oracle oinstall   10600448 Jul  7 22:27 control01.ctl
-rw-r-----. 1 oracle oinstall   10600448 Jul  7 22:27 control02.ctl
-rw-r-----  1 oracle oinstall 1073750016 Jul  7 22:25 huang.dbf
-rw-r-----. 1 oracle oinstall  209715712 Jul  7 21:30 redo01.log
-rw-r-----. 1 oracle oinstall  209715712 Jul  7 21:30 redo02.log
-rw-r-----. 1 oracle oinstall  209715712 Jul  7 22:27 redo03.log
-rw-r-----. 1 oracle oinstall  576724992 Jul  7 22:26 sysaux01.dbf
-rw-r-----. 1 oracle oinstall  954212352 Jul  7 22:21 system01.dbf
-rw-r-----. 1 oracle oinstall   33562624 Jul  7 22:00 temp01.dbf
-rw-r-----. 1 oracle oinstall  351281152 Jul  7 22:26 undotbs01.dbf
-rw-r-----. 1 oracle oinstall    5251072 Jul  7 21:30 users01.dbf

7、在Target 系统上Import 表空间的metadata

[oracle@localhost ~]$ impdp \'/ as sysdba\' dumpfile=huang.dmp  

Import: Release 19.0.0.0.0 - Production on Wed Jul 7 22:31:39 2021
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39061: import mode FULL conflicts with export mode TRANSPORTABLE


[oracle@localhost ~]$ impdp \'/ as sysdba\' dumpfile=huang.dmp  transport_datafiles=/u01/app/oracle/oradata/ORCL/huang.dbf

Import: Release 19.0.0.0.0 - Production on Wed Jul 7 22:41:50 2021
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" dumpfile=huang.dmp transport_datafiles=/u01/app/oracle/oradata/ORCL/huang.dbf 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Jul 7 22:41:57 2021 elapsed 0 00:00:02

8、查看并修改表空间状态（源、目端）

SQL> select tablespace_name,  
  2      status   
  3  from dba_tablespaces;  

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
HUANG                          READ ONLY

6 rows selected.

SQL> alter tablespace HUANG read write;

Tablespace altered.

huangliang0703

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
ORACLE-TTS

传输表空间的实质是，抽取表空间元数据传输 + 表空间下数据文件传输1、查看操作系统endianness格式SQL> col platform_name for a40 SQL> SELECT * 2 FROM V$TRANSPORTABLE_PLATFORM 3 ORDER BY PLATFORM_ID; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
复制链接

扫一扫