I have not been blogging for quite some time now and thought of ending the year with one last blog. I picked a very simple  yet powerful datapump topic for blogging. Read on …

As is the norm after exhausting all tuning options, we kind of hope that redo is the bottleneck for all performance problems :). So if your datapump performance issues still exists after multiple tuning iterations, As DBA’s you explore ways to run the import in NOLOGGING mode to help improve the performance. There aren’t too many easy options prior to Oracle 12c.  Let us  look at some of options available prior to 12c

Few of the ways to implement NOLOGGING are

1) Set the Database to NOARCHIVELOG mode, Hmm , this requires a bounce and I don’t have the luxury of having an scheduled outage in production. Next option …

2) Set all tables to NOLOGGING mode (after pre-creating them if needed)

3) If both the above options are not possible, then for some of us when our back is against the wall ….. we either quit or move forward. Since quitting is not an option , we tend to try undocumented stuff like “_disable_logging” hippy I finally found the silver bullet to better performance and off course it is OK if the database gets corrupted 🙂

It looks like Oracle Support had enough of it dealing with database corruption:). Starting with Oracle 12 , you have an option to import the data by disabling the archival only for the import operation. You also have the option of disabling archival only for table or Indexes.   Again some amount of redo is always generated  with NOLOGGING which is the norm than exception.

Also this option will only work if FORCE LOGGING is turned off.  This could be a pain point if you have DataGuard.

It is highly recommended to backup the database after running import in NOLOGGING mode  so that media recovery is possible. Otherwise you may have rerun the import operation which is easier said than done because of the dependencies.

Few examples of usage are given below

impdp … TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
impdp … TRANSFORM=DISABLE_ARCHIVE_LOGGING:N:TABLES
impdp … TRANSFORM=DISABLE_ARCHIVE_LOGGING:N:INDEXES

Tagged: DISABLE_ARCHIVE_LOGGING, TRANSFORM oracle, TRANSFORM=DISABLE_ARCHIVE_LOGGING, TRANSFORM=DISABLE_ARCHIVE_LOGGING 12c, TRANSFORM=DISABLE_ARCHIVE_LOGGING oracle