How find all the data loaded on previous day?

Question

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

How can I get all the data loaded into Oracle database yesterday?
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?

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

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:

CREATE DATABASE LINK prod_db 
  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;

  COUNT(*)
----------
       107

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 \
CONTENT=ALL \
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.

score 1 · Answer 2 · answered May 14 '13 at 02:19

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:

DUPLICATE DATABASE prod TO dupdb
  UNTIL TIME "TO_TIMESTAMP('13-05-2013 17:35:00', 'DD-MM-YYYY HH24:MI:SS')" 
  SPFILE
  NOFILENAMECHECK;

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).

How find all the data loaded on previous day?

2 Answers2