Oracle Database consistent backup using expdp

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.