传输表空间的实质是,抽取表空间元数据传输 + 表空间下数据文件传输
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.