I am wondering if anyone has experimented with Oracle partitioning as a means of archiving data. We are looking into exercising the idea of partitioning tables into a read only tablespace instead of waiting for Baan archiving to be perfected. Any information will be greatly appreciated.

Not used Oracle Partitioning.

But what we have done in the past is to create new Archive Tablespaces within Oracle for "old companies".

For example, after the recent Euro Conversions, we preserved the pre-Euro Company data in special read-only tablespaces (Baan Companies).

Is your purpose for archiving to keep a read-only record of certain data? Or is the idea to eliminate old/unwanted data from the Production Companies? What is the root problem? Performance? Backup duration?

Splitting tables across tablespaces does not sound like the best idea. In fact, is this possible?

NB. For info, a useful feature of Oracle 8 (Recovery Manager), is that read-only tablespaces will be recognised as 'unchanged' and hence not backed up as part of the normal database backup. This is useful since backup time durations will decrease. But be aware that an original backup copy of the read-only datafiles must be kept safe for recovery purposes.

Hi Pete,

I cannot find give any hints on the partitioning subject as I have not used it before myself. More importantly however is the matter of using read-only tablespaces for archiving data. If you are going to use read-only tablespaces to store data you have to keep in mind that Baan has no notion of the fact that the data is read-only unless it was moved across using the Baan Archiving tools.

We have done a similar exercise in making archive companies (NOT using the Baan Archive tools) for EURO conversion purposes. We did not use partitioning but rather cloned a Baan company into a new, read-only tablespace.

2 major issues:

1) Backup the read-only tablespaces: using RMAN with Oracle 8.0.6 (assuming you are also using RMAN) will not backup those tablespaces except for the very first time. So it's important you saveguard your first backup or alternatively keep a logical export of the same data somewhere handy.

2) When changing the software objects of your Baan installation by installing data dictionary dumps, patches, development work etc, ultimately you might run into the situation that the package combination that is linked to your 'archive' company is changed and requires a data dictionary rebuild. In case this also may require a reorganize tables you are in trouble because Baan will try to update the tables, indexes and reference counters regardless whether they reside in a read-only or read-write tablespace. This will of course result in a fatal error.
The only way to circumvent this problem is to create a VRC layer of which you are sure it will not change and hence not require data dictionary rebuilds.

Bottomline is that you can use read-only tablespace with Baan but with adament care.

Hope the information is useful to you.

Those "archived" companies bring also other problems if they are not moved into their own package combinations:

As soon you install something that needs a reorganizations of the tables/creation of runtime with reorg, Baan will try to reorganize also those companies that are read-only in Oracle - this must be adressed as well - we just encountered problems with that (right patrick?).

Regarding using tables splitted by several tablespaces, yes, that is possible and several companies do it (keeping tools tables away mostly, or the financial tables). Just create the tablespaces in Oracle and then adapt your $BSE/lib/ora/ora_storage accordingly before creating the tables:

So, if you ever wondered what those '*' reffer to:

*:*:T:... or
*:*:I:...

1st '*' field: table(s) ! - you can use wildcards here :D
2nd '*' field: company

Thanks much for the replies and advice. As this is a long term project I will not be experimenting with it for some time. I will keep you posted on our results. The reason we would like to do something like this is to reduce backup/recovery time frame and reduce the size of our development environment (copies of production).
Is anyone using Baan archiving for either BaanIV or Baan ERP? We have both applications and have not been succesful in implementing archiving on either one.

Hi Pete,

If you want to reduce your backup window and/or volume, you could also consider using incremental backups. RMAN is very powerful with that.

About the archiving I have not much to tell but I suggest you make a new thread just about your arching question in the Tools Admin and Installation (http://www.baanboard.com/baanboard/forumdisplay.php?s=&forumid=2) forum