Problem with selective transport of certain partitions of a patitioned table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Problem with selective transport of certain partitions of a patitioned table

  1. #1
    Join Date
    Oct 2004
    Posts
    10

    Problem with selective transport of certain partitions of a patitioned table

    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.
    Last edited by metello; 12-17-2004 at 08:19 AM.

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    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
    Last edited by mike9; 12-20-2004 at 04:42 AM.

  3. #3
    Join Date
    Oct 2004
    Posts
    10
    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.

  4. #4
    Join Date
    Oct 2004
    Posts
    10
    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.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Did you try transportable tablespace option?

    Tamil

  6. #6
    Join Date
    Oct 2004
    Posts
    10
    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?

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    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?

  8. #8
    Join Date
    Oct 2004
    Posts
    10
    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.
    Last edited by metello; 12-23-2004 at 04:33 AM.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    it's always funny hear

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

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by pando
    "its a production system but we dont have metalink access"
    Unlicenced??
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width