Archive for April, 2014

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 😦

Advertisements