Posted in 2016

Compression of dumpfile

I don’t see it as very useful, because someone who does not know about licensing can fall into this pond. Normal datapump with later gzip would do the same compression as I see and basic compression (without licencing ACO I think) does it pretty the same.. well simple data compress nearly the same.. so if you have basic datatypes in your schema, you can reduce the size of the dumpfile by 1/3.

[oracle@hackbook ~]$ expdp system schemas=jano dumpfile=backup.dmp compression=all compression_algorithm=high

Export: Release 12.1.0.2.0 - Production on Fri Jul 8 10:18:20 2016

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

UDE-01017: operation generated ORACLE error 1017
ORA-01017: invalid username/password; logon denied

Username: system 
Password: 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=jano dumpfile=backup.dmp compression=all compression_algorithm=high 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 80 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "JANO"."TEST"                               24.30 MB   10000 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/rCAT/dpdump/backup.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jul 8 10:19:27 2016 elapsed 0 00:00:35

[oracle@hackbook ~]$ ls -l /u01/app/oracle/admin/rCAT/dpdump/backup.dmp
-rw-r-----. 1 oracle dba 25542656 Jul  8 10:19 /u01/app/oracle/admin/rCAT/dpdump/backup.dmp
[oracle@hackbook ~]$ expdp system schemas=jano dumpfile=backup1.dmp 

Export: Release 12.1.0.2.0 - Production on Fri Jul 8 10:20:00 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=jano dumpfile=backup1.dmp 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 80 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "JANO"."TEST"                               38.25 MB   10000 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/rCAT/dpdump/backup1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jul 8 10:20:32 2016 elapsed 0 00:00:25

[oracle@hackbook ~]$ 
[oracle@hackbook ~]$ gzip /u01/app/oracle/admin/rCAT/dpdump/backup1.dmp
[oracle@hackbook ~]$ ls -l /u01/app/oracle/admin/rCAT/dpdump/backup1.dmp.gz 
-rw-r-----. 1 oracle dba 25502770 Jul  8 10:20 /u01/app/oracle/admin/rCAT/dpdump/backup1.dmp.gz
[oracle@hackbook ~]$ expdp system schemas=jano dumpfile=backup.dmp compression=all compression_algorithm=basic

Export: Release 12.1.0.2.0 - Production on Fri Jul 8 10:27:44 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=jano dumpfile=backup.dmp compression=all compression_algorithm=basic 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 80 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "JANO"."TEST"                               22.87 MB   10000 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/rCAT/dpdump/backup.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jul 8 10:28:20 2016 elapsed 0 00:00:32

[oracle@hackbook ~]$ ls -l /u01/app/oracle/admin/rCAT/dpdump/backup.dmp
-rw-r-----. 1 oracle dba 24051712 Jul  8 10:28 /u01/app/oracle/admin/rCAT/dpdump/backup.dmp
Advertisements

Author:

Database administrator who loves to work with Oracle software. (Sometimes not)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s