11g Release 2 RMAN Backup Compression

Oracle Compression

With Oracle 11g Oracle Advanced Compression provides comprehensive data compression capabilities to compress all types of data, backups, and network traffic in an application transparent manner.The Oracle Advanced Compression option contains the following features:

 

• Fast RMAN Compression
• Data Guard Network Compression
• Data Pump Compression
• OLTP Table Compression
• SecureFile Compression and Deduplication
• Flashback Data Archive (Total Recall)

Rman Compression
For RMAN backup compression evels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded. Unfortunately  use of LOW, MEDIUM and HIGH requires the "Advanced Compression license"

To configure RMAN to use compression at all you can use

RMAN>CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

followed  by   one of the following

RMAN>CONFIGURE COMPRESSION ALGORITHM 'BASIC';
RMAN>CONFIGURE COMPRESSION ALGORITHM 'LOW';
RMAN>CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';
RMAN>CONFIGURE COMPRESSION ALGORITHM 'HIGH'

Here are the results 

Method	Time(sec)	Backup Size(GB)
BASIC	266	1.258
LOW	66	1.637
MEDIUM	228	1.437
HIGH	668	1.166
NONE	74	3.241

 

 

Conclusion
Note  that  without compression backup time is  the best  but file size is  nearly  database  size

Also there is another factor that I did not take into account .The CPU load of these methods.we know that the complexity of compression level will generate CPU load on server.
Because of many factors (CPU source,disk speed ..)influencing backup speed and backup size you have to test backup and backup compression in your environment yourself.And you must consider your CPU nd disk performance .Also "Advanced Compression license" cost

How To Find Optimal Index Key COMPRESS Level For Indexes

Key compression on an index eliminates repeated occurrence of key column values.The amount of compression is dependent on repeated occurrences.The COMPRESS integer specifies the number of prefix columns to compress (up to the number of primary-key columns minus 1.)


Run the following SQL and select the COMPRESS level that shows the greatest percentage of savings:


analyze index {INDEX_NAME} validate structure;


select opt_cmpr_count, opt_cmpr_pctsave from index_stats where name = '{INDEX_NAME}';

To compress  the index

alter index  {INDEX_NAME} rebuild online compress  N;