RE: Method for migrating schema from PROD to DEV
Date: Fri, 6 Feb 2009 06:32:58 -0500
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
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
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
- Export Schema in PROD
- Export Schema in DEV (for backup)
- Drop user in DEV
- Create basic user in DEV
- Import user in DEV
- Verify object count - PROD vs DEV
- Compile schema
Does this sound right?
P Consider the environment. Please don't print this e-mail unless you really need to.Received on Fri Feb 06 2009 - 05:32:58 CST