TRANSFORM=DISABLE_ARCHIVE_LOGGING in Oracle 12c

a new parameter was introduced in Oracle 12c  for the datapump operations. this parameter is TRANSFORM=DISABLE_ARCHIVE_LOGGING

To explore this parameter let me first start importing a schema in my database without specifying the parameter:

my current database is in “archive log mode”

select LOG_MODE , force_logging from v$database;

query for archive log mode

The time taken for this import was 2 hours !!!

And, 55 GB of archive storage was generated during the import.

Now, i will re-import again using the parameter TRANSFORM=DISABLE_ARCHIVE_LOGGING in my below command:

nohup $ORACLE_HOME/bin/impdp “‘/as sysdba ‘”  DIRECTORY=EXP_DIR DUMPFILE=SCHEMA_JUNE28.dmp transform=disable_archive_logging:y logfile=IMP_disable_archive.log  &

the time taken to finish the import was 1 hour 28 minutes !!! half an hour less the the normal standard import operation.

And, the storage of the archive log was around 30 GB.

The conclusion of this exercise is that the new parameter introduced in 12c will benefit in 2 things:

  • faster import process completion
  • less storage for archive generation

Remark: make sure that when you use this parameter ,that if you have a logical standby setup for the database, that the database is in “archive log mode” and “force logging” is enabled.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s