Performing Cancel-Based Recovery in Oracle AI Database 26ai

Cancel-based recovery is a user-managed method of incomplete database recovery. After restoring suitable data file backups, you use SQL*Plus to apply required redo and enter CANCEL at a log prompt when you reach the intended boundary. Oracle AI Database 26ai continues to document this procedure.

The command is RECOVER DATABASE UNTIL CANCEL. The stopping decision determines how far the restored database advances. It does not select one transaction to undo or automatically locate the change that dropped a table. The database must reach a consistent recovered state before it can reopen.

This lesson uses SQL*Plus for the interactive procedure and compares it with an RMAN sequence target. RMAN supports time, SCN, and sequence boundaries, but it does not have an UNTIL CANCEL clause. Keep the two interfaces separate when planning or executing recovery.

Scenario: Required Redo Is Unavailable

Inspect Online Redo Groups and Members

Begin with the correct CDB and an authorized root session. The following query combines group information with member filenames. It can be used while the database is mounted for recovery:

SELECT l.thread#, l.group#, l.sequence#, l.archived,
       l.status AS group_status,
       f.status AS member_status, f.member,
       l.first_change#,
       TO_CHAR(l.first_time, 'YYYY-MM-DD HH24:MI:SS') AS first_time
FROM v$log l
JOIN v$logfile f ON f.group# = l.group#
WHERE f.type = 'ONLINE'
ORDER BY l.thread#, l.group#, f.member;

A redo group can have multiple multiplexed members containing the same redo. Consequently, multiple result rows may describe one group. Group numbers identify reusable groups, sequence numbers identify their successive redo contents, and filenames identify members. A filename such as redo01.log does not permanently mean sequence 74.

Interpret group_status separately from member_status. An INACTIVE group is no longer required for instance recovery, but its redo may still be needed for media recovery from a backup. An ARCHIVED value of NO does not mean that its online members are unreadable.

The query provides metadata and investigation leads. A recorded member path does not freshly prove that the file exists, can be read, and contains usable redo. Correlate the results with storage checks, diagnostics, and recovery or validation evidence appropriate to the incident.

Check Archived Redo and Backup Coverage

Inspect the archived-log records around the suspected gap. Include the thread and incarnation so that reused sequence numbers are not confused with the redo needed for this recovery:

SELECT thread#, sequence#, resetlogs_change#,
       first_change#, next_change#,
       TO_CHAR(first_time, 'YYYY-MM-DD HH24:MI:SS') AS first_time,
       TO_CHAR(next_time, 'YYYY-MM-DD HH24:MI:SS') AS next_time,
       dest_id, archived, status, deleted, name
FROM v$archived_log
WHERE thread# = 1
  AND sequence# BETWEEN 72 AND 75
  AND resetlogs_change# =
      (SELECT resetlogs_change# FROM v$database)
ORDER BY sequence#, dest_id, name;

Several rows can represent different copies of one archived log. An available status does not constitute a fresh validation of the file's contents. Conversely, no matching row does not prove that another destination, backup, or external copy cannot supply the redo. Check the backup repository and the actual recovery material.

V$LOG_HISTORY helps correlate recorded redo intervals, but it does not inventory readable archive files. Absence of sequence 74 from that view cannot establish that no recoverable copy exists. Its RECID column identifies a control-file record; it is not the log sequence number.

Similarly, V$ARCHIVED_LOG.APPLIED principally describes application on a physical standby. It is not a general progress indicator for media recovery of the primary. Use the recovery output and the monitoring methods from Lesson 5 to assess actual application.

These queries deliberately examine thread 1 in the current incarnation. A RAC recovery must account for all required threads. Equal sequence numbers in different threads do not imply identical database recovery points.

Choose the Boundary and Assess Data Loss

For the verified recovery path in this example, apply the necessary redo through sequence 73 and stop before sequence 74. Sequence 75 cannot replace missing required changes from sequence 74. Finding a later archive does not make the gap disappear.

This limit applies to the selected recovery chain. Suitable later backups or incremental backups can change which redo is required in other recovery plans. That is why the scenario explicitly assumes those alternatives have already been investigated.

Suppose sequence 74 began around 11:50 AM and the incident was assessed at noon. This suggests that roughly ten minutes of later database activity may be excluded. It does not establish an exact number of lost transactions, rows, or minutes of business work. FIRST_TIME describes redo timing.

A transaction can start before the boundary and commit afterward. Its start time alone does not prove its committed result will be present after recovery. Use application records and available commit evidence to determine which valid work requires reconciliation.

The lunchtime assumption provides no protection: scheduled jobs, integrations, and other PDBs may continue processing work. Confirm the acceptable recovery scope and expected application state before overwriting data files.

Prepare and Restore the Data Files

The backups must support the selected endpoint, including enough redo to make all required files consistent. They may be valid consistent backups or properly made online backups with the required redo. The newest overall backup is not automatically appropriate: ordinary forward media recovery cannot roll newer file contents backward.

Preserve the current control file assumed here. Do not replace surviving control files or online redo by copying every file from an old backup directory. A file map and the actual storage arrangement determine what must be restored and where it belongs.

A fresh closed database backup is not a universal prerequisite. It may preserve useful current-state material, but it does not substitute for the earlier backups needed to recover through sequence 73. Neither SHUTDOWN ABORT nor STARTUP FORCE is a mandatory default for this procedure.

Mount and Start Interactive Recovery

STARTUP MOUNT;
SHOW CON_NAME
SET AUTORECOVERY OFF
RECOVER DATABASE UNTIL CANCEL

ORA-00280: change 5782000 for thread 1 is in sequence #74
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL

Check the Recovered State Before Reopening

Correlate the recovery output with the correct instance's alert log. A log request such as ORA-00280 identifies required redo, while other messages may report an actual failure. Interpret the full message sequence rather than treating every ORA prefix identically.

With the current control file used in this example, inspect files reported as needing recovery:

SELECT file#, online_status, error, change#, time, con_id
FROM v$recover_file
ORDER BY file#;

CHANGE# identifies where recovery must start for the file. It does not identify the historical DROP or certify the selected endpoint. An empty result supports investigation but is not a standalone application acceptance test. Backup-control-file recovery also changes how this diagnostic should be interpreted.

ORA-01547 warns that OPEN RESETLOGS would fail with an accompanying error, even though the recovery attempt ended. ORA-01152 can identify a file that is too recent for the applied recovery state. Resolve these conditions before trying to open.

Additional redo may make the files consistent when that redo is available and remains within the acceptable target. Otherwise, a sufficiently old backup and a revised recovery attempt may be necessary. Repeating CANCEL cannot compensate for unavailable redo required to make the restored files consistent.

Open RESETLOGS and Verify the Containers

Only after successful recovery to an acceptable consistent state, open the CDB:

ALTER DATABASE OPEN RESETLOGS;

RESETLOGS establishes a new database incarnation and restarts redo sequencing. It does not make inconsistent data files consistent, reset database SCNs to 1, or change DBID to 1. The RESETLOGS_TIME value records the reopening event, which is different from the historical recovery target.

Open the intended PDBs according to the operating plan. If all applicable PDBs should open, a root SQL*Plus session can use ALTER PLUGGABLE DATABASE ALL OPEN;. Then check the actual states:

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

Keep application access controlled while validating the result. A successful CDB open does not itself establish that every required PDB is open or that CUSTOMERS contains the expected business data.

RMAN Alternative: Specify the Sequence Boundary

When the stopping sequence is known, RMAN can express the boundary directly. This is an alternative workflow using suitable backups, the current control file, and the same whole-CDB target. Connect RMAN to the root with appropriate SYSBACKUP or SYSDBA privileges and mount the CDB before this block:

RUN {
  SET UNTIL SEQUENCE 74 THREAD 1;
  RESTORE DATABASE;
  RECOVER DATABASE;
}

The sequence limit is noninclusive: sequence 74 is excluded, so the target is through sequence 73. SET UNTIL precedes both RESTORE and RECOVER so that backup selection and recovery use the same boundary. After successful recovery, perform the RESETLOGS open and verification described above.

RMAN also documents RECOVER DATABASE UNTIL AVAILABLE REDO for whole-database recovery. It follows available redo to a missing-log boundary. It does not express the separate business requirement to exclude a particular DROP, and available logs may include unwanted changes. The option is not supported for an individual data file, tablespace, or PDB.

Validate the Application and Protect Future Recovery

Connect to the PDB and schema that own CUSTOMERS. Confirm representative customer keys, important attributes, and relationships with orders or other application data. Object existence and a matching row count alone cannot establish that the database reached the desired business state.

Also confirm that the original storage problem has been resolved and assess the affected data for continuing corruption. Record the achieved recovery result and any valid transactions excluded after the selected boundary.

Reconcile those transactions using authoritative records before replaying them. A payment may already have settled or a message may already have been consumed outside the recovered CDB. Automatic or manual re-entry without checking those effects can create duplicates.

A fresh database backup after RESETLOGS is optional in Oracle 26ai. It can provide a useful baseline under the backup policy, and an ARCHIVELOG backup does not inherently require another shutdown. Earlier backups remain usable with the necessary recovery chain and incarnation information; preserve them according to retention requirements.

Multiplex online redo across suitable independent storage and protect archived redo to reduce exposure to a similar failure. In Data Guard environments, assess the documented procedures for the standby to follow the recovered primary. A RESETLOGS operation does not automatically mean every standby must be rebuilt.

This lesson assumes that the current control file survived. Losing all current control files introduces additional recovery requirements. The next lesson shows how to perform recovery using a backup control file.