Re: [SPAM] Re: cloning subset with RMAN and partitioned tables
- From: Jack van Zanen <jack@xxxxxxxxxxxx>
- To: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- Date: Thu, 4 Feb 2010 10:49:28 +1100
Hi All,
I had previously tried to exchange the partitions but I had to change this
to subpartitions
Create normal tables for every subpartition including the index based on the
structure of the partition table
exchange every subpartition
drop tablespaces that were not cloned
All up very simple and quick.
Jack
On 4 February 2010 05:32, Kellyn Pedersen <kjped1313@xxxxxxxxx> wrote:
> With as much mail that is going through, not sure if you got an answer
> for this one yet. I've had this happen to me in the past when someone
> created a partition for one of our tables that is recovered for our mart in
> the wrong tablespace in the primary system.
>
> Do you have global indexes on this partitioned table? If so, you will need
> to pull the ddl for them, drop them, (yes, I know, it's painful, I wrote a
> whole post in my blog on this one titled "dumb dba trick #8006" due to the
> amount of pain...:)) then you'll be able to drop the partitions, recreate
> the index(es) and go from there to cleaning up afterwards.
>
> Hope this helps!
>
>
> Kellyn Pedersen
> Multi-Platform DBA
> I-Behavior Inc.
> http://www.linkedin.com/in/kellynpedersen
> www.dbakevlar.blogspot.com
>
> "Go away before I replace you with a very small and efficient shell
> script..."
>
>
> --- On *Tue, 2/2/10, Jack van Zanen <jack@xxxxxxxxxxxx>* wrote:
>
>
> From: Jack van Zanen <jack@xxxxxxxxxxxx>
> Subject: Re: cloning subset with RMAN and partitioned tables
> To: "Tony van Lingen" <tony_vanlingen@xxxxxxxxxxxxxxxxxxxxx>,
> oracle-l@xxxxxxxxxxxxx
> Date: Tuesday, February 2, 2010, 9:18 PM
>
>
> Hi Tony,
>
> Thanks
>
> Tried that since sending the mail, but it complaints about being a
> composite partition and is unable to exchange partition.
>
> Jack
>
> On 3 February 2010 15:14, Tony van Lingen <
> tony_vanlingen@xxxxxxxxxxxxxxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=tony_vanlingen@xxxxxxxxxxxxxxxxxxxxx>
> > wrote:
>
>> Just a thought, how about exchanging the existing partitions with empty
>> tables, then dropping and re-creating the partitioned table and exchange the
>> tables with partitions again?
>>
>> Cheers,
>> Tony
>>
>> Around 3/02/2010 1:42 PM, Jack van Zanen said:
>>
>> Hi,
>>
>>
>> I have the following issue
>>
>> I have been asked to clone a subset of a very large database. Sofar
>> nothing tricky and this works fine,
>>
>> however...this database has a partitioned table with sub partitions spread
>> over multiple tablespaces. some of which have not been cloned across as the
>> requirement was the last few months worth of data.
>>
>> RMAN clone command was succesful till the last bit where it tried to drop
>> the tablespaces that were skipped. After that the database opened fine but
>> subsequent selects on the table complain about missing datafiles
>> (understandably) but I have no idea how to solve the problem.
>>
>> I have tried offlining the tablespace (no error but does not solve the
>> problem)
>> I have tried dropping the partition "ORA-14268: subpartition 'SYS_SUBP437'
>> of the partition resides in offlined tablespace"
>> I have tried dropping the sub partitions but get error ORA14616 table
>> not partitioned by List method"
>> I have tried offline the datafiles that are missing, but this does not
>> solve the problem (mount and open mode).
>> I have tried offline drop the datafiles that are missing, but this does
>> not solve the problem (mount and open mode).
>> I have tried to recreate the controlfile without the missing datafiles, no
>> luck
>>
>> Any other suggestions?
>>
>> --
>> Jack van Zanen
>>
>> -------------------------
>> This e-mail and any attachments may contain confidential material for the
>> sole use of the intended recipient. If you are not the intended recipient,
>> please be aware that any disclosure, copying, distribution or use of this
>> e-mail or any attachment is prohibited. If you have received this e-mail in
>> error, please contact the sender and delete all copies.
>> Thank you for your cooperation
>>
>>
>
>
> --
> Jack van Zanen
>
> -------------------------
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation
>
>
>
--
Jack van Zanen
-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation
Other related posts:
- » Re: [SPAM] Re: cloning subset with RMAN and partitioned tables - Jack van Zanen
