Skip to Main Content
  • Questions
  • Copying data from one environment to other

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, DIPU.

Asked: August 22, 2016 - 10:41 am UTC

Last updated: August 22, 2016 - 3:20 pm UTC

Version: 11 g

Viewed 1000+ times

You Asked

Hi,
what is the best way to copy data from one environment (PRD) to other environment (NON-PRD) in oracle installed on UNIX server?

Thanks,
DIPU

and Chris said...

"Best" is always subjective and depends on your exact requirements. Here are a few methods you could explore:

- If you want to completely refresh the non-prod environment from prod regularly (e.g. daily) so they are clones, look at snapshot standbys:

https://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ps.htm

- If you have one or more tablespaces that contain the data you want to refresh (and nothing else), check out transportable tablespaces

http://docs.oracle.com/database/121/ADMIN/transport.htm#ADMIN01101

Note: you have to place these in read-only mode to do the export. Probably not what you want in production! You can create the exports from backups to avoid this:

http://docs.oracle.com/database/121/BRADV/rcmttbsb.htm#BRADV89959

- If you just want to copy data structures (without data) or do things like change the schema name try data pump export/import:

http://docs.oracle.com/database/121/SUTIL/GUID-47B26B0B-3C95-4182-ACDF-2EEDD577FC9E.htm#SUTIL801

- Or if you have specialized requirements you could always roll your own SQL copy scripts!

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.