I have production database of about 5 terabyte size in production and about 50 GB in development/QA. I am on Oracle 184.108.40.206 on Linux. We have RMAN backups configured. I have a question about data archival stretegy. To keep the OLTP database size optimum, what options can be suggested for data archival?
1) Should each table have a archival stretegy?
2) What is the best way to archive data - should it be sent to a seperate archival database?
In our environment, only for about 15 tables we have the archival stretegy defined. For these tables, we copy their data every night to a seperate schema meant to store this archived data and then transfer it eventualy to a diffent archival database. For all other tables, there is no data archival stretegy.
What are the different options and best practices about it that can be reviewed to put a practical data archival strategy in place? I will be most thankful for your inputs.
Also what are the different data compression stregegies? For example we have about 25 tables that are read-only. Should they be compressed using the default oracle 9i basic compression? (alter table compress).
Data Archival depends on your business rules and requirements. This answer can be answered by your business or application owner.
In 11g you have some new compression commands, you can use those for read write table. This new feature is for OLTP databases. Conventional compress syntax is same in 9i and 11g so you can use 9i command if you want.
Here my point is: should the 'read-only' tables be the ones that should be compressed or should even the read-write tables be compressed? What I think is that the read-only tables are the ones that are candidate for compression. Can you tell what is the criteria on which to decide what tables should be compressed?
You are using 11g and you can compress read only as well as read-write tables in 11g. Both read only and read write tables are the candidate for compression always. This will save space and could increase the performance but always test it before. This was not an option in 10g. Read the following docs.
I would be careful in mixing compression with updates: an updated row is not compressed again after the change (for basic compression and in many cases also for OLTP compression) and so frequent updates may deny the effects of compression (and lead to row migration). Jonathan Lewis has recently written a great article series on the topic for AllThingsOracle starting with http://allthingsoracle.com/compression-oracle-basic-table-compression/. Another aspect is that compression will need a reorganization of the tables (by ALTER TABLE ... MOVE) since the activation of compression only has an impast on following operations.