Home » RDBMS Server » Performance Tuning » Re-creating partitioned tablespaces from backup.
Re-creating partitioned tablespaces from backup. [message #235909] Tue, 08 May 2007 06:52 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I have a table which is groving rapidly. which has a column Date.

I've partitioned this table monthwise into different tablespace.

We require last one years data and I've made the history data i.e. beyond one year as readonly(tablespace readonly).

If I take a backup and remove the history tablespace, in future if I require this removed data can I restore datafiles and recreate those partitioned tablespace and make the data available.

Please suggest me how to do this. Any specific document to this will help me.

Brayan.

Report message to a moderator

Re: Re-creating partitioned tablespaces from backup. [message #235919 is a reply to message #235909] Tue, 08 May 2007 07:23 Go to previous messageGo to next message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Hi,
As long as you have a backup of the readonly datafile you should be safe.But You were mentioning that you are going to remove (drop?) the history tablespace.Ideally oracle will not allow you to do a drop of this tablespace but will raise an error
"ORA-14404: partitioned table contains partitions in a different tablespace".

so you need to do an exchange partitions of the table so that the tablespace can be dropped.

Now before doing this take a backup of the control file and if you ever want to recover you may need to rollforward from here so that all your readonly files are included as well.

Thanks

Report message to a moderator

Re: Re-creating partitioned tablespaces from backup. [message #235926 is a reply to message #235909] Tue, 08 May 2007 07:35 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Thanks Sriram for for your detailed explanation.

My requirement is to purge the history data. That is why I thought of dropping a partition.

Thanks.

Report message to a moderator

Re: Re-creating partitioned tablespaces from backup. [message #236082 is a reply to message #235909] Wed, 09 May 2007 00:29 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Is there a better way to purge the history data from a tablespace( I mean entire partitioned tablespace).

Regards,
Ronald.

Report message to a moderator

Re: Re-creating partitioned tablespaces from backup. [message #236128 is a reply to message #236082] Wed, 09 May 2007 02:43 Go to previous messageGo to next message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Yes , The oracle way for this is

"Oracle Information Lifecycle Management" more tailored for oracle 10g but going through the link below you can tailor make it for your needs.

http://www.oracle.com/technology/deploy/ilm/index.html

Report message to a moderator

Re: Re-creating partitioned tablespaces from backup. [message #236195 is a reply to message #235909] Wed, 09 May 2007 04:45 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Sriram,

The ILM document talks about 10g. My database 9.2.0.7 on HP-UX.

So, In 9i how can I manage the same. Any other solution will also help me.

Brayan

Report message to a moderator

Re: Re-creating partitioned tablespaces from backup. [message #236202 is a reply to message #236195] Wed, 09 May 2007 05:08 Go to previous message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Hi ,

I had mentioned earlier as well that this more of a 10g technology , but all these are available in 9i as well ..you need to rework the same ..this is only a technology term that is bundled with 10g , you can use this in 9i as well , read the doc thoroughly which make things clear and you can impelement the same in 9i as well.
Try it out

Thanks

Report message to a moderator

Previous Topic: Long Running SQL - Oracle
Next Topic: TUNING
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 16 02:15:29 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.