Oracle DBA Revision 4 — Lessons 31 to 32 and 81

Contents

Recovery scope and targetBackup chainsBackup commandsRecovery pathsFlashbackRestore pointsBackup checksRecovery verification

Recovery scope and target

Identify the affected data and the state the application needs. Complete recovery preserves the committed work that can be reconstructed from all required recovery data. Point-in-time recovery returns the selected scope to an earlier consistent state and discards later changes within that scope.

Scope

A data file, table, PDB or whole database. Dependencies and the recovery method determine which boundary is valid.

Recovery point

The latest committed state or a specific earlier boundary. Use evidence from the correct database and incarnation.

Service return

Database opening, required PDB modes, application checks and working connections all contribute to restoration time.

RPO is the acceptable data-loss interval. RTO is the target time to restore usable service. A 15-minute RPO and a 60-minute RTO express two different requirements; meeting one does not establish the other.

Example

A valid order commits at SCN 4800, an unwanted batch commits at 5000, and another valid order commits at 5200. A whole-database recovery with UNTIL SCN 5000 excludes both commits at 5000 and 5200. The valid order at 4800 can remain when the recovery chain supports that target.

4800
Valid commit retained→5000
Upper boundary excluded→5200
Later commit excluded

SET UNTIL belongs before both RESTORE and RECOVER, so backup selection matches the target. Whole-CDB point-in-time recovery affects its PDBs. A single-PDB incident needs its own documented scope and prerequisites. Incomplete recovery normally finishes through OPEN RESETLOGS, creating a new incarnation; this is not a way to bypass unresolved recovery errors.

Backup chains

Recovery Manager (RMAN) can restore a full backup, a level 0 backup or an image copy, then apply the required recovery data. A level 0 is an incremental-chain base. An ordinary full backup can contain similar data blocks but is not a level 0 base.

Differential level 1

Sunday L0 → Monday L1 → Tuesday L1Each L1 covers changed blocks since the latest applicable incremental backup.

Cumulative level 1

Sunday L0 → Tuesday cumulative L1The cumulative L1 covers changed blocks since the level 0.

Redo still supplies changes needed beyond the selected backup checkpoints. Online backups require ARCHIVELOG mode and the necessary redo. A consistent, closed NOARCHIVELOG backup has a different recovery limit. Protect control-file records, database identity, startup configuration and encryption keys alongside accessible backup media. RMAN does not include every external file, such as network configuration or the password file.

Example

Two backup pieces on the same storage device can disappear in the same device failure. Keep recovery copies and the information needed to find them outside the failure being planned for. Record piece handles, available channels and key access so a recovery operator can locate and read the inputs.

-- In RMAN, connected to the intended target:
LIST BACKUP SUMMARY;
REPORT SCHEMA;
SHOW ALL;

Use an authorized SYSBACKUP or SYSDBA connection with the appropriate database scope. The inventory shows recorded backups and configuration; it does not by itself prove that their blocks can be read.

Backup commands

In a designated Oracle 19c lab, connect RMAN to the intended CDB root with SYSBACKUP or SYSDBA. For an online backup, confirm ARCHIVELOG mode, accessible archive logs, configured backup channels and enough destination space. Protect the control file, startup configuration and encryption keys required for recovery.

BACKUP AS BACKUPSET INCREMENTAL LEVEL 0 DATABASE
  PLUS ARCHIVELOG TAG 'BASE_BACKUP';
LIST BACKUP SUMMARY;

The explicit level 0 establishes the incremental base. PLUS ARCHIVELOG includes archived redo in the backup operation; it does not guarantee that every later recovery target will have all its required logs. Confirm the resulting pieces, completion, log coverage and access.

Level 1 alternatives

BACKUP AS BACKUPSET INCREMENTAL LEVEL 1 DATABASE
  TAG 'CHANGED_BLOCKS';

BACKUP AS BACKUPSET INCREMENTAL LEVEL 1 CUMULATIVE DATABASE
  TAG 'CHANGES_FROM_BASE';

Choose the variant that matches the backup design. The first command is differential; the second is cumulative. A cumulative backup can contain more changed blocks while reducing the number of intervening incremental backups needed for recovery. Keep the base and required redo until the recovery policy no longer needs them.

Note. Tags help identify backups. They do not establish that the pieces are readable or that a recovery target can be reached.

Recovery paths

Conditions determine the opening command

Path	Required completion	Opening
Complete database recovery, current control file	Apply all required changes successfully	ALTER DATABASE OPEN;
Database point-in-time recovery	Restore suitable backups and recover to the selected earlier target	ALTER DATABASE OPEN RESETLOGS;
Recovery with a restored backup control file	Perform the required media recovery, including when no data file was restored	ALTER DATABASE OPEN RESETLOGS;

RESETLOGS starts a new database incarnation. Resolve missing redo and other recovery errors before opening. Follow the backup policy for the new state and retain the earlier recovery chain when it remains needed.

Control-file bootstrap

NOMOUNT
Start the instance→Restore control file
Use verified backup identity→MOUNT
Plan and complete recovery

Without a current control file or a recovery catalog, restoring an autobackup normally needs the correct database identifier (DBID), backup location and search settings. A configured control-file autobackup can include the SPFILE when it is in use. Recovering the startup configuration and control file is only the beginning of the recovery procedure.

One PDB

For complete recovery confined to the INVENTORY PDB, an appropriate PDB procedure can preserve other open PDBs. Confirm the root connection and privileges, affected file type, available backups and redo, and required close/offline steps. Missing SYSTEM files require their documented procedure. After recovery, open the intended PDB and check its application service. Whole-CDB recovery is a different scope.

Watch Oracle DBA Revision 4 — Lessons 31 to 32 and 81 on YouTube

Flashback

Feature	Effect	History and conditions
Flashback Query	Read past committed rows	Available undo, suitable object privileges and valid historical scope
Flashback Table	Return selected tables to an earlier state	Undo, row movement, required privileges and compatible table history
Flashback Database	Rewind existing data files	Flashback logs, redo, intact files and configured recovery storage

Flashback Table and Flashback Database are available in Oracle 19c Enterprise Edition and unavailable in Standard Edition 2. Verify the exact deployment entitlement. Flashback Table cannot cross structural changes such as a truncate, and its statement cannot be cancelled by a later transaction rollback. Related tables may need a coordinated operation to preserve application consistency.

Inspect an earlier order status

In an existing application table orders(order_id, status), record a database-generated SCN before the change to be examined. Use a session with SELECT and FLASHBACK access to that table, and permission to execute DBMS_FLASHBACK. The required undo must remain available.

VARIABLE saved_scn NUMBER
EXEC :saved_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
SELECT order_id, status FROM orders WHERE order_id = 204;

-- Later, in the same session with the saved bind:
SELECT order_id, status
FROM orders AS OF SCN :saved_scn
WHERE order_id = 204;

SELECT order_id, status FROM orders WHERE order_id = 204;

If the status changed from READY to CANCELLED after the recorded SCN, the historical query can return READY while the current query returns CANCELLED. Both SELECT statements leave current data unchanged. Missing historical rows or an unavailable-history error must be interpreted with the target SCN, object history and undo availability.

Restore points

A normal restore point names an SCN. A guaranteed restore point retains the flashback history required for its target under the documented conditions. That retention can consume the fast recovery area; monitor space and assign an expiry decision. A configured retention target alone does not guarantee the entire requested window.

SELECT name, scn, guarantee_flashback_database, storage_size
FROM v$restore_point;

SELECT name, space_limit, space_used, space_reclaimable
FROM v$recovery_file_dest;

These root-level inspections require access to the named views. Compare the reported location and quota with the configured recovery area. Space columns are bytes; STORAGE_SIZE estimates space tied to a guaranteed restore point and is zero for a normal restore point. Inspect the applicable PDB, local-undo and restore-point rules before applying a root workflow to a PDB.

Backup checks

Check	What it establishes
RESTORE DATABASE PREVIEW	Which recorded backups RMAN would select
RESTORE DATABASE VALIDATE	Whether selected restore inputs pass the performed block validation
CROSSCHECK BACKUP	Availability through the configured access path; updates repository status
REPORT OBSOLETE	Backups no longer needed by the configured retention policy

RESTORE DATABASE PREVIEW;
RESTORE DATABASE VALIDATE;

Run these in RMAN against the intended recovery scope. Preview uses metadata. Validation reads backup blocks without writing restored data files and can consume substantial I/O. Review the reported pieces and errors. Neither command demonstrates application correctness after recovery.

Note. EXPIRED and OBSOLETE have different meanings. A backup can be temporarily inaccessible and marked EXPIRED while still being needed. Correct channel or media access before treating the status as a retention decision.

Recovery verification

Use an isolated recovery copy with verified destinations and no shared source data/control/recovery-area paths. Check the database identity, current or restored control file, recovery target, available redo and required keys. A current-control-file complete recovery normally opens without RESETLOGS; backup-control-file recovery and incomplete recovery follow different opening procedures.

After recovery, check the intended data and application path:

-- Authorized administrator in the intended root:
SELECT name, open_mode FROM v$database;
SELECT name, open_mode, restricted FROM v$pdbs;

-- Application session through its intended service:
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name,
       SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name
FROM dual;
SELECT order_id, status FROM orders WHERE order_id = 204;

Compare the observed order state with the selected recovery point, check related data and exercise the application request. Record which committed work is present, any intentionally excluded work, and elapsed time until the service is usable. A successful open message answers only part of that verification.

Recovery-time example

A recovery takes 35 minutes to restore, 20 minutes to apply changes and 15 minutes to validate application access and data. Usable service returns after 70 minutes. Against a 60-minute RTO, that is 10 minutes late. Evaluate the recovered point separately to determine whether the data-loss requirement was met.

References

• Backup and recovery concepts
• RMAN backup chains
• Complete database recovery
• Point-in-time recovery
• UNTIL boundaries
• Flashback Query
• Flashback Table prerequisites
• Flashback Database and restore points
• RESTORE, PREVIEW and VALIDATE
• RECOVER
• Backup validation
• CROSSCHECK status
• Feature availability by edition
• BACKUP syntax and incremental levels
• Recovery when configuration and control files are lost
• Restore-point space and container scope
• Recovery-area usage and quota
• Retention-policy reporting

https://www.apps-dba.com/https://www.youtube.com/@EnhanceSkillsDaily