cancel
Showing results for 
Search instead for 
Did you mean: 

Howto - productive & archive db?

Former Member
0 Kudos

Hi,

perhaps someone can give me some tips on creating this special database setup:

Tables in DB:

- some tables with parameter descriptions (small - only a handfull of new entries in a year)

- 2 tables with measurement values (parameterid / datetime / value) - these tables are growing quite fast (1 gig/ month)

For better handling of the Database only the newest data (e.g. last 2 years) should be inside. Older data in the measurement tables should be deleted in regular intervalls.

But we also want to keep a complete (archive) Database for later reporting etc.

So my database setup should look like this:

- MaxDB1 (productive db with only newest data)

-- older data will be deleted manualy once a year

- MaxDB2 (archive/standby db with complete data)

-- newest data from MaxDB1 should be imported once a day/week

Could this setup be achieved with MaxDB tools?

My first thought was like this:

- MaxDB1 (productive / online)

-- create fullbackup

-- enable autologbackup

- MaxDB2 (standby / admin)

-- setup as standby database for MaxDB1

-- restore fullbackup (only the first time) from MaxDB1

-- restore each autologbackup from MaxDB1

-- create fullbackup (each day)

- MaxDB3 (archive / online)

--restore fullbackup from MaxDB2 (each day)

But how can I delete old data in MaxDB1 without logging this and importing the delete process to MaxDB2?

When I disable the "Redo log management" for the deletion process the log history will be lost.

Or is there any chance for this?

Other options would be:

- Using the loader (I have no expirience in this9

- Sync manager ? (as I read it will be dropped after MaxDB 7.8?)

Any help would be appreciated.

Thanks in advance,

Ronald

Accepted Solutions (0)

Answers (2)

Answers (2)

lbreddemann
Active Contributor
0 Kudos

> But how can I delete old data in MaxDB1 without logging this and importing the delete process to MaxDB2?

You cannot leave out transactions from the redo stream as this will make recovery from it impossible.

> When I disable the "Redo log management" for the deletion process the log history will be lost.

This is a very special function - if you're not really sure how it works and what it does, just don't touch it!

> - Using the loader (I have no expirience in this9

Well, it's documented, the documentation is available..., there are even step-by-step examples....

> - Sync manager ? (as I read it will be dropped after MaxDB 7.8?)

Yes, it will be desupported. Don't go for this option.

regards,

Lars

Former Member
0 Kudos

Thanks for the answer. I will go the loader way.

grassu

lbreddemann
Active Contributor
0 Kudos

> - some tables with parameter descriptions (small - only a handfull of new entries in a year)

> - 2 tables with measurement values (parameterid / datetime / value) - these tables are growing quite fast (1 gig/ month)

> For better handling of the Database only the newest data (e.g. last 2 years) should be inside. Older data in the measurement tables should be deleted in regular intervalls.

What activity does not work as good with the large all-data-is-in-it table as it does with the cut-down version?

> But we also want to keep a complete (archive) Database for later reporting etc.

You need your data in your database. So why don't you keep it there?

MaxDB is perfectly capable of handling large data sets.

And, if I understand the brief description of your data design, your large table will be heavy right growing.

So all your database activity will work on the outer right part of the B*tree, which means: most pages won't even be touched in the daily work.

If you say it's for reporting, it may be an option to aggregate older data to get rid of unnecessary details data.

That way you can reduce some space requirement and still be able to do your reporting.

Another option:

Implement a do-it-yourself partitioning via a view:

That way you can have many smaller tables and access the full dataset over a UNION ALL view.

This could even be automated with an SQL script...

> So my database setup should look like this:

> - MaxDB1 (productive db with only newest data)

> - MaxDB2 (archive/standby db with complete data)

> Could this setup be achieved with MaxDB tools?

Sure.

One way to do that would be to use the loadercli in transport mode.

To your "first thought": Why don't you backup your MaxDB1 in the first place?

Edited by: Lars Breddemann on Sep 1, 2009 9:57 PM