With your database in archive log mode, a Data Pump Import may be severely slowed down by the writing of much redo into online logs and the the generation of many archive logs. A 12c New Feature enables you to avoid that slow down by suppressing redo generation for the import only. You can keep the database in archive log mode the whole time. Let’s see that in action!
First without the new feature:
[oracle@uhesse ~]$ sqlplus adam/adam SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:25:25 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Oct 25 2016 20:24:55 +02:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select log_mode from v$database; LOG_MODE ------------------------------------ ARCHIVELOG SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ------------------------------ ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 15.37 15 10 0 BACKUP PIECE 25.17 0 2 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@uhesse ~]$ impdp adam/adam tables=sales directory=DPDIR Import: Release 12.1.0.2.0 - Production on Tue Oct 25 20:26:45 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "ADAM"."SYS_IMPORT_TABLE_01": adam/******** tables=sales directory=DPDIR Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "ADAM"."SALES" 510.9 MB 10000000 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 25 20:29:46 2016 elapsed 0 00:03:00 [oracle@uhesse ~]$ sqlplus adam/adam SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:30:03 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Oct 25 2016 20:26:45 +02:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ------------------------------ ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 30.63 15 17 0 BACKUP PIECE 25.17 0 2 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected.
The import took 3 minutes and generated 7 archive logs. Now with the new feature:
SQL> drop table sales purge; Table dropped. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@uhesse ~]$ impdp adam/adam tables=adam.sales directory=DPDIR transform=disable_archive_logging:Y Import: Release 12.1.0.2.0 - Production on Tue Oct 25 20:31:20 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "ADAM"."SYS_IMPORT_TABLE_01": adam/******** tables=adam.sales directory=DPDIR transform=disable_archive_logging:Y Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "ADAM"."SALES" 510.9 MB 10000000 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 25 20:32:15 2016 elapsed 0 00:00:54 [oracle@uhesse ~]$ sqlplus adam/adam SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 20:32:25 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Oct 25 2016 20:31:20 +02:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ------------------------------ ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 30.63 15 17 0 BACKUP PIECE 25.17 0 2 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected. SQL> select logging from user_tables where table_name='SALES'; LOGGING --------- YES
About three times faster and no archive log generated! The table is still in logging mode after the import. Keep in mind to take a backup of the datafile that contains the table now, though! For the same reason you should take a backup after a NOLOGGING operation 🙂
Pickleball spielen 
#1 von pdsmith69 am Oktober 25, 2016 - 21:17
Or if you wish to use the DataPump API:
dbms_datapump.metadata_transform ( handle => v_handle, name => ‚DISABLE_ARCHIVE_LOGGING‘, value => 1);
#2 von Hanno Ernst am Oktober 25, 2016 - 21:43
Hi Uwe,
nice feature, but does it work that way if the database is in force_logging mode? Please add this to your article for completeness, that one should think about this…
thank you and
best regards
Hanno
#3 von Lukasz Zegar am Oktober 26, 2016 - 13:00
Hi Uwe
Thank you for your post, I didn’t know that, but is this option useful when we are doing an import on the primary database, working in the Data Guard environment?
Regards
#4 von Hanno Ernst am Oktober 26, 2016 - 13:53
Hello Lukasz,
normally it cannot be used at a primary, because this data would be missing in standby’s.
oracle support note says…
—
12c DISABLE_ARCHIVE_LOGGING DataPump parameter is introduced to disable logging for table, index or both during import job.
Logging is not completely disable but only a small amount is generated. Also don’t forget that there is a database parameter FORCE LOGGING which overwrites this feature.
—
So if your database is in force_logging=y which a primary database should be, this feature is not usable.
#5 von Adinath Kamode am Oktober 27, 2016 - 10:08
Good Article, Thank you for force logging explanation.