Home

Oracle RMAN Table Point-in-Time Recovery (TSPITR): A Step-by-Step Guide

Oracle’s RMAN Table Point-in-Time Recovery (TSPITR) is a powerful feature introduced in Oracle 12c Release 1. It allows database administrators to recover individual tables or table partitions to a specific point in time without restoring the entire database. This is particularly useful in scenarios like accidental data deletion, table corruption, or logical errors.

In this article, we’ll walk through the steps to perform TSPITR, simulate a recovery scenario, and explain each step in detail.

Scenario: Recovering a Dropped Table

Imagine you have a table named EMPLOYEES in the HR schema. Due to an accidental DROP TABLE command, the table is lost. You need to recover the table to its state just before the drop operation.

Step 1: Identify the Point-in-Time for Recovery

Before performing TSPITR, identify the exact point in time (or SCN) to which you want to recover the table. You can use the following query to find the approximate time of the drop operation:

SELECT 
    TO_CHAR(OPERATION_TIME, 'YYYY-MM-DD HH24:MI:SS') AS drop_time,
    SCN
FROM 
    DBA_RECYCLEBIN
WHERE 
    OBJECT_NAME = 'EMPLOYEES'
    AND OWNER = 'HR';

This query retrieves the drop time and SCN (System Change Number) from the recycle bin. Note the SCN or timestamp for recovery.

Step 2: Perform RMAN Table Point-in-Time Recovery

Use RMAN to perform TSPITR. The following command recovers the EMPLOYEES table to the specified SCN:

rman target /

RECOVER TABLE HR.EMPLOYEES
  UNTIL SCN <SCN_NUMBER>
  AUXILIARY DESTINATION '/u01/app/oracle/auxiliary';

Replace <SCN_NUMBER> with the SCN identified in Step 1. The AUXILIARY DESTINATION specifies the location for the auxiliary instance files.

Step 3: Export the Recovered Table

Once the recovery is complete, RMAN exports the recovered table to a Data Pump dump file. The dump file is stored in the auxiliary destination:

expdp \'/ as sysdba\' tables=HR.EMPLOYEES directory=DATA_PUMP_DIR dumpfile=employees_recovered.dmp logfile=employees_recovered.log

Step 4: Import the Table Back into the Main Database

Finally, import the recovered table back into the main database using Data Pump:

impdp \'/ as sysdba\' tables=HR.EMPLOYEES directory=DATA_PUMP_DIR dumpfile=employees_recovered.dmp logfile=employees_imported.log

Step 5: Verify the Recovered Table

After importing, verify that the EMPLOYEES table is restored correctly:

SELECT COUNT(*) FROM HR.EMPLOYEES;

Simulation of the Scenario

To simulate this scenario:

1. Create a Test Table:

   CREATE TABLE HR.EMPLOYEES AS SELECT * FROM HR.EMPLOYEES_ORIGINAL;

2. Drop the Table:

   DROP TABLE HR.EMPLOYEES;

3. Identify the Drop Time and SCN:
   Use the query in Step 1 to find the SCN.
4. Perform TSPITR:
   Use the RMAN command in Step 2 to recover the table.
5. Export and Import:
   Follow Steps 3 and 4 to export and import the table.
6. Verify:
   Use the query in Step 5 to confirm the table is restored.

Key Considerations

1. Auxiliary Storage:
   Ensure sufficient disk space is available for the auxiliary instance.
2. Dependencies:
   If the table has dependencies (e.g., foreign keys), you may need to recover the entire schema.
3. Backup Strategy:
   Regularly back up your database to ensure recoverability.

Conclusion

Oracle RMAN Table Point-in-Time Recovery (TSPITR) is a lifesaver for recovering individual tables or partitions without restoring the entire database. By following the steps outlined in this article, you can efficiently recover lost or corrupted data and minimize downtime.