oracle tts数据,Oracle 19c expdp 参数TTS_CLOSURE_CHECK估算传输表空间（TTS）时间

1 TTS_CLOSURE_CHECK 参数说明

在之前的几篇博客我们分别演示了不同情况下的传输表空间使用，如下：https://www.cndba.cn/dave/article/4397

Oracle TTS异构平台传输表空间 [单实例(11.2.0.4) —> RAC(18.4) PDB迁移] 操作示例

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

Oracle TTS异构平台传输表空间[RAC(18.4) PDB —> 单实例(11.2.0.4) 迁移] 操作示例

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

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

基本步骤都差不多：

1.将表空间设置为只读

2.expdp 导出表空间元数据

3.转换字节顺序，并将数据文件复制到目标端

4.impdp 导入表空间元数据https://www.cndba.cn/dave/article/4397https://www.cndba.cn/dave/article/4397

因为我们的测试环境数据量很小，所以expdp 执行的很快，但如果在生产环境，这个动作可能需要花费数小时，所以在导出导入的时候我们加直接排除统计信息：EXCLUDE=TABLE_STATISTICS, INDEX_STATISTICS。

在Oracle 19c中，Oracle Data Pump 引入了新特性，可以直接估算TTS expdp 的时间，在expdp时指定 TTS_CLOSURE_CHECK 参数等于TEST_MODE即可，当然这种方式产生的dump 文件标记为不可用，无法进入导入。

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

Test Mode只用来测试Transportable Tablespaces 和 Full Transportable 导出导入的时间，不需要将表空间设置为只读模式。

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

TTS_CLOSURE_CHECK 选项有如下选项：

TTS_CLOSURE_CHECK = [ ON | OFF | FULL | TEST_MODE ]

ON – Indicates that closure check be performed to ensure that the transportable tablespace set contains no references outside the set.

OFF – Indicates that the no closure check be performed. The user is responsible for verifying the transportable tablespace set containment.

FULL – Indicates that full multi-directional closure check be performed to ensure that the no remote references out of or into the transportable tablespace set.

TEST_MODE – Indicates that the tablespaces are not required to be in Read-Only mode. This option is for testing purposes only; to provide timing requirements of a transportable tablespace export operation. The resulting export dump file is not available for use by Data Pump Import.

注意事项：

1/ ON, OFF, and FULL are mutually exclusive. TEST_MODE is a Data Pump Export option only.

2/ Running a transportable operation with the TTS_CLOSURE_CHECK TEST_MODE option allows the tablespaces to remain read-write and provides the requested timing information. The resulting Data Pump Export dump file is not available for use by Data Pump Import.

3/ The time DataPump takes to conduct the closure check can be long, and at times unnecessary, especially when it is known that the transportable tablespace set is contained. Running a Data Pump transportable operation with the TTS_CLOSURE_CHECK OFF option will decrease the time required for DataPump transportable operations to complete. The main performance attribute of this feature is to by-pass a time consuming step for a transportable operation.

4/ Skipping the closure check should decrease the time for the transportable export operation to complete which increase availability. The ability to obtain timing requirements for the transportable export operation with the tablespaces in read-write mode also increases availability.

5/ The TTS_CLOSURE_CHECK parameter value can be set using the existing procedure DBMS_DATAPUMP.SET_PARAMETER. The following disables all closure check and enables test mode:

SYS.DBMS_DATAPUMP.SET_PARAMETER(jobhdl, 'TTS_CLOSURE_CHECK', DBMS_DATAPUMP.KU$_TTS_CLOSURE_CHECK_OFF+DBMS_DATAPUMP.KU$_TTS_CLOSURE_CHECK_TEST);

2 TTS_CLOSURE_CHECK 使用示例

test_mode模式不需要将表空间设置为只读，所以我们直接运行测试。

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

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

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 11:01:49 2021

Version 19.10.0.0.0

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

Last Successful login time: Tue Feb 02 2021 00:06:26 +08:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.10.0.0.0

SQL> show con_name

CON_NAME

------------------------------

DAVE

SQL> col status for a15

SQL> col tablespace_name for a20

SQL> select status,tablespace_name from dba_tablespaces;

STATUS TABLESPACE_NAME

--------------- --------------------

ONLINE SYSTEM

ONLINE SYSAUX

ONLINE UNDOTBS1

ONLINE TEMP

ONLINE USERS

ONLINE DAVE

6 rows selected.

SQL>

SQL> create directory dpump_dir as '/tmp';

Directory created.

SQL> grant read,write on directory dpump_dir to system;

Grant succeeded.

[dave@www.cndba.cn ~]$ expdp system/dave@www.cndba.cn directory=dpump_dir dumpfile=dave_tts.dmp transport_tablespaces=DAVE TTS_CLOSURE_CHECK=test_mode

Export: Release 19.0.0.0.0 - Production on Sat Feb 6 11:04:15 2021

Version 19.10.0.0.0

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

UDE-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 2 days

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@dave directory=dpump_dir dumpfile=dave_tts.dmp transport_tablespaces=DAVE TTS_CLOSURE_CHECK=test_mode

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

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/dave_tts.dmp

Dump file set is unusable. TEST_MODE requested.

******************************************************************************

Datafiles required for transportable tablespace DAVE:

/u01/app/oracle/oradata/CNDBA/dave/dave01.dbf

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sat Feb 6 11:04:28 2021 elapsed 0 00:00:10

[dave@www.cndba.cn ~]$

这里要注意2点：

1.dump 文件的状态：

Dump file set is unusable. TEST_MODE requested.

2.expdp 的执行时间。https://www.cndba.cn/dave/article/4397

参考：

Trasnportable Tablespaces TTS_CLOSURE_CHECK Options Clarifications (Doc ID 2744424.1)

19c DataPump New Features (Doc ID 2457955.1)

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