Use the following procedure to take the consistent backup using expdp (oracle’s datapump export utility)
Stop application activity(transactions) against database. This will ensure you that you don’t loose any transactions .
1. Make sure there are no pending transactions by running the following SQL:
SQL> select count(*) from v$transaction;
Above query should return with zero rows.
2. Find the current SCN from the database. SQL> select CURRENT_SCN from v$database; –Alternative ways to find current SCN: SQL> select dbms_flashback.get_system_change_number from dual; SQL> select timestamp_to_scn(sysdate) from dual;
3. Create expdp parameter file (hr_export.par):
Here is the example of hr_export.par:
$cat hr_export.par directory=dump_dir -- this is the directory you created for export dumpfile=hr_export%U.dmp parallel=4 logfile=hr_export.log job_name=hr_export exclude=grant,synonym -- any object types you want to exclude flashback_scn=2451671797 -- CURRENT_SCN take from step 2. TABLES=HR.EMPLOYEE,HR.DEPARTMENT
4. invoke the export process: $expdp parfile=emp_export.par
–It will prompt for the username and password for the export process.