Tablespace Point-in-Time Recovery in Oracle AI Database 26ai

Choose the appropriate recovery scope

Define a consistent recovery target

Recovery targets can use an SCN, a time, a supported log sequence and thread, or an appropriate restore point. The exact clause matters: do not assume that every command includes its stated boundary.

This lesson uses UNTIL SCN 1418901. That limit is exclusive, so recovery stops before SCN 1418901. The number is illustrative and must be replaced with a verified target for the actual incident.

Choose the target using reliable evidence about when the unwanted operation occurred. Timestamp-to-SCN mapping is approximate and its history is limited. A converted timestamp can assist investigation, but it is not independent proof that a particular transaction lies on the intended side of the boundary.

Recovery must produce files that meet Oracle's consistency requirements. It applies redo and suitable incremental changes forward from eligible starting files; it cannot take arbitrary current data files backward. Requiring every file header to display an identical checkpoint SCN is not a universal consistency test.

Understand the recovery set and auxiliary set

1. Choose the target and verify the recovery set

A self-contained recovery set accounts for relationships crossing its boundaries. Table partitions, LOB segments, indexes, and constraints can involve other tablespaces. Recovering one part without accounting for the others may violate transport restrictions or leave the application with incompatible states.

In an authorized SQL*Plus session connected to APPPDB, check the proposed set:

BEGIN
  DBMS_TTS.TRANSPORT_SET_CHECK('APP_DATA', TRUE, TRUE);
END;
/

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

When more than one tablespace is selected, pass the full comma-separated list to the check. Investigate every returned violation. The documented options may include expanding the eligible set or deliberately managing a relationship. Record any changes so that suspended or removed dependencies can be restored correctly.

Containment is one requirement, not the entire eligibility test. TSPITR cannot recover the current default tablespace or a recovery set containing undo/rollback segments or SYS-owned objects. Partitioned and dependent objects must also meet the applicable transport restrictions. Do not drop objects merely to silence a violation without understanding the consequences.

For example, APP_DATA might hold orders while another tablespace holds current invoice records. Returning the orders to yesterday could remove an order that today's invoice still references. Application code may enforce that relationship without a database foreign key, so the containment query cannot establish business consistency. Decide whether the recovery scope or the reconciliation plan must change before proceeding.

Use TS_PITR_OBJECTS_TO_BE_DROPPED in the target PDB to inspect objects created after the recovery target. Filter by the selected tablespaces and the verified target time. The view identifies potential object loss; it does not inventory every later INSERT, UPDATE, or DELETE affecting older objects.

Preserve useful later data where feasible and confirm the accepted loss with application owners. If the tablespace itself was dropped, RMAN cannot perform the same live containment check; the check occurs during the auxiliary export stage.

2. Verify the required backups and redo

3. Let RMAN prepare the auxiliary environment

The example uses fully automated TSPITR with the target CDB and APPPDB open. Connect RMAN to CDB$ROOT as an authorized common user with SYSBACKUP or SYSDBA. The operator must be able to use the documented operating-system authentication for the automatic auxiliary workflow.

Do not connect RMAN to a user-managed auxiliary for this example. An auxiliary connection tells RMAN to use that separately managed database. An optional recovery-catalog connection has a different purpose and does not change this distinction.

Plan sufficient host storage and I/O capacity for auxiliary data files, restored archives, temporary work, and supporting files. Configure the channels needed to access the backups. RMAN creates its auxiliary and takes the selected target tablespaces offline as part of processing.

AUXILIARY DESTINATION holds auxiliary-set files. Recovery-set files normally use their original locations. Use documented naming controls such as SET NEWNAME when those files need relocation; the auxiliary destination alone does not redirect every restored file.

4. Restore and recover to the selected boundary

With the prerequisites satisfied and RMAN connected to the root, run the illustrative command:

RECOVER TABLESPACE apppdb:app_data
  UNTIL SCN 1418901
  AUXILIARY DESTINATION '/u02/aux_tspitr';

The apppdb: qualifier selects APP_DATA in that PDB. Tablespace names can repeat across containers, so the connection context and qualifier matter. The path is writable storage on the database host, not a directory on the DBA's client computer.

This command orchestrates the restoration and recovery. RMAN restores the necessary recovery-set and auxiliary-set files and applies the required recovery information in the auxiliary. Recovery stops before SCN 1418901. Do not prepend a whole-CDB RESTORE DATABASE command or manually shut down the target for this example.

Monitor RMAN output and the relevant diagnostic messages. A restore finishing does not mean that recovery, transport, or cleanup has finished. Resolve errors according to the stage reached rather than assuming that an ended session means the tablespaces are ready.

5. Open the auxiliary with RESETLOGS

RMAN opens the recovered auxiliary database with RESETLOGS, then makes its recovery-set tablespaces read-only in preparation for transport.

This is the distinction from the whole-database examples in earlier lessons: RESETLOGS applies to the auxiliary database. TSPITR does not require the DBA to open the target CDB or target PDB with RESETLOGS. Other target tablespaces remain at their current state while the selected set is prepared for return.

6. Transport the recovered tablespaces into the target

RMAN uses Data Pump to create transportable tablespace metadata, shuts down the auxiliary, and replaces the target recovery-set tablespaces by plugging in their recovered files with that metadata.

The Data Pump dump describes the transported tablespaces and objects; the recovered data files hold their table data. This is different from exporting every row and inserting those rows into the existing target tables. A separate export made to preserve useful later objects is an additional recovery-planning action, not the automatic transport step.

After transport, RMAN makes the returned tablespaces read/write and immediately leaves them offline. It removes the auxiliary-set files after successful completion. The recovery-set files now belong to the target and must remain available.

If the operation fails, inspect its state before retrying or cleaning up. Do not delete a directory merely because it was involved in auxiliary recovery; establish which files are temporary and which now serve the target.

7. Back up, bring online, and validate

After RMAN reports successful TSPITR, back up the recovered tablespaces before bringing them online. For the following backup example, use a separate RMAN target connection directly to APPPDB with authorized backup privileges:

BACKUP TABLESPACE app_data;

This PDB connection uses the unqualified tablespace name. Keep it distinct from the root connection used for TSPITR. If the recovery set included additional tablespaces, back up every recovered member of that set.

After the backup succeeds, use an authorized SQL*Plus session connected to APPPDB:

ALTER TABLESPACE app_data ONLINE;

SELECT tablespace_name, status
FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA';

Online status establishes availability, not application correctness. Inspect representative rows, required indexes and constraints, and relationships to tablespaces that remained current. Restore dependencies deliberately suspended during preparation and check application behavior before resuming normal access.

TSPITR does not recover optimizer statistics for the recovered objects. Gather fresh statistics with an appropriate scope. Reconcile later business activity carefully: external payments, messages, or other databases may still record effects absent from the recovered tablespaces.

Why this backup requirement differs from ordinary RESETLOGS

Oracle's TSPITR procedure specifies backup before bringing the recovered tablespaces online. TSPITR has particular restrictions on using previous tablespace backups and recovering across the event with the current control file. Do not rely on pre-TSPITR tablespace backups as the normal protection for the returned tablespaces.

This does not mean that every earlier backup of the entire CDB is worthless. It also does not change Lesson 10's rule: opening a target database with RESETLOGS, by itself, does not make a new whole-database backup mandatory. TSPITR transports selected tablespaces back into the current target and has its own recovery limitations.

Consider Flashback and more focused alternatives