How To Import Data With Nologgin Option In Oracle 12c

A new feature has been added in datapump of oracle 12c. We can import data with nologgin option i.e without generating logs. We sometimes face issue while importing big tables, as it generates lot of archive logs.

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y – This parameter can be used to make the impdp nologging.

check the archive status before import

1 2 3 4 5 6 7 8 9 10

SQL> archive log list Database log mode        Archive Mode Automatic archival        Enabled Archive destination        USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     232 Next log sequence to archive   234 Current log sequence        234

Import a table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[oracle@localhost ~]$ impdp dumpfile=dbaclass.dmp logfile=dbaclass.log directory=DUMP tables=test.TEST TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y   Import: Release 12.1.0.2.0 - Production on Sat Aug 22 03:40:41 2015   Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.   Username: sys/oracle@ORCL as sysdba   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 "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01":  sys/********@ORCL AS SYSDBA dumpfile=dbaclass.dmp logfile=dbaclass.log directory=DUMP tables=test.TEST TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TEST"."TEST"                               10.66 MB   93925 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Aug 22 03:41:23 2015 elapsed 0 00:00:30

Check the archive status after import( No change observed)

1 2 3 4 5 6 7 8 9 10

SQL> archive log list Database log mode        Archive Mode Automatic archival        Enabled Archive destination        USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     232 Next log sequence to archive   234 Current log sequence        234

Check the alert log

1 2 3 4 5 6 7 8 9 10

Sat Aug 22 03:36:06 2015 Resize operation completed for file# 10, old size 224000K, new size 226560K Sat Aug 22 03:40:53 2015 DM00 started with pid=72, OS id=10034, job SYS.SYS_IMPORT_TABLE_01 Sat Aug 22 03:40:54 2015   DW00 started with pid=76, OS id=10036, wid=1, job SYS.SYS_IMPORT_TABLE_01

We can observe in the alert log that no archives has been generated as part of the import.

We can use nologging for specific object_types like TABLE or INDEX.

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE      

 

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX