Home » RDBMS Server » Backup & Recovery » Is there any kind of "Transparent Data Archival" in Oracle (Oracle10g (soon 11g))
Is there any kind of "Transparent Data Archival" in Oracle [message #469716] Thu, 05 August 2010 11:29 Go to next message
orauser1
Messages: 5
Registered: August 2010
Junior Member
Hi

we are running a larger OLTP application and would like to archive some of the older data transparently. What I mean by this is that it would be nice if data that is older than say 5 years would go to a kind of archive data store. Thereby queries on the OLTP system should still be able to transparently access this data while querying the original tables.

There is a new 11g feature called 'Flashback Data Archive' which goes into this direction but it archives all updates and deletes (to allow to reconstruct the data as of any point in time), which is not quite what we need.

Do you know of any other feature or solution that might help to achieve this 'transparent data archival'?

Regards orauser1
Re: Is there any kind of "Transparent Data Archival" in Oracle [message #469729 is a reply to message #469716] Thu, 05 August 2010 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There is a new 11g feature called 'Flashback Data Archive' which goes into this direction but it archives all updates and deletes (to allow to reconstruct the data as of any point in time), which is not quite what we need.

Why? Explain in details what you want and why FDA does not fit your need.

Regards
Michel

[Updated on: Thu, 05 August 2010 11:52]

Report message to a moderator

Re: Is there any kind of "Transparent Data Archival" in Oracle [message #469779 is a reply to message #469729] Thu, 05 August 2010 15:23 Go to previous messageGo to next message
orauser1
Messages: 5
Registered: August 2010
Junior Member
Hi Michel

Thanks for the quick reply. FDA can be used to reconstruct transparently how the data looked at a given point in time (e.g. before something got deleted), right.
We do not need this 'point in time' feature. We just would like to offload the current OLTP application from old data that is seldom (but still sometimes) used. In such seldom cases the data should be available transparently, that is the existing queries should not have to be adapted (some performance degradataion would be acceptable for these infrequent queries). Does this help?
Re: Is there any kind of "Transparent Data Archival" in Oracle [message #469782 is a reply to message #469779] Thu, 05 August 2010 15:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use partitioning for this and archive partitions that are seldomly needed on cheaper device (I think this is the purpose otherwise why not keeping them in their current place).

Regards
Michel
Re: Is there any kind of "Transparent Data Archival" in Oracle [message #469788 is a reply to message #469782] Thu, 05 August 2010 15:47 Go to previous messageGo to next message
orauser1
Messages: 5
Registered: August 2010
Junior Member
Hi Michel

when I archive partitions they are not really anymore online available, are they?
Re: Is there any kind of "Transparent Data Archival" in Oracle [message #469841 is a reply to message #469716] Fri, 06 August 2010 02:10 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
May be create a history user and put all the history tables into it.
Periodically schedule updation as data get old.
Re: Is there any kind of "Transparent Data Archival" in Oracle [message #469846 is a reply to message #469841] Fri, 06 August 2010 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, I meant archive to a cheaper device, as I said.
Actually, it depends on your actual purpose, so tell us.

Regards
Michel
Re: Is there any kind of "Transparent Data Archival" in Oracle [message #469880 is a reply to message #469846] Fri, 06 August 2010 04:04 Go to previous messageGo to next message
orauser1
Messages: 5
Registered: August 2010
Junior Member
Hi Michel

Sorry, I am a bit confused. I think I am struggling to understand what you exactly mean by archiving. What do you have in mind exactly?
Re: Is there any kind of "Transparent Data Archival" in Oracle [message #469965 is a reply to message #469880] Fri, 06 August 2010 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I think I am struggling to understand what you exactly mean by archiving.

So am I.

Quote:
What do you have in mind exactly?

What do YOU have in mind exactly?

It is the third time I ask you for your actual purpose.
This is the last time. I will no more reply without it.

Regards
Michel
Re: Is there any kind of "Transparent Data Archival" in Oracle [message #470108 is a reply to message #469965] Sun, 08 August 2010 11:40 Go to previous messageGo to next message
orauser1
Messages: 5
Registered: August 2010
Junior Member
Hi Michel

sorry for struggling trying to explain what we want: having the situation that the OLTP database grew considerably makes handling (e.g. exports, etc) and costs suboptimal. So it would be great if we could reduce the actual data size and move the seldom used data (old data) onto a cheaper place (e.g. by using compression, cheaper storage, whatever). Note that the old data should be still transparently available so that queries that involve old data still return the old data (if it takes more time then this would be ok). Does this help?
Re: Is there any kind of "Transparent Data Archival" in Oracle [message #470109 is a reply to message #470108] Sun, 08 August 2010 11:49 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 05 August 2010 22:27
Use partitioning for this and archive partitions that are seldomly needed on cheaper device (I think this is the purpose otherwise why not keeping them in their current place).


Does this not answer the question?

"Archive" here means "move".

Regards
Michel
Previous Topic: RMAN : format specifier %F
Next Topic: open resetlog
Goto Forum:
  


Current Time: Thu Mar 28 06:10:38 CDT 2024