Best way to archive old data
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Best way to archive old data

  1. #1
    Join Date
    Mar 2006
    Posts
    174

    Best way to archive old data

    I have one table that was over 100G and I only need to keep 1 year of current data, the rest can be archived. What is the best way to handle this situation.

    I am thinking to move everything except one year of archived data to a file and used External table if someone needs to look at it.

    thoughts?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Is the DATE column that defines how old the row is part of the predicate of most queries? can it be included?

    If the answer is Yes... I would partition table by that DATE column then archive and purge entire partitions.

    As to where to archive those partitions it all depends on business specs... one alternative would be to export/compress partitions then either store them on disk or tape. Import when needed.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2006
    Posts
    174
    thanks for your input.

    One of the option I also thought about was partition the table by date; however, if you partition by date, the table you archive the old data still in the database and the backup job still had to pick up that table and I am trying to cut down the backup run time.

    thoughts?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by matthew00 View Post
    One of the option I also thought about was partition the table by date; however, if you partition by date, the table you archive the old data still in the database and the backup job still had to pick up that table and I am trying to cut down the backup run time.
    Not really.

    There are two alternatives.

    Alternative #1 -
    Offline archive. Export partition to be archived the truncate partition. Dump file can be either compressed and kept on disk or moved to tape.

    Alternative #2 -
    Online archive. Swith partition to be archived to a table sitting on a read-only tablespace temporarily open for writing then set it back to read-only. Once you have one good backup of archive tablespace you can safely skip it during normal backups.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Mar 2006
    Posts
    174
    great thank

  6. #6
    Join Date
    Mar 2006
    Posts
    174
    Not sure if I fully understand option 2 so please clarify.

    Quote Originally Posted by PAVB View Post
    Not really.


    Alternative #2 -
    Online archive. Swith partition to be archived to a table sitting on a read-only tablespace temporarily open for writing then set it back to read-only. Once you have one good backup of archive tablespace you can safely skip it during normal backups.


    so you saying, copy the archived data into the table that sitting on the read only tablespace. backup this tablespace one time and remove it from backup schedule. what about new data that coming? we need to set up the job to refresh the archived data?

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Not totaly sure what you mean by "refresh the archived data".

    I was assuming archived data will not change after been archived - is that correct?

    The beauty of a read-only tablespace is that after taking a good backup tablespace can be excluded from further backup processes - provided tablespace remains in backup mode.

    Using this archiving approach - in general - archive process runs once a month switching an active partition to a table in an archive tablespace.

    Process looks like...
    1- Put archive tablespace in read-write mode
    2- Switch a.k.a. archive partition to archive tablespace
    3- Put archive tablespace in read-only mode
    4- Take a backup

    Archive tablespace doesn't need to be backup until next archive window.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Mar 2006
    Posts
    174
    Thanks so much for taking time out to help me on my question.

    What I mean was, let say archived data of one year of today is different from archived data of one year 6 months from today. another word, we just have to keep archiving data and only keep one year of current data.

    Thanks again

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Not sure if I'm following.

    Archival process should run lets say once a month. If this is the case I would partition "live" table by YYYYMM. At the end of each month archival process will take care of taking out the oldest partition leaving 12 month worth of data in "live" table.

    Each time archival process runs it moves out - switches - a whole partition out of "live" table into an archival table sitting on archive tablespace.

    In regards to the "archive" you can either opt for having an individual archival table per month - the same you get when switching partition - or create a partitioned archival table which you will load by switching the table that just got the switched partition from "live" table.

    Hope this helps.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Mar 2006
    Posts
    174
    I am so sorry if what I said is confusing. Here is an example and please let me know:

    1. I have table named TEST and one column named EXPORT_DATE and the date column type.
    2. I then partition named TEST table on column EXPORT_DATE with date range. (one year as current data).
    3. create a temp table named ARCHIVED_TEST which will hold the data that was older than 1 year

    question:

    1. What do I need to do to move the archived data that was older than 1 yr from TEST table to ARCHIVED_TEST? If I understand correctly, you recommended that the archival process should be running at least one per month? Are you referring to a job that I need to set up?

    Thanks for your patience.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width