签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c expdp 参数TTS_CLOSURE_CHECK估算传输表空间(TTS)时间

2021-02-06 11:07 172 0 原创 Oracle 19c
作者: Dave

1 TTS_CLOSURE_CHECK 参数说明

在之前的几篇博客我们分别演示了不同情况下的传输表空间使用,如下:

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

Oracle 11g 单实例 TTS(异构平台传输表空间) 操作示例
https://www.cndba.cn/dave/article/4392

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

基本步骤都差不多:

1.将表空间设置为只读
2.expdp 导出表空间元数据
3.转换字节顺序,并将数据文件复制到目标端
4.impdp 导入表空间元数据

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

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

Test Mode只用来测试Transportable Tablespaces 和 Full Transportable 导出导入的时间,不需要将表空间设置为只读模式。https://www.cndba.cn/dave/article/4397

https://www.cndba.cn/dave/article/4397
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/4397https://www.cndba.cn/dave/article/4397

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

参考:

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)

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

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

Dave

关注

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

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

        QQ交流群