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.

On Mon, 17 May 2010 15:43:31 -0700, keith.michaels@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

Mladen Gogala <gogala.mladen@gmail.com> writes:

> On Mon, 17 May 2010 15:43:31 -0700, keith.michaels@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@netfront.net ---

"keith.michaels@gmail.com" <keith.michaels@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

On May 18, 2:43=A0am, "keith.micha...@gmail.com"
<keith.micha...@gmail.com> wrote:
> I need to know why exporting a database periodically is not a suitable
> method for preserve access to historical data. =A0A customer with 10-20
> year retentions is using this to "preserve access" for compliance
> reasons. =A0I would like to push them towards IBM Optim, Outerbay,
> Solix, etc. but I don't know how to explain the shortcomings of their
> method. =A0Apparently 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

On May 17, 6:43=A0pm, "keith.micha...@gmail.com"
<keith.micha...@gmail.com> wrote:
> I need to know why exporting a database periodically is not a suitable
> method for preserve access to historical data. =A0A customer with 10-20
> year retentions is using this to "preserve access" for compliance
> reasons. =A0I would like to push them towards IBM Optim, Outerbay,
> Solix, etc. but I don't know how to explain the shortcomings of their
> method. =A0Apparently 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 --

On May 18, 6:26=A0am, Mark D Powell <Mark.Powe...@hp.com> wrote:
> On May 17, 6:43=A0pm, "keith.micha...@gmail.com"
>
> <keith.micha...@gmail.com> wrote:
> > I need to know why exporting a database periodically is not a suitable
> > method for preserve access to historical data. =A0A customer with 10-20
> > year retentions is using this to "preserve access" for compliance
> > reasons. =A0I would like to push them towards IBM Optim, Outerbay,
> > Solix, etc. but I don't know how to explain the shortcomings of their
> > method. =A0Apparently 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. =A0However, 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. =A0Using delimited files also supports
> importing the data into a different database vendor product
> environment. =A0After all can you be sure that business conditions will
> not have warranted conversion. =A0The 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. =A0 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.

On May 18, 10:45=A0am, "keith.micha...@gmail.com"
<keith.micha...@gmail.com> wrote:
> On May 18, 6:26=A0am, Mark D Powell <Mark.Powe...@hp.com> wrote:
>
>
>
> > On May 17, 6:43=A0pm, "keith.micha...@gmail.com"
>
> > <keith.micha...@gmail.com> wrote:
> > > I need to know why exporting a database periodically is not a suitabl=
e
> > > method for preserve access to historical data. =A0A customer with 10-=
20
> > > year retentions is using this to "preserve access" for compliance
> > > reasons. =A0I would like to push them towards IBM Optim, Outerbay,
> > > Solix, etc. but I don't know how to explain the shortcomings of their
> > > method. =A0Apparently the existing records don't have a nice date fie=
ld
> > > 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. =A0However, 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. =A0Using delimited files also supports
> > importing the data into a different database vendor product
> > environment. =A0After all can you be sure that business conditions will
> > not have warranted conversion. =A0The 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. =A0 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. =A0The "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. =A0We 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-r=
aises-outlook/