Types of Incomplete Recovery in Oracle AI Database 26ai

Separate the Endpoint, Control File, and Scope

A useful recovery plan answers three questions: how far should recovery proceed, which control file will support the operation, and what database scope will be recovered? Keeping these questions separate makes both the commands and their consequences easier to understand.

In Oracle AI Database 26ai, whole-database point-in-time recovery concerns the multitenant container database, or CDB. A pluggable database, or PDB, can be the scope of a separate PDB recovery procedure. Tablespace point-in-time recovery, or TSPITR, is another specialized operation. None of these scopes determines whether the target is expressed as a time or an SCN.

This lesson compares endpoint choices and their limitations. The command fragments below illustrate separate alternatives, not a sequence to execute. The RMAN target-setting statements belong inside an appropriate RUN block before its restore and recovery commands. Preparation, restoration, verification, and reopening are developed in the next lesson.

Compare the Main Recovery Endpoint Methods

Time-Based Recovery

Time-based recovery is useful when incident evidence is expressed as a clock time. Suppose an incorrect batch commits changes at approximately 14:30. A target before that event can exclude the unwanted changes, provided the available backups and recovery history support the selected endpoint.

Distinguish the time the batch started, the time its changes committed, and the time someone discovered the problem. Recovering to just before discovery may leave the harmful changes in place. A batch that commits repeatedly may require an endpoint before its first harmful commit rather than before its final completion message.

SQL*Plus syntax illustration:

RECOVER DATABASE UNTIL TIME '2026-09-10:14:29:00'

SQL*Plus uses the displayed date format for this command. RMAN accepts a date expression, so an explicit format mask makes the intended interpretation clearer and avoids dependence on the session's default date format.

RMAN target-setting illustration:

SET UNTIL TIME "TO_DATE('2026-09-10 14:29:00', 'YYYY-MM-DD HH24:MI:SS')";

The RMAN example supplies a date format, not a time zone. Reconcile application timestamps with the database recovery context before choosing the value. The sample timestamp illustrates syntax; it does not establish a suitable target for another database or incident.

Time-based recovery also depends on timestamps recorded in redo and their relationship to database changes. A clock-time target should not be presented as a guarantee of arbitrary subsecond precision. When a reliable SCN for the required boundary is available, an SCN-based target provides a more direct expression of that boundary.

RECOVER DATABASE UNTIL CHANGE 850001

SET UNTIL SCN 850001;

Log Sequence-Based Recovery

A log sequence target is useful when the recovery boundary is known in terms of redo logs. For example, you may have confirmed that sequence 480 is the first required sequence that cannot be supplied, after investigating alternate copies and backup locations.

RMAN target-setting illustration for thread 1:

SET UNTIL SEQUENCE 480 THREAD 1;

The specified sequence is excluded. In a simple single-thread example, this means recovery proceeds through the preceding required redo and stops before sequence 480. It does not mean that Oracle applies sequence 480 and then stops. The available recovery material must still bring the restored database to a consistent state at the resulting endpoint.

Sequence numbers are associated with redo threads and incarnations. In RAC, multiple threads contribute to the database's recovery history. A target naming one thread does not permit other files or threads to remain at incompatible states. All redo needed for the chosen consistent endpoint must be available.

Sequence-based recovery is therefore a way to express a known boundary, not a license to skip a required log. If another valid copy of the missing redo becomes available, a later target or complete recovery may again be possible.

Cancel-Based Recovery

Cancel-based recovery allows an operator to stop user-managed recovery while responding to SQL*Plus prompts for redo files. It is commonly associated with older recovery procedures and missing-log scenarios, where the operator supplies available redo and stops at a selected boundary.

SQL*Plus syntax illustration:

RECOVER DATABASE UNTIL CANCEL

When the intended boundary is reached, the operator enters CANCEL at the recovery prompt instead of another filename. This is a SQL*Plus recovery mode. RMAN does not use UNTIL CANCEL in the same way; its usual approach is to establish an explicit target.

Do not cancel merely because the suggested pathname is unavailable. The required redo may exist in another archive destination, a backup, suitable standby storage, or an online redo log. Conversely, cancelling after applying some logs does not prove that the restored files are sufficiently recovered to open. Oracle's consistency requirements still apply.

The operator must know which history has been applied and why stopping there is appropriate. Cancel-based recovery offers control over the interaction, but an explicit time, SCN, or sequence target is often easier to document and reproduce when the endpoint is already known.

Named Restore Points and Available Redo

A named restore point gives a memorable name to a recorded SCN. It can help identify a planned boundary, such as the state before an application deployment. Applicable Oracle recovery workflows can use restore-point names, avoiding the need to communicate an otherwise opaque number.

A normal restore point does not preserve missing backups, redo, or flashback history. It must not be confused with a guaranteed restore point, which has separate flashback guarantees and storage implications. Also, do not assume every restore-point operation uses the same exclusive boundary convention as an RMAN UNTIL SCN expression.

Another RMAN option, RECOVER DATABASE UNTIL AVAILABLE REDO, performs whole-database recovery until a log is missing. It is not supported for an individual data file, tablespace, or PDB. This option changes the database through recovery; it is not a read-only report of available logs or a substitute for deciding whether the resulting state is acceptable.

Recovery Using a Backup Control File

The control file describes database structure and records information needed for recovery. An intact current multiplexed copy may be usable when another copy is lost. When all usable current copies are unavailable, restoring a backup control file may be necessary. Some historical recovery scenarios also require appropriate structural metadata from an earlier state.

These conditions do not create a separate time-selection method. In SQL*Plus, USING BACKUP CONTROLFILE identifies the recovery context. In RMAN, restoring a backup control file establishes that context without adding the SQL*Plus clause to RMAN recovery commands.

Complete recovery can still be possible with a backup control file when the necessary files and recovery information are available. Nevertheless, recovery using a backup control file requires RESETLOGS. Its use alone is therefore not proof that committed transactions were lost.

A useful preparation operation is to generate a control-file reconstruction script:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

This produces a text script in a trace file, not a binary backup of the control file. The script can become stale after structural changes and is not a replacement for RMAN control-file backups. Multiplexed current control files, control-file autobackups, and appropriate reconstruction information provide complementary forms of protection.

Recovery Scope Determines the Reopening Procedure

After whole-CDB incomplete recovery, opening the database with RESETLOGS establishes a new incarnation. Subsequent redo belongs to the new continuation of database history. RESETLOGS does not create a new DBID or make every earlier backup and archived log unusable.

PDB point-in-time recovery follows the documented PDB procedure, including reopening the recovered PDB with RESETLOGS. It does not mean that the entire CDB should be opened RESETLOGS to complete a scoped PDB operation. Unaffected PDBs are not deliberately rewound merely because another PDB is recovered.

TSPITR uses an auxiliary recovery workflow. Recovering selected tablespaces to a past state does not require opening the production target database RESETLOGS solely because TSPITR occurred. This is why the broad instruction to open the database RESETLOGS after every operation described as point-in-time recovery is misleading.

Preserve recovery material according to retention requirements. RMAN can use suitable backups and redo along supported ancestral recovery paths. A fresh backup after successful recovery is a practical baseline, while earlier backups may remain important for investigation or further recovery.

Choose a Target That Matches the Business Need

Consider how the same incident can lead to different target expressions. An application log may initially identify a harmful operation at 14:30, suggesting time-based recovery. Further investigation may establish its exact database change boundary, making an SCN target preferable. If the constraint is instead an unavailable redo interval, a verified sequence boundary may be the clearest expression. These are different ways of describing the recovery decision, not different kinds of business damage.

Record the reason for selecting the endpoint along with its value. Include the relevant time interpretation, redo thread where applicable, and the evidence connecting the target to the incident. Another DBA reviewing the plan should be able to explain why the proposed endpoint excludes the unwanted change or precedes the unavailable recovery information.

Then check that the restore operation selects backups compatible with that endpoint. A newer backup may already contain changes beyond the target, so selecting the most recent backup without considering the target can defeat the intended recovery. This is why an RMAN target normally applies to both restore and recovery within the same planned operation.

Finally, define how success will be recognized. A successful open demonstrates that Oracle accepted the recovered database state, but business verification must establish that the intended records are present and the unwanted effects are absent. Representative application checks should be identified before recovery so that the reopening decision is based on evidence.

A mistaken table truncation illustrates why a DBA might intentionally exclude available redo. Replaying all history would reproduce the truncation. Ordinary ROLLBACK does not reverse a committed TRUNCATE, and recycle-bin Flashback Drop does not recover a table merely because it was truncated. Evaluate supported targeted recovery and Flashback options according to the actual operation and available history.

If an earlier state is selected, later valid commits within the recovered scope are excluded along with unwanted changes. Identify those consequences before choosing the target. The amount of actual lost work depends on what occurred after the endpoint; the term incomplete recovery does not itself count missing transactions.

Coordinate verification and reconciliation with application owners. Orders, payments, or messages may already exist outside the recovered database. Re-entering work without checking those systems can duplicate operations. A technically consistent recovery endpoint must also correspond to a business state that can be accepted and reconciled.

The next lesson explains the recovery command syntax and the preparation, restore, recovery, and verification steps used in an incomplete database recovery.