impdp中的DISABLE_ARCHIVE_LOGGING参数测试

联系：手机/微信(+86 13429648788) QQ(107644445)

标题：impdp中的DISABLE_ARCHIVE_LOGGING参数测试

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在oracle 12c版本中引入了impdp中的TRANSFORM中的DISABLE_ARCHIVE_LOGGING值，可以实现在导入的时候使用nologging处理从而减少日志量也增加速度,但是在force logging情况下该参数无效
创建测试表

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb
 
SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 7 10:20:45 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> create table t_xifenfei as select * from dba_objects;
 
Table created.
 
SQL> insert into t_xifenfei select * from t_xifenfei;
 
217838 rows created.
 
SQL> /
 
435676 rows created.
 
SQL> /
 
871352 rows created.
 
SQL> /
 
1742704 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from t_xifenfei;
 
  COUNT(*)
----------
   3485408

导出测试表

[oracle@localhost ~]$ expdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei REUSE_DUMPFILES=yes
 
Export: Release 12.2.0.1.0 - Production on Fri Apr 7 11:55:01 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
Starting "XFF"."SYS_EXPORT_TABLE_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei REUSE_DUMPFILES=yes
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Master table "XFF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/xffdb/dpdump/4A93528C587D82CEE055000000000001/t_xifenfei.dmp
Job "XFF"."SYS_EXPORT_TABLE_01" successfully completed at Fri Apr 7 11:55:59 2017 elapsed 0 00:00:58

归档模式下不使用DISABLE_ARCHIVE_LOGGING导入

[oracle@localhost rdbms]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:43:23 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> select force_logging from v$database;
 
FORCE_LOGGING
---------------------------------------
NO
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16
 
[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 02:46:05 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
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 02:47:08 2017 elapsed 0 00:01:02
 
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:47:30 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19

这里可以看出来,导入过程使用时间为1分钟多,导入过程日志切换 了3次

归档模式下使用DISABLE_ARCHIVE_LOGGING导入

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:49:23 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Last Successful login time: Sat Apr 08 2017 02:46:05 -04:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> drop table t_xifenfei purge;
 
Table dropped.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:50:00 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20
 
 
[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 02:54:49 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
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 02:55:00 2017 elapsed 0 00:00:10
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:55:45 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

这里可以看出来当使用了DISABLE_ARCHIVE_LOGGING为Y之后导入日志没有发生切换,导入时间仅为10s.

非归档模式下不使用DISABLE_ARCHIVE_LOGGING导入

SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21
SQL> drop table xff.t_xifenfei purge;
 
Table dropped.
 
[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 03:22:42 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
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 03:23:17 2017 elapsed 0 00:00:27
 
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:23:49 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21

这里测试在非归档模式下不设置DISABLE_ARCHIVE_LOGGING，日志量增加不明显，导入时间变为为27秒.

非归档模式下使用DISABLE_ARCHIVE_LOGGING导入

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:24:10 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Last Successful login time: Sat Apr 08 2017 03:22:43 -04:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> drop table t_xifenfei purge;
 
Table dropped.
 
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Current log sequence           22
 
[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 03:25:51 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
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 03:26:01 2017 elapsed 0 00:00:10
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:26:37 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Current log sequence           22

这里可以看出来在非归档模式下使用DISABLE_ARCHIVE_LOGGING导入时间为10s,日志量也没有明显增加。

在force logging在非归档情况下使用不DISABLE_ARCHIVE_LOGGING参数

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:07:07 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
Last Successful login time: Sat Apr 08 2017 03:29:36 -04:00
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> drop table t_xifenfei purge;
 
Table dropped.
SQL> alter system switch logfile;
 
System altered.
 
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     22
Current log sequence           24
SQL> select force_logging from v$database;
 
FORCE_LOGGING
---------------------------------------
YES
 
[oracle@localhost ~]$ impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 10:10:39 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
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 10:11:02 2017 elapsed 0 00:00:21
 
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:11:17 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log lsit;
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     22
Current log sequence           24

这可以可以看出来在非归档情况下force logging无明显增加日志量和导入时间

在force logging在归档情况下使用DISABLE_ARCHIVE_LOGGING参数

SQL> drop table xff.t_xifenfei purge;
 
Table dropped.
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> select force_logging from v$database;
 
FORCE_LOGGING
---------------------------------------
YES
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25
 
[oracle@localhost ~]$ impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
 
Import: Release 12.2.0.1.0 - Production on Sat Apr 8 10:33:28 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
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 10:34:50 2017 elapsed 0 00:01:15
[oracle@localhost ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:35:09 2017
 
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

这里可以看出来在force logging情况下，设置DISABLE_ARCHIVE_LOGGING参数不生效
从上述测试在不管是非归档还是归档情况下使用DISABLE_ARCHIVE_LOGGING都会减小导入时间，减少归档量，但是需要注意如果数据库是force logging情况下，DISABLE_ARCHIVE_LOGGING参数会无效。

最新文章

  1. jquery复选框 选中事件 及其判断是否被选中
  2. 使用PM2将Node.js的集群变得更加容易
  3. Vue-loader 开启压缩后的一些坑
  4. POJ2635The Embarrassed Cryptographer（大数取余+素数筛选+好题）
  5. 好玩的算法（JS版）
  6. JSP之request对象
  7. MySql中的时间类型datetime，timestamp，date，year比较
  8. C# 合成图片
  9. 超级坑人的Couchbase数据库问题!!!
  10. Asp.net MVC + EF + Spring.Net 项目实践（四）
  11. SQL Server Compact/SQLite Toolbox 使用
  12. 手机Soc芯片简介
  13. less语法
  14. Java Web 开发中的中文乱码与解决方式
  15. MySQL master-slave主从复制环境搭建初试
  16. Jboss 数据源密码明文加密
  17. java IO字节流
  18. Windows Forms Application Creation and Initialization
  19. attributes["wv"].nodeValue
  20. 前端工程师面试问题归纳（二、问答类JQ相关）

热门文章

  1. mssql sqlserver 将逗号分隔的一列数据转换为多列数据的方法分享
  2. Python—图形界面开发
  3. Redis—配置文件详解
  4. CodeForces - 1238D（思维）
  5. 【WPF on .NET Core 3.0】 Stylet演示项目 - 简易图书管理系统(2) - 单元测试
  6. 最近公共祖先（LCA）基础模板(倍增法)
  7. Django restful framework中自动生成API文档
  8. 终结 finalize() 和对象引用
  9. SpringBoot系列之快速创建项目教程
  10. TCP协议 - 面向连接