12

I'd like to figure out the best way to archive the data that is no needed anymore, in order to improve the application performance and also to save disk space. In your experience what is the best way to implement this, what kind of tools can I use? It is better to develop an specific in house application for that purpose?

Rulas
  • 1,164
  • 3
  • 16
  • 22
  • 1
    There is many ways of archiving off of Oracle. U don't need to develop any tools. Whats sort of hardware/budget do you have. Do have SANS installed. Do you need to have your archive data, almost available, or can it be packed away in a offsite cupboard for years? – scope_creep Nov 11 '09 at 21:07
  • 1
    Why do you think archiving will improve application performance ? If you are going to archive data, you need to think how you will access it later (if you need to). Bear in mind future schema changes and database upgrades will affect the ability to 'just' reload it. – Gary Myers Nov 11 '09 at 21:51
  • 4
    I think that we really need to hear more about your needs here ... the volume of data, how complex the schema is and how many tables you need to archive from, whether you ever need that data again and if so what would be the SLA for accessing it ... – David Aldridge Nov 12 '09 at 07:37
  • @David- Well the thing is I will no need the data anymore, I want to have it as backup, Im talking about 60 Gb or something. – Rulas Nov 12 '09 at 14:12
  • 1
    @Gary- Maybe you are right about there are no much difference in the application performance, the main goals of this archiving are things like 1. Reduction of the time required for database maintenance activities: Reorganization, Export/Import, index creation/re-creation, adding disk space. 2. Reduction of the time required for recovery in case of a disaster occurs. 3. Reduction of the time required for the daily backups (less use of the tape backup infrastructure). 4. Reduction of the cost of the hardware (disk and disk chassis) required to store the data. – Rulas Nov 12 '09 at 14:13

3 Answers3

5

One way to manage archiving:

  1. Partition your tables on date range, e.g. monthly partitions
  2. As partitions become obsolete, e.g. after 36 months, those partitions can now be moved to your data warehouse, which could be another database of just text files depending upon your access needs.
  3. After moving, the obsolete partitions can be removed from your primary database, so always maintaining just (e.g.) 36 months of current data.
  4. All this can be automated using a mix of SQL/Shell scripts.
Sam
  • 404
  • 4
  • 7
2

The best way to archive old data in ORACLE database is:

  1. Define an archive and retention policy based on date or size.
  2. Export archivable data to an external table (tablespace) based on a defined policy.
  3. Compress the external table and store it in a cheaper storage medium.
  4. Delete the archived data from your active database using SQL DELETE.
  5. Then to clean up the space execute the below commands: alter table T_XYZ enable row movement; alter table T_XYZ shrink space;
  6. If you still want to free up some disk space back to the OS (As Oracle would have now reserved the total space that it was previously using), then you may have to resize the datafile itself: SQL> alter database datafile '/home/oracle/database/../XYZ.dbf' resize 1m;

For more details, please refer: http://stage10.oaug.org/file/sroaug080229081203621527.pdf

Ravi Ramanathan
  • 21
  • 3
  • Re step 4. if the delete is a performance concern, i might consider pulling the current data into a new table, dropping the old table, and renaming the new one to the old name – EvilTeach Jan 13 '16 at 03:39
-2

I would export the data to a comma-delimited file so it can be exported into almost any database. So if you change versions of Oracle or go to something else years later you can restore it without much concern.

Use the spool file feature of SQL*Plus to do this: http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/#savingoutput

Todd Moses
  • 10,969
  • 10
  • 47
  • 65
  • 1
    This is a legitimate operation that is documented in Oracle SQL. There is proof in the link. – Todd Moses Nov 12 '09 at 13:29
  • 2
    For anything other than small amounts of data, this is a poor option. It has difficulty with complex datatypes and even with simple VARCHAR2 columns that might contain the delimiter. – Gary Myers Nov 12 '09 at 22:06
  • There are also potiential issues with clobs. – EvilTeach Jan 13 '16 at 03:43