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.

Leave a Reply

Your email address will not be published. Required fields are marked *