RE: Method for migrating schema from PROD to DEV
- From: "Sais, Gene" <Gsais@xxxxxxxxxxxxxxxxxxxx>
- To: "'stmontgo@xxxxxxxxx'" <stmontgo@xxxxxxxxx>, "'JEREMY.SHEEHAN@xxxxxxx'" <JEREMY.SHEEHAN@xxxxxxx>
- 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of steve montgomerie
Sent: Thursday, February 05, 2009 11:40 AM
To: JEREMY.SHEEHAN@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@<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@xxxxxxx<mailto:JEREMY.SHEEHAN@xxxxxxx>> 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.
Other related posts:
