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
First, create a database link to you production database on your development database:
CREATE DATABASE LINK prod_db
CONNECT TO system
IDENTIFIED BY "hackme"
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 \
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 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;
To specify the retention time (in seconds):
SQL> alter system set undo_retention=86400;
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.