Archive for the ‘Data Pump Utility’ Category

This is just a quick comparison between timing & filesize for a 35GB database which we backed up using datapump with & without COMPRESSION.

First time I took a backup without COMPRESSION. Following is logs for this activity.

Export: Release 11.2.0.3.0 - Production on Wed Mar 5 07:46:00 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "BACKUP"."SYS_EXPORT_FULL_01":  backup/********@TESTDB DIRECTORY=oraclebackup FULL=Y DUMPFILE=Full_EXPORT_TESTDB_yya_20140305t.dmp LOGFILE=FULL_EXPORT_TESTDB_yya.LOG PARALLEL=4 CONTENT=ALL
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 33.64 GB
Processing object type DATABASE_EXPORT/TABLESPACE


. . exported "ORDDATA"."ORDDCM_MAPPING_DOCS"             7.890 KB       1 rows
Master table "BACKUP"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BACKUP.SYS_EXPORT_FULL_01 is:
  /orabackup/TESTDB/export/Full_EXPORT_TESTDB_yya_20140305t.dmp
Job "BACKUP"."SYS_EXPORT_FULL_01" successfully completed at 09:04:05

Filesize generated is about 32GB in size and it took about 1 hour 15 mins.

-rw-r-----    1 testdb_t oinstall 35114491904 Mar  5 09:04 Full_EXPORT_TESTDB_yya_20140305t.dmp

Now, second run of same database with COMPRESSION parameters is done. Following is the logs for this activity.

Export: Release 11.2.0.3.0 - Production on Wed Mar 5 09:24:22 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "BACKUP"."SYS_EXPORT_FULL_01":  backup/********@TESTDB DIRECTORY=oraclebackup FULL=Y DUMPFILE=Full_EXPORT_TESTDB_yya_comp_20140305t.dmp LOGFILE=FULL_EXPORT_TESTDB_yya_comp.LOG PARALLEL=4 COMPRESSION=ALL CONTENT=ALL
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 33.64 GB
Processing object type DATABASE_EXPORT/TABLESPACE
. . exported "TS_APPSXTY_V2"."TS_CONFIG_REPORTS_X"   486.4 MB   12283 rows



. . exported "TS_APPSXXT_V2"."TS_EXCEL_TEMPLATES"        0 KB       0 rows
Master table "BACKUP"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BACKUP.SYS_EXPORT_FULL_01 is:
  /orabackup/TESTDB/export/Full_EXPORT_TESTDB_yya_comp_20140305t.dmp
Job "BACKUP"."SYS_EXPORT_FULL_01" successfully completed at 11:29:51

This time file generated is of size 18GB and it took about 2 hours.

-rw-r-----    1 testdb_t oinstall 19814805504 Mar  5 11:29 Full_EXPORT_TESTDB_yya_comp_20140305t.dmp

So, COMPRESSION parameter is no doubt very useful in space constraint situations but it comes at cost of time as well as higher CPU utilization and additional license cost 😦

Recently i encountered a problem where few tables are skipped
from export backup taken with expdb utility in 11g.Export log
doesn’t give any clue that some tables have been skipped. i only
came to know about missing table after import is done and application
starts giving error.

Well, this is expected behavior in 11g, as tables with no rows
(a row never inserted into table) is skipped from export backup. Till
10g, whenever we create a table, some initals extents are aloocated to
the table which consumes some space without actually having any data.

This is changed in 11g and only when some data is inserted in to table,
only then extents are allocated to table. Suppose, you deleted all the
data from table then also extents remains allocated to table and it got
included in export backup.

This behavior can be changed by setting the parameter DEFERRED_SEGMENT_CREATION
to false, which is “true” by default.

This is a dynamic parameter and can be changed any time.


SQL> show parameter DEFERRED_SEGMENT_CREATION

NAME TYPE VALUE
-------------------------- ------------- --------
deferred_segment_creation boolean TRUE

SQL> alter system set deferred_segment_creation=false scope=both;

System altered.

SQL> show parameter DEFERRED_SEGMENT_CREATION

NAME TYPE VALUE
-------------------------- ------------ -----------
deferred_segment_creation boolean FALSE

If you want to take export backup of empty tables after setting this parameter then you have to allocate initial extents:

alter table

allocate extents;

This is because DEFERRED_SEGMENT_CREATION parameter only works for new tables that will be created not for already existing empty tables.