| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: archive old data
Hi Nancy
One way we did it three / four years ago whilst working on a finance system was to build a second "archive" database. The main database table in the production system held details of the financial transactions and was partitioned into 13 partitions split on months, at each month end the oldest one was exported off and removed from live and a new partition was "opened" for the next month.
There was a requirement that archived data needed to be available but didn't need to be online, so we built a second database with just the finance transactions table with six empty partitions. All of the other tables in the live system didn't get archived as they were either lookup tables or small enough that the old data didn't impact the performance and storage from a users perspective. This helped us as the "archive" database then had a set of a few hundred synonyms that were links to the tables in the live system.
When archived data was needed by the business it was imported to the archive partition's and users pointed the application at this database. The access times were impacted by this method but our remit on providing archived data on this legacy system was to save costs on disk space and hardware. We scripted the rollover so it happened automatically at month end. The solution was not perfect but it worked and saved disc space in not needing to replicate the whole database and not having to archive all of the data and allowed recovery of archived data quite quickly. The production database held 1.5 - 2 terabytes of data by the way mostly in the financial transactions.
hth
kind regards
Pete
--
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Pete Finnigan
INET: oracle_list_at_peterfinnigan.demon.co.uk
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Sep 16 2003 - 08:54:33 CDT
 |
 |