Hello,

I'm looking for a reliable software solution for archiving a production
database to a backup database.

the main goal of the archiving tool :
Without archiving, the production database would have a total of 100Gb/year
over 300 tables.
The customer only wants the data of the last 3 months in the production
database so that it can be easily backup each night.
Data older then 3 months should be transferred once a month to an archive
database.
This archive database should have the same structure as the production
database and should also be on-line 24h a days.
Data in the archived database should be kept for 30 years.

We have a lot of administration tables (250 out of 300) which are only
updated, no records are added. The other 50 tables (used for registrations)
have lots of inserts in them.
The administration tables should be synchronized from the production DB to
the archive DB, while the registration tables should be transferred based on
a date.

Which are the possibities ? Any good 3rd party solutions ? And without 3rd
party solution ?

When replying, please CC to my email account
--
Greetz,
Frederic Hoornaert

<fhoornaert (AT) hotmail (DOT) com> wrote

You could use partitions to partition the data. Your biggest problem will
be keeping the schema stable for 30 years.
Jim

Hello Frederic,

Princeton softech is your 3rd party vendor. Tool is very good but
expensive and complex.

As a oracle only solution here is the how you can design your archiving
strategy:

1) once a month create new tablespaces (bets is one for data and one for
indexes) that contains all tables to be archived (or you can create 12 sets
once a year). Good naming standard for objects is "<table_base_name>_0104",
for example

2) Every month during maintenance window place tablespaces to archive into
read only mode and move them to archive database using transportable
tabespaces approach (basically export metadata, copy datafiles and import
metadata)

3) If you still need production access to the archive tables - create
database links and synonyms for access

Partition approach can be used, but it complicates ts transport, since now
you'd have to exchange partition segment with the table segment before the
transport.

Please, let us know if you'd have additional questions on subject

Regards,

Support,
DBA Infopower
www.dbainfopower.com

Makers of Statspack Research tool - performance explorer-i / Statspack

<

Hello Frederic,

Princeton softech is your 3rd party vendor. Tool is very good but
expensive and complex.

As a oracle only solution here is the how you can design your archiving
strategy:

1) once a month create new tablespaces (bets is one for data and one for
indexes) that contains all tables to be archived (or you can create 12 sets
once a year). Good naming standard for objects is "<table_base_name>_0104",
for example

2) Every month during maintenance window place tablespaces to archive into
read only mode and move them to archive database using transportable
tabespaces approach (basically export metadata, copy datafiles and import
metadata)

3) If you still need production access to the archive tables - create
database links and synonyms for access

Partition approach can be used, but it complicates ts transport, since now
you'd have to exchange partition segment with the table segment before the
transport.

Please, let us know if you'd have additional questions on subject

Regards,

Support,
DBA Infopower
www.dbainfopower.com

Makers of Statspack Research tool - performance explorer-i / Statspack

<

Hello Frederic,

Princeton softech is your 3rd party vendor. Tool is very good but
expensive and complex.

As a oracle only solution here is the how you can design your archiving
strategy:

1) once a month create new tablespaces (bets is one for data and one for
indexes) that contains all tables to be archived (or you can create 12 sets
once a year). Good naming standard for objects is "<table_base_name>_0104",
for example

2) Every month during maintenance window place tablespaces to archive into
read only mode and move them to archive database using transportable
tabespaces approach (basically export metadata, copy datafiles and import
metadata)

3) If you still need production access to the archive tables - create
database links and synonyms for access

Partition approach can be used, but it complicates ts transport, since now
you'd have to exchange partition segment with the table segment before the
transport.

Please, let us know if you'd have additional questions on subject

Regards,

Support,
DBA Infopower
www.dbainfopower.com

Makers of Statspack Research tool - performance explorer-i / Statspack

<