Why export is not a good archiving tool
in [Oracle]
|
From: keith.michaels on 17 May 2010 18:43 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.
From: Mladen Gogala on 17 May 2010 21:48 On Mon, 17 May 2010 15:43:31 -0700, keith.michaels(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. As other people will undoubtedly tell you, export is a snapshot tool, not a backup tool. The difference between a snapshot and a backup is that backup makes it possible to do a complete recovery of all committed transactions, up to the point of failure. Snapshot, on the other hand, allows you to restore the state of the database at the moment that the snapshot was taken. You can not do recovery from export. That means that export makes it possible for you to store a logical state of your database at some important moment, like the end of the financial year. To recover from a backup, you need the backup itself plus all of the archived logs from the moment of the backup to the moment of the recovery. No archive logs means that there is no recovery. You can also do cold backups, which are taken while the database is shut down, and which can serve both as a snapshot and a basis for recovery but you still need all of the archives. Export, being a logical snapshot tool, allows you to export a single table or schema, which backup does not. Backup knows nothing of tables, indexes or schemas. Backup does data files, it's a physical tool. It's 9:45 PM, I am getting ready to go to bed, this is as much as I can type this late. There are several good books about Oracle backup, all of them written by Robert Freeman. Please, check them out, they are very instructive. -- http://mgogala.byethost5.com
From: galen_boyer on 17 May 2010 22:42 Mladen Gogala <gogala.mladen(a)gmail.com> writes: > On Mon, 17 May 2010 15:43:31 -0700, keith.michaels(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. [...] > export makes it possible for you to store a logical state of your database at > some important moment, like the end of the financial year. Seems to me that export sort of solves their issue. They aren't looking for a backup for recovery, but instead, they are looking to be able to go back to a point in time and access some data for compliance purposes. That point in time is very analogous to "like the end of the financial year". -- Galen Boyer --- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Tim X on 18 May 2010 01:47 "keith.michaels(a)gmail.com" <keith.michaels(a)gmail.com> writes: > 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. L:ike most things, there is no hard rules here. Using export in some situations would be fine. It will depend on what you need, how often you need it and how quickly it needs to be made available when it is needed. What export is NOT good for is ding regular backups. However, it may be fine for ad hoc snapshots, depending on database size, what you plan to do with the snapshot and your environment. For backups, there are far better solutions. It is also likely that if we are talking a recent version of Oracle, running on enterprise hardware with enterprise level storage that there are much better solutions already provided by Oracle and whatever storage technology you are using. It is very likely that a 'native' Oracle solution will be far easier to integrate into their business processes than purchasing another solution from a 3rd party. I also wouldn't be surprised to find the company is already paying for that technology, so better to look there before adding yet another vendor and license nightmare. Tim -- tcross (at) rapttech dot com dot au
From: Vladimir M. Zakharychev on 18 May 2010 05:49
On May 18, 2:43 am, "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. As others pointed out, exports are no substitute for proper backups. However they might do for data retention policy compliance if done properly: taken in CONSISTENT mode, labeled with a timestamp, signed with a public key or at least SHA-1 hash and stored on durable WORM media, like DVD-R, in several vaults across the country. ;) The good thing about exports is that you can do partial exports (only the data you want) and partial imports (just the tables you want,) so if you want to dig for just a few rows from a couple of tables you might be able to restore them pretty fast into any available database whereas doing full database restore might take much longer and will require you to create new Oracle instance using Oracle software version that was in use back then or at least backwards-compatible with it (imagine you need to do this 15 years later - you will need to find proper Oracle software distribution and then find a compatible OS that can run it on compatible hardware or VM.) However, considering that export/import functionality evolves over time, too (EXP/IMP are already deprecated in favor of DataPump for example,) you might be even better off taking data dumps into some database-independent format (like flat files that could be loaded using any bulk-loading tool into any database that would be in use 15 years from now,) compressing, encrypting and signing these dumps to protect them from tampering with or unauthorized access and storing them on reliable WORM media. Not sure it's going to be cheaper that IBM Optim, Solix or HP IAP, and certainly not that convenient, but it's nevertheless an option. My $0,02. Regards, Vladimir M. Zakharychev N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com |