PDB Flashback vs CDB Flashback After a Failed Upgrade
CDB1 runs 19c with PDBs APP and REPORTING. FLASHBACK DATABASE is enabled for the CDB, the FRA is 800 GB, and the retention target is two hours. Before a vendor upgrade on Saturday, the DBA creates a guaranteed restore point named BEFORE_UPGRADE for PDB APP only. The upgrade runs for five hours. On Monday, users report that a critical table in APP contains wrong data from the upgrade, but REPORTING is fine. The FRA alert shows flashback logs consuming most of the space. The DBA considers FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE and FLASHBACK PLUGGABLE DATABASE APP TO RESTORE POINT BEFORE_UPGRADE. A Data Recovery Advisor check also reports an offline missing datafile in REPORTING, unrelated to the upgrade.
1. Which flashback command meets the requirement with the least impact, and what are the prerequisites and side effects?
Read the worked answer
The least invasive command is FLASHBACK PLUGGABLE DATABASE app TO RESTORE POINT before_upgrade;, run from CDB$ROOT after ALTER PLUGGABLE DATABASE app CLOSE IMMEDIATE;. It rewinds only APP; REPORTING and the rest of the CDB are unaffected. Prerequisites: the CDB must have flashback database enabled, APP must have local undo so its undo is isolated, the guaranteed restore point must still exist, and the required flashback logs must be present. After the command, open APP with ALTER PLUGGABLE DATABASE app OPEN READ WRITE;. Side effects: all APP changes after the restore point are discarded, including any post-upgrade fixes; REPORTING is not rewound. FLASHBACK DATABASE TO RESTORE POINT would rewind the entire CDB including REPORTING and is therefore the wrong tool. The guaranteed restore point is not automatically dropped; drop it after validation to release FRA space.
2. Why is a guaranteed restore point necessary here, and how does it affect FRA space? What would you do if the FRA cannot be expanded?
Read the worked answer
A normal restore point is only a label; its flashback logs can be aged out under the two-hour retention target. A guaranteed restore point forces Oracle to retain all flashback logs needed to reach it, which is why it survives the five-hour upgrade and the Monday discovery. The cost is FRA space: while the guaranteed restore point exists, flashback logs cannot be reclaimed even if the FRA fills. Monitor V$RESTORE_POINT, V$FLASHBACK_DATABASE_LOG, and V$RECOVERY_FILE_DEST. If the FRA cannot be expanded, the restore point may cause space pressure and failed flashback. You cannot safely drop the guaranteed restore point before the flashback, so add space or free non-flashback FRA files. After a successful flashback and validation, DROP RESTORE POINT before_upgrade; so normal retention can reclaim logs. Do not disable flashback database to solve space; that would destroy the recovery option.
3. How should the DBA handle the REPORTING datafile failure reported by DRA without compromising the APP flashback?
Read the worked answer
Treat the REPORTING datafile failure as a separate media-recovery task because the APP flashback only rewinds APP. Use DRA: LIST FAILURE; ADVISE FAILURE; REPAIR FAILURE; and review the generated script rather than executing it blindly. If datafile 9 is offline and missing, restore and recover that datafile from the REPORTING backup while the CDB is open: RESTORE DATAFILE 9; RECOVER DATAFILE 9; ALTER DATABASE DATAFILE 9 ONLINE;, using the appropriate PDB or root context. Do not run FLASHBACK DATABASE to fix the missing file; that would rewind all PDBs, including APP. DRA detects the missing file but does not fix logical data errors, so it is not a substitute for the APP flashback. Sequence the work so the APP flashback is validated first, or repair REPORTING first if the missing file blocks CDB operations; either way, keep the two recovery actions separate.