联系：手机/微信(+86 17813235971) 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参数会无效。