Point-in-Time Recovery (Incomplete Recovery)

Oracle DBA Certification 14 min min read Updated: Nov 16, 2026 Advanced
Point-in-Time Recovery (Incomplete Recovery)
Advanced Topic 5 of 8

When Do You Need It?

Complete recovery brings back everything, including a bad change. Point-in-time recovery (PITR) stops before the mistake. Use it when someone drops a schema, truncates a table or deletes the wrong data and the change has already been committed.

Simple example: You wrote a letter and accidentally erased the second page at 2:30 pm. PITR is rewinding your typing history to 2:29 pm and stopping there.

Ways to Choose the Stopping Point

ClauseExample
UNTIL TIMESET UNTIL TIME "TO_DATE('2026-11-10 14:29:00','YYYY-MM-DD HH24:MI:SS')"
UNTIL SCNSET UNTIL SCN 1234567
UNTIL SEQUENCESET UNTIL SEQUENCE 215 THREAD 1
Restore pointSET UNTIL RESTORE POINT before_upgrade

Whole Database PITR

sql SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; RMAN> RUN { SET UNTIL TIME "TO_DATE('2026-11-10 14:29:00','YYYY-MM-DD HH24:MI:SS')"; RESTORE DATABASE; RECOVER DATABASE; } SQL> ALTER DATABASE OPEN RESETLOGS;
This affects everyone: Whole-database PITR throws away all changes after that moment in every application. Take a backup first, tell the business and consider the options below first.

Prefer a Smaller Tool

ProblemBetter tool
One table was changedFlashback Table, or RECOVER TABLE
One application (PDB) was damagedPDB point-in-time recovery
Wrong rows deleted recentlyFlashback Query to read the old rows and re-insert

PDB Point-in-Time Recovery

sql SQL> ALTER PLUGGABLE DATABASE freepdb1 CLOSE IMMEDIATE; RMAN> RUN { SET UNTIL TIME "TO_DATE('2026-11-10 14:29:00','YYYY-MM-DD HH24:MI:SS')"; RESTORE PLUGGABLE DATABASE freepdb1; RECOVER PLUGGABLE DATABASE freepdb1 AUXILIARY DESTINATION '/tmp/aux'; } SQL> ALTER PLUGGABLE DATABASE freepdb1 OPEN RESETLOGS;

Other PDBs keep running and are not affected. The auxiliary destination is a temporary work area (not always needed when local undo is used).

Recover One Table

sql RMAN> RECOVER TABLE shop.orders UNTIL TIME "TO_DATE('2026-11-10 14:29:00','YYYY-MM-DD HH24:MI:SS')" AUXILIARY DESTINATION '/tmp/aux' REMAP TABLE shop.orders:orders_before;

RMAN builds a temporary database, extracts the table as it was and loads it under the new name orders_before. You then compare and copy the rows you need.

Find the Right Moment

sql SELECT current_scn, TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM v$database; SELECT sequence#, first_time, next_time FROM v$log_history ORDER BY sequence# DESC FETCH FIRST 10 ROWS ONLY;
Tip: If you are not sure of the exact time, recover to a time a few minutes before the mistake. If you went too early you can restore again and use a later time.

After the Recovery

  • Check the data carefully before telling users.
  • Take a new full backup immediately, because OPEN RESETLOGS started a new incarnation.
  • Write down what happened and which transactions were lost.

Conclusion

Point-in-time recovery rewinds to just before a mistake. Always compare it with smaller tools like flashback first, because PITR costs you all later changes.

Quick Summary: SET UNTIL, RESTORE, RECOVER, OPEN RESETLOGS. Use PDB or table recovery to limit the impact.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators