March 19, 2025

Oracle Export Previous SCN Data & Import

into an Existing Table – Step-by-Step Guide

Scenario:

In this guide, we will explore how to use Oracle Data Pump Export (expdp) and Import (impdp) to back up historical data from a previous SCN (System Change Number) and restore it into an existing table. This approach is useful when you need to retrieve older data while preserving the table structure.

Step 1: Setting Up a Backup Location

Before exporting data, we need to create a physical backup location where the dump files will be stored.

On Linux server:

mkdir -p /data/backup

On Oracle Database:

We create a logical directory pointing to the physical backup location:

Command:

create a directory TEST_DIR2 as '/data/backup';

TEST_DIR2 --> Logical directory pointing to the physical backup location.

To verify the directory:

SELECT * FROM dba_directories WHERE directory_name = 'TEST_DIR2';

Step 2: Checking Current Table Data

Inserted few more records

Before exporting, we check the current number of rows in U1.T3. After inserting additional records, we note that the SCN values differ:

6 records → SCN: 6255279
11 records → SCN: 6255453

Since the client only requires historical data, we will export the table using the previous SCN (6255279)

Step 3: Exporting Historical Data with a Specific SCN

To export data from a previous SCN, we create a parameter file (exp.par) with the following content:

dumpfile=dbaclass.dmp logfile=dbaclass.log directory=TEST_DIR2 tables=U1.T3 flashback_scn=6255279

Explanation of Key Terms:

Dump File (.dmp) → A binary file containing schema objects, data, and metadata for backup.
Log File (.log) → Records the details of the export/import process, including any errors or warnings.

After preparing the parameter file, we execute the export command:

The system will prompt for a database connection. Use the SYS user credentials and press Enter when asked for a password.

Once completed, the export process will generate the required dump and log files.

expdp parfile=exp.par

exporting parfile and successful

Step 4: Dropping the Table Before Importing

Before restoring the old data, we drop the existing table to avoid conflicts:

Step 5: Importing Historical Data

Now, we restore the previously exported SCN data using the following command:

impdp dumpfile=dbaclass.dmp logfile=dbaclass_import.log directory=TEST_DIR2 tables=U1.T3

Once the process is complete, we verify that only the 6 records from SCN 6255279 have been restored successfully.

Summary of Steps

✔ Created a backup directory (/data/backup) on the Linux server.
✔ Created a logical directory (TEST_DIR2) in the Oracle Database.
✔ Exported previous SCN data using expdp.
✔ Dropped the existing table before import.
✔ Restored U1.T3 with historical data using impdp and verified the records.

Final Thoughts

By following these steps, you can successfully export and restore data from a specific SCN using Oracle Data Pump. This method is particularly useful for recovering deleted data or migrating older records to a different environment.

💡 Have questions? Need help? Drop a comment below!

📢 Want more Oracle database tutorials? Follow this blog for the latest updates!

Search This Blog

ARBOne