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.

This feature reduces the required maintenance of redo logs by DBAs

Comments

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

Register Archive log file manually in standby Database

How to check UNDO tablespace usage and who is using more undo