Backing Up and Dropping Partitions
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Backing Up and Dropping Partitions

  1. #1
    Join Date
    Jun 2007
    Posts
    23

    Question Backing Up and Dropping Partitions

    Hello,
    I just had a "fun" discussion with a client DBA. We were discussing the best method to archive historical partitions in a data warehouse (large fact tables).

    The fact tables are daily partitioned, with a month's worth in a single monthly tablespace. We want to keep 12 months online and archive the 13th month each month.

    What are the recommended steps to do this type of archiving/backup? We aren't putting things into another DB - just need the tablespace backed up so we can drop partitions and re-use the space.

    Do we just use expdp to export the partitions and the issue a "alter table drop partitoin" and then shrink that month's tablespace (presumably keeping the tablespace online)? Or maybe even drop that tablespace?

    Or is it best to use partition exchange to a new tablespace (exchanging all partitions to plain old tables), then use transportable tablespaces as a backup solution? If this is optimal, do we drop the tablespace or keep it around (with no storage)?

    Or is there a third option using standard RMAN read-only backups (which we also have for "just in case" purposes)?

    Or maybe none of the above?

    If you can be detailed on the steps, I'd appreciate it (it's the details we've been "discussing" )
    Thanks.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Here is what we do...

    1- Export affected partition (exp/compress on the fly)
    2- Move dump file to tape
    3- Drop affected partition
    4- Coalesce tablespace

    Notes:

    #1 For some tables where we know reload is likely, we do not drop the partition, we just truncate it.

    #2 Since all our indexes are partitioned, no need to rebuild.

    #3 Since we do not have a tablespace per each partition, no need to deal with empty tablespace.

    By the way, we move selected Data Warehouse data to tape after seven -as in 7- years.
    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
    Jun 2006
    Posts
    259
    Quote Originally Posted by paulb
    Hello,
    I just had a "fun" discussion with a client DBA. We were discussing the best method to archive historical partitions in a data warehouse (large fact tables).
    Do you want an "archive" or a backup they are two different things.
    An archive is usefull for long term retention of data. Considering that many govenrment regulations require data be retained for 7 years, it is my opionion that archives should be in an open format not proprietary.

    If you use expdb, exp, or just a tablespace backup. Then you will also need to be able recreate the original environment where those were taken. Certainly the hardware and all of the software will have been change by that time.

    PM me if you want additional details.

  4. #4
    Join Date
    Jun 2007
    Posts
    23
    Quote Originally Posted by PAVB
    Here is what we do...

    1- Export affected partition (exp/compress on the fly)
    2- Move dump file to tape
    3- Drop affected partition
    4- Coalesce tablespace

    Notes:

    #1 For some tables where we know reload is likely, we do not drop the partition, we just truncate it.

    #2 Since all our indexes are partitioned, no need to rebuild.

    #3 Since we do not have a tablespace per each partition, no need to deal with empty tablespace.

    By the way, we move selected Data Warehouse data to tape after seven -as in 7- years.
    On step 1, I didn't know expdp could compress on the fly ... did I miss that feature? Maybe you're using plain exp?

    #3 - I'm interested, how are your tablespaces set up then? Do you have one tablespace for all partitions? Seems like you wouldn't have the flexibility of setting things to read-only and using RMAN (or maybe you're not using RMAN?). I just had another thread about this topic and Mr. Aldridge recommended a tablespace per partition ... thoughts on that?

    Oh, and I forgot to mention, this is 10gR2 on linux ... (not that it matters)

  5. #5
    Join Date
    Jun 2007
    Posts
    23
    Quote Originally Posted by ixion
    Considering that many govenrment regulations require data be retained for 7 years, it is my opionion that archives should be in an open format not proprietary.
    I PM'd you as well, but I'd love to hear this discussed publicly ... I understand what you are saying (been through SOX audits before - joyful). Are you suggesting a text file dump (select * ...)?

    I wonder what others think about this. Definitely simple to implement ... so why not this vs. other methods?

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by paulb
    On step 1, I didn't know expdp could compress on the fly ... did I miss that feature? Maybe you're using plain exp?)
    This is not a feature of export. We are using exp + compress, exp sends dump into a pipe; compress gets data from the pipe and writes the compressed file.

    You have to do the other way around when importing.

    Quote Originally Posted by paulb
    #3 - I'm interested, how are your tablespaces set up then?
    Depending on size of target table we have anything in between 1 to 4 tablespaces per table. These are generic tablespaces, we assign partitions depending on tablespace utilization/availability.

    Just what works for us.
    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.

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Well, I think u answered your question in the question itself. All the methods u mentioned have pros and cons and can be used, depends on the situation
    Personally I like exchange partition approach, but it depends on the concrete situation
    PAVBs method is simple, and reliable, that's what I like about it
    IXION on the other hand puts that in dependency on the regulations, so all these factors should influence your solution.
    Just to add, u may look at the
    DBMS_STREAMS_TABLESPACE_ADM package in 10g Rel 2.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by paulb
    Or is it best to use partition exchange to a new tablespace ...
    Partition exchange doesn't move the data or index segments to a different tablespace, it is just an exchange of the logical table and table partition attributes. The data stays put, it just belongs to a different logical object.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jun 2007
    Posts
    23
    Dave, sorry for not being clearer on that - yes, we know exchange doesn't put it in a new tablespace, I was just thinking out loud that we might want to do that to then transport the new tablespace out as a backup (leaving the old one in case it has other partitions).

    Good thoughts on all this - sounds like there is no one single perfect solution. Thanks all.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by paulb
    ...sounds like there is no one single perfect solution.
    Exactly, like in quantum physics you are gonna end with many correct answers for a single question
    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.

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