Issue: Data Loading into development environment from production database

My production database is 40gb but I do not want all of those 40gb of data into my dev env and so my question is

  1. How can I get all the data loaded into Oracle database yesterday?
  2. Is there a way in Oracle to get all data loaded into db on previous day, manage all dependencies and generate another dev snapshot with one day minimal data?
  • can you add a database link between prod and dev?
    – hal9000
    May 13, 2013 at 15:08
  • You could use Data Pump with FLASHBACK_SCN or FLASHBACK_TIME parameters importing data via network link. May 13, 2013 at 15:20
  • What do you mean by "manage all dependencies"? If there are multiple tables with foreign keys, that could become quite difficult... I'd probably take the 40 GB and be done with it.
    – hal9000
    May 13, 2013 at 16:46
  • continuing what @Yasir said, you can use the psuedo column ora_rowscn but i would recommend against it. it's not entirely reliable and you'll have hard time keep table dependencies.
    – haki
    May 13, 2013 at 17:31
  • @hal9000: I agree best bet would be to get 40gb db and get done with it but the thing is...prod data is populated on daily basis and I need to work with latest data set.
    – Rachel
    May 13, 2013 at 20:21

2 Answers 2


Oracle Data Pump Network-Mode Import may be one of possible solutions. You can invoke Data Pump Import in network mode using database link (NETWORK_LINK parameter) and specifying which version of data you want to import with FLASHBACK_SCN or FLASHBACK_TIME parameters.

First, create a database link to you production database on your development database:

  CONNECT TO system
  IDENTIFIED BY "hackme"
  USING 'orcl';

Where orcl is the connect string you're using to connect to your production database. You can test if the link is working by querying some table in the production database:

SQL> select count(*) from hr.employees@prod_db;


See Database Administrator's Guide for more info about creating database links.

Then to import data into development database from production database, you would run Data Pump Import like this:

[oracle@oca ~]$ impdp sys/hackme \
SCHEMAS=hr,oe \
NETWORK_LINK=prod_db \
FLASHBACK_TIME="TO_TIMESTAMP('13-05-2013 17:35:00', 'DD-MM-YYYY HH24:MI:SS')"

In this example, two schemas are imported, CONTENT specifies that both metadata and data should be imported (default), and FLASHBACK_TIME specifies the time of the snapshot you want to import.

Due to the restrictions of FLASHBACK_TIME:

The FLASHBACK_TIME parameter pertains only to the Flashback Query capability of Oracle Database. It is not applicable to Flashback Database, Flashback Drop, or Flashback Data Archive.

You have to ensure that you have enough undo data on your source production database for the snapshot to be consistent, since Flashback Query depends entirely on undo.

Based on your requirements, you would need to guarantee undo retention to at least 24 hours to be able to get the previous versions of the data you're importing. You would also need to size you undo tablespace on the source database appropriately.

To guarantee the retention, connect to your source database and alter your current undo tablespace like this:

SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.

To specify the retention time (in seconds):

SQL> alter system set undo_retention=86400;
System altered.

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
undo_management                      string      AUTO
undo_retention                       integer     86400
undo_tablespace                      string      UNDOTBS1

(86400 is 60 seconds * 60 minutes * 24 hours.)

Sizing the undo tablespace:

SQL> alter database datafile '/oradata/ocaexam/undotbs01.dbf' resize 10g;

You can determine what's the best size for your undo tablespace using Undo Advisor in Enterprise Manager.

If you would like to automate your import process, you could use PL/SQL and Oracle Scheduler as described here.


The other option is Backup-Based Database Duplication where you would use UNTIL clause to specify the SCN or timestamp until which you want to recover your duplicate database. You would need to configure and start the auxiliary instance, and then issue in RMAN the command similar to the following:

  UNTIL TIME "TO_TIMESTAMP('13-05-2013 17:35:00', 'DD-MM-YYYY HH24:MI:SS')" 

There are a rew requirements to such duplication:

  • you need to have recent backups of your source database
  • your source database should be in ARCHIVELOG mode
  • your auxiliary instance should have access to the source database backups

Certainly, this solution may be a bit slower than using Oracle Data Pump, but you can speed it up by using Incremental Backups; in this case IIRC RMAN favours incremental backups over archived logs (see "How RMAN Duplicates a Database" in Database Backup and Recovery User's Guide).

  • Thanks Yasir...I will try this both approaches and see if I get expected result out.
    – Rachel
    May 14, 2013 at 14:56
  • Just ask if you need a deeper explanation or examples. This answer is rather short. May 14, 2013 at 15:03
  • Yes, if you can provide me with both detailed explanation and example than it would be very helpful.
    – Rachel
    May 14, 2013 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.