DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, January 19, 2015

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

No comments: