Oracle DBA

What is Point-in-Time Recovery?

Point-in-Time Recovery is a technique to restore a database to a specific moment in the past. Unlike complete recovery (which applies all redo/archived logs until the most recent commit), PITR deliberately stops recovery at a chosen point.

• Use case: Undoing user errors (e.g., accidental data deletion).
• Requirement: Database must run in ARCHIVELOG mode, with valid backups and archive logs available.
• Outcome: Database opens with RESETLOGS, starting a new incarnation.

Demo Scenario

Let’s simulate a common situation:

1. A table is created and populated with data.
2. Rows are accidentally deleted.
3. Using RMAN, we recover the database to the point before the deletion.

Step 1: Setup and Insert Data

SQL> CREATE USER odba IDENTIFIED BY odba;
SQL> GRANT CONNECT, RESOURCE TO odba;

SQL> CREATE TABLE odba.emp AS
     SELECT * FROM hr.employees;

SQL> INSERT INTO odba.emp
     SELECT * FROM hr.employees CONNECT BY LEVEL <= 1000;
SQL> COMMIT;

SQL> SELECT COUNT(*) FROM odba.emp;

  COUNT(*)
----------
    100000

At this stage, the table odba.emp has 100,000 rows.

Step 2: Accidental Deletion

SQL> DELETE FROM odba.emp;
SQL> COMMIT;

SQL> SELECT COUNT(*) FROM odba.emp;

  COUNT(*)
----------
         0

Disaster! The data has been wiped out. We’ll use PITR to restore it.

Step 3: Identify the Recovery Point

Check the current log sequence from the source database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
     FROM v$log ORDER BY SEQUENCE#;

From this, note the sequence number just before the deletion happened (for example, sequence 11).

Step 4: Startup in MOUNT Mode

Shut down and restart in mount mode:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 5: Perform RMAN Recovery

Run RMAN with UNTIL SEQUENCE (or UNTIL TIME if you prefer time-based recovery).

$ rman target /

RMAN> RUN {
  SET UNTIL SEQUENCE 12;
  RESTORE DATABASE;
  RECOVER DATABASE;
}

Sample RMAN Output

Starting restore at 25-SEP-25
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00001
channel ORA_DISK_1: restore complete
Finished restore at 25-SEP-25

Starting recover at 25-SEP-25
archived log for thread 1 with sequence 11 is already on disk
archived log for thread 1 with sequence 12 is already on disk
media recovery complete
Finished recover at 25-SEP-25

Step 6: Open Database with RESETLOGS

After incomplete recovery, you must reset the logs:

SQL> ALTER DATABASE OPEN RESETLOGS;

Step 7: Verify the Data

Now check the restored table:

SQL> SELECT COUNT(*) FROM odba.emp;

  COUNT(*)
----------
    100000

Success! The table has been restored to its state before the deletion.

Key Takeaways

• PITR is invaluable for recovering from user errors without losing the entire database.
• Always ensure you have the necessary backups and archive logs before attempting recovery.
• Recovery can be targeted by UNTIL SEQUENCE or UNTIL TIME.
• After incomplete recovery, the database must be opened with RESETLOGS.

Final Thoughts

Point-in-Time Recovery with RMAN is a life-saving tool for DBAs. By practicing these steps in a controlled environment, you’ll be ready to handle real-world incidents with confidence and precision.