Get more benefited from ACO
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)
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!