Working with Oracle Tablespace Point-in-time Recovery

Point-in-time recovery (PITR) is the umbrella concept for restoring data to a transactionally consistent moment in the past. In Oracle terminology, PITR is typically an incomplete recovery, because you intentionally stop applying redo before the most current point in time.

The key idea is scope. Sometimes you must “rewind” the entire database (database PITR). In other scenarios, you want to recover only a subset of the database to an earlier time while leaving the rest of the database current. Tablespace point-in-time recovery (TSPITR) exists for that purpose: recover one or more tablespaces to an earlier time without forcing a full database rollback.
A common TSPITR story looks like this: a developer drops, truncates, or corrupts data in a single application tablespace, but other teams continue to run workload in other areas of the database. Rolling back the entire database is not acceptable. With TSPITR, you recover the affected tablespace(s) to the target time in an auxiliary environment, extract the required objects, and then bring those objects back into the primary database.

Before you attempt TSPITR in Oracle 23ai, verify the following prerequisites (conceptually—and in your runbooks, explicitly):

1. Recoverability inputs exist: you have usable backups for the datafiles in the tablespace(s) you need to recover, and you have the redo required to roll forward to the target time (archived redo logs, and in some strategies incremental backups).
2. Control file metadata is available: you can access current recovery metadata (typically via the control file and, optionally, a recovery catalog).
3. Enough resources for an auxiliary environment: TSPITR is performed by creating an auxiliary database (or an RMAN-managed auxiliary instance) that temporarily hosts restored/recovered files.
4. ARCHIVELOG mode is enabled: without archived redo, your options for PITR/TSPITR are severely limited.

A commonly taught building block is making a backup of the current control file metadata:
Syntax

ALTER DATABASE BACKUP CONTROLFILE TO <CONTROLFILE_NAME>;

Example

ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control_backup.ctl';

In modern practice, you will typically rely on RMAN to orchestrate the control file usage, restore operations, and recovery targets—rather than manually copying files and hand-driving recovery steps.

TSPITR is designed to isolate recovery work away from the primary database. At a high level:

1. Clone and recover: create an auxiliary environment and recover the target tablespace(s) to the desired time/SCN.
2. Extract and reintroduce: export the recovered objects from the auxiliary environment and import them back into the primary database (typically using Oracle Data Pump).

The series of images below illustrates the conceptual flow. The current database is also referred to as the primary database, while the temporary environment is the auxiliary database.

A complete recovery brings a database (or a subset such as a tablespace or datafile) forward to the most current time possible by applying all available redo needed to reach the latest consistent point. The goal is no loss of committed transactions.

A point-in-time recovery (incomplete recovery) intentionally stops short of the most current time. This is useful when:

1. Redo required for complete recovery is missing or damaged.
2. A human or application error must be “undone” (for example, accidental deletes/updates, or a destructive operation).
3. You must isolate and reverse a specific event without rolling back unrelated work.

In Oracle, a recovery target is typically specified by one of these common “coordinates”: time, SCN, log sequence, or a restore point name. After an incomplete recovery, the database (or auxiliary instance in a TSPITR workflow) transitions through a RESETLOGS boundary, which creates a new redo stream and a new recovery lineage. Operationally, that is why post-recovery backups are mandatory: you want backups that align with the new incarnation and redo history.

When the objective is to return data to an earlier point, Flashback features can be faster than restore-and-recover because they avoid rebuilding files from backups. For example, Flashback Table can rewind one or more tables (when the conditions for flashback are met). Flashback Database can rewind an entire database, and Oracle also supports flashback operations at the pluggable database (PDB) level in multitenant environments.

However, flashback is not a universal substitute for PITR/TSPITR. Some scenarios still require recovery workflows that use backups and redo (for example, certain structural changes or when flashback prerequisites were not enabled/retained). In those cases, TSPITR remains a critical tool because it targets only the affected tablespace(s) rather than rewinding everything.

RMAN tablespace point-in-time recovery enables you to recover one or more tablespaces to an earlier time without affecting the rest of the database. It is most useful when the problem is localized to a subset of tablespaces, or when flashback options are not suitable for the specific failure mode.

The next lesson concludes this module.