This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Mar 4, 2013 12:37 PM by ADaniel RSS

Duplicate huge schema

693765 Newbie
Currently Being Moderated
Hi all,
I have a schema that contains 2000 GB of data.
What is the best practice to duplicate the schema?
Thanks
dyahav
  • 1. Re: Duplicate huge schema
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post details of OS and database versions. Are you wanting to make en exact copy of this schema into another schema in the same database ? If so, why ? Pl elaborate

    HTH
    Srini
  • 2. Re: Duplicate huge schema
    693765 Newbie
    Currently Being Moderated
    Hi,
    Oracle 11g R2
    os - windows 2008

    I have a schema in a production environment and I want to copy the schema into a testing environment.
    Thanks
    dyahav
  • 3. Re: Duplicate huge schema
    TSharma-Oracle Guru
    Currently Being Moderated
    If you are moving only 1 schema in many schemas. You can use datapump utility to move the data. Make sure you use "'PARALLEL" parameter while doing expoert and import. Datapump is much faster than conventional export or import.

    Here is the link for your help.

    http://www.morganslibrary.org/reference/datapump.html
  • 4. Re: Duplicate huge schema
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    As noted, datapump is one option

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/part_dp.htm#i436481

    HTH
    Srini
  • 5. Re: Duplicate huge schema
    Ranjith Explorer
    Currently Being Moderated
    Hi dyahav,

    Is your Testing env also the same machine or different ?
    If different machine, is same like a production machine (Same OS, Same Oracle version ?)

    Basically data dump is much slower method. (For 2 TB ,almost will it take 1 day to export and import . correct me if im wrong) .

    is there only one schema in the production machine or multiple ?
    If multiple , total schema size is 2000 GB or any one schema size is 2000GB ?

    You want to copy the data in online or offline ?

    Thanks,
    Ranjith
  • 6. Re: Duplicate huge schema
    693765 Newbie
    Currently Being Moderated
    Hi all,
    Thanks for the quick responses and great links.

    I have to copy one schema of 2000 GB in the same machine and I can do it over a night (which means that it can be issued offline)
    Actually, I have no experience in such schema size and I have to estimate the time required to do that and to find the best technique.

    1. Do you know how to estimate the time required?
    2. Is there any other technique to copy a schema?
    3. If I have to copy the schema to other machine (identical HW and SW), is there other technique?

    Thanks!
    dyahav
  • 7. Re: Duplicate huge schema
    Ranjith Explorer
    Currently Being Moderated
    Hi dyahav,

    1. we can't estimate the exact time for any methods.(It depends on various factors like network spped, HW/SW configurations etc.) But datapump will take one day or more to complete 2000 GB of data (to export and import).

    2,3. Yes . "Transportable Tablespace" . Using this method , you can export only metadata from source schema and rest of the data (tablespace) we can move just plug and play method.

    Thanks,
    Ranjith
  • 8. Re: Duplicate huge schema
    693765 Newbie
    Currently Being Moderated
    Hi,

    As mentioned, export and import of 2000 GB will take around one day. It means that 4000 GB will take two days? Is it linear?
    From your experience, parallel export and import improve the performance?
    Does anybody have a link to a benchmark?
    Thanks
    dyahav
  • 9. Re: Duplicate huge schema
    TSharma-Oracle Guru
    Currently Being Moderated
    Hi, It really depends upon a H/W configuration. It is but obvious that if you make things parallelize it will share the workload and provide the output much faster. I can only tell that we have Sun M4000 and database size is 1400 Gb and datapump takes 4.5 hours to export this 1400 Gb of database. Make sure when you Import , avoid importing Indexes and create all indexes after th eimport with parallel and nologging option.
  • 10. Re: Duplicate huge schema
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Does this schema you want to export have a dedicated/exclusive tablespace (i.e. not shared with any other schemas) ? If so, and you are using EE version of the database, transportable tablespace could be another option.

    http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces013.htm

    HTH
    Srini
  • 11. Re: Duplicate huge schema
    693765 Newbie
    Currently Being Moderated
    Thanks!
  • 12. Re: Duplicate huge schema
    Richard Harrison . Expert
    Currently Being Moderated
    You could just use rman to duplicate the database?

    Is there more than one scheme you have to worry about?

    Regards,
    Harry
  • 13. Re: Duplicate huge schema
    693765 Newbie
    Currently Being Moderated
    Hi,

    I have only one schema to copy from.
    What are the advantages of using RMAN?
    Thanks
    dyahav
  • 14. Re: Duplicate huge schema
    Richard Harrison . Expert
    Currently Being Moderated
    It saves rebuilding the indexes which is generally the longest part of the import process. The duplicate should be as fast as you can copy the files at the oslevel.

    Cheers,
    Harry
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points