Point-in-time recovery deliberately returns a recovery scope to an earlier consistent state. The decision includes the legitimate work that will be lost after the boundary. This part explains whole container database recovery from physical backups, an exact SCN limit, and verification of the resulting application state.
Oracle DBA Lesson 32 Part 4 — Choose and Verify a Point-in-Time Recovery
A recovery boundary with a visible cost
| Commit | Change | Result wanted after recovery |
|---|---|---|
| SCN 100 | An approved price becomes USD 10 | Retain the correct price |
| SCN 120 | An unwanted update changes that price to USD 0 | Exclude the unwanted update |
| SCN 140 | A customer creates a valid new order | The order is lost with the later history |
The selected boundary is UNTIL SCN 120. It is an upper, noninclusive limit: the change at 120 is excluded. Recovery reaches a consistent state before that SCN, conventionally described as through SCN 119. Changes do not need to exist at every intervening number. Equal diagram spacing represents order, not elapsed seconds or a measured rate. SCNs are database change-order numbers. They are not wall-clock times or universally unique transaction IDs. See UNTIL clause.
The desired result is the correct USD 10 price, with the unwanted update excluded. The valid order committed at 140 is also absent. Returning a database to an earlier state abandons its later transaction history in that recovery scope. It cannot automatically pick only unwanted statements to remove. The application decision maker must accept that loss and plan any permitted reconciliation of legitimate business activity.
Whole-CDB recovery covers the root, the seed, and all PDBs, and it interrupts the database's services. If the incident is confined to a table or a PDB, evaluate an eligible narrower repair or Flashback option before accepting whole-CDB impact. Those alternatives need their own prerequisites and procedure. This part does not teach PDB point-in-time recovery, or the next part's complete recovery of one PDB. See whole-CDB point-in-time recovery and repair options.
Determine the target from evidence
For a real incident, identify the exact database, the current incarnation, and the affected transactions. Retain supported investigation evidence and check the approved target against the application history. An incident report saying "around nine twenty" does not by itself identify the commit boundary. Time-based recovery depends on timestamps recorded in redo. Choose a verified SCN when an exact boundary is required. See UNTIL SCN and time semantics.
Record a supported SCN after the good transaction has committed and before starting the unwanted transaction, while other activity is controlled. Distinguish that approved stop boundary from an alleged exact commit SCN. CURRENT_SCN queried later cannot be used as proof of when an earlier transaction committed. Investigation of an already completed incident requires an appropriate evidence procedure. Do not invent a commit SCN from a screenshot or an approximate clock time.
The target lies within the current incarnation. An earlier incarnation requires a separately reviewed incarnation and control-file procedure. SCNs from another database or recovery branch must not be substituted. See V$DATABASE identity and current SCN and recovery incarnation concepts.
Select an eligible starting backup
An earlier backup has a starting state at SCN 80, with price USD 8 and no later order. Required changes advance restored files to the approved boundary. A starting backup from after SCN 120 is too late. Recovery applies changes forward. Choosing a recent backup that already includes the unwanted state cannot produce the earlier target by ordinary redo application.
In an actual database, inspect each required data file's backup, checkpoint, and recoverability information. A backup job's completion time is not a universal checkpoint for all its files. Online backups can need redo even to make their restored contents consistent. Required backup pieces, selected incremental dependencies, and the full needed redo path must remain accessible, including the changes needed to finish at the target. Physical data files, disk backups, and redo logs reside on disk. This part teaches recovery file dependencies, not a memory-buffer mechanism. See RESTORE selection and locations and RECOVER sources and target prerequisites.
Conditions for point-in-time recovery
Use ARCHIVELOG mode, a retained current control file, reviewed startup settings, and a target in the current incarnation. Use a same-identity recovery copy. A matching DBID or a different directory name does not establish physical isolation. A new-identity alternate-host clone needs its own duplication workflow.
Retain backups of all required data files from before the chosen target, the required logs and selected incrementals, sufficient space, configured media channels, and encryption access where it applies. Use a root common SYSDBA or SYSBACKUP connection for RMAN. The SQL*Plus state-change examples use SYSDBA in the same root.
Mount and set the target before both operations
For an intact copy, use SQL*Plus in its root:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
Review a failed database's actual state before selecting a shutdown or startup path. Mounting reads the retained control-file inventory while application access stays closed. Match identity and every destination to the already verified recovery copy.
In RMAN, connected to that same root:
RUN {
SET UNTIL SCN <VERIFIED_STOP_SCN>;
RESTORE DATABASE;
RECOVER DATABASE;
}
SET UNTIL comes first so the same target governs backup selection and recovery termination. The RUN block keeps those operations under one reviewed scope. The setting lasts until the block ends or is changed. Setting an earlier target only after restoring can leave files too new to recover to it. See SET scope and RECOVER target timing.
If a preview is needed, use the same approved target in its own complete RUN block:
RUN {
SET UNTIL SCN <VERIFIED_STOP_SCN>;
RESTORE DATABASE PREVIEW SUMMARY;
}
A setting in that preview block expires when the block ends. Repeat the verified SET UNTIL in the actual restore and recover block. Preview reports repository-based selection. Actual accessibility, validity, and recovery success need separate evidence. See RESTORE PREVIEW.
Restore reconstructs the selected physical target files and can overwrite their contents. Inspect the restore log for the actual selected pieces and destinations. Ordinary restartable restore can skip already matching files. An intact copy that specifically needs FORCE does not treat FORCE as an incident default. Recovery applies the required changes to all files in the scope. A missing required log, an unexpected piece, or an unresolved error means stop and investigate. Preserve the error before changing any plan. See RESTORE behavior and RECOVER prerequisites.
Open the new recovery branch
After successful recovery to the approved point, while the CDB is mounted, use SQL*Plus at the same root:
ALTER DATABASE OPEN RESETLOGS;
Point-in-time recovery requires RESETLOGS for read/write opening. Resolve outstanding recovery requirements and errors before opening. RESETLOGS is not a generic response whenever normal OPEN fails.
The operation establishes a new database incarnation identifying a new stream of redo. New work follows the recovered state. Changes on the abandoned later branch are not automatically part of the new history. The branch diagram is a history relationship, not physical movement of log files. Record it in RMAN:
LIST INCARNATION;
Inspect the actual database identity, incarnation key and status, and reset SCN and time in the returned record. These fields describe history. They do not prove that the desired application data is present. Preserve appropriate previous backups, redo, and incarnation records. Oracle 19c can recover using eligible pre-RESETLOGS backups with the needed history and logs, so earlier backups are not automatically invalid. A fresh backup is a protection-plan choice. A fresh baseline is not a universal prerequisite Oracle imposes merely because RESETLOGS occurred. See incarnation and pre-RESETLOGS backup behavior and LIST INCARNATION.
Verify the application, retained work, and lost work
Inspect the required PDB open modes and open each application PDB that needs opening, according to its approved expected mode. Verify all required services, including PDBs unrelated to the original price incident. Do not assume that opening the root has restored application availability.
SELECT con_id, name, open_mode FROM v$pdbs ORDER BY con_id;
-- Only if APPDB is mounted and its approved mode is READ WRITE:
ALTER PLUGGABLE DATABASE APPDB OPEN;
Reconnect as the application account through the verified recovery service. Check the database, container, and service identity before interpreting price and order data:
SELECT sys_context('USERENV','DB_NAME') AS database_name,
sys_context('USERENV','CON_NAME') AS container_name,
sys_context('USERENV','SERVICE_NAME') AS service_name
FROM dual;
Use the prepared application's actual queries to verify its expected values: the correct retained price, exclusion of the unwanted change, and absence of the known legitimate later order. Also test the required application operation. Compare those observations with the approved loss record and resolve discrepancies.
Recap
Agree on the earlier consistent point, the recovery scope, and the legitimate later-work loss. Set the target before selecting and restoring backups and recovering changes. After successful recovery, open with RESETLOGS and record the new incarnation. Verify the required services and the application result, then protect the recovered state under the backup plan.
No comments:
Post a Comment