Problem with selective transport of certain partitions of a patitioned table

[metello]

Hi,

Scenario:
Productive Enviroment: 4 very large tables partitioned by range(timestamp month). Partitions resides each one alone in a distinct tablespace. Other tables with master data.

Test Enviroment:
I need only two or three months from partitioned tables + master data tables.
I'm trying to avoid export/import sequence, using a daily phisically cold backup on productive enviroment.

Test:
I copied all datafiles needed (master data+two partitions) + control files + every action to clone the productive instance on Test environment.

DB mount (not open)
alter db datafile xxx oflline drop (for missing datafiles)
Alter db open

At this point I tryied:
alter table drop partition (for missing partitions)
--> Error: file xx cannot be read

drop tablespace xxx including contents
--> Error: A partitioned table has partitions in different tablespaces.

May someone help me?
Is there other way to restore

Thanks.

[mike9]

Hi,

I'm working on Oracle 9.2 EE and had recently the same problem.
The solution my dba found was something like:

1) set the tablespaces containing the partition you dont want to copy to readonly
2) make a full backup (RMAN) skiping the read only tbs
3) duplicate (RMAN) the backup and skip the read only tbs
4) drop the local indexes
5) drop the invalid partitions
6) drop tbs
6) recreate the indexes


You may have a look at:
http://metalink.oracle.com/metalink/...&p_id=182648.1
http://metalink.oracle.com/metalink/...&p_id=270094.1

PS: next time please specify the exact error number you get

[metello]

Originally posted by mike9
Hi,

I'm working on Oracle 9.2 EE and had recently the same problem.
The solution my dba found was something like:

1) set the tablespaces containing the partition you dont want to copy to readonly

I cannot do it, We're talking about a productive environment.

2) make a full backup (RMAN) skiping the read only tbs

I cannot do it, I can only use the daily scheduled cold backup

3) duplicate (RMAN) the backup and skip the read only tbs
4) drop the local indexes
5) drop the invalid partitions
6) drop tbs
6) recreate the indexes


You may have a look at:
http://metalink.oracle.com/metalink/...&p_id=182648.1
http://metalink.oracle.com/metalink/...&p_id=270094.1

I've not access to metalink... Sorry.

Thank you for your interest...

m.

[metello]

The error codes:

At command "alter table x drop partition y":

ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: 'xxxxx.dbf'

At command "drop tablespace z including contens"

ORA-14404: partitioned table contains partitions in a different tablespace

Thanks in advance.

[tamilselvan]

Did you try transportable tablespace option?

Tamil

[metello]

Originally posted by tamilselvan
Did you try transportable tablespace option?

Tamil

I'm looking documentation for it, my impression is that it's necessary to run an exchange partition command with a table to transport the tablespace. Is it not right?

[mike9]

You have a productive system but no access to metalink?

You can shut down your DB for cold backups but can't set temporarily tablespaces to read only?

[metello]

Originally posted by mike9
You have a productive system but no access to metalink?

You can shut down your DB for cold backups but can't set temporarily tablespaces to read only?

Do you want to help me to solve the problem or are you investigating about my company?

I have not a productive system, the company I'm working to has it. Someone in the company can have access to metalink, I cannot (I hope you'll not want to know why)

I cannot shutdown the database (that isn't mine), the database is included in a batch backup strategy, I cannot modify it.

Regards.

[pando]

it's always funny hear

"its a production system but we dont have metalink access"

[abhaysk]

Originally posted by pando
"its a production system but we dont have metalink access"

Unlicenced??