Generate consistent backup through Oracle Datapump export

The original export utility when setting with consistent=y will create the export dump file of the database objects from the point in time to the beginning of the export session.

Conventional Export
==============

exp userid="/ as sysdba" owner= file=.dmp log=.log consistent=y

However we don’t have have consistent parameter for datapump export. By default datapump export is consistent, but not for the entire dump file.It is consistent for one table at a time and not for the entire export.

We can use FLASHBACK_SCN or FLASHBACK_TIME parameters to achieve the consistency of the backup. Find the below equivalent commands for consistent backups in both original export and datapump export.

Datapump Export
==============

vi expdp.par

schemas=
directory=
dumpfile= .dmp
logfile= .log
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"
job_name=

expdp userid="/ as sysdba" parfile=expdp.par

Data Migration using Oracle Datapump QUERY option

Consider the case when we want to migrate the selected records from source database to target database we have the below great technique available onwards 10g

I used QUERY option of Oracle Datapump tool for satisfy the requirements of Data Migration from source database to target database.
🙂

Approach-1
1. Taking backup using Oracle Datapump tool from source database
2. Move the backup on target database
3. Import the backup

OR

Approach-2
1. Create and configure database link in target database
2. Taking backup using Oracle Datapump tool from source database and import on target database using network_link option

expdp scott/******* parfile=mstpara.par

-bash-4.1$ cat mstpara.par
TABLES=EMP
directory=DB_ORCL
DUMPFILE=expdp_emp_test.dmp
logfile=expdp_emp_test.log
query=EMP:"where dept = '505'"

References

http://dba-oracle.com/t_oracle_expdp_tips.htm
http://amit7oracledba.blogspot.in/2013/12/query-parameter-in-datapump-utility.html
http://arjudba.blogspot.in/2008/04/exportimport-datapump-parameter-query.html
https://community.oracle.com/message/12587311
http://shaharear.blogspot.in/2008/09/using-query-parameter-in-expdpimpdp.html
http://arjudba.blogspot.in/2008/04/exportimport-datapump-parameter-query.html

Oracle Data Pump: Interactive Mode – Attach/Detach

I had a customer who was importing a long running Data Pump import job. The client needed to go home and still have a way to monitor the import job.

Starting with Oracle 10g Data Pump there is a useful feature in situations in which you start a job at one location and need to check on it at a later time from a different location

http://blogs.xtivia.com/home/-/blogs/oracle-data-pump%3A-interactive-mode-attach-detach