Re: Method for migrating schema from PROD to DEV

From: steve montgomerie <stmontgo_at_gmail.com>
Date: Thu, 5 Feb 2009 11:39:59 -0500
Message-ID: <5d4504220902050839h18dfaedco225d82b44b09a5bc_at_mail.gmail.com>

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>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 Thu Feb 05 2009 - 10:39:59 CST