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