Home » RDBMS Server » Server Administration » Refresh Production data to Development Schema (12.0.1.0.1,Oracle Linux 6)
Refresh Production data to Development Schema [message #630018] Thu, 18 December 2014 12:14 Go to next message
preet_kumar
Messages: 199
Registered: March 2007
Senior Member
Schema Refresh from Production to Development

1a. Create the user TEST and give all grants including (EXP_FULL_DATABASE,IMP_FULL_DATABASE)

2a.
impdump parfile=testimportfull.par

userid=test/test
network_link=prodlink
directory=importdir
LOGFILE=fullimport.log
CONTENT=all
EXCLUDE=STATISTICS
EXCLUDE=USER
SCHEMAS=test
Remap_tablespace=testprod:testdev

3a. Recompile for invalid objects

Now after a week i would like to refresh the Development Database from Production so i follow the below steps

1b. Disable Triggers
Disable Constraint WHERE constraint_type = 'R'
Drop Sequences

2b. Import data only.

impdump parfile=testimportcontent.par

userid=test/test
network_link=prodlink
directory=importdir
LOGFILE=importdataonly.log
CONTENT=data_only
table_exists_action=truncate
EXCLUDE=STATISTICS
EXCLUDE=USER
SCHEMAS=test
DATA_OPTIONS=skip_constraint_errors
Remap_tablespace=testprod:testdev

3b. Import the Sequence.


userid=test/test
network_link=prodlink
directory=importdir
LOGFILE=importsequence.log
CONTENT=METADATA_ONLY
SCHEMAS=test
INCLUDE=SEQUENCE
Remap_tablespace=testprod:testdev

4b. Enable Triggers
Enable Constraint WHERE constraint_type = 'R'


5b. Recompile for invalid objects

I am using the above method to refresh data from Production to our Development system which is Standard Edition so no Parallel option with impdp.
The issue is at Step 2b it takes almost 11 hours which i find very long for a total DB size of 150GB.
Could anyone suggest a faster option to refresh the development schema from production.

Thanks in advance
Re: Refresh Production data to Development Schema [message #630019 is a reply to message #630018] Thu, 18 December 2014 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
RMAN> DUPLICATE DATABASE
Re: Refresh Production data to Development Schema [message #630020 is a reply to message #630019] Thu, 18 December 2014 12:29 Go to previous messageGo to next message
preet_kumar
Messages: 199
Registered: March 2007
Senior Member
Sir please read my query i am not looking for Duplicating Database but refreshing a Schema which is not the same.
Re: Refresh Production data to Development Schema [message #630021 is a reply to message #630020] Thu, 18 December 2014 12:45 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
with free advice, sometimes you get what you paid for it.

You can't push a string.
Re: Refresh Production data to Development Schema [message #630022 is a reply to message #630021] Thu, 18 December 2014 12:47 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
Importing data with all the indexes existing will slow it down immensely.
Why not drop all the objects or at least the indexes?
Re: Refresh Production data to Development Schema [message #630023 is a reply to message #630020] Thu, 18 December 2014 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Transportable tablespaces.

Re: Refresh Production data to Development Schema [message #630025 is a reply to message #630023] Thu, 18 December 2014 12:53 Go to previous messageGo to next message
preet_kumar
Messages: 199
Registered: March 2007
Senior Member
I have clearly mentioned in my query its a Standard Edition and Transportable tablespaces is not supported in SE.
Re: Refresh Production data to Development Schema [message #630026 is a reply to message #630025] Thu, 18 December 2014 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you think we check each the time the options available in each version and each edition?
Don't be so disrespectful in your answer if you want some help, above all when you have showed so few smartness in your previous topics.

BlackSwan wrote on Thu, 18 December 2014 19:45
with free advice, sometimes you get what you paid for it.
You can't push a string.


And feedback and thank people who have helped you in your previous topics, NOW.

From now on, you are in my kill file, goodbye.

Re: Refresh Production data to Development Schema [message #630027 is a reply to message #630026] Thu, 18 December 2014 13:30 Go to previous messageGo to next message
preet_kumar
Messages: 199
Registered: March 2007
Senior Member
@joy_division
Thanks for your response but if i drop the Index now it would take the same time recreating it.
Re: Refresh Production data to Development Schema [message #630051 is a reply to message #630027] Thu, 18 December 2014 15:30 Go to previous message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
Untrue.
Creating indexes at the end after all data is imported is exponentially faster that importing data with the index already existing as in essence, the index is continually updating.
Previous Topic: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward
Next Topic: How do I calculate the size of my database?
Goto Forum:
  


Current Time: Fri Jan 19 23:59:42 CST 2018

Total time taken to generate the page: 0.01394 seconds