Point-in-Time Recovery, Duplicate Database, Restore Drills, RPO/RTO, and Backup Security

Learning outcomes

ServiceHub policy says “backups run every hour, therefore RPO is one hour and RTO is two hours.” No one has recovered a PDB, measured the elapsed restore, or proved that the TDE/backup-encryption keys are available off-host. Schedule frequency is an input—not an RPO/RTO result. A disaster-recovery system is successful only when a tested recovery can reach an acceptable point and return a validated service within the required time.

1. RPO is acceptable data-loss point; RTO is acceptable restoration duration

Recovery Point Objective (RPO) is the maximum acceptable loss of committed business data, normally expressed as a time/SCN distance from the incident to the latest recoverable point. Recovery Time Objective (RTO) is the maximum acceptable time to restore usable service.

A backup every hour does not automatically provide one-hour RPO: archived redo gaps, failed backup jobs, unprotected control-file metadata, missing encryption keys, or a corrupt backup can move the actual recoverable point backward. Likewise, a “two-hour restore estimate” is not RTO evidence until the full workflow is timed.

2. Pick the PITR target from evidence before touching files

SELECT  DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS current_scn,  SYSTIMESTAMP AS captured_atFROM dual;SELECT *FROM (  SELECT    thread#,    sequence#,    first_change#,    first_time,    next_change#  FROM v$log_history  ORDER BY first_time DESC)FETCH FIRST 20 ROWS ONLY;

For a user error, use audit/application logs, Flashback Query, transaction evidence, alert logs, and redo history to identify the latest safe target before the damaging operation. Record both human-readable time and the chosen SCN/log boundary.

3. Whole-database PITR rewinds the whole CDB and creates a new incarnation

SHUTDOWN IMMEDIATE;STARTUP MOUNT;RUN {  SET UNTIL SCN 123456789;  RESTORE DATABASE;  RECOVER DATABASE;}ALTER DATABASE OPEN RESETLOGS;LIST INCARNATION;

RESETLOGS resets online redo sequence context and creates a new database incarnation. Modern RMAN can navigate incarnations when metadata/backups are available, but operational procedures must record which incarnation is current and take a new recovery baseline after major PITR events.

4. PDB PITR narrows the logical rewind

-- SQL*Plus/root:ALTER PLUGGABLE DATABASE FREEPDB1 CLOSE;-- RMAN/root:RUN {  SET UNTIL SCN 123456789;  RESTORE PLUGGABLE DATABASE FREEPDB1;  RECOVER PLUGGABLE DATABASE FREEPDB1;}-- SQL*Plus/root:ALTER PLUGGABLE DATABASE FREEPDB1 OPEN RESETLOGS;

Other PDBs can remain available. PDB PITR requires backups covering the target and appropriate redo/undo metadata; recovering to orphan PDB incarnations has local-undo prerequisites. A PDB RESETLOGS creates a new PDB incarnation rather than rewinding every other PDB.

5. RMAN DUPLICATE is the preferred isolated recovery laboratory

DUPLICATE creates an auxiliary database from backups or from an active source. It is used for test/recovery drills, development clones, migration staging, and standby creation. The auxiliary instance needs storage, initialization parameters/SPFILE, password-file/Oracle Net setup as required, and—when encryption is involved—the necessary keystore.

# RMAN connects to source TARGET and prepared AUXILIARY.# Passwords are prompted/retrieved securely, never embedded in the command file.DUPLICATE TARGET DATABASE TO sh15dup  FROM ACTIVE DATABASE  SPFILE    SET db_unique_name='sh15dup'    SET db_create_file_dest='/u02/oradata/sh15dup';

Active duplication copies from the live target over Oracle Net and can use backup sets/image copies. A backup-based duplicate is valuable for proving that stored backups work without reading current source data. Free permits only one installation per logical environment, so a second Free auxiliary must be in another logical environment such as another VM/container host context that complies with the restriction.

6. Backup encryption is recoverability plus key management

RMAN can transparently encrypt backup sets using an Oracle keystore, use password-based encryption, or use dual-mode strategies. Encrypted backup bytes are useless without the required key material/password. Back up/escrow the keystore and recovery credentials separately from the database host and test them during restore drills.

In Oracle AI Database 26ai, RMAN backup encryption supports enhanced AES-XTS algorithms. When COMPATIBLE >= 23.0.0, the configured/default new-backup encryption uses AES-XTS, with AES256 (XTS) the default algorithm; existing older AES-CFB backups remain restorable.

SHOW ENCRYPTION ALGORITHM;SELECT algorithm_name,algorithm_description,is_defaultFROM v$rman_encryption_algorithmsORDER BY algorithm_name;

CONFIGURE ENCRYPTION FOR DATABASE ON;CONFIGURE ENCRYPTION ALGORITHM TO 'AES256';BACKUP AS BACKUPSET  CURRENT CONTROLFILE  TAG 'SH15_ENCRYPTED_TEST';

Do not enable transparent backup encryption unless the keystore lifecycle is already engineered. In current licensing, RMAN backup encryption to disk is part of Oracle Advanced Security: included in Free but an extra-cost option for EE/EE-ES. Re-check the deployed offering.

7. Compression and encryption are separate decisions

You can encrypt a backup without choosing Advanced Compression algorithms, and you can use BASIC backup compression without the Advanced Compression option. LOW/MEDIUM/HIGH compression has different licensing/CPU tradeoffs. The best combination depends on CPU, storage, network, media manager, encryption overhead, and RTO.

8. Deliberately wrong: store wallet/keystore backups beside the encrypted backups only

If the same host/volume/account compromise destroys both encrypted backup pieces and the only keystore backup, encryption becomes a self-inflicted recovery failure. If an attacker steals both with unrestricted credentials, encryption protection may also be weakened. Separate failure/security domains and access control are part of backup architecture.

9. Timed restore drill: measure actual RPO/RTO

Do not run a destructive drill against FREE. Use SH15DUP or another isolated duplicate environment. Define the stopwatch boundary before starting.

10. Cross-platform timing wrappers

date -Istime rman target / @sh15_restore_validate.rmandate -Is

$started = Get-Date& rman.exe target / cmdfile='C:\drill\sh15_restore_validate.rman'$ended = Get-Date$ended - $started

A RESTORE ... VALIDATE drill measures backup-read path but not full RTO. Periodically run a genuine restore/recover/duplicate drill to isolated storage and include application validation.

11. Application acceptance checks matter

SELECT SYS_CONTEXT('USERENV','CON_NAME') AS con_name FROM dual;SELECT COUNT(*) AS work_ordersFROM servicehub_owner.work_orders;SELECT MAX(work_order_id) AS latest_work_order_idFROM servicehub_owner.work_orders;SELECT status_code,COUNT(*)FROM servicehub_owner.work_ordersGROUP BY status_codeORDER BY status_code;

Replace these with real business invariants: latest accepted transaction, ledger totals, referential checks, required scheduler jobs, services, users/grants, and integration connectivity. A database that opens but fails business invariants has not met RTO.

12. Production judgment and chapter bridge

Backups are a recoverability system, not a schedule. Keep bootstrap metadata, archived redo, backup media, catalog metadata, and encryption keys in deliberate independent failure domains. Measure RPO/RTO through recurring isolated restores, rotate which backup sets/regions/media are tested, and feed observed durations/errors back into capacity and policy.

The 26ai RU baseline is 23.26.3. RMAN encryption requires COMPATIBLE >= 10.2; current AES-XTS behavior applies when COMPATIBLE >= 23.0.0. Oracle Advanced Security is included in Free but extra-cost on EE/EE-ES for RMAN disk encryption; BASIC compression is the portable no-Advanced-Compression-option path. DUPLICATE and multi-host DR drills require an auxiliary topology and independent storage/network/key setup.

Authoritative references

• Performing Flashback and Database PITR — database/PDB PITR and RESETLOGS/incarnation prerequisites
• Duplicating Databases — active/backup-based duplicate and auxiliary requirements
• Configuring RMAN — Backup Encryption — 26ai AES-XTS algorithms and encryption modes
• Licensing Information — Oracle Advanced Security/Advanced Compression boundaries
• Backup and Recovery User's Guide — restore drill and recovery architecture