Sunday, June 30, 2013

Using New Oracle Database 12c Feature for DataPump DISABLE_ARCHIVE_LOGGING

New DataPump feature disables redo logging when loading data into tables and when creating indexes.


For my testing of the new feature I will be importing the SOE schema from the SwingBench tool. The SOE schema is about 2G and size.

Test 1

I will be to import the normal way.

$impdp parfile=impdp.par

impdp.par

directory=DATA_PUMP_DIR2
dumpfile=exp_schema_soe%U.dmp
logfile=imp_schema_soe.log
parallel=2

Before the import there were zero archive logs

In the first test we should see redolog and archive logs being generated during the import of the SOE schema.

Import

During the import we can see redologs and archivelogs are being generated during the import

After the import 25 archive logs were generated

The import had an elapsed time of 14 minutes with 41 seconds


Test 2 

I will import using the new feature DISABLE_ARCHIVE_LOGGING

SQL> drop user SOE cascade;

$impdp parfile=impdp_noarchive.par

impdp_noarchive.par

directory=DATA_PUMP_DIR2
dumpfile=exp_schema_soe%U.dmp
logfile=imp_schema_soe.log
parallel=2
transform=disable_archive_logging:Y

Before the import there were zero archive logs

Import

During the import we see that there is no archivelog being generate since there is no redo generation.
After import was complete we can see that no new archivelog where generated.

The import had an elapsed time of 10 minutes with 06 seconds

Note from Oracle Documentation:
With redo logging disabled, the disk space required for redo logs during an Oracle Data Pump import will be smaller. However, to ensure recovery from media failure, the DBA should do an RMAN backup after the import completes.
Even with this parameter specified, there is still redo logging for other operations of Oracle Data Pump. This includes all CREATE and ALTER statements, except CREATE INDEX, and all operations against the master table used by Oracle Data Pump during the import.

7 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thanks For the nice post Javier, i have one doubt here if there is o logging in import then how our DR will sync up in this case and keep up with primary?

    ReplyDelete
  3. Arpit,

    Thank you for the comment in a standby environment you should have force logging turn on and that means even if you use the disable_archive_logging it would still generate redo since force logging is on.

    If you have a standby environment with force logging turned off you will have a corrupted standby if someone were to execute a no logging operation like using the append hint or the new data pump feature. This is true for pre 12c versions as well.

    ReplyDelete
    Replies
    1. Hi Javier ,

      Nicely explained , I have a question regarding the param in DR environment so if force logging is turned on than we see heavy archive generation and the import time will still be the same right ? iam not able to get how this parameter is useful in DR environments .

      Delete
    2. Sri,
      Here is what you could do if you want to use this feature and you need force logging turned on because you have a standby. What you would do would be to turn off redo transport on the primary then on the standby turn off redo apply. On the primary turn off the force logging then import using the transform=disable_archive_logging:Y. Once the import is complete turn on force logging on the primary then do an incremental restore of the standby. Once the incremental recovery is complete turn on the redo transport and redo apply.

      Delete

About Me

My Photo

Senior DBA with over 13 years experience, specializing in "Database Performance Tuning" and High Availability (RAC, Data Guard & Oracle Golden Gate).