RE: Cloning 21 GB Table - Use of Snapshot ? - Review Approach please

We have performed Multi-Terabyte Database migrations across OS's using = the snapshot methodology. We have mixed in insert append and exp/imp = depending on the types of objects and layered snapshots over the top of = whatever data movement methodology is chosen.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of VIVEK_SHARMA Sent: Monday, March 21, 2005 9:23 AM
To: tim_at_evdbt.com; gorbyx_at_gmail.com; tanel.poder.003_at_mail.ee; = jonathan_at_jlcomp.demon.co.uk; jclarke_at_centroidsys.com; = oracle-l_at_freelists.org; spatenau_at_gmail.com Subject: RE: Cloning 21 GB Table - Use of Snapshot ? - Review Approach = please

Hi Folks

Another Idea suggested by some colleagues here:-

To create a snapshot between the source & target table of type complete And then alter the snapshot to type FAST & once the target table has exactly
the same data as source table.

How does this approach (of creating the target clone via snapshot) Compare against the "insert /*+ append nologging parallel(x, 4) */ into <target>" approach on the following points:-

   impact on production performance,
   time taken for completion,
   redo, undo generation ,
   other issues, if any

Awaiting your response, Thanks again.

P.S. Sending to individual IDs too as this e-mail posting seems to have failed a few times on the List.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman Sent: Friday, March 11, 2005 4:28 AM
To: oracle-l_at_freelists.org
Subject: Re: Cloning 21 GB Table - Review Approach please

Will Arhive Redo Log be generated by the INSERT /*+ APPEND */ Command if the Target Table has been PRE-Created with NOLOGGING?

No. Won't generate any "undo" either.

Any possibility of giving Hint for NOLOGGING in the INSERT Command ?

Yes. /*+ APPEND NOLOGGING */

Why not use the built-in parallelism of Oracle? Unless you've left PARALLEL_MAX_SERVERS at the default of 5, you can add the PARALLEL hint To both the INSERT and SELECT clauses. You might also want to precede with
ALTER SESSION ENABLE PARALLEL DML and (of course) succeed with a COMMIT...     alter session enable parallel dml;
    insert /*+ append nologging parallel(x, 4) */ into <target> x     select /*+ full(y) parallel(y, 4) */ ... from <source> y;     commit;

No faster way to clone. Your mileage may vary, but if you have a decent sized box it shouldn't take more than 60-90 mins, tops...

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 23 2005 - 05:53:37 CST