Get more benefited from ACO

[Former Member]

Oracle ACO, a compression methodology launched by Oracle with 11g.

Most of the customers got benefited with this in terms of storage reductions, performance improvements etc.. We have this enabled last year in a different way to get more benefited!

Details: Oracle ACO is an compression methodology which is a pack of:

OLTP table compression

Index key compression (Oracle 10G on)

Securefile compression

RMAN compression

Data guard network compression

Activity type: It's available as an online activity, end to end activity can be done ONLINE (Yes, no downtime is required for normal compression!)

Methodologies available: In place compression and tablespace migration (Out place)

Methodology that we selected: Tablespace migration (Involved storage migration) with compression

Benefits that we got: Huge storage reduction from existing system, Well managed data files, New TS structure (LMTS etc.)

Steps: Earlier we have our entire database stored in sapdata1 - sapdata40 file systems (Each file system was having different sizes).  We created equally sized data files from sapdata50 to sapdata60 -> Created *_new tablespaces --> Migrated data from old tablespace to new tablespace with compression option (-C) ON --> Deleted the old tablespace once the entire data was moved --> Renamed *_New tablespace to original tablespace

Finally we got all our database in sapdata50-sapdata60, so removed all the old file systems and LUNs which saved some additional storage as well.

This method need a post compression downtime because it has storage migration involved, downtime is needed to move system,sysaux tablespaces to new storage (sapdata50-60 in above scenario).

Results: We ended up with 3x compression ratio (ECC database has become 1TB from it's original size 3TB, Storage has reduced from 5.5TB to 2TB).

Learnings: Execution is quite simple, managing system load during compression is a key. There are some restrictions, mentioned in below notes.

1289494 - FAQ: Oracle compression

1109743-Use of Index Key Compression for Oracle Databases

1436352 - Oracle 11g Advanced Compression for SAP Systems

1431296 - LOB conversion and table compression with BRSPACE

1847870 - Oracle 11g: Advanced Compression - Known issues and solution

Hope it helps!