RE: Method for migrating schema from PROD to DEV

From: Sais, Gene <Gsais_at_mypalmbeachclerk.com>
Date: Fri, 6 Feb 2009 06:32:58 -0500
Message-ID: <1F5BB50762DC2048B9ADD414A336E1C598F31DC3C3_at_CLERKMAIL07.Clerk.local>

This how I used to refresh development. I have been converted to RMAN duplicate, copies a 150gb Peoplesoft db in 30 mins. The only caveat is getting it to work with RAC, which I have scripted.

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of steve montgomerie Sent: Thursday, February 05, 2009 11:40 AM To: JEREMY.SHEEHAN_at_fpl.com
Cc: oracle-l_at_freelists.org
Subject: Re: Method for migrating schema from PROD to DEV

We do this often for PeopleSoft

I drop all user tables and then do a drop user cascade, for some reason this is faster than simply drop user cascade

We do a datapump export with this
EXCLUDE=STATISTICS PARALLEL=16 Then again on import we specify PARALLEL=16. You can't specify EXCLUDE=STATISTICS if you have already taken the export with no stats.

Takes us about 3 hours to load 500 gb and then after we gen stats

Here's some code that may help you with tablespaces to make sure the target has enough space

set serveroutput on
DECLARE
v_source_ts_size NUMBER:=0;
v_target_ts_size NUMBER:=0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Comparing ts storage parameters from X to Y');

    DBMS_OUTPUT.PUT_LINE('========================================================');

    FOR i in (SELECT distinct tablespace_name from dba_data_files)     LOOP
        SELECT sum(bytes) into v_source_ts_size FROM dba_data_files WHERE tablespace_name = i.tablespace_name;

        SELECT sum(bytes) into v_target_ts_size FROM dba_data_files_at_<db link here> WHERE tablespace_name = i.tablespace_name;

            IF v_target_ts_size < v_source_ts_size THEN
                dbms_output.put_line ('Please add ' || (v_source_ts_size - v_target_ts_size)/1024/1024 || ' mb to the ' ||  i.tablespace_name || ' tablespace');
            END IF;

        v_source_ts_size :=0;
        v_target_ts_size :=0;

    END LOOP; END;
/
On Mon, Nov 24, 2008 at 4:12 PM, SHEEHAN, JEREMY <JEREMY.SHEEHAN_at_fpl.com<mailto:JEREMY.SHEEHAN_at_fpl.com>> wrote:

Does anyone have a proven method for migrating a schema from PROD to DEV? I plan on using export/import, but I've got a few reservations about the migration steps.

I think the best method would be to do the following

1. Export Schema in PROD
2. Export Schema in DEV (for backup)
3. Drop user in DEV
4. Create basic user in DEV
5. Import user in DEV
6. Verify object count - PROD vs DEV
7. Compile schema

Does this sound right?

Jeremy

P Consider the environment. Please don't print this e-mail unless you really need to.

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

Received on Fri Feb 06 2009 - 05:32:58 CST