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.