Backing Up and Dropping Partitions

[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).

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.

[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.

[ixion]

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.

[paulb]

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)

[paulb]

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?

[PAVB]

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.

#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.

[Bore]

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.

[slimdave]

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.

[paulb]

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.

[PAVB]

...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