Performing Time-Based Recovery in Oracle AI Database 26ai

Evaluate the Recovery Options First

For an eligible dropped table that remains in the recycle bin, Flashback Drop may provide a direct remedy. If that option is unavailable or unsuitable, consider RMAN table recovery or another supported scoped procedure. The choice depends on the object, available history, and the complete recovery requirement.

Flashback Table and Flashback Drop address different situations. A normal ROLLBACK does not undo a committed DROP TABLE, and truncating a table is not the same incident as dropping it. Establish the actual operation before choosing a remedy merely because its name contains the word flashback.

Confirm the Whole-CDB Prerequisites

The main procedure assumes a CDB in ARCHIVELOG mode, a current control file and SPFILE, and a target within the current incarnation. Data file backups and the required recovery history must support the selected endpoint. Access to backup storage, RMAN channels, and any necessary decryption keys or passwords must be established.

Connect RMAN to CDB$ROOT as an appropriately privileged common user, such as one with SYSBACKUP or SYSDBA. The root connection administers recovery of the whole CDB. It does not permit independent point-in-time recovery of only the root while leaving its PDBs at unrelated states.

A lost control file or SPFILE, an ancestor-incarnation target, and RAC or Data Guard coordination require additional procedures. Do not insert a generic RESTORE CONTROLFILE command into this example without addressing backup discovery and startup requirements. The later control-file lesson treats that recovery context separately.

Preserve useful current-state material and incident evidence where feasible. A fresh closed database backup is not a universal prerequisite and does not replace the earlier backups needed for noon. Stop application access before restoration and prevent automatic jobs or client reconnections from resuming writes during verification.

Perform the RMAN Time-Based Recovery

The following example assumes an initially open CDB that can shut down normally. If it is already mounted, omit the shutdown and startup commands. The target is the verified noon boundary for the scenario, not a value to reuse indiscriminately in another database.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

RUN {
  SET UNTIL TIME "TO_DATE('2025-01-18 12:00:00', 'YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}

ALTER DATABASE OPEN RESETLOGS;

Treat this as a supervised recovery sequence. Resolve any restore or recovery error before proceeding to RESETLOGS. The final open command does not bypass missing redo, incompatible files, or incomplete restoration.

Step 1: Mount Before Restoring

STARTUP MOUNT starts the instance and mounts the database using the control file without opening it for normal application access. In this RMAN workflow, mounting precedes RESTORE DATABASE. Describing MOUNT as an open mode obscures this distinction.

Confirm the target environment before overwriting data files. An operating-system directory listing or file modification time is not a recoverability test. Backup suitability depends on database metadata, file state, target requirements, and available recovery history.

Step 2: Set the Target Before Backup Selection

SET UNTIL TIME appears before both RESTORE and RECOVER in the same RUN block. This ensures that RMAN selects data file backups compatible with the endpoint and applies recovery information under that same target. Setting the target only after restoration can leave files that are too recent.

RMAN selects suitable backups under the target and other criteria. These need not be the newest backups overall or all belong to one backup set. A newer backup containing changes beyond noon cannot be rolled backward to noon by ordinary forward media recovery.

Step 3: Restore a Starting State and Recover Forward

RESTORE DATABASE retrieves the data files that provide the starting state. RECOVER DATABASE then applies the required incremental changes and redo to reach the selected endpoint. Restoring a backup does not necessarily make its files consistent or place the database at the requested time.

If RMAN needs archived logs that are available in backup, it can restore them using suitable channels. A required gap cannot simply be skipped. Investigate alternate copies, storage access, and viable recovery paths before changing the intended target.

Step 4: Reopen Only After Successful Recovery

After successful whole-CDB incomplete recovery, OPEN RESETLOGS establishes a new database incarnation and restarts redo sequencing. It does not reset database SCNs or DBID to 1. Oracle's recovery consistency requirements must already be satisfied.

Opening the CDB does not automatically open its PDBs. In a separate SQL*Plus session connected to the root, the following opens all applicable PDBs when that matches the operating plan:

ALTER PLUGGABLE DATABASE ALL OPEN;

Otherwise, open the intended PDBs individually. Keep application access controlled until the recovered state has been checked. Opening containers is a technical milestone, while deciding that service can resume requires application verification.

Stage Archived Logs in Another Directory

The recovery may need working space for archived logs restored from backup. This is a different task from configuring where Oracle generates new archived logs during normal operation. RMAN can stage restored logs in a selected server-side directory.

If needed, add this setting inside the same RUN block before RECOVER:

SET ARCHIVELOG DESTINATION TO '/u02/recovery_arch';

The path is a Linux/Unix-style illustration. Use a real directory on the database server with suitable access and capacity. RMAN knows where it restored the archives and can use available logs already on disk. There is no general requirement to manually restore every archive before starting recovery.

Do not change persistent production archiving settings solely to tell RMAN where to stage these files. The historical ARCHIVE LOG START TO approach is not the recovery-location mechanism used here. Windows environments should use their actual server-side paths rather than copying the sample directory.

User-Managed SQL*Plus Alternative

SQL*Plus user-managed media recovery remains documented, but it has separate restore preparation and command syntax. The following is an alternative workflow after suitable user-managed data file restoration and mounting. It is not an additional command to execute after the RMAN sequence.

RECOVER DATABASE UNTIL TIME '2025-01-18:12:00:00'

The SQL*Plus form uses a colon between the date and time and a 24-hour time representation. It differs from the RMAN TO_DATE expression. AUTO at the recovery prompt, or the AUTOMATIC keyword, can automate supplying expected archived-log filenames.

When restored archives are in another directory, this single SQL*Plus command specifies that location:

RECOVER AUTOMATIC FROM '/u02/recovery_arch' DATABASE UNTIL TIME '2025-01-18:12:00:00'

Automatic application does not create missing redo or confirm that noon is the correct business endpoint. The files must still be recoverable to a consistent state. Use RESETLOGS as one keyword when following the appropriate reopening procedure; do not test recovery by deliberately trying to open an insufficiently recovered database.

Monitor Recovery and Verify CUSTOMERS

Use the monitoring methods from Lesson 5: preserve RMAN output, correlate it with the correct instance's alert log, and inspect relevant progress measurements from a separate session. A completed recovery phase and an open database are different observations.

Where the recovered state permits it, optional read-only inspection can help assess the data before committing to RESETLOGS. This cannot bypass unresolved media recovery. Return to mounted state before continuing recovery or performing the final RESETLOGS open.

After reopening, inspect the intended CDB and PDB states from SQL*Plus connected to the root:

SELECT name, open_mode FROM v$database;
SELECT name, open_mode FROM v$pdbs ORDER BY con_id;

Then connect to the PDB owning CUSTOMERS and validate the application data. A matching table name in another PDB is not evidence that the affected application was recovered. Use the correct owner and container when comparing results with the recovery requirements.

Object existence or a row count alone is insufficient. A table can exist and still contain the wrong business state. Agree on representative validation evidence before recovery so that the reopening decision is based on something more than the absence of Oracle errors.

Interpret RESETLOGS Metadata Correctly

RESETLOGS_TIME records when RESETLOGS occurred, which can be later than the noon recovery target. It is not proof that the database was recovered to noon. RESETLOGS_CHANGE# describes the incarnation transition and must also be interpreted with the recovery context.

Establish the result using the configured target, recovery output, relevant diagnostic records, and application checks. Redo sequence numbering restarts during RESETLOGS, but subsequent log switches may already have occurred by the time you inspect it. Observing a sequence other than 1 does not alone invalidate the recovery.

Plan a Retry and Future Protection

If the endpoint was too early and RESETLOGS has not established the new branch, further available redo may permit advancement. If too much redo was applied, a restore-based retry generally requires restoring suitable backups again. After RESETLOGS, further recovery planning must account for incarnation history.

Old-branch redo cannot simply be appended to the new branch, but earlier backups and recovery material are not automatically invalidated. Preserve them according to retention requirements. Avoid an absolute claim that recovery can never reach a later state after RESETLOGS; the available procedures depend on the preserved history and intended result.

A fresh whole-database backup after RESETLOGS is optional in Oracle 26ai. It may provide a useful new baseline under the backup policy, and an RMAN backup in ARCHIVELOG mode does not inherently require another shutdown. In Data Guard environments, assess the documented options for the standby to follow the recovered primary rather than automatically rebuilding every standby.

The next lesson shows how to perform a cancel-based recovery.