Why export is not a good archiving tool
in [Oracle]
|
From: Mark D Powell on 18 May 2010 09:26 On May 17, 6:43 pm, "keith.micha...(a)gmail.com" <keith.micha...(a)gmail.com> wrote: > I need to know why exporting a database periodically is not a suitable > method for preserve access to historical data. A customer with 10-20 > year retentions is using this to "preserve access" for compliance > reasons. I would like to push them towards IBM Optim, Outerbay, > Solix, etc. but I don't know how to explain the shortcomings of their > method. Apparently the existing records don't have a nice date field > to identify old data and leaving it in place doesn't protect it from > damage or loss. Export is a great tool for making logical backups that may be needed with the existing or next release of the database. However, there is no guarantee that an export dump file created today will be readable 20 years from today. On the other hand a delimited or fixed position text extract file will almost surely be readable. Using delimited files also supports importing the data into a different database vendor product environment. After all can you be sure that business conditions will not have warranted conversion. The DDL for the target table and sqlldr control cards can be generated as part of the extract and used guide conversion if necessary. Once extracted into delimited text files the data can be encrypted and/ or compressed using standard OS utilities. In the event of a non- compatible platform change the files can easily be extracted and uncompressed/decrypted on the existing platform, copied to the new one, and then re-compressed/encrypted as necessary. For long-term archival where you may need to access the data nothing beets pain text. IMHO -- Mark D Powell --
From: keith.michaels on 18 May 2010 13:45 On May 18, 6:26 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > On May 17, 6:43 pm, "keith.micha...(a)gmail.com" > > <keith.micha...(a)gmail.com> wrote: > > I need to know why exporting a database periodically is not a suitable > > method for preserve access to historical data. A customer with 10-20 > > year retentions is using this to "preserve access" for compliance > > reasons. I would like to push them towards IBM Optim, Outerbay, > > Solix, etc. but I don't know how to explain the shortcomings of their > > method. Apparently the existing records don't have a nice date field > > to identify old data and leaving it in place doesn't protect it from > > damage or loss. > > Export is a great tool for making logical backups that may be needed > with the existing or next release of the database. However, there is > no guarantee that an export dump file created today will be readable > 20 years from today. > > On the other hand a delimited or fixed position text extract file will > almost surely be readable. Using delimited files also supports > importing the data into a different database vendor product > environment. After all can you be sure that business conditions will > not have warranted conversion. The DDL for the target table and > sqlldr control cards can be generated as part of the extract and used > guide conversion if necessary. > > Once extracted into delimited text files the data can be encrypted and/ > or compressed using standard OS utilities. In the event of a non- > compatible platform change the files can easily be extracted and > uncompressed/decrypted on the existing platform, copied to the new > one, and then re-compressed/encrypted as necessary. > > For long-term archival where you may need to access the data nothing > beets pain text. > > IMHO -- Mark D Powell -- Thanks for the replies. The "periodic" nature of the process is also a concern because we don't know how to keep good records of what is in each snapshot. We can save the date of the export but who knows where specific data is located, if we accumulate a few hundred exports over the years.
From: joel garry on 18 May 2010 17:09
On May 18, 10:45 am, "keith.micha...(a)gmail.com" <keith.micha...(a)gmail.com> wrote: > On May 18, 6:26 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > > > > > On May 17, 6:43 pm, "keith.micha...(a)gmail.com" > > > <keith.micha...(a)gmail.com> wrote: > > > I need to know why exporting a database periodically is not a suitable > > > method for preserve access to historical data. A customer with 10-20 > > > year retentions is using this to "preserve access" for compliance > > > reasons. I would like to push them towards IBM Optim, Outerbay, > > > Solix, etc. but I don't know how to explain the shortcomings of their > > > method. Apparently the existing records don't have a nice date field > > > to identify old data and leaving it in place doesn't protect it from > > > damage or loss. > > > Export is a great tool for making logical backups that may be needed > > with the existing or next release of the database. However, there is > > no guarantee that an export dump file created today will be readable > > 20 years from today. > > > On the other hand a delimited or fixed position text extract file will > > almost surely be readable. Using delimited files also supports > > importing the data into a different database vendor product > > environment. After all can you be sure that business conditions will > > not have warranted conversion. The DDL for the target table and > > sqlldr control cards can be generated as part of the extract and used > > guide conversion if necessary. > > > Once extracted into delimited text files the data can be encrypted and/ > > or compressed using standard OS utilities. In the event of a non- > > compatible platform change the files can easily be extracted and > > uncompressed/decrypted on the existing platform, copied to the new > > one, and then re-compressed/encrypted as necessary. > > > For long-term archival where you may need to access the data nothing > > beets pain text. > > > IMHO -- Mark D Powell -- > > Thanks for the replies. The "periodic" nature of the process is also > a concern because we don't know how to keep good records of what is in > each snapshot. We can save the date of the export but who knows where > specific data is located, if we accumulate a few hundred exports over > the years. In some systems, logic for things like relations between tables and how things are defined are outside of the db. I agree that flat files are the best over the long term, but I've certainly seen minor point version application upgrades alone make archived data access difficult. Modern object types also can have issues. This is really a lot tougher than most people admit. The first step must be to be specific about what data needs such a long term recall. Even NASA has screwed it up, and not through lack of expertise. Some physical media will only last about 10 years, software obsolescence for some media may be even shorter. Heck, I've seen DLT's not even get it write, so to speak. You want to start with a mature technology. jg -- @home.com is bogus. http://www.signonsandiego.com/news/2010/may/18/hp-net-income-jumps-28-pct-raises-outlook/ |